Quickly format cell background-CodePudding

I have a spreadsheet with rows that look like this when pulled as a 2D array:

let array = 
[
[“Alex”, 35, “NA”, “NA”, “NS”, “NA”],
[“Edwin”, 23, “NS”, “NA”, “NS”, “NA”],
[“Jacob”, 56, “NA”, “NS”, “NS”, “NS”],
[“Yandy”, 19, “NA”, “NA”, “NS”, “NS”],
]

I need to set the background color of any cell that says “NA”. I currently have a for loop to do this, but I want reduce the time it takes to run this script. Is it correct that this may be done with the map method or with a foreach loop? I'm having trouble with this as it is a 2D array.

CodePudding user response：

Try this:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const vs = sh.getDataRange().getValues();
  const cs = sh.getDataRange().getBackgrounds();
  vs.forEach((r,i)=>{
    r.forEach((c,j)=>{
      if(c=="NA") {
        cs[i][j] = "#ffff00";//yellow background
      }
   });
  });
  sh.getDataRange(1,1,cs.length,cs[0].length).setBackgrounds(cs);
}