How can I optimize my code to minimize the amount of times I'm calling the sheet?

Question

I have a function set up to look at a tab within a sheet, check for certain conditions and then send an email if those conditions are met. The body of the email is set up with certain variables that are comprised of columns from the sheet. The function seems to be working fine, but I'm receiving a warning from Apps Script about using "getValues()" instead of "getValue()" and minimizing calls to servers.

I've tried to grab the range of the spreadsheet into an array, but then I'm not sure how I can go ahead and define variables based off of individual columns within that array.

This is the code that I currently have. If it matters, I also have a separate function setup with this spreadsheet to move rows from one tab to another.

function emailNotification(){
    var nowH=new Date().getHours();
    var nowD=new Date().getDay();
      if((nowD > 0 &&  nowD <6) && (nowH > 9.5 && nowH < 5.5)) {
        var ss = SpreadsheetApp.openByUrl('spreadsheet url');

           var dataSheet = ss.getSheetByName("tab name");

              for (var i = 2; i <= dataSheet.getLastRow(); i++) {

              var firstName = dataSheet.getRange(i, 5).getValue();
              var lastName = dataSheet.getRange(i, 6).getValue();
              var recipient = dataSheet.getRange(i, 3).getValue();
              var isHired = dataSheet.getRange(i, 1).getValue();
              var orgName = dataSheet.getRange(i, 11).getValue();
              var emailSent = dataSheet.getRange(i, 18).getValue();
              var body = "Hello" + "," + "\n" + "\n" + firstName + " 
               "+lastName + " " + "has been added to the"+ " " + orgName 
                + "." + "\n" + "\n" + "Best," + "\n" + "Me";   
                 var htmlText = body.replace(/\n/g,'<br\>');
                   var subject = "New Hire";

                 if (isHired == "Yes" && emailSent != "Email Notification 
                      Sent" ) {

                 MailApp.sendEmail(recipient, "email", subject, body)
                 MailApp.sendEmail(recipient, subject, body,{
                htmlBody : htmlText})
                // MARK THE ROW AS COMPLETED
               dataSheet.getRange(i, 18).setValue("Email Notification 
                 Sent");
                  }
               }
              }

This is the message I receive:

Method Range.getValue is heavily used by the script File: Code Line: 57

Any assistant would be kindly appreciated.

Welcome. We already have some similar questions. Please search for them as is suggested on [ask]. — Rubén, Sep 05 '19 at 16:33
Hello! I tried but wasn't able to surmise a solution from the other similar questions. — hpgenomix, Sep 05 '19 at 16:45
What similar questions did you find? Is there is a question about the same error message? If so, what isn't clear about the answers that the question got? — Rubén, Sep 05 '19 at 16:52
Possible duplicate of [Long processing time likely due to getValue and cell inserts](https://stackoverflow.com/questions/35289183/long-processing-time-likely-due-to-getvalue-and-cell-inserts) — Rubén, Sep 05 '19 at 18:16
The post that I had examined talked about the same problem, but I was still unsure of how to grab column values from a defined range. — hpgenomix, Sep 10 '19 at 14:12
Consider to take some steps back and search for a question about "how to grab column values from a defined range" or post an specific question about this. — Rubén, Sep 10 '19 at 14:23

score 1 · Answer 1 · answered Sep 05 '19 at 17:35

Try this:

Your missing the recipient and I would use SpreadsheetApp.openById();

function emailNotification() {
  var nowH=new Date().getHours();
  var nowD=new Date().getDay();
  if(nowD>0 && nowD<6 && nowH>9 && nowH<5) {//integers only
    var ss=SpreadsheetApp.openByUrl('spreadsheet url');//need url
    var sh=ss.getSheetByName("tab name");//need sheet name
    var vA=dataSheet.getRange(2,1,sh.getLastRow(),sh.getLastColumn()).getValues();
    for (var i=0;i<vA.length;i++) {
      var recipient=vA[i][?];//no recipient defined
      var firstName=vA[i][4];
      var lastName=vA[i][5];
      var recipient=vA[i][2];
      var isHired =vA[i][1];
      var orgName =vA[i][2=10];
      var emailSent =vA[i][17];
      var html=Utilities.formatString('Hello,<br /><br /> %s %s has been added to the %s.<br /><br />Best, <br />Me',firstName,lastName,orgName);
      var subject="New Hire";
      if (isHired=="Yes" && emailSent != "Email Notification Sent" ) {
        MailApp.sendEmail(recipient, subject,'',{htmlBody:html});
        sh.getRange(i+2,18).setValue("Email Notification Sent");
      }
    }
  }
}

Thanks so much, this worked perfectly! Can you elaborate on the " var html =utilities....." line. Why did you choose this method for creating the body of the email instead of the original method? — hpgenomix, Sep 10 '19 at 14:10

How can I optimize my code to minimize the amount of times I'm calling the sheet?

1 Answers1