I have a sheet that is dynamically updating with leads from my marketing campaign. Currently, it has over 9K rows and will continue to grow.

I need to check values for Col F for leads that came in today; however, when I run a loop, it takes too long since it has 9k rows.

I want to run a loop for rows that have today's date only. Is there a way to have my script efficiently look for today's date first (without having to loop through 9k rows), and then start my loop?

Below is what I had originally. The code would start from the top of the sheet and look at Col F values and then email me if a cell had only "az-" instead of a full location value. Content below has been modified for privacy

function Google_Check_Lead_Values() {
var title = "Google | Leads"
  var app = SpreadsheetApp;

  var Def_url = ("website")
  ///EDIT ^ URL 

  var ss = app.openByUrl(Def_url).getSheetByName("Google | Leads");
    

var last_row = ss.getLastRow();

for (i = 1;i <=last_row; i  ) {

  var location = ss.getRange(i,6).getValue();

  var location_len = location.length;
if (location_len <= 3){

  MailApp.sendEmail({
          to: "email1",
          //cc: "email2",
          subject: "Discrepancy found on "   title,
          htmlBody:"Hi Team, <br>This is an automated notifcation to let you know that an issue was found on "   title   ". Please take a look at the sheet linked below: <br><br> email"
          });//end of email 
}//end of if
}//end of for

}//end of function

CodePudding user response：

Try it like this:

function Google_Check_Lead_Values() {
  const title = "Google | Leads"
  const ss = SpreadsheetApp.openByUrl("url")
  const sh = SpreadsheetApp.openByUrl(Def_url).getSheetByName(title);
  const dt = new Date();
  const dtv = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate()).valueOf();
  const vs = sh.getDataRange().getValues();
  vs.forEach(r => {
    let loc = r[5].toString();
    let d = new Date(r[0]);
    let dv = new Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf();
    if( dv == dtv && loc.length < 4) { 
      MailApp.sendEmail({ to: "email1", subject: "Discrepancy found on "   title, htmlBody: "Hi Team, <br>This is an automated notifcation to let you know that an issue was found on "   title   ". Please take a look at the sheet linked below: <br><br> email", })
    }
  });
}

CodePudding user response：

I believe your goal is as follows.

• You want to reduce the process cost of the search of value from a large sheet.
• You want to achieve this using Google Apps Script.
• In your situation, by searching the values from the columns "A" and "F", you want to send emails.

In order to search the values from a sheet, there are several methods. Using for loop, TextFinder and query language. In my test, when the query language is used, the process cost was the lowest of these 3 patterns. So, in this answer, I would like to propose to search the values using use the query language. Ref

Sample script:

function Google_Check_Lead_Values() {
  var title = "Google | Leads"
  var Def_url = "###"; // Please use your Spreadsheet URL.
  var ss = SpreadsheetApp.openByUrl(Def_url);

  const spreadsheetId = ss.getId();
  const today = new Date();
  const tommorow = new Date();
  tommorow.setDate(tommorow.getDate()   1);
  const vToday = Utilities.formatDate(today, Session.getScriptTimeZone(), "yyyy-MM-dd");
  const vTommorow = Utilities.formatDate(tommorow, Session.getScriptTimeZone(), "yyyy-MM-dd");
  const sheetName = "Google | Leads";
  const query = `SELECT * WHERE A >= DATE '${vToday}' AND A < date '${vTommorow}' AND E = 'az-'`;
  const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?sheet=${sheetName}&tqx=out:csv&tq=${query}`;
  const res = UrlFetchApp.fetch(encodeURI(url), { headers: { authorization: "Bearer "   ScriptApp.getOAuthToken() } });
  const [, ...values] = Utilities.parseCsv(res.getContentText());
  if (values.length == 0) return;
  values.forEach(_ => {
    MailApp.sendEmail({
      to: "email1",
      //cc: "email2",
      subject: "Discrepancy found on "   title,
      htmlBody: "Hi Team, <br>This is an automated notifcation to let you know that an issue was found on "   title   ". Please take a look at the sheet linked below: <br><br> email"
    });
  });
}

• When this script is run, the today date values are searched from the column "A", and also it searches whether the cell value of column "F" is the value of az-. When the values are found, the emaiil is sent. In this case, from your showing script, for example, when 2 rows are found, 2 emails are sent.

• If you know the Spreadsheet ID, you can also directly use it to const spreadsheetId = ss.getId(); like const spreadsheetId = "###SpreadsheetID###";.

• In this sample script, about the search of the column "F", it seaches whether the value is az-. But, if you want to search the length of cell value, please modify as follows.

  • From

      const query = `SELECT * WHERE A >= DATE '${vToday}' AND A < date '${vTommorow}' AND E = 'az-'`;
    

  • To

      const query = `SELECT * WHERE A >= DATE '${vToday}' AND A < date '${vTommorow}' AND E MATCHES '^.{0,3}$'`;
    

References:

• Query Language Reference (Version 0.7)
• Related thread.
  • https://stackoverflow.com/a/56663884