Using Javascript for Automation to copy cells in Numbers

Question

I want to use JXA to automate some updating of Numbers spreadsheets. For example, copying a range of cells from one spreadsheet to another one with a different structure.

At this point, I'm just testing a simple program to set or read the value of a cell and I can't get this to work.

When I try to set a value I get "Error -1700: Can't convert types." and when I try to read a value I get back a [object ObjectSpecifier] rather than a text or number value.

Here's an example of the code:

Numbers = Application('Numbers')
Numbers.activate()
delay(1)
doc = Numbers.open(Path('/Users/username/Desktop/Test.numbers'))
currentSheet = doc.Sheets[0]
currentTable = currentSheet.Tables[0]
console.log(currentTable['name'])
console.log(currentTable.cell[1][1])
currentTable.cell[1][1].set(77)

When I run this, I get and output of [object ObjectSpecifier] for the two console.logs and then an error -1700: Can't convert types when it tries to set a cell.

I've tried several other variations of accessing or setting properties but can't get it to work.

Thanks in advance,

Dave

To troubleshoot the problem, write your code in AppleScript first, get it working there, and then figure out how to translate it to JXA. That way you'll know if the error is being caused by a mistake in your own code or a defect in JXA which, in addition to lousy error reporting, has a lot of accidental and deliberate design flaws as well. — foo

syntaxera syntaxera · Accepted Answer · 2015-06-18T23:03:23

Here is a script that sets and gets a cell's value and then sets a different cell's value in the same table:

// Open Numbers document (no activate or delay is needed)

var Numbers = Application("Numbers")

var path = Path("/path/to/spreadsheet.numbers")
var doc = Numbers.open(path)

// Access the first table of the first sheet of the document

// Note:
//  .sheets and .tables (lowercase plural) are used when accessing elements
//  .Sheet and .Table (capitalized singular) are used when creating new elements

var sheet = doc.sheets[0]
var table = sheet.tables[0]

// Access the cell named "A1"

var cell = table.cells["A1"]

// Set the cell's value

cell.value = 20

// Get the cell's value

var cellValue = cell.value()

// Set that value in a different cell

table.cells["B2"].value = cellValue

Check out the Numbers scripting dictionary (with JavaScript selected as the language) to see classes and their properties and elements. The elements section will show you the names of elements (e.g. the Document class contains sheets, the Sheet class contains tables, and so on). To open the scripting dictionary, in Script Editor's menu bar, choose Window > Library, and then select Numbers in the library window.

In regards to the logging you were seeing - I recommend using a function similar to this:

function prettyLog(object) {
    console.log(Automation.getDisplayString(object))
}

Automation.getDisplayString gives you a "pretty print" version of any object you pass to it. You can then use that for better diagnostic logging.

Using Javascript for Automation to copy cells in Numbers

1 Answers