What have I missed in telling this cell to set its background?

Question

I'm using Google Apps Script attached to a spreadsheet to get data from an existing API and make it easily viewable (aka, put it into a spreadsheet).

I'm trying to color the cell a color based on the content of the cell (after filling from the API).

Here's a snippet of code:

var range = sheetToChange.getRange(5, 3, 40);
for(i = 0; i < 40; i++) {
 switch(range[i]) {
  case("7 star"): range[i].setBackground('green'); break;
  case(""): break;
  default: range[i].setBackground('red'); break;
 }
}

It should grab the cells from 5,3 to 5,43 and start looping through them to read their content and set a background. If the content is the string "7 star" then the background should go green, and if it's completely empty it should stay blank, but if it's anything else it should go red.

Instead, it exits with the error TypeError: Cannot read properties of undefined (reading 'setBackground')

What am I doing wrong here?

Thanks in advance for your help!

Answer 1

Creating an array of ranges

Try it like this:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const rA = [...Array.from(new Array(10).keys(), x => sh.getRange(x + 5, 3))];//this creates an array of ranges
  for (i = 0; i < 10; i++) {
    let expr = rA[i].getDisplayValue();
    switch (expr) {
      case "7": 
        rA[i].setBackground('green'); 
        break;
      case "2":
        break;
      default: 
        rA[i].setBackground('red'); 
        break;
    }
  }
}

Sheet0:

Another possibility (there are many):

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(5, 3, 10).getValues().flat();
  for (i = 0; i < 10; i++) {
    let expr = vs[i];
    let r = sh.getRange(i + 5, 3);
    switch (expr) {
      case "7":
        r.setBackground('green');
        break;
      case "2":
        break;
      default:
        r.setBackground('red');
        break;
    }
  }
}

This should run a bit faster:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const obj = { "7 star": "#00ff00", "": "#ffffff" }
  const rg = sh.getRange(5, 3, sh.getLastRow() - 4);
  const vs = rg.getValues().flat();
  const bs = rg.getBackgrounds();
  let o = vs.map( v => [~Object.keys(obj).indexOf(v) ? obj[v] : "#ff0000"])
  rg.setBackgrounds(o);
}

Sheet0:

• Array.methods()