Google Appscript using for loop with if

user_12288371 0
Viewed 59

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});
  
 }}
}
google-apps-script google-sheets javascript
1 Answers

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.

user_112252910
Related