Hi, this is Gary with MacMost.com.
Let me show you how you can write script that will simulate a form in Mac Numbers.
MacMost is brought to you thanks to a great group of more than 500 supporters.
Go to MacMost.com/patreon.
There you can read more about it, join us, and get exclusive content.
So in Numbers for iPad you can create a form.
So when you have a database like this and you want to add more items to it.
More rows.
You can switch into Form mode and then enter these four pieces of data and it will add
a new row.
It's a really cool thing you can do on the iPad with Numbers.
But unfortunately you can't do it on the Mac.
However we can simulate it using a script.
So here I have a Numbers document and it has two sheets in it.
In Sheet 1 I've got a database which is basically just a table and each row is a record.
I can add to it by simply hitting return and entering more data here like that.
But what would be nice is to have a form to fill out instead.
Now for this simple example it's really not necessary.
For other types of things it may be.
On Sheet 2 here I've got a form and this is basically just two tables.
This first table here, I've just taken away all the borders and stuff and I've put in
just some labels.
The second table is just some blank cells.
You could format this anyway you want and change the fonts and the borders and all of
that.
I just wanted to have a table here that is basically four blank spots and I can enter
in data.
So if I enter in some data here, like that, then I would like to be able to just take
an action and then this data is added to this database.
I can do that.
I'm actually going to run it for you right now and you can see it very quickly added
that information there and cleared this out and got it ready for me to enter in another
entry.
So how did I do that?
Well, I used a script.
You can do a script in ScriptEditor or Automator.
I chose to use ScriptEditor for a few reasons which you'll see.
Here is the script.
So let's go through it.
Do note that this is Java Script not Apple Script.
You can switch between them here so make sure, if you're going to write this out or paste
it in, that you have selected JavaScript.
It's pretty simple.
There's not that many lines of code here.
At the beginning all I'm doing is setting some variables.
I set Numbers equal to the Application.
Then I get two tables from this document.
This script only works if this document's open.
Otherwise it's going to throw an error.
So the database variable I'm setting to Numbers.documents.sheets zero.
So the first sheet because they start counting at zero.
So sheets zero and then tables named database.
So you can see here that there's a title here database.
Then the second table is called form and I'm looking in the same document but sheet 1,
so the second sheet here and the table named Data Entry.
If I go here I don't see it but if I select the table, then I look on the side here for
Table Name.
I turn that on and you could see the name Data Entry.
I just don't want to see it there so I've hidden it.
So now I've got those and two variables, database and form.
Now I'm going to loop through the values here.
So I'm going to set an array to blank, nothing, and I'm going to use a for loop and loop starting
with zero, the first cell, going until end, the length of all of the rows here.
So it'll be four and increasing each time.
So just going through each of these.
Zero, 1, 2, and 3.
Then I get the value from that.
So form.rows i , so the row cells zero, so the first column, and then value.
Then I'm going to use push to put that onto the values array.
So it starts values start at nothing and then it's going to add four different values.
Then it's going to go and work on this table.
The first thing I'm going to do is create a new row.
You can do that using addRowBelow.
Then the way you would do that is you want to refer to that table and then a cell.
So in this case we'll do Column A. It doesn't really matter which column.
But then the rows.length.
So in other words there are eight rows here so this will actually be eight so A eight
addRowBelow.
So it's just going to basically add a row toward the bottom.
If I had done something like A four it would add a row after the fourth row.
I wanted to add one to the bottom.
Then I'm going to loop through all of the values that I put in that array and set the
value of each cell going across from each column to each of those values.
So it's going to get four values there and it's going to set it to be four across.
Then it's done its main task.
But what I want it to do is to then go back to this table here and I want it to set the
value of each of these cells to blank.
I'm going to do it a weird way.
I'm going to start at the end so the number of cells here, minus one, because it will
say there's four cells here in this column so I want to start at cell 3 and then go backwards,
that's why it's i minus minus instead of i plus plus.
So it's going to loop backwards so it's going to go 3, 2, 1, 0 and set them all to blank.
The reason I'm doing it backwards is because what happens is the last cell that's set is
then the one that is selected.
Here I can actually type the data in, run the script, and then type in starting right
away since this one would be selected.
So that's what we've got.
All I did before when I showed you it working was I hit the Run button and it ran.
So I could just leave this script open here and hit the Play button every time I wanted
to record some data.
But what I'm going to do instead is Save it out to that I can access it by the Menu Bar.
Notice here I've got a little script icon in the Menu Bar.
I can put that there by going in ScriptEditor to ScripEditor Preferences and checking Show
Script Menu in Menu Bar.
Once that's there you'll see I've got Open Scripts Folder and I've can open ScriptEditor,
Scripts Folder, User Scripts folder or Computer Scripts Folder.
This would be for all the users on the machine.
I want to do the Users Scripts Folder just for me.
My user here.
That will open up this Scripts Folder here which you could see is in the Library.
Then I've got an Applications Folder there, if not you can create one.
Under Applications you can create a new folder.
So let's create a new folder here.
I'm going to name it the same exact name as the App.
So I'm going to call it Numbers.
I'm going to make sure it's in the Applications folder there.
So in Applications I've got Numbers and Safari.
The scripts stored in Numbers will only be available if I'm running Numbers which is
what I want here.
So what I'm going to do here is Save As and I'm going to save it here.
So let me drag this into the Save dialogue.
I'm saving it there.
Submit Form.
So now you can see the Scripts folder under Applications.
Under Numbers I have Submit Form.
So I can close that and I can close the script here.
If I look here in this menu but first making sure I'm running Numbers, I'll see Submit
Form.
It says Numbers Scripts.
If I'm running something else, like say I'm going to switch to Finder, you won't see that
there.
You have to be running Numbers.
So there is Submit Form.
So it's a Menu choice now.
I can even create a keyboard shortcut for that.
So now I can go in and enter more data.
Now I can go there, Submit Form, and it will first ask me for permission.
It's only going to do this the very first time.
You can see it did it!
It added that data there.
So you can use this script as a starting point for doing more complex things.
For instance, you could do things like making sure that numbers are numbers or they are
between certain ranges.
You could make sure that strings are a certain length and throw an error up there if it isn't
right instead of inserting the data into the new row.
Now the one thing this doesn't do it doesn't allow you to edit the data.
But you could use more complex scripts that do.
For instance, you could have a form that actually pulls data from a row from the database and
puts it in the form.
Then it allows you to update that row.
It would require having a bunch more extra cells and probably several different scripts
that you could use to trigger different things like moving from row to row and updating the
data from the form back
to the database.