AppleScript for conditional formatting, selection and deleting of rows

You can try something like this:

tell application "Numbers"
	tell front document
		tell sheet 1 -- "Tab" 1
			tell table 1
				set background color of cells of column "D" whose value is "konto" to "red"
				set background color of cells of column "D" whose value is "budget" to "green"
				--set format of column "E" to pop up menu
				set format of cells 2 thru -1 of column "E" to pop up menu -- use this if have header row
			end tell
		end tell
		tell sheet 2 -- "Tab" 2
			tell table 1
				--delete (rows 1 thru -2) -- leaves just last row
				delete (rows 2 thru -2) -- leaves header row and last row
			end tell
		end tell
	end tell
end tell

Notes:

You need to have the document open and frontmost.

You have to address (tell) the document, sheet (=tab), and table.

The script changes the actual background color of cells in Column "D". It is not possible to set a Conditional Formatting rule from AppleScript. The resulting visual effect is the similar, however.

You can use column 4 instead of column "D" if you want.

SG

jklarsen wrote:

I have noticed that if I select column "B" where all cells have content, If I just choose "pop up menu" then it fills in the content of the selected cells all at once.

Anyway to select column "B" and then just choose/active the menu item "pop up" using AppleScript, because that would solve everything.

Hi Jan,

I may not be following what you have and what you want, but if you already have values in the cells in Column B and you just want to convert the cells in that column to Pop Up Menu with the menu items automatically populated (so that's ready for use as you add new rows to the table) then you don't need to gui script. You can just do something like this:

tell application "Numbers"
	tell front document
		tell sheet 1
			tell table 1
				set rangeName to "B2:B" & address of last row
				tell range rangeName to set its format to pop up menu
			end tell
		end tell
	end tell
end tell

Before:

After:

If you do not already have values in B then it would be easier and more reliable to have the script add some values in the top rows of the column and then convert the column (actually the range of body cells) to Pop Up Menu as above than it would be to try to gui script through the interface.

SG

Well, if you are okay with GUI scripting (accessibility features), the code below is an example I found that makes a three-item popup in cell A3. Some work is required to make it a useful script. It is for Mac OS only. I suppose it will continue to work as long as Apple makes no changes to that part of the interface.

I'm hoping one day Numbers will allow a pop up to refer to a range of cells rather than be a hard-coded list of items.

set menuItems to {"Joe Bloggs", "Molly Mouse", "Peter Pan"}

tell application "Numbers"
	activate
	tell the first table of the active sheet of document 1
		tell cell "A3"
			-- Set the cell value to the first menu item required. This also selects the cell.
			set value to item 1 of menuItems
			-- THEN set the cell format. This causes the menu to adopt the value just set as a single item instead of having the three default items.
			set the format to pop up menu
		end tell
	end tell
end tell

tell application "System Events"
	tell application process "Numbers"
		set frontmost to true
		tell window 1
			-- The inspector doesn't have to be visible, but its "Cell" pane does have to be selected. (?)
			click radio button "Cell" of radio group 1
			-- Adds the remaining items by clicking the "+" button and inputting the texts the required number of times.
			repeat with i from 2 to (count menuItems)
				click button 1 of group 1 of scroll area -1
				keystroke (item i of menuItems)
			end repeat
		end tell
	end tell
end tell

Are you sure you are referenced the correct columns?

In your original post you mention column 5 ("E") but the error message mentions column "G".

Are you using the exact same same syntax to set background color?

set background color of cells of column "D" whose value is "konto" to "red"

The message seems to indicate you left out "cells of".

Perhaps you could post your script.

SG

jklarsen wrote:

Anyway to select and gather all content of the cells in column G and assign that to all the cells?

AppleScript can set the format of cells to pop up menu but I haven't found a way to have it set values in the pop up menu.

You can set up one pop up menu cell the way you like and manually copy-paste that into other cells. It's possible to automate that a little with gui-scripting but qui-scripting tends to get clunky. It's easier to just do it manually.

SG

Hi Jan,

I think you're very close. Try the names of the colors exactly as in my example, i.e. "red" not "Red" and "blue" not "Blue" , etc.

SG