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