Creating a Form In Mac Numbers Using a Script

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.



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 = "";
}

Comments: 7 Responses to “Creating a Form In Mac Numbers Using a Script”

    Ian MacGregor
    5 months ago

    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”?

    5 months ago

    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.

    John Clark
    5 months ago

    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.

    PJ
    4 months ago

    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.

    4 months ago

    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.

    Jon Evans
    3 months ago

    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?

    3 months ago

    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.

Comments Closed.