I have created the following function that iterates over the rows in a Google Sheet and puts the text "No" in a specific column if the values in either of two other colums contain any of five possible substrings, otherwise it puts the text "Yes" in that column. The function works as intended, but is very slow.

Specifically, this is a sheet containing user account information. Each row is a user record. emailCol contains the user's email address, and lastNameCol contains the user's last name. If either the email address or last name contains any of the substrings in the array patterns, then I set the value of realAcctCol to "No" otherwise I set that value to "Yes".

This script currently takes 20 minutes to iterate 1400 rows. Is there any way to do this faster?

I come from a Microsoft Excel/VBA background, so I tend to iterate over ranges. Am I missing an obvious way to do this more efficiently?

function checkRealUsers() {
    
  const patterns = ["notreal","demo","notauser","test","debug"];
  const realAcctCol = 9;
  const emailCol = 3;
  const lastNameCol = 2;
  var curRow = 2;
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Registered Customers");
  var curEmail = sheet.getRange(curRow, emailCol).getValues()[0][0];
  var curLastName = sheet.getRange(curRow, lastNameCol).getValues()[0][0];
  //console.log(curVals[0][0]);

  while(curEmail != "") {
    sheet.getRange(curRow, realAcctCol).setValues([["Yes"]]);
    //console.log(curEmail.toLowerCase()   " "   curLastName.toLowerCase());
    for (var ndx in patterns) {
      //console.log(patterns[ndx]);
      if ((curEmail.toLowerCase().indexOf(patterns[ndx]) != -1) || (curLastName.toLowerCase().indexOf(patterns[ndx]) != -1)) {
        //console.log("Test account!");
        sheet.getRange(curRow, realAcctCol).setValues([["No"]]);
      }
    }
    curRow = curRow   1;
    curEmail = sheet.getRange(curRow, emailCol).getValues()[0][0];
    curLastName = sheet.getRange(curRow, lastNameCol).getValues()[0][0];
  }
}

CodePudding user response：

• Oops I put the wrong answer in here but I think I got it back because they saved it in history(thanks SO for doing that).

This function uses arrays to accomplish the same thing "hopefully" but should run much faster.

function checkRealUsers() {
  const patterns = ["notreal", "demo", "notauser", "test", "debug"];
  const curRow = 2;
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Registered Customers");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, 9).getDisplayValues();
  let col9 = sh.getRange(2, 9, sh.getLastRow() - 1).getValues();
  vs.forEach((r, i) => {
    if (patterns.includes(r[2]) || patterns.includes[r[1]]) {
      col9[i][0] == "No"
    }
  });
  sh.getRange(2,9,col9.length,col9[0].length).setValues(col9);
}

CodePudding user response：

I believe your goal is as follows.

• Your script works fine. But, you want to reduce the process cost of your script.
• You want to retrieve the values from the columns "B" and "C". When those retrieved values include the words of "notreal","demo","notauser","test","debug", you want to put No to the column "I". When those retrieved values don't include the words of "notreal","demo","notauser","test","debug", you want to put Yes to the column "I".

In this case, how about the following modified script?

Modified script:

function checkRealUsers() {
  const patterns = ["notreal","demo","notauser","test","debug"];
  const sheet = SpreadsheetApp.getActive().getSheetByName("Registered Customers");
  const values = sheet
    .getRange("B2:C"   sheet.getLastRow())
    .getValues()
    .map(([b, c]) => [patterns.every(p => !b.toLowerCase().includes(p) && !c.toLowerCase().includes(p)) ? "Yes" : "No"]);
  sheet.getRange(2, 9, values.length, 1).setValues(values);
}

• In this modification, the values are retrieved from the columns "B" and "C", and check whether the retrieved values include the values of ["notreal","demo","notauser","test","debug"], and then, an array including the values of No and Yes is created. And, the created array is put to the column "I".

References:

• map()
• every()
• includes()