macOS Using Applescript to Color Cells in NUMBERS

[VideoBeagle]

I'm trying to take a list of Color Codes and spit out a chart with the colors actually visible.

I'm trying the script* in this post from the Apple Support Forums:

Change cell background color based on the… - Apple Community

discussions.apple.com

The script worked once on a sample.. but then not again (and it didn't work quite like I expected)

This is what my table (sample) looks like**:

When I ran it and it worked, once, it gave me this

Why the first cell that has no "#" which should be the trigger according to the script colored as well, I don't know...but as I said, it didn't work a second time

Can anyone help me figure this all out?


*This is the script, by Apple Forum user SGIII , I'm trying reposted for convience:

property hexChrs : "0123456789abcdef"

tell application "Numbers"
    tell front document
        tell active sheet
            tell (first table whose class of selection range is range)
                set outList to {}
                repeat with aCell in cells of (get selection range)
                    set v to value of aCell
                    if text 1 of v is "#" then set v to text 2 thru -1 of v
                    set hexColor to my hexColorToRGB(v)
                    set inverseColor to {}
                    repeat with i from 1 to 3
                        copy (65535 - (item i of hexColor)) to end of inverseColor
                    end repeat
                    if v is "808080" then set inverseColor to {65535, 65535, 65535} -- hack to adjust gray
                    set background color of aCell to hexColor
                    set text color of aCell to inverseColor
                end repeat
            end tell
        end tell
    end tell
end tell

return outList

on hexColorToRGB(h) -- convert
    if (count h) < 6 then my badColor(h)
    set rgbColor to {}
    try
        repeat with i from 1 to 6 by 2
            set end of rgbColor to ((my getHexVal(text i of h)) * 16 + (my getHexVal(text (i + 1) of h))) * 257
        end repeat
    end try
    if (count rgbColor) < 3 then my badColor(h)
    return rgbColor
end hexColorToRGB

on getHexVal(c)
    if c is not in hexChrs then error
    set text item delimiters to c
    return (count text item 1 of hexChrs)
end getHexVal

on badColor(n)
    display alert n & " is not a valid hex color" buttons {"OK"} cancel button "OK"
end badColor

**(I changed my table to match the look in the original post to simplify my understanding of the code.....this is the actual table:

 

[VideoBeagle]

I'm gonna use this as a log as I go thru solving this.....

using code from https://iworkautomation.com/numbers/cell.html I've made the following script which is coloring the cells red....
So I think I can use some of the if statement from the earlier script to do what I want.... We'll see

tell application "Numbers"
    activate
    tell document 1
        tell active sheet
            set thisTable to first table
            tell thisTable
                #set the width of every column to 36
                #set the height of every row to 24
                set alignment of every cell to center
                set vertical alignment of every cell to center
                repeat with i from 1 to the count of cells
                    set the value of cell i to (i as string)
                    set background color of cell i to {65535, 0, 0}
                end repeat
            end tell
        end tell
    end tell
end tell

 

[VideoBeagle]

ok, trying to hammer out how to make the color that gets set flexable.

Using the script below, I get the error:

error "Can’t make \"{255,255,0}\" into type color." number -1700 from "{255,255,0}" to color

using the test table of :

I'm getting the info from the cell right, so that's good. but it's the turning it into color info that's not working yet.
Trying to use "the value of cell i as color" didn't work either.. so it doesn't like the sytanx of the cell value.

tell application "Numbers"
    activate
    tell document 1
        tell active sheet
            set thisTable to first table
            tell thisTable
                #set the width of every column to 36
                #set the height of every row to 24
                set alignment of every cell to center
                set vertical alignment of every cell to center
                repeat with i from 1 to the count of cells
                    #set the value of cell i to (i as string)
                    set bgcolor to the value of cell i
                    set background color of cell i to value of cell
                    #set background color of cell i to bgcolor
                    #set background color of cell i to {65535, 0, 0}
                    return bgcolor
                end repeat
            end tell
        end tell
    end tell
end tell

 

[VideoBeagle]

Ok, a bunch of tries and fails and some googling and I see that there doesn't seem to be a way to take the combined RGB field from a cell (ie, a string that is "{255,255,255}") and use that as a color...
But I can take 3 seperate variables for R, G, and B, and turn them into a color....
Now, my actual data that I've been building the samples from has that info, so it's an issue of figuring out how to make this run row by row as opposed to cell by cell.

 

[chown33]

In your first post, you say "it didn't work a second time". Please explain what happened or what you observed that leads you to this conclusion.

I'm asking because if you don't change any of the cell values, then running the script a 2nd or 3rd time will simply tell every cell to set the colors that the cell already has. In other words, no colors will change, because every cell is already the color that its value represents.

If you manually change some cell colors, say to black text on white, then run the script, you should see colors change.

If there's something else that happens which leads you conclude it didn't work, we need to know what that is. If there's an error message, please post it. If there's something else, then describe it.


When I run the script in post #1 after a minor change, it works every time I run it.

If I change the value in a cell, then the next time it runs, the cell's color changes. And if I don't change a cell's value, then the next time it runs, the cell's color is unchanged, because its value is unchanged.


The change I made to the script was to change this line:
repeat with aCell in cells of (get selection range)

to this:
repeat with aCell in cells of selection range

This may be a peculiarity of the version of AppleScript, Numbers, or MacOS I happen to be using on the computer under test. It might help if you identified your OS and Numbers versions.


Posts #3 and #4 present a very different issue. They amount to "How do I parse an AppleScript list given as a text string into an actual AppleScript list of numbers?". AppleScript itself is pretty difficult to write parsers with, so writing such a parser will not be a simple task.

Maybe if you explained what you're trying to accomplish, i.e. your overall goal, someone can offer suggestions on how to achieve that goal. This may not involve Numbers or AppleScript, but may involve other programming languages, apps, or methods.

 

[VideoBeagle]

In your first post, you say "it didn't work a second time". Please explain what happened or what you observed that leads you to this conclusion.

I'm asking because if you don't change any of the cell values, then running the script a 2nd or 3rd time will simply tell every cell to set the colors that the cell already has. In other words, no colors will change, because every cell is already the color that its value represents.

I undid the changes made and tried again, and it would often only change the first cell.

ANYHOW, as the day went on (and this thread) I stopped changing my data to fit the script, and started figuring out a script to fit my data.

My script is now well on the way to doing what I want, which is largely to set Background color based on the R, G, B values in my rows.

To turn:

Into:

The script below does it as I expected it to.

The next step is to get it to ignore the rows with no data in it. (It current gives me errors for blank rows rather than just skipping it).

error "Numbers got an error: Can’t make {missing value, missing value, missing value} into type color." number -1700 from {missing value, missing value, missing value} to color

something that breaks the loop..or not break, but jumps to the next iteration should do it....just need to find it...

EDIT: Figured it out, a simple IF/THEN fixed it, so now the Working Script is

tell application "Numbers"
    activate
    tell document 1
        tell active sheet
            set thisTable to first table
            tell thisTable
                #set the width of every column to 36
                #set the height of every row to 24
                set alignment of every cell to center
                set vertical alignment of every cell to center
                repeat with i from 2 to the count of rows
                    set ColorName to value of first cell of row i
                    set RVariable to value of second cell of row i
                    set GVariable to value of third cell of row i
                    set BVariable to value of fourth cell of row i
                    set BGColor to {RVariable, GVariable, BVariable}
                    if BGColor is not {missing value, missing value, missing value} then
                        set the background color of fifth cell of row i to BGColor
                    end if
                    
                    log i
                    log BGColor
                    
                end repeat
            end tell
        end tell
    end tell
end tell

 

[chown33]

I briefly looked for an AppleScript control statement that advances to the next iteration of the loop, and didn't find one. The equivalent in C is 'continue', which has a very different meaning in AS.

It's possible to stop a loop entirely (C equivalent: 'break') using the 'exit' keyword.

One way to stop a conversion on an error is with a try / on error construct. Any error in the try section, such as attempting to use an empty value as a number, will transfer execution to the on error block.

Control Statements Reference

Defines the AppleScript scripting language. Includes many brief sample scripts.

developer.apple.com

The body of the on error block can be empty. In other words, there are no commands or other statements between the on error and end.

 

[VideoBeagle]

I briefly looked for an AppleScript control statement that advances to the next iteration of the loop, and didn't find one. The equivalent in C is 'continue', which has a very different meaning in AS.

Well, in the past decade or more, you've helped me whenever I've dabbled in AppleScript. Googling turned nothing up for me, and if you couldn't find anything, that seals it.
For this case (as I edited the post above) a simple IF/THEN took care of it for me.

EDIT: I moved to using real data samples. the RGB values are from blender which is in the form of decimel values from 0-1, so I need to multiply that by the 65535 that Numbers uses for RGB. The IF/THEN doesn't account for the values grabbed being null, so I've replaced it with just a Try section to ignore the errors.

I could rework that to a different IF/THEN if needed, but for now it's good.

The new script is:

tell application "Numbers"
    activate
    tell document 1
        tell active sheet
            set thisTable to first table
            tell thisTable
                #set the width of every column to 36
                #set the height of every row to 24
                set alignment of every cell to center
                set vertical alignment of every cell to center
                repeat with i from 2 to the count of rows
                    try
                        set ColorName to value of first cell of row i
                        set RVariable to (65535 * (value of second cell of row i))
                        set GVariable to (65535 * (value of third cell of row i))
                        set BVariable to (65535 * (value of fourth cell of row i))
                        set BGColor to {RVariable, GVariable, BVariable}
                        set the background color of fifth cell of row i to BGColor
                    end try
                                        
                end repeat
            end tell
        end tell
    end tell
end tell

Next step is to pretty things up, print the name in the color box as an inverse color...see if I can hide columns...or maybe just use a second sheet to present the results with a first sheet as the data works space.

 

[chown33]

Glad it's working now. An if/then is certainly another way of handling things, as long as the condition is testable without triggering an error.

It looks like you've done that for the case where all values are missing, but not the cases where only 1 or 2 values are missing. For example, what about 2 missing values and one good value? There are 3 such cases. Then you have the cases with 2 good values and 1 missing value.

You could cover those cases with additional conditions, but there will be a total of 7 conditions where "missing value" occupies one or more places in the list. It would be simpler to use try ... on error and just let the error happen and ignore it.

 

[VideoBeagle]

It would be simpler to use try ... on error and just let the error happen and ignore it.

I'm editing before you get back to me I'm using Try now.

 

[VideoBeagle]

THIS is the finished code. IT's running now and we'll see if Numbers handles the full list....the test lists work, so it's a processing/memory thing.

(*
Script for NUMBERS to add a color swatch to a cell of a row with RGB info in it.
the table this is written for has R,G,and B in it's own cells (Columns B,C,D) in float (0-1)format. It puts the swatch in Column F and then adds the Color name or code (from Column A) into the cell in an inversed color

*)




tell application "Numbers"
    activate
    with timeout of 3600 seconds #Script reads thru whole table cell by cell, apparantly, before excuting.  Will time out without this.
        tell document 1
            tell active sheet
                set thisTable to first table
                try
                    tell thisTable
                  
                        ### Formatting commands for table appearance ###
                        set the width of fifth column to 500
                        #set the height of every row to 24
                        set alignment of every cell to center
                        set vertical alignment of every cell to center
                        ### End Formatting Commands ###
                  
                  
                        repeat with i from 2 to the count of rows
                            try #Catches cells without values in them, otherwise would error on calculations
                                set ColorName to value of first cell of row i
                                set RVariable to (65535 * (value of second cell of row i))
                                set GVariable to (65535 * (value of third cell of row i))
                                set BVariable to (65535 * (value of fourth cell of row i))
                                set BGColor to {RVariable, GVariable, BVariable}
                                set the background color of fifth cell of row i to BGColor
                          
                          
                                ### Section adds the ColorScale name from the first cell into swatch cell in an inversed color for clarity --- (needs some adjustment for greys) ###
                                set value of fifth cell of row i to ColorName as text
                                set InverseColor to {}
                                repeat with iTwo from 1 to 3
                                    copy (65535 - (item iTwo of BGColor)) to end of InverseColor
                                end repeat
                                set the text color of fifth cell of row i to InverseColor
                                #log InverseColor
                                ### End Colorscale name section ###
                          
                            end try
                        end repeat
                    end tell
                end try
            end tell
        end tell
    end timeout
end tell

# Beagle for Luck
#                 ___
#                /      \__
#|\            /      @   \
#\ \______|      \   .:|)
# \       ##|      | \__/
#  |  ####\__/   \
#  /   /  ##           \
# /   _________\    \
# L_JJ                \__JJ

EDIT: Updated code with comments to explain mad thinking.

The beagle should look right in compiled script editor, but here it is out of code

             ____
            /    \__
|\         /    @   \
\ \_______|    \  .:|)
 \      ##|    | \__/
  |    ####\__/   \
  /  /  ##       \|
/  /__________\   \
L_JJ           \__JJ