Setting the value of a Numbers cell using AppleScript

applescriptnumbers

I'm trying to write a numbers script (to do this, as it happens), in which I need to change the value of a cell.

However, if I do something like

tell application "Numbers"
    activate
    tell document 1
        tell sheet 3
            set the value of cell "E1" to 1000
        end tell
    end tell
end tell

then I get the error

 error "Numbers got an error: Can’t set cell \"E1\" of sheet 3 of document 1 to 1000." number -10006 from cell "E1" of sheet 3 of document 1

but this contains no information about why it can't set that cell. The document, the sheet and the cell all exist.

I guess this might depend on my particular spreadsheet, but is this the correct way to set the value of a cell, and if so, what else can I do to understand why it's not working?

Best Answer

I'm in front of a Mac again and gave your code a go and also got the same error, so it's probably somewhat misleading in terms of what it actually means.

However, I did play around with your script a bit and was able to get the code to work by using the following code:

tell application "Numbers"
  tell table 1 of sheet 3 of document 1
    set the value of cell 1 of column "E" to 1000
  end tell
end tell

However, now that I've got this to work, I just wanted to flag you seem to be hard coding the cell etc. That is, if the position changes then the script isn't going to be dynamic. Whether this is an issue for you or not will depend on what you're trying to do and how your Numbers spreadsheet is designed.

Related Solutions

Getting the First Cell NOT Containing Certain Text in Numbers

As it turns out, I've figured it out. It did require Applescript. What I did was loop through the rows and selected the values in the row where "not" was not present in column B. Here's the Applescript I used via Automator:

on run {input, parameters}

    tell application "Numbers"
        tell table "Calculator 1" of sheet 2 of document of front window
            set aRange to "A2:A22"
            set bRange to "B2:B22"
            set aRows to the rows of range aRange
            set bRows to the rows of range bRange

            set correctRow to -1
            set correctCol to -1

            repeat with i from 1 to count of bRows
                set aCell to "A" & (i + 1)
                set bCell to "B" & (i + 1)

                if value of (cell bCell) is not "not" then
                    set correctRow to value of (cell aCell)
                    set correctCol to value of (cell bCell)
                end if
            end repeat

            set the value of (cell "C2") to correctRow
            set the value of (cell "C4") to correctCol
        end tell

        display dialog "Updated!"
    end tell

    return input
end run

Keep in mind that the cells I used are specific to my spreadsheet, but generally this method could be applied for any 2D MATCH() function.

Create a table has the name of the each row in each row of the header column, and use the desired MATCH() function per row in this way (see the picture of the table in my question).
Use this Applescript to loop through the MATCH() functions and find the correct row and column. Change "C2" to whichever cell will be your Correct Row and "C4" to whichever cell will be your Correct Column.
Use the INDEX() function to get the value of the cell and put it into another cell, using the coordinates the Applescript calculated for you.

How to get the current sheet name into the cell in Numbers.app

REGEX.EXTRACT(REFERENCE.NAME(;2);"[^:]+";;) returns the sheet name of the current sheet

Best Answer

Related Solutions

Getting the First Cell NOT Containing Certain Text in Numbers

How to get the current sheet name into the cell in Numbers.app

Related Question