Google sheet simple percentage calculation java script on 2500 rows times out

Question

I wrote a script to calculate the percentage between:

• value in cell(i,2) and value cell (i+1,2) and return the percentage difference in cell(i,3).

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

Answer 1

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.