CODEWITHSUNDEEP
javascriptfor-loopgoogle-apps-scriptgoogle-sheets
Tane
Tane

Reputation: 491

Apps Script - For loop is slow. How to make it faster?

My spreadsheet has a column (A) with over 1000 rows of values like 10.99€, 25.99 € and so on. for optimizing purposes, I am looping through this column and removing the "EUR" mark and replacing "." with ",". While the code works, my problem is that it takes super long to execute and for thousands of products it sometimes time outs. I know I am probably not following the best practices, but this was the best solution I could come up with because of my limited JavaScript skills. Any help?

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Table');
  var lastRow = sheet.getRange(1,1).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow();
  for (var i = 1; i < lastRow +1; i++) {
    var price = sheet.getRange(i,1).getValue();
    var removeCur = price.toString().replace(" EUR","").replace(".",",");
    sheet.getRange(i,1).setValue(removeCur);
  }
}

Upvotes: 0

Views: 2634

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14537

It's a classic question. Classic answer -- you need to replace cell.getValue() with range.getValues(). To get this way 2D-array. Process the array with a loop (or map, etc). And then set all values of the array at once back on sheet with range.setValues()

https://developers.google.com/apps-script/guides/support/best-practices?hl=en

For this case it could be something like this:

function main() {
  var ss    = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Table');
  var range = sheet.getDataRange();
  var data  = range.getValues(); // get a 2d array

  // process the array (make changes in first column)
  const changes = x => x.toString().replace(" EUR","").replace(".",",");
  data = data.map(x => [changes(x[0])].concat(x.slice(1,)));

  range.setValues(data);  // set the 2d array back to the sheet
}

Just in case here is the same code with loop for:

function main() {
  var ss    = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Table');
  var range = sheet.getDataRange();
  var data  = range.getValues();

  for (var i=0; i<data.length; i++) {
    data[i][0] = data[i][0].toString().replace(" EUR","").replace(".",",")
  }
  
  range.setValues(data);  
}

Probably the loop for looks cleaner in this case than map.

And if you sure that all changes will be in column A you can make the script even faster if you change third line in the function this way:

var range = sheet.getRange("A1:A" + sheet.getLastRow());

It will narrow the range to one column.

Upvotes: 7

Trillyy
Trillyy

Reputation: 365

Well, there's something you can do to improve your code, can't guarantee it will help you to make it faster, but we'll see.

Here's the updated version

function myFunction() {
   var ss = SpreadsheetApp.getActive();
   var sheet = ss.getSheetByName('Table');
   var lastRow = sheet.getRange(1,1).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;
   var price;
   var removeCur;
   for (var i = 1; i < lastRow; i++) {
       price = sheet.getRange(i,1).getValue();
       removeCur = price.toString().replace(" EUR","").replace(".",",");
       sheet.getRange(i,1).setValue(removeCur);
   }
}

What I did:

  1. Line 5: I removed the +1 in the loop and added on lastRow directly. If you have 1000 rows, you'll save 1000 assignments
  2. Line 6-7: removed declarations in the loop. If you have 1000 rows, you'll save 2000 re-declarations (not sure if it does, but it's best practise anyway)

You could use regex for the replace, so you do it only once, but I think it's slower, so I kept the 2 replaces there

Upvotes: 0

Related Questions