CODEWITHSUNDEEP
loopsgoogle-apps-scriptgoogle-sheetsreplacesetvalue
pedegasc
pedegasc

Reputation: 23

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 :)

Upvotes: 1

Views: 96

Answers (2)

Mart&#237;n
Mart&#237;n

Reputation: 10084

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

Upvotes: 1

Tanaike
Tanaike

Reputation: 201378

I believe your goal is as follows.

  • You want to reduce the process cost of your script.

In this case, how about the following modifications?

Modied script 1:

In this pattern, the column "D" is overwritten.

function ATM() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var range = sheet.getRange("A1:D" + sheet.getLastRow());
  var values = range.getValues().map(r => [r[0] == "ATM" ? "Cash withdrawal" : r[3]]);
  range.offset(0, 3, values.length, 1).setValues(values);
  sheet.getRange('A1').activate();
}

Modied script 2:

In this pattern, TextFinder is used.

function ATM() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var ranges = sheet.getRange("A1:A" + sheet.getLastRow()).createTextFinder("ATM").matchEntireCell(true).findAll().map(r => r.offset(0, 3).getA1Notation());
  sheet.getRangeList(ranges).setValue("Cash withdrawal");
  sheet.getRange('A1').activate();
}

References:

Upvotes: 3

Related Questions