Creating a Form In Mac Numbers Using a Script

Creating a Form In Mac Numbers Using a Script


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.

6 thoughts on “Creating a Form In Mac Numbers Using a Script

  1. Hi thank you so much for all your videos very helpful.
    I have a question and I’m hopeful you can help me with it.
    I’m currently using the formula countif to give me a result corresponding to what I need ( contif A1,>O) so I aways get the results of 1 when the number on that cell is greater then 0)
    I would like to add a second function there but can’t seam to figure out how to go about it.
    I need the results of the formula to be 1 for any number great then 0 and -1 if the the number is less the .
    Example if cell A1 is ( 2 ) the result is 1, if cell A1 is (-2) the result is -1.
    Hope it makes sense.
    Thank you in advance 🙏🙏🙏🙏

Leave a Reply

Your email address will not be published. Required fields are marked *