I wrote a script to calculate the percentage between:
My sheet has over 2000 rows, and the script stops at around row 500 as the function times out.
I am trying to make my script run faster if such thing exists (I am quite new to Java scripts and would be interested in understanding how to optimise my script).
Script is:
function SimplePerc() {
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Main_Sheet");
for (var i=sheet1.getLastRow()-1 ; i>1; i--)
{
var numerator = sheet1.getRange(i,2).getValue();
var denominator = sheet1.getRange(i+1,2).getValue();
var percentagecalc = (numerator /= denominator)-1;
sheet1.getRange(i,3).setValue(percentagecalc);
}
}
Something like this:
function SimplePerc() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Main_Sheet");
const vs = sh.getRange(2, 2, sh.getLastRow() - 1).getValues();
const rs = sh.getRange(2, 3, sh.getLastRow() - 1).clearContent().getValues();
vs.forEach((r, i, arr) => {
if (i < vs.length - 1) {
rs[i][0] = (r[0] / arr[i + 1][0]) - 1;
}
})
sh.getRange(2, 3, rs.length, 1).setValues(rs)
}
This probably not completely correct because I don't wish to debug it.