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];
}
}
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);
}
Perhaps this will work better for you:
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) => {
for(let j = 0;j < patterns.length; j++ ){
if(~r[2].toString().indexOf(p) || ~r[1].toString().indexOf(p)) {
col9[i][0] == "No";
break;
}
}
});
sh.getRange(2,9,col9.length,col9[0].length).setValues(col9);
}
I believe your goal is as follows.
"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?
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);
}
["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".