How to interact with a Numbers sheet via AppleScript?

Question

This question is related to:

• SUMIFS with comparisons to relative cells

where I'm trying to figure out a way to do what I want using AppleScript.

I've been searching the Web to find a way to set the active sheet in Numbers before I attempt to make a modification there. The preliminary steps are essentially:

tell application "Numbers"
    activate
    try
        set the chosenDocumentFile to "/path/to/myfile.numbers"
        open the chosenDocumentFile
    on error errorMessage number errorNumber
        if errorNumber is not -128 then
            display alert errorNumber message errorMessage
        end if
    end try
    tell chosenDocumentFile
        try
            -- this is where I'm stuck
        end try
    end tell
end tell

Most of the stuff I've found online talks about creating or deleting sheets, but I haven't found anything that provides a clear example of how to select a sheet.

In my case I currently have 5 sheets in my spreadsheet. I know I want to select the second sheet to act on (sheet 2 - right?)

I've tried:

open the sheet 2

open sheet 2

select sheet 2

activate sheet 2

None of them seem to work, and all of them prevent the next line of code within that enclosing try statement from being executed.

I'm hoping there's some really simple answer for this (I still have lots to do before I get the code to do everything I want it to, but I'm stuck here right now).

Answer 1

Belatedly…

set active sheet of document 1 to sheet 2 of document 1

Basically, you have to specify which document for each sheet reference.

Of course, if you include a document tell block, then you can avoid that:

tell document 1 of application "Numbers"
set active sheet to sheet 2


end tell

Answer 2

As far as I know, Numbers does not have an explicit AppleScript command to select a given sheet.

There is the active sheet property of the document, e.g.,

tell application "Numbers" to get active sheet of front document

which will return the active sheet; however, while one might think that e.g.,

tell application "Numbers" to set active sheet of front document to sheet 2

assuming there is a sheet 2, would set e.g., sheet 2 to the active sheet, it does not set it as the active sheet.

Here is the method I use the makes a given sheet the active sheet in the front document:

Note: This is a hack method and it does cause a saved document to show as edited, even though it's not altering the value of the target cell. It works by setting the value of cell "A1" to it's current value, so while it's perceived as an edit, no data has been altered from its current value. However, it does work for me in Numbers 5.1 in macOS High Sierra, and is why I use it.

my selectSheet(2)

on selectSheet(n)
    try
        tell application "Numbers"
            activate
            set value of first cell of first table of sheet n of front document to ¬
                (value of first cell of first table of sheet n of front document)
        end tell
        --  # Press escape key to remove focus from cell A1.
        tell application "System Events" to key code 53
    on error eStr number eNum
        display dialog eStr & " number " & eNum buttons {"OK"} ¬
            default button 1 with icon caution
        return
    end try
end selectSheet

---

Note: The example AppleScript code is just that and, sans any included error handling, it does not contain any other error handling as may be appropriate. The onus is upon the user to add any error handling as may be appropriate, needed or wanted. Have a look at the try statement and error statement in the AppleScript Language Guide. See also, Working with Errors.