Finding Italicised words in Numbers (mac software)

[consolecmnd]

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.

[Nigel_Garvey]

Hi @consolecmnd. Welcome to MacScripter.

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

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

[Nigel_Garvey]

@Nigel_Garvey

The RTF text is then “pasted” into a TextEdit document

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

[consolecmnd]

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

---

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

[Piyomaru]

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

[Nigel_Garvey]

@consolecmnd

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

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!

[Nigel_Garvey]

@Piyomaru

I think it could be written more simply.

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.

[Piyomaru]

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.

[consolecmnd]

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.

[Nigel_Garvey]

@consolecmnd

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.

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

[consolecmnd]

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

[consolecmnd]

I’ve been trying to update the script for a while and I think I’ve hit the limitations of what AS can do in this admittedly niche use case.

For example:

1. I receive .xls files using the Calibre font. They need to be updated to a Mac system font for the italics to be found. I can do that manually and it works by select all and update the font, but when done in AS, it strips the formatting (italic, bold, etc)
   1a. I tried like this (AppleScript Font - Apple Community), so of course the italic text will become Arial and not Arial Italic
   tell application "Numbers" to set font name of thisTable's cell range to "Arial"

2. I find highlighting the cell isn’t enough since I work with large blocks of text per cell. I would like the italicised word to also be highlighted, but due to how Numbers treats cells as Text Properties, it doesn’t seem possible as discussed here: Change text colour of specific words in N… - Apple Community
   2a. I tried like
   tell application "Numbers" to set background color of word to {0, 0, 65535}

3. Finally, more a want than need, I can scope to the cell range, so I’d want to remove all rows and cells not within the range. I get files with hundreds of blank rows/columns that require clean up before running the script, so automating their removal would save a lot of time.

Am I correct in thinking this is outside the capability of Apple Script?

[Mockman]

Hi Nigel,

Apologies for bringing this up well after the fact but I cannot get this script to work. I’m running on Sierra so that is likely the cause of the problem.

When I attempt to compile the script (either in Debugger 7 or Script Editor), I get an error on the following line and the colon after attribute is highlighted:

set wordFont to (attrString’s attribute:(“NSFont”) atIndex:(i) effectiveRange:(missing value))

I looked at some apple docs which I can’t say that I understand but saw that some of the associated code used the plural attributes. I added an ‘s’ and the script then compiled.

However, when attempting to run it on a numbers document (v5.1), I get this error below:

-[NSConcreteAttributedString attributes:atIndex:effectiveRange:]: unrecognized selector sent to instance 0x610000222a60 (-10000)

Any obvious suggestions?

[Mockman]

Re point 1

As you have seen, the application is capable of changing the typeface without obliterating any font changes.

You might consider using ui scripting to change the typeface. It’s obviously clunky but seems to work.

tell application "Numbers"
	set t1 to table 1 of sheet 1 of document 1
	tell t1
		set selection range to cell range
	end tell
end tell

tell application "System Events"
	tell process "Numbers"
		
		-- The Format inspector should be open and on Text		
		set pub1 to pop up button 1 of scroll area 4 of window 1
		
		perform action "AXPress" of pub1
		delay 0.1
		pick menu item "Test Calibre" of menu 1 of pub1
		
	end tell
end tell

Regarding point 3, it is difficult to offer any suggestions without having an idea of how your spreadsheet is laid out.

[Nigel_Garvey]

@Mockman

When I attempt to compile the script (either in Debugger 7 or Script Editor), I get an error on the following line and the colon after attribute is highlighted:

set wordFont to (attrString’s attribute:(“NSFont”) atIndex:(i) effectiveRange:(missing value))

Hi Mockman.

That’s a puzzle. According to the documentation I have, attribute:atIndex:effectiveRange: has existed since macOS 10.0, so it should be OK in Sierra. The only thing I can think of that might stop the line actually compiling is if you have something on your computer that’s providing attribute as a keyword. (I don’t remember when macOS stopped supporting third-party OSAXen.) Does it make any difference if you put vertical bars around it?

set wordFont to (attrString's |attribute|:("NSFont") atIndex:(i) effectiveRange:(missing value))

… or maybe comment out use scripting additions?

I looked at some apple docs which I can’t say that I understand but saw that some of the associated code used the plural attributes. I added an ‘s’ and the script then compiled.

However, when attempting to run it on a numbers document (v5.1), I get this error below:

-[NSConcreteAttributedString attributes:atIndex:effectiveRange:]: unrecognized selector sent to instance 0x610000222a60 (-10000)

The plural attributes method is actually attributesAtIndex:effectiveRange: (attributesAtIndex all one word) and it returns a dictionary containing all the available attributes at the given index. So the attribute required would have to be specified when examining the dictionary rather than when getting it:

-- (Not actually tested in context)
set allAttributes to attrString's attributesAtIndex:(i) effectiveRange:(missing value)
set wordFont to allAttributes's objectForKey:("NSFont")

[Mockman]

@Nigel_Garvey

Does it make any difference if you put vertical bars around it

Yes, it works when this is done.

You’d mentioned some time previously that bars around a satimage term would make a difference (with ‘NSUrl’, I think) but I wasn’t sure with this as attribute is a system events/processes term in straight applescript and a seemingly generic term so I was wondering exactly what I’d put them around. As a curiosity, when I put them around the plural attributes, it just removed them upon compiling.

Re: plural method… that makes sense when you explain it but I would not have figured it out on my own.

[edit]
Oh… I do have satimage installed and Sierra does support 3rd party osaxen, but a search for attribute within its dictionary turns up nothing. I don’t believe I have any other osax installed. There is a latenightsw post by Mark Aldritt that covers dealing with satimage in Mojave so maybe that’s the release in question.

[ionah]

Hi all,

I think I’ve found a solution using NSTextStorage's attributeRuns.

use framework "Foundation"
use framework "AppKit"
use scripting additions

tell application "Numbers"
	activate
	
	-- get used range
	set theTable to table 1 of sheet 1 of document 1
	set theCells to cells of theTable whose value ≠ missing value
	set theRange to "A2:" & name of item -1 of theCells
	set selection range of theTable to range theRange of theTable
	
	-- copy the selected cells
	tell application "System Events" to tell process "Numbers"
		click menu item "Copier" of menu 1 of menu bar item "Édition" of menu bar 1
	end tell
	
end tell

-- get attributed string from clipboard
delay 0.2
set thePasteBoard to current application's NSPasteboard's generalPasteboard()
set attString to thePasteBoard's readObjectsForClasses:{current application's NSAttributedString} options:(missing value)
if attString = missing value then return "try to increase delay"
set attString to attString's lastObject()'s mutableCopy()
set attString to attString's attributedSubstringFromRange:{1, (attString's |length|()) - 1}

-- get attribute runs
set textStorage to (current application's NSTextStorage's alloc()'s initWithAttributedString:attString)
set attRuns to textStorage's attributeRuns()

-- make italic textes highlighted
attString's beginEditing()
repeat with aRun in attRuns
	set backColor to (current application's NSDictionary's dictionaryWithObject:(current application's NSColor's colorWithRed:(255 / 255) green:(218 / 255) blue:(27 / 255) alpha:1) forKey:(current application's NSBackgroundColorAttributeName))
	set theStyledString to aRun
	set theRange to aRun's range()
	set isItalic to ((aRun's fontName() as string) contains "italic")
	if isItalic then (attString's addAttributes:backColor range:theRange)
end repeat
attString's endEditing()

-- upadate the clipboard
thePasteBoard's clearContents()
thePasteBoard's writeObjects:{attString}

-- paste the new content
tell application "Numbers"
	activate
	tell application "System Events" to tell process "Numbers"
		click menu item "Coller" of menu 1 of menu bar item "Édition" of menu bar 1
	end tell
end tell

It’s using this Numbers file:
test.zip (79.6 KB)

[consolecmnd]

Thanks so much to everyone for your efforts helping with this little script.

I was able to slightly modify @ionah script above to achieve what I think is the final form in highlighting italics, but all credit to @ionah for the big brain thinking.

The way it’s written greatly improves performance and doesn’t require any document clean up and prep before running.

I’ve added:

• additional highlight formatting for text color. This way the highlight remains consistent regardless of the original color
• turning on word-wrap in the end as my document would default to non-wrapped
• an additional check for ‘oblique’ as well as ‘italic’ as some fonts have either/or
• updated to be a droplet

Note to others:

• The Range is selected by the top/left most cell to the bottom/right most cell. Meaning, if your final column’s bottom most row isn’t as low as the rest, the column will fall outside the ‘range’ since it isn’t the most bottom/right cell.
  – To solve, simply add placeholder text to expand the range selection

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

on open droppedItems

	tell application "Numbers"
		activate
		
		-- get used range
		set theTable to table 1 of sheet 1 of document 1
		set theCells to cells of theTable whose value ≠ missing value
		-- The range is defined by the top left most cell and bottom right most cell.
		-- Columns may be missed if right most column doesn't have bottom most cell.
		set theRange to "A2:" & name of item -1 of theCells
		set selection range of theTable to range theRange of theTable
		
		-- copy the selected cells
		tell application "System Events" to tell process "Numbers"
			click menu item "Copy" of menu 1 of menu bar item "Edit" of menu bar 1
		end tell
		
	end tell
	
	-- get attributed string from clipboard
	delay 0.2
	set thePasteboard to current application's NSPasteboard's generalPasteboard()
	set attString to thePasteboard's readObjectsForClasses:{current application's NSAttributedString} options:(missing value)
	if attString = missing value then return "try to increase delay"
	set attString to attString's lastObject()'s mutableCopy()
	set attString to attString's attributedSubstringFromRange:{1, (attString's |length|()) - 1}
	
	-- get attribute runs
	set textStorage to (current application's NSTextStorage's alloc()'s initWithAttributedString:attString)
	set attRuns to textStorage's attributeRuns()
	
	-- make italic textes highlighted
	attString's beginEditing()
	repeat with aRun in attRuns
		-- define colors
		set backColor to (current application's NSDictionary's dictionaryWithObject:(current application's NSColor's colorWithRed:(255 / 255) green:(203 / 255) blue:(92 / 255) alpha:1) forKey:(current application's NSBackgroundColorAttributeName))
		set frontColor to (current application's NSDictionary's dictionaryWithObject:(current application's NSColor's colorWithRed:(245 / 255) green:(0 / 255) blue:(114 / 255) alpha:1) forKey:(current application's NSForegroundColorAttributeName))
		
		set theStyledString to aRun
		set theRange to aRun's range()
		set isItalic to ((aRun's fontName() as string) contains "oblique") or ((aRun's fontName() as string) contains "italic")
		if isItalic then (attString's addAttributes:backColor range:theRange)
		if isItalic then (attString's addAttributes:frontColor range:theRange)
	end repeat
	attString's endEditing()
	
	-- update the clipboard
	thePasteboard's clearContents()
	thePasteboard's writeObjects:{attString}
	
	-- paste the new content
	tell application "Numbers"
		activate
		tell application "System Events" to tell process "Numbers"
			click menu item "Paste" of menu 1 of menu bar item "Edit" of menu bar 1
		end tell
		
		-- Apply Word Wrap to selected cells
		tell application "System Events" to tell process "Numbers"
			click menu item "Wrap Text" of menu 1 of menu bar item "Table" of menu bar 1
		end tell
	end tell
end open