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: 5 Responses to “Creating a Form In Mac Numbers Using a Script”

    Ian MacGregor
    3 weeks 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”?

    3 weeks 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
    2 weeks 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
    2 weeks 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.

    2 weeks 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.

Leave a New Comment Related to "Creating a Form In Mac Numbers Using a Script"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)