How to change cells background color in the google sheets with java

I try to change the cells background color with java, and try to use the official tutorial:

https://developers.google.cn/sheets/api/guides/conditional-format?hl=ko

But after code executing, I got the error:

com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "message" : "Invalid requests[0].addConditionalFormatRule: Invalid ConditionValue.userEnteredValue: =LT($D2,median($D$2:$D$11))",
    "reason" : "badRequest"
  } ],
  "message" : "Invalid requests[0].addConditionalFormatRule: Invalid ConditionValue.userEnteredValue: =LT($D2,median($D$2:$D$11))",
  "status" : "INVALID_ARGUMENT"
}

I do not understand what means “=LT($D2,median($D$2:$D$11))” and where the range sets. On the screen-shot range is A2:D5 – so how to this range should be set and what wrong with this example?

enter image description here

Answer

Regarding where do we set the range, It is being set in AddConditionalFormatRuleRequest -> ConditionalFormatRule -> ranges

Regarding what this formula means =LT($D2,median($D$2:$D$11)), Since this is a custom formula, $D2 will increment based on the range provided. From D2 up to D11. It will check if the current Column D row is less than the median value of range D2:D11. See LT() and MEDIAN()

Sample Request via API Explorer:

{
  "requests": [
    {
      "addConditionalFormatRule": {
        "index": 0,
        "rule": {
          "ranges": [
            {
              "sheetId": 116889903,
              "startRowIndex": 1,
              "startColumnIndex": 0,
              "endColumnIndex": 4,
              "endRowIndex": 11
            }
          ],
          "booleanRule": {
            "condition": {
              "type": "CUSTOM_FORMULA",
              "values": [
                {
                  "userEnteredValue": "=LT($D2,median($D$2:$D$11))"
                }
              ]
            },
            "format": {
              "backgroundColor": {
                "red": 1,
                "blue": 0,
                "green": 0
              }
            }
          }
        }
      }
    }
  ]
}
  • In this example, the range I set is starting from rowIndex 1 which is equivalent to sheets row 2. (GridRange object is zero-based). start columnIndex is 0 since we want it to start at sheet column 1.
  • Notice that endRowIndex is 11 (Sheet row 12) and endColumnIndex is 4 (Sheet column 5). It is because based on GridRange, endRowIndex and endColumnIndex are exclusive which means it is not included in the range.

Output:

enter image description here

  • For a java code sample code, please refer to the official document example here.

But if you just want to change the cell background color without conditional formatting, you can use UpdateCellsRequest or RepeatCellRequest.

The difference between the 2 is that, In UpdateCellsRequest you will provide the backgroundColor in each cell in your range. While in RepeatCellRequest you only need to set the backgroundColor once and it will be reflected in all your range.

Sample Update Cells Request via API Explorer:

{
  "requests": [
    {
      "updateCells": {
        "range": {
          "sheetId": 116889903,
          "startRowIndex": 0,
          "startColumnIndex": 0,
          "endRowIndex": 1,
          "endColumnIndex": 4
        },
        "rows": [
          {
            "values": [
              {
                "userEnteredFormat": {
                  "backgroundColor": {
                    "red": 0,
                    "blue": 1,
                    "green": 0
                  }
                }
              },
              {
                "userEnteredFormat": {
                  "backgroundColor": {
                    "red": 0,
                    "blue": 0,
                    "green": 1
                  }
                }
              }
            ]
          }
        ],
        "fields": "userEnteredFormat"
      }
    }
  ]
}
  • In this example, even though I set the range to A1:D1, but since I only provided 2 CellData values with background color blue and green. Only 2 cells were updated

Output:

enter image description here

Sample Repeat Cell Request via API Explorer:

{
  "requests": [
    {
      "repeatCell": {
        "range": {
          "sheetId": 116889903,
          "startRowIndex": 0,
          "endRowIndex": 1,
          "startColumnIndex": 0,
          "endColumnIndex": 4
        },
        "cell": {
          "userEnteredFormat": {
            "backgroundColor": {
              "red": 0,
              "blue": 0,
              "green": 1
            }
          }
        },
        "fields": "userEnteredFormat"
      }
    }
  ]
}
  • I only entered 1 CellData which will reflect to all the cells within the range provided.

Output:

enter image description here