One advantage that Numbers for iPad has over Numbers for Mac is Form mode. If you want to simulate a form in Mac Numbers, you can do it using a few lines of JavaScript code in Script Editor and even make those script available in the menu bar. This could be the starting point for a more complex set of scripts that check values and even allow you to edit row data in a form too.
You can also watch this video at YouTube.
Watch more videos about related subjects: JavaScript (14 videos), Numbers (200 videos).
You can also watch this video at YouTube.
Watch more videos about related subjects: JavaScript (14 videos), Numbers (200 videos).
Want to know more about how to use Numbers on your Mac?Video Transcript
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.
Here is the script. Remember to switch your Script Editor language from AppleScript to JavaScript.
// get the two tables involved
var Numbers = Application("Numbers");
var database = Numbers.documents[0].sheets[0].tables['Database'];
var form = Numbers.documents[0].sheets[1].tables['Data Entry'];
// get all of the values from the form
var values = [];
for(var i=0;i<form.rows.length;i++) {
var value = form.rows[i].cells[0].value();
values.push(value);
}
// create a new row at the bottom of the database
var newRow = database.cells['A'+database.rows.length].addRowBelow();
// put the values into that row
for(var i=0;i<values.length;i++) {
newRow.cells[i].value = values[i];
}
// clear out the form, going backwards so to end on the first cell
for(var i=form.rows.length-1;i>=0;i--) {
form.rows[i].cells[0].value = "";
}
Thank you, Gary, this is a wonderful tutorial - Numbers is awesome. But, why doesn't Apple add this functionality to Numbers? I remember using OpenOffice Calc and you can create a form in a spreadsheet and use it to add records to a spreadsheet. Speaking of which, where is Apple's native database app? We have Numbers, Keynote and Pages but no "Base"?
Ian: I don't know why Apple doesn't add this to Numbers. I guess there are thousands of things they could add, and they do add things, but they can't add them all. I don't know if there is enough demand for Apple to add a database app. That's especially true as web services are mostly used for things like that now.
Ian,
Apple previously had a great simple flat database application for Macs and iOS. Users could synchronize the database's between the two platforms. It required separate purchases for each (MacOS and iOS) was called Bento and Apple executives decided to kill it. They now want people to pay for the much more expensive product "FileMaker".
I keep hoping for a replacement for Bento. I used it for personal property inventory tracking. It was rather robust.
Gary. An idea for a future episode. You will know that Excel contains a number of database functions (DSUM, DMAX). Maybe talk about what techniques (if any) exist within Numbers to perform similar tasks.
PJ: From what I can tell, DSUM is a very old Excel function that is somewhat replaced by SUMIF and SUMIFS, which work the same way in Numbers. So in most (all?) cases you would just use those new functions in both Excel and Numbers. DMAX, I assume, would be like a MAXIF function which doesn't exist. But you can find the maximum in a column that meets a criteria easily enough by adding another column, using IF to duplicate the value in the original column only if the criteria is met, and then using MAX or MAXA to find the maximum value in that column.
Hi Gary
I'm trying to use your script with a table of my own making which is one column wide and 13 rows long instead of your 4. When I run the script I'm getting this error "Error on line 8: Error: Can't get object." and "Error -1728: Can't get object." in the results section. Line 8 is "for(var i=0;i<form.rows.length;i++) {". I've checked everything I can think of but still no success. Any ideas?
Jon: If the error is "Can't get object" then the variable form isn't right. Or, form.rows isn't right. Those are the only references objects on that line. So the problem is happening earlier. Try recreating exactly what I did first, and then altering it to fit your own needs.