0
167
ViewsGoogle Apps Script - Insert values to column B based on values on column A, when there is thousands of rows
I have a script that I need to improve. The script goes through all the rows on column A. Then, it inserts a value on the next cell based on value. For example: If the value on cell A2 is 4,9, then it will insert UNDER10 to the cell B2. It works. But, it works so slowly. If I have thousands of rows on column A sometimes the script times out. Does anybody know a way to make this script faster?
Below is my script:
function myFunction() {
const ss = SpreadsheetApp.getActive().getActiveSheet()
const lastRow = ss.getLastRow();
for (var i = 1; i < lastRow +1; i++) {
var value = ss.getRange(i,1).getValue();
var newValue = ss.getRange(i,2);
if (value < 10) {
newValue.setValue("UNDER10");
} else if (value < 20) {
newValue.setValue("UNDER20");
} else if (value > 20) {
newValue.setValue("OVER20");
}
}
}1 answers
Answer question0
This improvement should work. Note that I assumed column A include numbers (google sheet refer 4,9 as string. therefor, the statement if(value < 10) is not realy valid).
To test my code, I used 4.9, 14.9, etc.
function myFunction() {
const ss = SpreadsheetApp.getActive().getActiveSheet()
const lastRow = ss.getLastRow();
// get all the range at once
let range = ss.getRange(2, 1, lastRow -1, 2);
// get all the values in 2D array
let values = range.getValues();
// for each pair of values [price, custom value], calculate the custom value
values.forEach((value)=> {
// NOTE that i parse float out of price.
// Google sheet refer 4,9 as string (i assume you ment 4.9)
value[0] = parseFloat(value[0])
if (value[0] < 10) {
value[1] = "UNDER10"
} else if (value[0] < 20) {
value[1] = "UNDER20";
} else if (value[0] > 20) {
value[1] = "OVER20";
}
})
// set the new values into the spreadsheet
range.setValues(values)
}
If you ment to compare each number in each row (for example, in 'A2' cell: if(4 < 10 && 9 < 10)) please comment and I'll fix accordingly.