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