Hi,
I need to easily see the cells in a Numbers spreadsheet that I have updated in the last 10 minutes. I have put this applescript together with Chat GPT and some basic knowledge. I have triggered it in Automator and it runs and brings the correct spreadsheet to the front but the cells don’t change colour. I am wondering if anyone is able to help me see what I am doing wrong?
After 10 minutes I need the cells to turn back to not having any background fill.
My Applescript skills are very basic and so I would appreciate any help?
on run {input, parameters}
tell application “Numbers”
activate
try
set theDocument to document “Placemats.numbers”
tell theDocument
set editedCells to {}
repeat with t in every table of every sheet
repeat with r in every row of t
repeat with c in every cell of r
if modification date of c is greater than ((current date) - (10 * minutes)) then
set end of editedCells to c
end if
end repeat
end repeat
end repeat
repeat with editedCell in editedCells
set background color of editedCell to {65535, 0, 0} – Red color
end repeat
end tell
end try
end tell
return input
end run
You haven’t defined the minutes variable.
Which I’m guessing should be 60 (seconds).
Are you sure cells have a modification date
Also if in a previous run it did set the background colour you need to undo it.
So you should test if a cell has a background colour. Or add it to a unEditedCells list. Then clear the background on the unEditedCells
As Technomorph has noted, cells in a Numbers spreadsheet do not appear to have a date property. So, I can’t envision how the ChatGPT script could be made to work.
I wrote a script that highlights changes in the frontmost Numbers spreadsheet, but it requires that the user manually set the points from which and to which changes are shown. Also, I only tested this script on a small spreadsheet, and it might be unusably slow with a large spreadsheet.
This script creates a plist file in the user’s preferences folder. With a small spreadsheet, the size of this file was 1 KB.
use framework "Foundation"
use scripting additions
on main()
set theSelection to choose from list {"Save Values", "Highlight Changes", "Remove Highlighting"} default items "Save Values"
if result is false then error number -128
set {theCells, theValues} to getCellsAndValues()
if (item 1 of theSelection) is "Save Values" then
writePlist("spreadsheetCells", theCells)
writePlist("spreadsheetValues", theValues)
else if (item 1 of theSelection) is "Highlight Changes" then
highlightChanges(theCells, theValues)
else
removeHighlighting()
end if
end main
on getCellsAndValues()
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set theCells to name of every cell
set theValues to value of every cell
end tell
repeat with anItem in theValues -- replaces missing value with ""
if contents of anItem is missing value then set contents of anItem to ""
end repeat
return {theCells, theValues}
end getCellsAndValues
on highlightChanges(newCells, newValues)
set oldCells to readPlist("spreadsheetCells") as list -- saved cells
set oldValues to readPlist("spreadsheetValues") as list -- saved values
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set selectionRange to selection range
repeat with i from 1 to (count newCells)
if (item i of newCells) is not in oldCells then -- new cells
set background color of cell (item i of newCells) to {65535, 65535, 0} -- yellow color
else if (item i of newValues) is not (item i of oldValues) then -- existing cell with changed value
set background color of cell (item i of newCells) to {65535, 65535, 0} -- yellow color
end if
end repeat
set selection range to selectionRange -- restore selection to original location
end tell
end highlightChanges
on removeHighlighting()
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set selectionRange to selection range
set background color of every cell to {65535, 65535, 65535} -- white color
set selection range to selectionRange
end tell
end removeHighlighting
on readPlist(theKey)
set theDefaults to current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.peavine.HighlightNumbersCells"
return theDefaults's objectForKey:theKey
end readPlist
on writePlist(theKey, theValue)
set theDefaults to current application's NSUserDefaults's alloc()'s initWithSuiteName:"com.peavine.HighlightNumbersCells"
theDefaults's setObject:theValue forKey:theKey
end writePlist
main()
Thank you for taking the time to reply and for your advice.
Since getting the script I have found out that cells indeed don’t have a modification date and that what I was trying to do was nigh on impossible!
Thank you so much for investing your time into this and for solving my problem.
It works perfectly for what I need and I only run it on a small spreadsheet of ever changing numbers.
You are incredibly generous with your time and your efforts are very much appreciated.
1 Like