Text substitution and change cell background colour. Numbers & AppleScript

Question

I am working with Numbers spreadsheet files whose final output must be in German. The source of the data is the Apple Configurator app (via Automator), which produces everything in English despite the German regional settings applied to the user account... that's just a tiny bit of background.

I have found some AppleScript to help with this already.

tell application "Numbers"
    tell front document's active sheet
        tell (first table whose selection range's class is range)
            repeat with c in column "B"'s cells
                tell c to if its value contains "TRUE" then ¬
                    set its value to my SUBSTITUTE(its value, "TRUE", "RICHTIG")
            end repeat
        end tell
    end tell
end tell

to SUBSTITUTE(str, f, r)
     set text item delimiters to f
     set temp to str's text items
     set text item delimiters to r
     return temp as string
end SUBSTITUTE

This can be easily reapplied to deal with other words in the column that I am interested in.

Can somebody though help me with modifying this so that it can also change the background colour of the cell? So for example, if "TRUE" is in a given cell (in a particular column) it will get changed to "RICHTIG" and the background will then change to light green?

Likewise for "FALSE" to be changed to "FALSCH" and a light-red background colour.

I"ve tried already with "set its background colour to "green" for example, on a new line right after set its value to my SUBSTITUTE(its value, "TRUE", "RICHTIG") but it throws the error "A identifier can’t go after this identifier"

I shoudl also stress that the all the columns have their "data type" already set to "text"

Thanks.

WL

Answer 1

I offer this with the disclaimer that I am unable to test this, as I do not use Numbers. However, with the help of this page, I am proposing this code as an example of what might help you achieve your objective:

        repeat with c in cells of column "B"
            if value of c contains "TRUE" then
                set value of c to my SUBSTITUTE(value of c, "TRUE", "RICHTIG")
                set background color of c to {0,65535,0}
            else if value of c contains "FALSE" then
                set value of c to my SUBSTITUTE(value of c, "FALSE", "FALSCH")
                set background color of c to {65535,0,0}
            end if
        end repeat

Answer 2

Thanks for getting me about 95% of the way there.

The color scale that Pages/AppleScript uses actually works on to 0-65k scale. Thanks for the "Digital Colour Meter" software on my Mac helping me spot that one, where all permutations of {255,0,0} appear black! But you were still on the right track.

Here is the solution that got me over the line...

repeat with c in column "B"'s cells
                if value of c contains "TRUE" then
                    set value of c to my SUBSTITUTE(value of c, "TRUE", "RICHTIG")
                    set background color of c to {0, 65000, 0}
                else if value of c contains "FALSE" then
                    set value of c to my SUBSTITUTE(value of c, "FALSE", "FALSCH")
                    set background color of c to {65000, 0, 0}
                end if
end repeat