google-apps-scriptrosteringrosters

Quota script for a roster


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.


Solution

  • 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:

    105 5
    106 6
    107 7
    108 8
    109 9
    110 10
    111 11
    112 12
    113 13
    114 14
    115 15
    116 16
    117 17
    118 18
    119 19
    120 20
    121 21
    122 22
    123 23
    124 24

    After:

    105 6
    106 7
    107 8
    108 9
    109 10
    110 11
    111 12
    112 13
    113 14
    114 15
    115 16
    116 17
    117 18
    118 19
    119 20
    120 20
    121 21
    122 22
    123 23
    124 24