How to specify colors for a Google Sheet Cell through the Google Sheet API

Question

Here is the code I'm using to access a google sheet and change some of the characteristics of one of the cells in Javascript

The API seems to require you to specify a percentage of RGB colors rather than the more traditional actual RBG color in either RBG notation (such as 255,242,204) or hex (#fffccc). Is there an alternative?

Also, I'm confused as to why I can't specify a single cell range to update. In the code below, all I want to update cell BB338, but the API seems to force me to go one row higher to specify a Starting Row and one column to the left to specify the Starting Column number. Is there a way of specifying just a single cell to format?

I've tried using the RGB colors and the hex color designation, and the cell background turns black, so that isn't working.

I've tried specifying the same row as the start and end row and the same column as the start and end column, and nothing happens in the cell I specify.

I can't seem to find any API 4 documentation that really delves into this. All I find are overviews and some specifics that aren't what I'm looking for.

{
  "requests": [
    {
      "repeatCell": {
        "range": {
          "sheetId":@[User::var_SheetID],
          "startRowIndex": 337,
          "endRowIndex": 338,
          "startColumnIndex": 53,
          "endColumnIndex": 54
        },
            "cell": {
             "userEnteredFormat": {
                "backgroundColor": {
                  "red": 1.0,
                  "green": 0.95,
                  "blue": 0.8
                },
                "textFormat": {
                  "fontSize": 9,
                  "bold": true
                }
              }
            },
        "fields": "userEnteredFormat(backgroundColor,textFormat)"
      }
    }
  ]
}

I would expect that the API would allow me to use normal RGB or HEX color specifications for a cell, but it doesn't seem to. I would also expect that I can specify a single cell to format, but the "repeat cell" specification doesn't seem to allow that. Is there a different way of doing this?

Multivocal

I can't upvote more, percentage approach is rally frustrating and not documented anywhere. – Porringer

Answer 1

To get the correct percentages of the colors take the RGB color and divide by 255 so in your example (255,242,204) will end up as:

{
  "red": 255/255,
  "green": 242/255,
  "blue": 204/255
}

or

{
  "red": 1.0,
  "green": 0.949019608,
  "blue": 0.8
}

Chant

Answer 2

I've faced the same problem with hex-rgb color conversion and I've created a function to convert between formats:

function hexToRgb(hexColor) {
    hexColor = hexColor.replace('#', '');
    return [
        parseInt(hexColor.substring(0, 2), 16) / 255.0,
        parseInt(hexColor.substring(2, 4), 16) / 255.0,
        parseInt(hexColor.substring(4, 6), 16) / 255.0
    ];
}

And also an extract of the json passed to gsheet api...

{
            "repeatCell": {
                "range": {
                    "sheetId": 0,
                    "startRowIndex": row_index,
                    "endRowIndex": row_index + 1,
                    "startColumnIndex": 0,
                    "endColumnIndex": 9
                },
                "cell": {
                    "userEnteredFormat": {
                        "backgroundColor": {
                            "red": rgb_color[0],
                            "green": rgb_color[1],
                            "blue": rgb_color[2]
                        },
                        "horizontalAlignment": "CENTER",
                        "textFormat": {
                            "foregroundColor": {
                                "red": 1.0,
                                "green": 1.0,
                                "blue": 1.0
                            },
                            "fontSize": 10,
                            "bold": True
                        }
                    }
                },
                "fields": "userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)"
            }
        }

Even though it's been a long time since the question was asked... I think the solution I have developed may be useful to someone.

In relation to the part of the question you ask about whether it is possible to directly indicate the cell to modify... I have not found any other way than to indicate the range as you do in your question...

Mace