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

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

CodePudding user response：

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();
}

Modied script 1:

Modied script 2:

References: