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

To set a formula, use:

set value to "=theFormula"

You need the =.

In your most recent short example you haven't specified a table name and those columns aren't in the same table as the formula so Numbers doesn't where to look.

Have you tried the shortcut I posted?

SG

There should be no = when you view in the formula editor.

But you need the = in the AppleScript to tell Numbers you mean a formula instead of a Text value. After the script is run you won't see the =.

SG

Yeah, I did note that it shouldn't be an issue for the script where you extract the current formula text, modify it, and write it back, since that should include the leading =.

Just for grins, does it make a difference if you include the '='? I'm guessing not, but sometimes troubleshooting like this takes iteration.

The error code itself is odd - -10006 is usually a 'permission denied' error message (or sometimes 'write permission denied'. Is there anything unusual about this table? it's not locked?

Now that I think about it, all the references, quotes, =, etc. are all moot - if the formula isn't valid, Numbers should still enter it and just give a Syntax Error warning. Therefore I think the problem does lie more with the sheet, or the table and not with the function/formula itself.

Interesting - Numbers does recognize the cell contents as a formula - both from the error message you get, plus the fact that it shows the formula editor... if the contents was just recognized as text then it would appear in-cell.

Very strange...

It looks valid. The script seems to work as expected if I run on my machine (with dummy data). Yet somehow it throws an error on your sheet.... curious...

My guess would be the Header Names setting since that changes how Numbers interprets cell references. However, since the original script extracted the existing formula/cell references, they should have been in the form that Numbers prefers, so setting it back shouldn't have been a problem.

Also, I have that setting enabled, and the script worked for me...

So, I'm stuck between "happy you found a solution" and "wanting to know more" :)

anotherjas wrote:

SUCCESS!!

Back to research on the metrics of ancient poetry!

Glad to hear the scripting works, though why it didn't work before and works now may have to remain a mystery.

I was wondering about your project. Curious when I learned it had to do with ancient poetry, I decided to ask AI what the Pl and T likely stand for, telling it I thought the poetry might be Greek. It told me that in classical studies abbreviations for authors are often standardized from the Latinized names. Its guess: Pl stands for Plato, who wrote poetry in addition to his philosophizing, and T stands for Tyrtaeus. How did it do?

SG

Ah, the machine tells me Roman comic poets. I threw it off by telling it I guessed they were Greek.

The need to "escape" special characters (typically ") with \ is an AppleScript thing, not related to regular expressions, which AppleScript does not handle natively as does, say, Javascript for Automation (JXA).

Getting the right escaping can be tedious.

SG

I think you need to show what ChatGPT came up with.

As BadUnit shows, it's entirely possible to set a cell formula via AppleScript, which means there's something up with the script you're using.