Finding Italicised words in Numbers (mac software)

Hi all! First time Apple Script user, first time poster.

What I’m trying to do is find the cells in Numbers which contain italicised words, then highlight the cell by changing the background colour.

I have made a droplet and was able to find certain words which trigger the background colour update condition, but not specific word formatting. Having trouble with the syntax.

Right now I have:

on open droppedItems
	repeat with anItem in droppedItems
		tell application "Numbers"
			open anItem
			delay 2
			tell the front document
				try
					-- Loop through each sheet in the document
					repeat with sheetIndex from 1 to count of sheets
						tell sheet sheetIndex
							-- Loop through each table in the sheet
							repeat with tableIndex from 1 to count of tables
								tell table tableIndex
									-- Loop through each cell in the table
									repeat with rowIndex from 1 to count of rows
										repeat with colIndex from 1 to count of columns
											try
												set cellValue to formatted value of cell colIndex of row rowIndex
												
												if cellValue is not missing value then
													set cellText to formatted value of cell colIndex of row rowIndex
													set cellFormat to formatted value of rich text of cellText
													-- display notification "cellText: " & cellText
													set fontStyle to font of word of cellFormat
													
													display notification "font: " & fontStyle
													
													repeat with i from 1 to count of paragraphs of cellText
														if (font of word i of cellFormat) contains "italic" then
															-- Change the background color to pink (RGB: 255, 192, 203)
															set background color of cell colIndex of row rowIndex to {65535, 49344, 51400}
															exit repeat
														end if
													end repeat
												end if
											on error errMsg
											end try
										end repeat
									end repeat
								end tell
							end repeat
						end tell
					end repeat
				on error errMsg
					display dialog "Error: " & errMsg
				end try
			end tell
		end tell
	end repeat
end open

I feel like the issue is in this area

set cellFormat to formatted value of rich text of cellText
set fontStyle to font of word of cellFormat

Then of course below with the repeat with i from 1 to count of paragraphs of cellText section as the syntax is similar, but the script doesn’t reach that far since the display notification "font: " & fontStyle doesn’t pop any notifications.

Thank in advance. Hope this is enough info to go off of.

Hi @consolecmnd. Welcome to MacScripter.

That’s not a bad attempt for a “first-timer”. :slightly_smiling_face:

I can’t see any way within Numbers itself to discover if a word other than the first in a cell is in italics. The cell’s font name value is probably that of the first character of the first word.

It’s possible to kludge together a Heath Robinson system that does what you want, but I don’t know if you’ll like it! The script below copies any cell value that’s text to the clipboard using scripted Command-c keystrokes. (The droplet has to be given permission for this in System Settings.) The RTF text is then “pasted” into a TextEdit document and examined there for italic words. If any are found, the background colour of the cell from which the words came is set to your pink. This is a clumsily slow system, so looping through objects in Numbers itself is kept to a minimum and the word checks for each cell finish as soon as there’s a hit.

-- Assumptions:
-- 1. TextEdit is set to open new documents as rich text documents.
-- 2. This droplet has been added to the applications allowed to control the computer
--     (System Settings-> Privacy & Security-> Accessibility).

on open droppedItems
	-- Open TextEdit with a new window, but don't necessarily bring it to the front.
	tell application "TextEdit"
		run
		make new document
	end tell
	-- Bring Numbers to the font.
	tell application "Numbers" to activate
	
	repeat with anItem in droppedItems
		-- Open this file and get the tables of each sheet of the resulting document as a list of lists.
		tell application "Numbers"
			set frontDoc to (open anItem)
			set tablesOfSheets to tables of sheets of frontDoc
		end tell
		
		-- Loop through each sheet-representing list in the list of lists.
		repeat with thisSheet in tablesOfSheets
			-- Loop through each table in this list.
			repeat with thisTable in thisSheet
				set thisTable to thisTable's contents
				-- Get the names and values of the cells in this sheet.
				tell application "Numbers" to set {cellNames, cellValues} to {name, value} of cells of thisTable's cell range
				-- Loop through the values.
				repeat with c from 1 to (count cellValues)
					-- If this value's text then …
					if (class of item c of cellValues is text) then
						-- Get the name of the correspondindg cell.
						set thisCellName to item c of cellNames
						-- Set the table's selection range to that cell.
						tell application "Numbers" to set thisTable's selection range to thisTable's range thisCellName
						-- Keystroke Command-"c" to copy the cell's text to the clipboard.
						tell application "System Events" to keystroke "c" using {command down}
						-- Wait half a sec to give it time to work. (Adjust if necessary.)
						delay 0.5
						tell application "TextEdit"
							-- Set the text of the TextEdit document to the clipboard's RTF content.
							-- I'm not sure this is supposed to work, but it does on my Ventura system!
							set text of front document to (the clipboard as «class RTF »)
							-- Get the font names of the first letters of the words in the document.
							set fontNames to font of words of text of front document
						end tell
						-- Loop through the font names.
						repeat with thisFontName in fontNames
							-- If any contain "Italic" then set the background colour of the NUmbers cell
							-- to pink. No need to check any further words from the cell.
							if (thisFontName contains "Italic") then
								tell application "Numbers" to set background color of cell thisCellName of thisTable to {65535, 49344, 51400}
								exit repeat
							end if
						end repeat
					end if
				end repeat
			end repeat
		end repeat
	end repeat
	
	-- tell application "TextEdit" to quit saving no
end open

OK. Now that I’ve had more time to remind myself how to do it, here’s a version which doesn’t need TextEdit but instead derives an NSAttributedString from the clipboard’s RTF contents (using ASObjC code) and parses the font info from that.

(*
	This droplet must added to the applications allowed to control the computer
	(System Settings-> Privacy & Security-> Accessibility).
*)

use AppleScript version "2.4"
use framework "Foundation"
use framework "AppKit"
use scripting additions

on open droppedItems
	set |⌘| to current application
	set thePasteboard to |⌘|'s class "NSPasteboard"'s generalPasteboard()
	set wordStartRegex to |⌘|'s class "NSRegularExpression"'s regularExpressionWithPattern:("\\b\\w") options:(0) |error|:(missing value)
	
	-- Bring Numbers to the font.
	tell application "Numbers" to activate
	
	repeat with anItem in droppedItems
		-- Open this file and get the tables of each sheet of the resulting document as a list of lists.
		tell application "Numbers"
			set frontDoc to (open anItem)
			set tablesOfSheets to tables of sheets of frontDoc
		end tell
		
		-- Loop through each sheet-representing list in the list of lists.
		repeat with thisSheet in tablesOfSheets
			-- Loop through each table in this list.
			repeat with thisTable in thisSheet
				set thisTable to thisTable's contents
				-- Get the names and values of the cells in this sheet.
				tell application "Numbers" to set {cellNames, cellValues} to {name, value} of cells of thisTable's cell range
				-- Loop through the values.
				repeat with c from 1 to (count cellValues)
					-- If this value's text then …
					if (class of item c of cellValues is text) then
						-- Get the name of the correspondindg cell.
						set thisCellName to item c of cellNames
						-- Set the table's selection range to that cell.
						tell application "Numbers" to set thisTable's selection range to thisTable's range thisCellName
						-- Keystroke Command-"c" to copy the cell's text to the clipboard.
						tell application "System Events" to keystroke "c" using {command down}
						-- Wait half a sec to give it time to work. (Adjust if necessary.)
						delay 0.5
						
						-- Derive an NSAttributedString from the RTF data on the clipboard.
						set RTFData to (thePasteboard's dataForType:("public.rtf"))
						set attrString to (|⌘|'s class "NSAttributedString"'s alloc()'s initWithRTF:(RTFData) documentAttributes:(missing value))
						-- Use the regex above to get the range of the initial letter of every "word" in the text.
						set regexMatches to (wordStartRegex's matchesInString:(attrString's |string|()) options:(0) range:({0, attrString's |length|()}))
						set wordStartRanges to (regexMatches's valueForKey:("range"))
						-- Loop through the ranges, getting the font attribute at each start location index in NSAttributedString.
						repeat with thisRange in wordStartRanges
							set i to (thisRange as record)'s location
							set wordFont to (attrString's attribute:("NSFont") atIndex:(i) effectiveRange:(missing value))
							if (wordFont's fontName()'s containsString:("Italic")) then
								tell application "Numbers" to set background color of cell thisCellName of thisTable to {65535, 49344, 51400}
								exit repeat
							end if
						end repeat
					end if
				end repeat
			end repeat
		end repeat
	end repeat
end open

Hi @Nigel_Garvey, you’re too kind, this is great!

I tried the first script this earlier in the day but hitting permission issues even though I’ve set up the system settings to let the script do its thing. But that’s old news, as you’ve got a second script posted.

Trying your second script, the error persists: (‘Italics’ is the name of the droplet)
"System Events got an error: italics is not allowed to send keystrokes. (1002)"

https://www.reddit.com/r/applescript/comments/z8vc3m/request_script_editor_keeps_losing_permission_to/

I don’t know if this is the reason, but it basically says:

The current issue is that throughout the day, we will get errors that [written program name] is “unable to send keystrokes” [Error 1002].

The issue has progressed to become worse, and so has the solution.

For OS x 13: (Editor note: ← Applies to me)

  • The solution is to trick the computer into letting it work.
  • First, remove “Script Editor” from Privacy & Security → Accessibility, then re-add “Script Editor”.
  • Next, run any script that is NOT the initial one to cause the error.
  • Now it should be fixed.

For OS x 12 and earlier:

  • Remove the program in question from Privacy & Security → Accessibility, re-add program, and it should work as desired.

Also worth noting that once one script is not allowed to send keystrokes, none of them will be allowed to send keystrokes until the above mentioned solution is implemented.

For context, I’m running:
macOS Ventura 13.6.3

Permissions

  • Privacy & Security / Automation / italics (has access to Numbers, System Events)
  • Privacy & Security / Accessibility / the following applications have control of the computer:
    – AppleScript Utility,
    – italics,
    – Numbers,
    – Script Editor,
    – System Events,
    – TextEdit

In an attempt to fix, I toggled all off, closed System Pref, opened back up and turned them all on. No change

Anyway, I appreciate your effort! It seems I need to coerce some settings to allow your script to properly run. One step forward, two steps back :smile:


EDIT:
Got the permissions sorted. Basically recreated another droplet with your script and the above enabled.

Also, good news! It works! Cells containing italicised words have a pink background.
A small tweak left is to update the script to stay within in the range of the table data.

Right now it runs for a long time going to the final column and row, but I think that’s something I can try sorting out. System is busy hitting ‘copy’ while script is running, so unable to do anything else in the interim.

This is amazing and will be very useful. Thanks again :slight_smile:

I think it could be written more simply.

tell application "Numbers"
	tell front document
		tell active sheet
			try
				set theTable to first table whose class of selection range is range
			on error
				return "" --No Selection
			end try
			
			tell theTable
				set selList to every cell of selection range --Get cells in selection
				repeat with i in selList
					set curF to font name of i
					if curF contains "italic" then
						set background color of i to {65535, 0, 0} --Red
					end if
				end repeat
			end tell
		end tell
	end tell
end tell

Hi @consolecmnd.

That’s great news. I had a few problems myself with not being allowed to send keystrokes while writing the scripts. I think that if the droplet’s subsequently edited, the system assumes it’s been tampered with and withdraws the permission. The only sure way I found to reset was, like you, to delete the droplet, causing it to disappear from the list in System Settings, save another copy, and enable that. HOWEVER, it’s vitally important to save the code somewhere else before deleting the droplet! I neglected to do this while writing the second script last night and had to restart from scratch! :rofl:

1 Like

Hi @Piyomaru.

A cell’s font name is the name of the font of the text’s first character. My more complex scripts above are written to catch italics in a cell with mixed fonts. They do make the reasonable assumption that if the first word character after a word boundary is italicised, the rest of the “word” is too. But the word doesn’t have to be the first in the cell.

Japanese typography doesn’t specify italics in such detail, but this was the first time I knew that such detailed decorations were done. For the first time in a long time, I feel the difference in culture.

This is a follow up for any future people looking at this.

My intention was to update @Nigel_Garvey’s script to ignore blank columns and rows, but testing code changes while authoring and giving permissions etc to each new droplet became a slog and I decided to delete the rows/columns by hand before running instead.

Another improvement I’m considering is changing the font to something guaranteed to work. The xls files I receive will sometimes use a font the script can’t account for, so changing it from the jump would be a good update. (eg. Georgia)

Also wanted to note something that stumped me as it might help others new to Apple Script.

Because we’re using System Events (I think), there’s a sensitive layer of permissions this script requires, so you can’t just “save” your file.

You need to save, then “export”, as exporting will include a “Code sign: Sign the Run Locally” option. Then once again add to the System Pref Accessibility list to test.

Makes things very slow to develop, but ultimately makes sense as a requirement.

I’ll append any version updates to this thread when and if they happen. :wave:

Hi consolecmnd.

The trick is not to save the script as a droplet until it’s doing what you want. Insert a line at the top which “opens” a list containing an alias or file specifier to some test document file and run the script during development in either Script Editor or Script Debugger, saving it as an ordinary script file in the meantime. When saving the droplet (application) version, you can either disable the added line or leave it in.

(*
	This droplet must added to the applications allowed to control the computer
	(System Settings-> Privacy & Security-> Accessibility).
*)

use AppleScript version "2.4"
use framework "Foundation"
use framework "AppKit"
use scripting additions

open {choose file} -- Added line for testing in a script editor..

on open droppedItems
	-- Script code omitted here for brevity.
end open
1 Like

Hot dang! That little tip is going to make this so much easier! Thanks again @Nigel_Garvey :smiley: