Google Appscript using for loop with if

Question

The aim of my script is to loop through one column of data (Col 2 in my example) and where the cell says 'Approved' then adjust the formula which is sitting in the corresponding Col1 to be saved as a value. The script below achieves this but runs awfully slowly - can anyone help in speeding it up?


var ss = SpreadsheetApp.getActiveSpreadsheet();
var data = ss.getSheetByName('data');
var tracker = ss.getSheetByName('Tracker');
var rowlength = tracker.getLastRow();

for (r=2; r<rowlength+1; r++) {
 
 var ApprovedCell = tracker.getRange(r,2).getValue();
 
 if (ApprovedCell == 'Approved'){
    var FormulaCell = tracker.getRange(r,1);
    FormulaCell.copyTo(FormulaCell,{contentsOnly:true});
  
 }}
}

Just in case. Perhaps you need no script at all. You can use =IF() formula. Put in the cells of column A something like this: =IF(B2="Approved", C2*2+D2, "") . That means: the cell will contain C2*2+D2 if cell B2 contains 'Approved', else cell C2 will contain nothing. — Yuri Khristich
– Yuri Khristich, Commented Oct 26, 2020 at 18:22
@YuriKhristich the OP did not say that he wants nothing when B column is "Approved". He cleary said, he wants to replace the formula with its value. So he needs a particular value there, not an empty string. — Marios
– Marios, Commented Oct 26, 2020 at 18:46

Marios · Accepted Answer · 2020-10-26 17:48:06Z

Explanation:

The issue with your current solution is that you are iteratively using getValue, getRange and copyTo which is an extremely inefficient approach especially when the size of the data becomes large.
Instead you can use getValues() and getFormulas() to get all the values and the formulas respectively, of the given range, and then use setValues() to set all the desired values/formulas back to the sheet.
The following script will iterate over the values with a forEach() loop and will store the value, if cell in column B is 'Approved', otherwise store the formula, to an empty array repAr.
Then you can efficiently use a single line of code to set all the values/formulas back to column A: tracker.getRange(2,1,repAr2D.length,1).setValues(repAr2D);

Solution:

function myFunction(){

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tracker = ss.getSheetByName('Tracker');
  const vals = tracker.getRange('A2:B'+tracker.getLastRow()).getValues();
  const formulas = tracker.getRange('A2:A'+tracker.getLastRow()).getFormulas().flat();
  const repAr = [];
  vals.forEach((r,i)=>
  repAr.push(r[1]=='Approved'?r[0]:formulas[i]));
  const repAr2D = repAr.map(r=>[r]);
  tracker.getRange('A2:A'+tracker.getLastRow()).clearContent();
  tracker.getRange(2,1,repAr2D.length,1).setValues(repAr2D);
}

Bonus info:

Instead of a regular if condition, I used a ternary operator to make the code more clear.
getFormulas() returns a 2D array and this is why I am using flat() to convert it to a 1D array. This is more convenient given that the range is a single column and you can just slice the array with a single index.
It is a good practice to clear the content before you set data to a range that already contains some data.

Collectives™ on Stack Overflow

Google Appscript using for loop with if

1 Answer 1

Explanation:

Solution:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explanation:

Solution:

Comments

Your Answer

Sign up or log in

Post as a guest

Related