loopsgoogle-apps-scriptgoogle-sheetsreplacesetvalue

Speed up a loop - How to batch replace values of cells based on another cells value without thousand of iterations

I have a transactions sheet, In column A, there is the type of the transaction. In column D, there is the description of the transaction

I would like all the rows where the column A is "ATM" to have the same description.

This is what I wrote, a typical loop. However it takes AGES :D So if you know another technique with let's say, an indexation of all the rows to modify with a bulk change of all the values, it would be wonderful :D

Thanks a lot !

function ATM() {

  // changes the description of the ATM lines into Cash withdrawal
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var rowCount = spreadsheet.getDataRange().getNumRows();
for (i=1; i<=rowCount; i++) { 
var ATM = spreadsheet.getRange(i,1).getValue() ;  // IF A column cell is ATM then
if (ATM=="ATM") {
spreadsheet.getRange(i,4).setValue("Cash withdrawal"); // Changes the 4th column of the row into cash withdrawal
}
}
  spreadsheet.getRange('A1').activate();
};

I tried the code I wrote above. It works perfectly but it takes ages. I would like to speed the process up :)

Solution

  • You can try looping but after importing the values with .getValues; and then set values with the whole array. If the spreadsheet has too many columns you could specify a smaller range (say .getRange("A:E").getValues())

    function ATM() {

  // changes the description of the ATM lines into Cash withdrawal
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var rowCount = spreadsheet.getDataRange().getNumRows();
  var data = spreadsheet.getDataRange().getValues()
  //Logger.log(data)
    for (i=1; i<=rowCount-1; i++) {
      if(data[i][0] == "ATM"){
        data[i][3] = "Cash withdrawal"
      }
  }
  spreadsheet.getDataRange().setValues(data)

}

    With data in 1000 rows and 5 columns it took 3 seconds: enter image description here