How to interact with specific sheets in numbers via AppleScript?

Question

astoller Author

Level 1

15 points

How to interact with specific sheets in numbers via AppleScript?

Environment: Mojave (10.14.5), Numbers 6.2 (6369), Automator 2.9 (444.42)

Full Disclosure: Posted same on apple.stackexchange.com , trying to reach wide audience to get answer...

Issue:

Trying to automate process:

1. Open specific document
2. Switch to specific sheet (sheet 2)
3. Manipulate contents of one (eventually many) cell on that sheet

Stuck on #2. Doc opens but never switches to second sheet and through debugging code (via display notification ...'s ) I can tell that it never gets past my various attempts to switch to that sheet (or get it's name, etc.)

The code has been modified many times in attempts to get this to work, but essentially:

on run {input, parameters}
  set msg to ""
  tell application "Numbers"
    activate
    try
      set msg to msg & " A"
      set the chosenDocumentFile to "/path/to/mydocument.numbers"
      open the chosenDocumentFile
    end try
    tell chosenDocumentFile
      try
        -- *** TBD - BULLET 2 ***
        -- (current attempt to get something to work)
        set msg to msg & " B1"
        repeat with s from 1 to (count of sheets)
          tell sheet s
            set msg to msg & " B1b"
            set n to get name
            set msg to msg & " [" & n & "]"
          end tell
          set msg to msg & " B1c"
        end repeat
        set msg to msg & " B2"
      end try
    end tell
    set msg to msg & " C"
  end tell
  set msg to msg & " D"
  display notification msg
  return input
end run

The displayed message is: A B1 B2 C D

Any help is much appreciated.

FWIW:

I've also looked at a number of threads here:

(removed links to get under the 5000 character limitation on posts - sigh)

- Numbers and Apple Script Help
- Loop through all sheets in Numbers
- Needs an applescript to navigate among sheets
  - This is apparently for an older version of MacOS / Numbers and indicates "It uses GUI scripting, which you need to enable in advance", which I don't fully understand and am hoping is not still needed
- AppleScript: Moving between sheets and obtaining name of current sheet
  - Similar caveat to above
- Numbers 3.5 Formula "name of sheet"
  - Looked somewhat promising, but didn't seem to help/work for me
- ¿How operate with cells of diferents seehts?

MacBook Air 13", macOS 10.14

Posted on Jul 9, 2019 4:08 PM

Reply

Answer 1

Top-ranking reply

astoller Author

Level 1

15 points

Jul 10, 2019 5:42 PM in response to SGIII

Okay - so I just have to know the number of the sheet (I can't reference it by name? in case I add or remove sheets?)

The cell reference appears to be problematic - the code right now looks (minus debugging lines) like this:

...

tell application "Numbers"

activate

...

tell chosenDocumentFile -- (set in above eclipses)

tell sheet 2

tell table 1

set value of cell "AK4" to "TEST"

end tell -- table 1

end tell -- sheet 2

end tell -- chosenDocumentFile

end tell -- application "Numbers"

...

I'm getting an error message in Automator saying:

Syntax Error

Can't make "AK4" into type integer.

I get the same error if I try "D51" (obviously the message changes to indicate that cell rather than "AK4")

Am I misunderstanding something here?

Reply

Answer 2

SGIII

Level 8

36,026 points

Jul 9, 2019 8:10 PM in response to astoller

Not sure exactly what you are trying to do but here is a simple script that places a 1 in cell "A1" of the first table on each sheet.

tell application "Numbers"
	tell front document
		repeat with i from 1 to count sheets
			tell sheet i
				set value of first table's cell "A1" to 1
			end tell
		end repeat
	end tell
end tell

The hierarchy is document-> sheet -> table -> cell. Sheets don't have cells. Tables do.

SG

Reply

Answer 3

SGIII

Level 8

36,026 points

Jul 10, 2019 7:41 PM in response to astoller

astoller wrote:

I just have to know the number of the sheet (I can't reference it by name? in case I add or remove sheets?)

You can reference a sheet by either by sheet number or name. That goes for tables and documents too (though with documents you need to include the .numbers extension in the name.). Here is a working example:

tell application "Numbers"
	tell document "MyDocumentName.numbers"
		tell sheet "MySheetName"
			tell table "MyTableName"
				if exists cell "AK4" then set value of cell "AK4" to "TEST"
			end tell -- table 1
		end tell -- sheet 2
	end tell -- chosenDocumentFile
end tell -- application "Numbers"

In general I find "wide" tables (beyond 10 columns or so) unwieldy in Numbers. You might consider restructuring your data and taking advantage the ability to add multiple, single-purpose tables on one sheet.

You may find these pages useful.

SG

Reply

Answer 4

astoller Author

Level 1

15 points

Jul 11, 2019 9:54 AM in response to astoller

The design morphed as I learned more about what I was doing. The code is now set to run as a Service and looks like this:

on run {input, parameters}
	tell application "Numbers"
		tell front document
			tell active sheet
				tell table 1
					set value of cell "P20" to "TEST3"
				end tell -- table 1
			end tell -- active sheet
		end tell -- front document
	end tell -- application "Numbers"
	return input
end run

The setting of the cell value is my next focus, I intend to have copy a formula from another cell, modify it, and then paste it into the currently selected cell(s).

I'm also modifying my spreadsheet to have distinct Sheets for each year of interest rather than adding columns to the existing table within a single Sheet (and thus no longer have "AK4").

Thanks SG - much appreciated.

Reply

Answer 5

SGIII

Level 8

36,026 points

Jul 9, 2019 9:38 PM in response to astoller

And to interact specifically with Sheet 2:

tell application "Numbers"
	tell front document
		tell sheet 2
			tell table 1
				set value of cell "A1" to 1
			end tell
		end tell
	end tell
end tell

SG

Reply

How to interact with specific sheets in numbers via AppleScript?

Similar questions