So, the problem: two tables in one sheet, one table with data, the other table to collect information from the data via lots of different COUNTIFS formulae in 400+ cells. Each of the COUNTIFS ends with the test-value of a column (in the data table), and a condition, "=Pl". (i.e. find the text string Pl). Works great.
Now I have duplicated that sheet, renamed the sheet with a new title, and in this new sheet, I'm trying to find/replace every instance of the condition "=Pl" to the new condition "=T" (i.e. find every instance of T instead of Pl)
I asked ChatGPT to do the scripting for me (which I could never have figured out on my own). And Chat got pretty close, without being able to jump the final hurdle of getting Numbers to understand the test-value locations in the data table.
So the correct answer (offering just one cell as an example) is supposed to look like this:
But what Chat's AppleScript produced (and caused an error message in every cell where the replacement happened) this:
COUNTIFS(Data Table::$Total 5@,"=5",Data Table::$clause 0,"=B0-D12*",Data Table::$Author,"=T")
The formula is letter perfect, but Numbers can't read the addresses in that formula (i.e. can't recognize the columns where it should look)
And, because you asked, I'm sharing Chat's script that moved through 400 cells and replaced every instance of Pl with a T:
tell application "Numbers"
activate
tell document "Correlation scratch sheet.numbers"
tell sheet "All Septenarii Terence"
tell table "Table 3"
set rowCount to row count
set columnCount to column count
repeat with r from 1 to rowCount
repeat with c from 1 to columnCount
tell cell c of row r
try
set cellFormula to formula
if cellFormula contains "Pl" then
log "Row " & r & ", Col " & c
log "Original: " & cellFormula
-- Replace "Pl" with "T"
set updatedFormula to my replaceText("Pl", "T", cellFormula)
-- Remove quotes around table names (restore to unquoted)
set updatedFormula to my unquoteTableNames(updatedFormula)
-- Unescape any internal quotes (remove \")
set finalFormula to my replaceText("\\\"", "\"", updatedFormula)
log "Final: " & finalFormula
-- Set the final cleaned formula
set value to finalFormula
end if
end try
end tell
end repeat
end repeat
end tell
end tell
end tell
end tell
-- Replace text utility
on replaceText(findText, replaceWith, sourceText)
set AppleScript's text item delimiters to findText
set textItems to every text item of sourceText
set AppleScript's text item delimiters to replaceWith
set newText to textItems as string
set AppleScript's text item delimiters to ""
return newText
end replaceText
-- Convert 'Data Table' → Data Table (remove quotes from table name)
on unquoteTableNames(formulaText)
set cleanedFormula to formulaText
set referenceList to {"'Data Table'::$CI", "'Data Table'::$clause 0", "'Data Table'::$clause 2", "'Data Table'::$Author", "'Data Table'::$Total 5@"}
repeat with refItem in referenceList
set unquotedRef to my unquoteTableOnly(refItem as text)
set cleanedFormula to my replaceText(refItem, unquotedRef, cleanedFormula)
end repeat
return cleanedFormula
end unquoteTableNames
-- Convert 'Table'::$Column → Table::$Column
on unquoteTableOnly(rawRef)
if rawRef contains "'::$" then
set AppleScript's text item delimiters to "'::$"
set parts to every text item of rawRef
set tableName to item 1 of parts
set columnName to item 2 of parts
set AppleScript's text item delimiters to ""
return tableName & "::$" & columnName
else
return rawRef
end if
end unquoteTableOnly
