Google Sheets / How to change the format of cells whose values are the same as the values from the array?

Please, tell How to change the visual content in a cell (background, font-weight and color), provided that the value stored in this cell matches the value of the array being checked? I watched on ConditionalFormatRule and try to write script to decide this problem on nodejs

const client = new google.auth.JWT(
    keys.client_email,
    null,
    keys.private_key,
    ["https://www.googleapis.com/auth/spreadsheets"]
)

const gsapi = google.sheets({
    version: 'v4',
    auth: client
})

const spreadsheetIdCurr = '1pircQiSbWuiaMVOVFi9ZfVizB5KEx4YSh7FqdWEp4_f';

const checkData = {values: ["aaa", "b"]}; // Arrey for checking

async function colorizeResult() {

    res = await gsapi.spreadsheets.batchUpdate({
        spreadsheetId: spreadsheetIdCurr,
        requestBody: {
            requests: [
                {
                    updateCells: {
                        range: {
                            sheetId: 0,
                            startColumnIndex: 0,
                            endColumnIndex: 5,
                            startRowIndex: 0,
                            endRowIndex: 2,
                        },
                    },
                    addConditionalFormatRule: {
                        rule: {
                            booleanRule: {
                                condition: {
                                    type: "ONE_OF_LIST",
                                    values: [{userEnteredFormat: {userEnteredValue: checkData[0]}},
                                        {userEnteredFormat: {userEnteredValue: checkData[1]}},],
                                },
                                format: {
                                    textFormat: {
                                        bold: true,
                                        foregroundColor: {red: 1, green: 1, blue: 1}
                                    },
                                    backgroundColor: {red: 0.1, green: 0.3, blue: 1}
                                }
                            }
                        }
                    }

                }
            ]
        },
    })

}
colorizeResult();

..., but it doesn't work. I got a message (node:10700) UnhandledPromiseRejectionWarning: Error: Invalid value at 'requests[0]' (oneof), oneof field 'kind' is already set. Cannot set 'addConditionalFormatRule'

Solution 1:

I believe your goal is as follows.

• You want to set the conditional format rule to the range of "A1:E2" using the values of "aaa" or "b".
• You want to achieve this using googleapis for Node.js.
• You have already been able to get and put values for Google Spreadsheet using Sheets API.

Modification points:

• In your request body, updateCells is not used.
• In your situation, I thought that CUSTOM_FORMULA instead of ONE_OF_LIST might be suitable.

When these points are reflected to your request body, it becomes as follows.

Modified script:

async function colorizeResult() {
  res = await gsapi.spreadsheets.batchUpdate({
    spreadsheetId: spreadsheetIdCurr,
    requestBody: {
      requests: [
        {
          addConditionalFormatRule: {
            rule: {
              booleanRule: {
                condition: {
                  type: "CUSTOM_FORMULA",
                  values: [
                    {
                      userEnteredValue: `=REGEXMATCH(A1,"${checkData.values.map(e => `^${e}\$`).join("|")}")`, // Modified
                    },
                  ],
                },
                format: {
                  textFormat: {
                    bold: true,
                    foregroundColor: {
                      red: 1,
                      green: 1,
                      blue: 1,
                    },
                  },
                  backgroundColor: {
                    red: 0.1,
                    green: 0.3,
                    blue: 1,
                  },
                },
              },
              ranges: [
                {
                  sheetId: 0,
                  startColumnIndex: 0,
                  endColumnIndex: 5,
                  startRowIndex: 0,
                  endRowIndex: 2,
                },
              ],
            },
          },
        },
      ],
    },
  });
}

Note:

• When this script is run several times, the conditional format rule is added. In this answer, I modified your request body for removing your current issue. For example, I thought that if you want to update the existing the conditional format rule, you can also use UpdateConditionalFormatRuleRequest.

• This modified script supporses that you have already been able to get and put values for Google Spreadsheet using Sheets API. Please be careful this.

• If userEnteredValue: `=REGEXMATCH(A1,"${checkData.values.map(e => `^${e}\$`).join("|")}")`, was not useful for your actual situation, please modify it to userEnteredValue: `=OR(${checkData.values.map(e => `A1="${e}"`).join(",")})`, and test it again. I think that both script will be the same result.

References:

• AddConditionalFormatRuleRequest