Changing background color of specific cell on Google sheet

Question

I am working on this script bound to a Google Sheets spreadsheet where I have this function running from a time-driven trigger. I would like to be able to target specific cells on the sheet (if the cell value = "Open") so that I can change the background color of the cell.

I am wondering how can I go about making this work? I am able to target the cell, however, I don't know how to change the property of the cell background as the .setBackground() cannot be called.

function myColorFunction() {
  var s = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getSheetByName("Form Responses 1").getRange(2,6,ss.getLastRow());
  var cellRange = range.getValues();
  Logger.log(cellRange);
  Logger.log(cellRange.length);
  Logger.log(cellRange.valueOf());

  for(i = 1; i<cellRange.length; i++){
     if(cellRange[i] == "Open")
     {
       Logger.log("change color here");
     } else {
       Logger.log("don't change color");
     } 
  }
}

Answer 1

You can use setBackground property with getRange. Try the below snippet.

function myColorFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getSheetByName("Form Responses 1").getRange(2,6,ss.getLastRow());
  var cellRange = range.getValues();

  for(i = 0; i<cellRange.length-1; i++){
     if(cellRange[i][0] == "Open")
     {
       ss.getSheetByName("Form Responses 1").getRange(i+2,6).setBackground("red");
       ss.getSheetByName("Form Responses 1").getRange(i+2,6).setFontColor('white');
     }
  }
}

Answer 2

Modifications

Since the introduction of the V8 runtime, one can take advantage of modern ES6 features such as arrow functions, and destructuring. Additionally, the script can be optimized to avoid it becoming slow on larger collections.

First of all, it is important to follow the best practices. I/O (input/output) operations such as getRange, setBackground, setValue are slow. Do not use them inside loops, use batch operations like getBackgrounds, setBackgrounds, setValues, setFontColors, etc.

Secondly, it is much faster to work on a grid of values than offsetting the range each time you need to go to another row. Chain getDataRange and getValues to get all values from the sheet and work directly on the resulting array (if you have a lot of data in the sheet, use the getRange to your liking, just follow the same principle).

Finally, hardcoding sheet name makes the script inflexible, it should either be a parameter of the function, or at least a declared constant in a context accessible to the function.

---

Snippet

Given the modifications mentioned above, the snippet (of the other answer) can be modified as follows:

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);
}

---

Notes

1. To save time, you can make the two map calls at the same time, but I split them for clearer flow and to avoid mutating either the backgrounds or the fontColors array.
2. You should also remove both "red" and "white" from being hardcoded, as well as the index of the column to set the color on.