Can Apple Script find/replace for conditions in formulas within cells?

Thank you, SG, for your encouragement and sample script, and for your web link for lots more examples.

See below where I've actually pasted ChatGPT (4.5)'s suggestion for how to move through a table with 400+ cells, looking to replace one element of the formula.

JAS

Thank you, SG, for your interest.

Here's a screenshot of part of the data table:

and here's the right hand side of the table (with other columns of interest):

Sorry it's so small--it's a big data table. But it's interesting that Chat had to work out what do with my (naively) using characters like "$" and "@" in my data. All I want is an updated formula that looks for T rather than Pl in the Author column. Chat worried about deeper troubles.

JAS

Thank you both, SG and Camelot, for your interest. Here's Chat's attempt to fix just a single formula in one cell:

tell application "Numbers"

activate

tell document "Correlation scratch sheet.numbers"

tell sheet "All Septenarii Terence"

tell table "Table 3"

tell cell 3 of row 2

try

set formula to "COUNTIFS(Total 5@, \"=5\", clause 0, \"=B0-D12*\", Author, \"=T\")"

log "✅ Formula successfully assigned"

on error errMsg number errNum

log "❌ Error setting formula: " & errMsg & " (Error " & errNum & ")"

end try

end tell

end tell

end tell

end tell

end tell

and here is the error message that was logged in the script editor:

tell application "Numbers"

activate

set formula of cell 3 of row 2 of table "Table 3" of sheet "All Septenarii Terence" of document "Correlation scratch sheet.numbers" to "COUNTIFS(Total 5@, \"=5\", clause 0, \"=B0-D12*\", Author, \"=T\")"

--> error number -10003 from formula of cell to any

(*❌ Error setting formula: Numbers got an error: Can’t set formula of cell to "COUNTIFS(Total 5@, \"=5\", clause 0, \"=B0-D12*\", Author, \"=T\")". (Error -10006)*)

end tell

So I tried to run your script and am stuck at the same syntax error message for the one cell it attempted to replace:

The good syntax looks like this:

COUNTIFS(Data Table::$Total 5@,"=5",Data Table::$clause 0,"=B0-D12*",Data Table::$Author,"=Pl")

and appears like this:

the substitution that gets the syntax error message looks like this:

COUNTIFS(Data Table::$Total 5@,"=5",Data Table::$clause 0,"=B0-D12*",Data Table::$Author,"=T")

Thank you, Camelot. But the find/replace script that I downloaded from SGIII is also generating the same syntax error code (with no = before the COUNTIFS).

Let's see if SG understand why the find/replace isn't working.

JAS

So, running SG's shortcut, here's the cell before I run the shortcut:

And here's the cell after I run the shortcut:

and the cell contains this:

Isn't there some magical command to get Numbers to re-evaluate the table?

and, thank you, everybody, for sticking with me on this. It's an essential problem I'd love to master, but at some point, when you've all become exasperated, I'll just manually enter T in 400 cells.

JAS

I'm studying Roman play scripts of two comic playwrights, Plautus (Pl) and Terence (T). And my work analyzes the interplay of measures of rhythm and segments of syntax. My syntax annotation shows clauses starting from a ^ symbol and ending with a $ symbol. I also use the @ symbol to mark accent beats. And when I first made these choices in my text mark-up, I didn't think through the trouble I might be causing myself by using regular expression characters in ordinary text strings. But little did I know when I was making these choices that one day I'd have AI to backslash all my characters so that $ is just a $ in formula syntax.

This is not to say that Tyrtaeus (Tyrt.) isn't fully worthy of scholarly attention for his Spartan marching beat. Just not my attention.

Thanks again.

JAS

To be clear: that was the syntax error message I got when trying to use SG's shortcut. (i.e. the shortcut didn't place an = symbol at the start of the formula)