Automating Numbers to create a cell border

I’ve been automating numbers and the one thing I am stuck on is cell borders. I’ve found some suggestions which appear to be for older versions of Numbers and where I am at is the following script:


tell application "Numbers"
	activate
	tell document 1 to tell active sheet to tell table 1
		set selection range to range "D13:AG13"
	end tell
end tell

tell application "System Events" to tell process "Numbers"
	if menu item "Show Inspector" of menu 1 of menu bar item "View" of menu bar 1 exists then
		keystroke "i" using {option down, command down} -- “Show Inspector” menu command 
	end if
	tell window 1 -- inspector window 
		click radio button "Cell" of radio group 1
		tell scroll area 4
			tell button 3 of list 1
				set {xPosition, yPosition} to position
				set {xSize, ySize} to size
			end tell
			click at {xPosition + (xSize div 2), yPosition + (ySize div 2)}
			
			select button 3 of list 1
			click button 3 of list 1
			
			click menu button "Border Styles"
			click menu item "1 pt" of menu 1 of menu button "Border Styles"
		end tell
	end tell
end tell

What is not working is the “select button 3 of list 1” which fails to click on the all borders selection of the 3x3 grid that selects different border types. I can see ‘Border’ box cell getting selected, but it doesn’t change colour when clicked by AppleScript in the way that a real mouse click does.

Feels so close to what I want!

Try to extract the coordinates of the button then trigger it using Cliclick available at : https://www.bluem.net/en/projects/cliclick/

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) samedi 13 octobre 2018 12:04:54

Hi masaccio, Have you found a way to select all borders in automator or via script editing. Please do let me know. I am stuck on the same problem where automator is unable to select all borders from 3 x 3 grid table.

Please help. On the verge of my solution except this problem.


-- Enhanced by Yvan KOENIG (VALLAURIS, France) samedi 6 juin 2020 15:16:52
-- use the free CLI cliclick

property variante : true
-- true --> predefine the index of wanted style
-- false --> try to select the style by its name, see comments below

tell application "Numbers"
	activate
	set view_loc to localized string "View" from table "TSApplication"
	set showInspector_loc to localized string "Show Inspector" from table "TSApplication"
	set cell_loc to localized string "Cell" from table "TSApplication"
	set allBorders_loc to localized string "All borders" from table "TSApplication"
	if not variante then
		set borderStyles_loc to localized string "Border Styles" from table "TSApplication"
		set digitPt_loc to localized string "%@ pt" from table "TSText"
		set onePt_loc to my remplace(digitPt_loc, "%@", "1")
	end if
	tell document 1 to tell active sheet to tell table 1
		set selection range to range "C3:E6" --"D13:AG13"
	end tell
end tell

tell application "System Events" to tell process "Numbers"
	set frontmost to true
	-- The original test was wrong !!!!
	tell menu bar item view_loc of menu bar 1
		click it
		-- set theNames to name of menu items of menu 1 --> {"Masquer la barre d’onglets", "Afficher tous les onglets", missing value, "Inspecteur", missing value, "Afficher les règles", "Guides", missing value, "Commentaires", missing value, "Masquer les activités de la collaboration", missing value, "Afficher les outils de disposition", "Afficher les couleurs", "Afficher les outils d’ajustement de l’image", "Afficher le navigateur multimédia", missing value, "Agrandir/réduire", missing value, "Afficher les avertissements", missing value, "Activer le mode plein écran", missing value, "Masquer la barre d’outils", "Personnaliser la barre d’outils…"}
		set inspector_loc to 4 -- the localized string is not reachable in many languages under 10.13.6
		tell menu item inspector_loc of menu 1
			click it
			-- name of menu items of menu 1 --> {"Formater", "Organiser", missing value, "Afficher l’onglet suivant de l’inspecteur", "Afficher l’onglet précédent de l’inspecteur", missing value, "Afficher l’inspecteur" or "Masquer l’inspecteur"}
			if menu item showInspector_loc of menu 1 exists then
				keystroke "i" using {option down, command down} -- “Show Inspector” menu command 
				-- click menu item showInspector_loc of menu 1
			end if
		end tell
	end tell
	
	tell window 1 -- document window 
		-- its properties
		-- class of UI elements--> {scroll area, button, scroll area, button, button, scroll area, menu button, button, button, radio group, UI element, button, button, static text, scroll area, button, button, button, menu button, toolbar, tab, static text}
		tell radio group 1
			-- class of UI elements --> {radio button, radio button, radio button, radio button}
			-- name of radio buttons --> {"Tableau", "Cellule", "Texte", "Disposition"}
			click radio button cell_loc
		end tell -- radio group 1
		tell scroll area 4
			-- click once in the scroll area to put the target in it
			set {x, y} to its position
			tell me to do shell script "/usr/local/bin/cliclick c:" & x + 2 & "," & y + 2
			-- class of UI elements --> {pop up button, static text, UI element, static text, button, pop up button, static text, image, list, color well, text field, incrementor, pop up button, menu button, button, scroll bar}
			tell list 1
				-- class of UI elements--> {button, button, button, button, button, button, button, button, button}
				set theHelps to help of every button
				repeat with indx from 1 to count theHelps
					if (item indx of theHelps as string) is allBorders_loc then
						tell button indx
							set {x, y} to its position
							set {w, h} to its size
						end tell
						-- this button doesn't respond to standard actions so use cliClick
						tell me to do shell script "/usr/local/bin/cliclick c:" & x + (w div 2) & "," & y + (h div 2)
						exit repeat
					end if
				end repeat
			end tell -- list 1
			-- name of menu buttons --> {"Styles de bordure"}
			if variante then
				set borderStyles_loc to 1 -- so we my drop localization code
			end if
			tell menu button borderStyles_loc
				click it
				if not variante then
					set menuItems to name of menu items of menu 1 --> {"Récents", "0,25 pt", missing value, "Styles sélectionnés", "0,25 pt", missing value, "Styles de tableau", "3 pt", "1 pt", "1 pt", "3 pt", "3 pt", "3 pt", "3 pt", missing value, "Style par défaut", "Pas de bordure"}
					-- Two menu items share the name "1 pt"
					-- We may scan the list of menu items skipping the 5 first ones
					-- but such code can't select the 2nd  item "1 pt"
					-- and it would be worse for item "3 pt"
					repeat with indx from 6 to count menuItems
						if item indx of menuItems as string is onePt_loc then
							click menu item indx of menu 1
							exit repeat
						end if
					end repeat
				else
					-- My choice is to trigger the menu item directly by its index
					-- 8 --> "3 pt" gray
					-- 9 --> "1 pt" solid
					-- 10 --> "1 pt" dots
					-- 11 --> "3 pt" black
					-- 12 --> "3 pt" red
					-- 13 --> "3 pt" green
					-- 14 --> "3 pt" white
					set indx to 12 -- edit to fit your needs
					click menu item indx of menu 1
				end if
			end tell -- menu button borderStyles_loc
		end tell -- scroll area 4
	end tell -- window 1
end tell -- System Events

#=====
(*
replaces every occurences of d1 by d2 in the text t
*)
-- not used if variante is true
on remplace(t, d1, d2)
	local oTIDs, l
	set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d1}
	set l to text items of t
	set AppleScript's text item delimiters to d2
	set t to l as text
	set AppleScript's text item delimiters to oTIDs
	return t
end remplace

#=====

As always, numerous instructions used to build the code are kept disabled.

Download Cliclick from www.bluem.net
and install it as : “/usr/local/bin/cliclick”
By default, the folder “Macintosh HD:usr:” is invisible.
but you may open it with :

set p2usr to POSIX file "/usr/"
tell application "Finder" to open p2usr

If I remember well, a subfolder named “local” exists
but you will have to create its subfolder “bin” to get the hierarchy:
“Macintosh HR:usr:local:bin:” where you will store “cliclick”.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) samedi 6 juin 2020 15:21:18

No need terrible scripts to select border style from 3x3 grid table in the Numbers. You need only 1 simple thing to click those buttons - set focused to true:


-- Select the range
tell application "Numbers"
	activate
	tell document 1 to tell active sheet to tell table 1
		set selection range to range "A3:C6"
	end tell
end tell

-- Show Inspector
tell application "System Events"
	if menu item "Show Inspector" of menu 1 of menu item "Inspector" of menu 1 of menu bar item "View" of menu bar 1 of application process "Numbers" exists then keystroke "i" using {option down, command down} -- “Show Inspector” menu command 
end tell

-- Set border style
tell application "System Events" to tell application process "Numbers"
	tell window 1 -- inspector window
		click radio button "Cell" of radio group 1
		tell scroll area 4
			tell button 3 of list 1
				set focused to true -- focus the button from 3x3 grid
				click it
			end tell
			click menu button "Border Styles"
			click menu item "1 pt" of menu 1 of menu button "Border Styles"
		end tell
	end tell
end tell