GOOGLE SCRIPT FUNCTION CHANGE CELL BACKGROUND code example

Example: set range background color google script multiple colors

const myColorFunction = ({
  sheetName = "Form Responses 1",
  targetValue = "Open"
} = {}) => {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = ss.getSheetByName(sheetName);

  const rng = sheet.getDataRange();

  const numHeaders = 1;

  const backgrounds = rng.getBackgrounds();
  const fontColors = rng.getFontColors();

  const newBckgs = backgrounds.map((row) => {
    const [firstCell] = row;

    if (firstCell === targetValue) {
      row[5] = "red";
    }

    return row;
  });

  const newColors = fontColors.map((row) => {
    const [firstCell] = row;

    if (firstCell === targetValue) {
      row[5] = "white";
    }

    return row;
  });

  rng.setBackgrounds(newBckgs);
  rng.setFontColors(newColors);
}