Tracing Dependent Cells in Apple Numbers Spreadsheets with AppleScript

I recently needed a way to find which spreadsheet cells, in Apple's desktop Numbers.app, reference a particular cell in their formulas. I would think this formula "trace dependents" feature would be needed often enough for Apple to implement it in their desktop spreadsheet software, as Microsoft does with Excel, but apparently not.

Apple does however provide a scripting interface, via AppleScript, for Numbers.app. Using a quick back and forth chat with ChatGPT, I was able to get an AppleScript solution working.

 

By the way, this is just one example of how I've been using ChatGPT in recent weeks. The new "reasoning" capabilities of LLMs, in my opinion, are a huge turning point. I am already finding lots of ways to save time and effort with OpenAI's reasoning models, and they're only going to get better over the coming year. This isn't an AI article, so I'll just leave it at that.

I used ChatGPT's "o1" and "o3-mini-high" models to create and refine the following script. To use it, paste it into Apple's "Script Editor.app" (in the .../Applications/Utilities/ folder). When a Numbers.app spreadsheet is open, press the "play" button and enter a cell name. The dependent cells will be listed.

-- Lists cells that reference the given cell in their formulas
--   written by ChatGPT o1 and o3-mini-high on 2025-03-16
-- Prompt the user for the cell reference to search for
display dialog "Enter cell reference to search for:" default answer "C7"
set userRef to text returned of result

-- Parse the user input into its column and row parts
set columnPart to ""
set rowPart to ""
set foundDigit to false
repeat with i from 1 to (length of userRef)
	set theChar to character i of userRef
	if theChar is in "0123456789" then
		set foundDigit to true
		set rowPart to rowPart & theChar
	else if foundDigit is false then
		set columnPart to columnPart & theChar
	else
		set rowPart to rowPart & theChar
	end if
end repeat

-- Build a regex that matches the reference only (optionally allowing a $ before the column and row),
-- and ensures it isn’t immediately followed by another digit.
set regexPattern to "(^|[^A-Za-z0-9])\\$?" & columnPart & "\\$?" & rowPart & "($|[^0-9])"

tell application "Numbers"
	set doc to front document
	set foundCells to {}
	
	repeat with s in sheets of doc
		repeat with t in tables of s
			set rowCount to (count of rows of t)
			set columnCount to (count of columns of t)
			repeat with colIndex from 1 to columnCount
				repeat with rowIndex from 1 to rowCount
					set theCell to cell rowIndex of column colIndex of t
					try
						set cellFormula to formula of theCell
						if cellFormula is not missing value then
							try
								-- Use grep with the regex to check for a valid reference match.
								set matchResult to do shell script "echo " & quoted form of cellFormula & " | grep -E " & quoted form of regexPattern
								set end of foundCells to (name of theCell) & " in table \"" & (name of t) & "\" of sheet \"" & (name of s) & "\""
							on error
								-- No match found; ignore this cell.
							end try
						end if
					end try
				end repeat
			end repeat
		end repeat
	end repeat
	
	if (count of foundCells) is 0 then
		return "No cells found referring to " & userRef & "."
	else
		set AppleScript's text item delimiters to return
		set output to foundCells as text
		set AppleScript's text item delimiters to ""
		return output
	end if
end tell