Numbers | JavaScript for Automation (JXA)

To work with data in a Numbers table, you have to get hold of this table. For that, you must work your way through the sheets of the currently open documents like so:

const app = Application("Numbers");
const table = app.documents[0].sheets[0].tables[0];

This gives you an Object Specifier for the first table in the first document’s first sheet (since in JavaScript, the first array index is zero). Instead of numbers, you can use the name of a document, sheet, or table:

const app = Application("Numbers");
const table = app.documents["MyDocument"].sheets["First Sheet"].tables[0];

“Name” is used in a loose sense here. It is the filename of the document (without extension), the name of the sheet as set in the GUI, and the title of a table. Using document names instead of numbers has the advantage that you do not have to figure out which document Numbers considers to be the first, second, etc.

To find out the names of objects, you can use simply console.log():

 const app = Application("Numbers");
  console.log(`Documents: ${app.documents.name().join('/')}`);
  app.documents().forEach(d => 
    console.log(`${d.name()}, sheets => ${d.sheets.name()}`));

This will list all currently open Numbers documents on the first line and on subsequent lines the sheets for each of these documents.

Once you have an Object Reference to a table, you can work with the cells in it. To get all values from a table as an Array, use table.cells.values(). The cells are returned in row-column order, i.e. first all cells of the first row, beginning with the left-most column, then all cells of the second row, etc.

Getting cell values#

To work with cell values, it is not particularly practical go retrieve all of them. Instead, you might want to work only with values in one row, one column, or a rectangular area of the table. These objects are called range in Numbers, and they are always attached to a table.

To get all the values of the first row of a table as an Array, use table.rows[0].cells.value(). Similarly, to get all values of a table’s second column, use table.columns[1].cells.value() or table.columns["B"].cells.value(). To obtain an arbitrary rectangular range of cell values, specify a range like this: table.ranges['A1:C5'].cells.value()

As you can see, rows, columns and ranges contain a collection of cells, and each cell contains a value.

The property value will always be set, and it will always contain the visible value of the cell. You will not know if a cell contains a formula by looking at its value alone. Instead, check its property formula: If it is defined, it will contain the text of the formula itself.

If you want to get all cells from a table that contain a formula, use something like this: table.cells().filter(c => c.formula()) That code returns an array of all cells containing a formula.

Defining ranges#

To define a range, you usually specify two diagonally opposite cell coordinates: table.ranges['A1:B3'] defines the cells “A1”, “A2”, “A3, “B1”, “B2, and “B3”. If you wanted to specify a complete column, you could use table.ranges['C'] and for a contiguous range of columns table.ranges['D:E'].

However, this approach does not work to create a range from one or more rows. That is, table.ranges['1'] will not specify a range consisting of the first row but of “A1” only. To get the first row in a range, use table.ranges['1:'] or table.ranges['1:1'].

Note that range definitions use Numbers’ cell addresses, i.e. the first row is “1”, and the first column is “A”. That’s different from JavaScript’s Array indices, which start at zero.

Setting single cell values#

Unsurprisingly, you’ll have to set the value property of a cell to change its content: table.rows[0].cells[0].value = 'Top left cell' will put the string „Top left cell“ into cell “A1”. If you want to put a formula into a cell, you have to follow the same approach: table.rows[0].cells[0].value = '=A2' will dynamically display the content of cell “A2” in cell “A1”.

Note that you cannot set the property formula on a cell, since it is read-only – you must put the formula literally into the value property.

Setting many cell values#

Setting a single value or a couple of them as described before works fine. But setting or changing a lot of them, for example in a loop, can take a long time.

But it is possible to speed things up with a workaround. If you want to change adjacent values in a column, you can copy them to the clipboard as a single, newline-separated string and then paste the clipboard back into the Numbers table. Before pasting, you have to set the selectionRange property on the table to the range you wish to modify:

const arrayOfValues = ["A","c","d e", "FFF"];
const ca = Application.currentApplication();
ca.includeStandardAdditions = true;
ca.setTheClipboardTo(arrayOfValues.join('\n').replace(/$/m,','));
app.activate();
table.selectionRange = table.ranges[`H1:H${arrayOfValues.length}`]; 
const se = Application("System Events");
se.keystroke("v", {using: ["command down"]});
delay(0.1);
table.columns["H"].cells[0].value = arrayOfValues[0];

Note that this is only a clumsy workaround and might require some fiddling around with the parameter of delay().

The code

sets the clipboard to the list of values, separated by newlines;
it then sets Number’s selection range to the range of cells that are to be changed. It’s important here to specify the start and end row numbers: If you don’t give an end row number, the clipboard content will be repeatedly inserted until the column is filled.
using “System Events”, it sends the key code for „Paste“, namely Cmd-V

Numbers handles the pasted data like a CSV file. Therefore, it considers the content of the clipboard to consist of tabular data, with a separator marking different columns. This separator can be a space, a comma, a semicolon, or a tab. If the values you want to paste contain any of these characters, Numbers will split them into two or more columns. And you don’t want that.

Appending a comma (or a space, a semicolon, or a tab) to the first value in the clipboard ensures that Numbers copies all values into the selected column without splitting them up. Which separator should be used depends on the kind of values you want to paste – comma works fine with strings that may contain spaces (and no commas). Finally, the script must remove the comma (or whatever was added to the first line of the clipboard) from the first cell in the column, after a short delay. This is necessary since the paste command takes some time to complete.

Although this approach is a lot faster than setting the values in a loop for large numbers of values, it will still cause a noticeable delay in Numbers when it is updating the table. And the appended pseudo-separator adds a level of complexity that shouldn’t be needed.

Creating new sheets and tables#

As shortly described in the chapter „Working with Apps“, you can’t create sheets or tables using the make method that is used in AppleScript. Instead, you call a method with the same name as the object you want to create, like this: const newTable = app.Table({name: "Shiny new table"}); and insert it into the sheet’s collection of tables afterward: app.documents[0].sheets[0].tables.push(newTable);

push seems to be the only method usable with tables and sheets. Therefore, it is not possible to add a table at an arbitrary position in the tables array.

Setting cell attributes#

To set the color, format, or other attributes of cells, you must define a range containing these cells first. The next script illustrates that by setting the background color of every odd-numbered row to a pinkish shade of red.

const firstCol = table.columns[0].name();
const lastCol = table.columns[newTable.columnCount()-1].name();
const pink = [65500, 32000, 32000];
table.rows().filter(r => r.address() % 2).forEach(r => {
  const formatRange = newTable.ranges[`${firstCol}${r.address()}:${lastCol}${r.address()}`];
  formatRange.backgroundColor = pink;
})

The first two lines determine the names of the first and last columns. Then the color is defined. Though the scripting dictionary doesn’t say anything about the definition of color, specifying its values as RGB values between 0 and 65535 seems to work.

Next, the code loops over all rows, weeding out the even-numbered ones (for which r.address() % 2 is 0). For each odd-numbered row, it builds a range from the first to the last column and sets its backgroundColor property to pink.

Similarly, you can set the format for cells in a range. However, only pre-defined formats are applicable – you can’t use user-defined formats. Other properties you can set on a range of cells include fontName, fontSize, alignment and textColor.