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