I'm trying to make a script that reads a specific column(B), checking each cell for "is this value < 120?".

If the value is < 120, I want the script to add a value of "1" to the corresponding cell in a different column(E).

This is what I've come up with so far, but it's not working and I can't figure out why.

function quota1() {

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Roster");


  var workingCell = sheet.getRange("B:B").getValue();

  if(workingCell < 120){
    sheet.getrange("E:E").add(1);

  }

}

Picture of roster for better understanding.

CodePudding user response：

If col2 less 120 increment column 5 by 1

function quota1() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Roster");
  const vs = sh.getRange(1,1,sh.getLastRow(),5).getValues();//get all data
  let vo = vs.map((r,i) =>{
    if(r[1] < 120) {
      return [r[4]   1];//add one
    } else {
      return [r[4]];//no change
    }
  });
  //Logger.log(JSON.stringify(vo));//easier see the column
  sh.getRange(1,5,vo.length,vo[0].length).setValues(vo)
}

Before:

After: