'Google sheets api append multiple values with formatting

I have an array of values that i want to insert to a google sheet via API, i need to give color formatting to certain cells depending on the content of the cell like the image below:

I get to insert the values using append but i have no found any example on formatting append requests.

let values = [["Department",    "Product",  "Cost", "Status"],
["Clothes", "Socks", "12"   "On Stock"],
["Clothes", "Hat",  "15.99",    "Out of stock"],
["Fresh","Apple",   "18",   "Pending"],
["Fresh",   "Bannana",  "17"    "Out of stock"],
["Kitchen", "Spoon",    "0.99", "Out of stock"]]

await googleSheets.spreadsheets.values.append({
  auth,
  spreadsheetId,
  range: "Products",
  valueInputOption: "USER_ENTERED",
  resource: {
    values: values,
  },
});

What is the best approach to achieve this? batchUpdate? how would it be implemented with batchUpdate?

Solution 1:^[1]

In your situation, how about using ConditionalFormatRule? When ConditionalFormatRule is used, when a script is run one time, the colors are automatically reflected. In order to achieve this, a sample script is as follows.

Sample script:

const googleSheets = google.sheets({ version: "v4", auth }); // Please use your authorization script.
const spreadsheetId = "###"; // Please set Spreadsheet ID.
const sheetId = "###"; // Please set Sheet ID.

// These values and colors are from your showing image.
const colorObj = [
  { value: "On Stock", rgb: [182, 215, 168] }, // #b6d7a8
  { value: "Out of stock", rgb: [244, 204, 204] }, // #f4cccc
  { value: "Pending", rgb: [252, 229, 205] }, // #fce5cd
];
const requests = colorObj.map(({ value, rgb }, i) => ({
  addConditionalFormatRule: {
    index: 0,
    rule: {
      booleanRule: {
        condition: {
          values: [
            {
              userEnteredValue: value,
            },
          ],
          type: "TEXT_EQ",
        },
        format: {
          backgroundColor: {
            red: rgb[0] / 255,
            green: rgb[1] / 255,
            blue: rgb[2] / 255,
          },
        },
      },
      ranges: [
        {
          sheetId,
          startColumnIndex: 3,
          endColumnIndex: 4,
          startRowIndex: 1,
        },
      ],
    },
  },
}));
const res = await googleSheets.spreadsheets.batchUpdate({
  spreadsheetId,
  resource: { requests },
});

In this modification, this ConditionalFormatRule is reflected in the range of "D2:D".

Note:

I guessed that from your showing script, you might be using googleapis for Node.js. So, this sample scirpt is for googleapis for Node.js.

References:

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Tanaike