There doesn’t seem to be any way to use Applescript to set the “Alternating Row Color” option, so I’m wondering if there’s a way to replicate this using a script since I have to do this for many files. Alternatively, can this be done in Automator or some other macro program, that it would activate that feature on all open spreadsheets?
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
set rowCount to count rows
repeat with x from 1 to rowCount by 2
try
set background color of row x to {52427, 65535, 26214}
set background color of row (x + 1) to {59149, 6, 3483}
end try -- quick get-out for tables with odd-numbered rows
end repeat
end tell
That works, but my rows are now red and yellow. How do I get them gray and white?
Also, it’s quite involved when it comes to very large files. Is there any way to use GUI scripting to just turn on the option in the format panel?
Set the background colour of a single cell (eg C8) to the grey you want. Run this:
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
background color of cell "C8"
end tell
and insert the result (eg {52319, 52319, 52319} as the first colour in my first script.
Set the colour of the single cell to white (not “No Fill”), get its colour by re-running the script above, and use it to replace the second colour in my first script.
Run my first script again.
It’s possible. But GUI scripting’s a method of last resort and the locations and identities of the items you want to affect can vary with OS version, application version, document state, and an individual user’s Preference settings. The following works on my machine:
-- Assuming that the table concerned is table 1 of sheet 1 of the document,
-- select a cell in it to ensure that the relevant format panel exists.
tell application "Numbers"
activate
tell table 1 of sheet 1 of document 1
set selection range to range "A1:A1"
end tell
end tell
tell application "System Events"
tell application process "Numbers"
set its frontmost to true
tell window 1
-- Select the relevant tab in the panel.
click radio button "Table" of radio group 1
-- A short delay may be needed here to allow
-- time for the switch, but not on my machine.
tell scroll area 4
-- The following specifier should cope with both British and American spellings.
tell (get first checkbox whose name begins with "Alternating Row Col")
if (its value = 0) then click
end tell
end tell
end tell
end tell
end tell
Hopefully that’ll be the default set up in the document …
Hi, thanks so much. So when I run it, I get this error:
System Events got an error: Can’t get radio button “Table” of radio group 1 of window 1 of application process “Numbers”.
I have the one spreadsheet open and the table panel showing. I wonder if the issue is that “Table” is not a radio button but a tab?
After playing around a bit, I finally have a script that actually works for me.
Besides your very helpful start, the trick was to use Automator > Watch Me, record what you need done, then take the steps in the list and drag down where it creates an Applescript script for you. Then inspect the elements from there and piece together a working script.
tell application "System Events"
tell application process "Numbers"
set its frontmost to true
tell window 1
click radio button 1 of radio group 1
if value of checkbox "Alternating Row Color" of scroll area 3 = 0 then
click checkbox "Alternating Row Color" of scroll area 3
end if
end tell
end tell
end tell
What I could use help with is an opening dialog that asks, “Do you want the Alternating Row Colors turned on or off” with On and Off (and I guess Cancel) buttons that would set the value of the If statement to either “0” or “1” depending on the choice, because right now all this does is turn it on. The companion script to turn it off replaces the “0” with “1”. This would avoid having to maintain two scripts to run depending on whether they should be turned on or off, and more importantly, can be run over a variety of files without regard to their current state.
Here is my final script for anyone who might benefit from using it. It gives the user the choice to turn on or off the Alternating Row Colors options.
tell application "System Events"
tell application process "Numbers"
set myChoice to button returned of (display dialog "Alternative Row Colors?" buttons {"Yes", "No", "Cancel"} default button 1)
if myChoice is "Yes" then
tell window 1
click radio button 1 of radio group 1
if value of checkbox "Alternating Row Color" of scroll area 3 = 0 then
click checkbox "Alternating Row Color" of scroll area 3
end if
end tell
else
tell window 1
click radio button 1 of radio group 1
if value of checkbox "Alternating Row Color" of scroll area 3 = 1 then
click checkbox "Alternating Row Color" of scroll area 3
end if
end tell
end if
end tell
end tell