CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
McChief
McChief

Reputation: 425

Send emails triggered automatically by date

I have a Google spreadsheet in the following link:

Google Spreadsheet

I want to send emails automatically triggered by a date in a cell. I have created code but can't get it to work.

The body of the email is in cell A1 of the "Email Alerts" Sheet.

In the "H&S Reviews" Sheet:

Column K = the trigger date
Column I = the email address
Column J = the "name" for the email
Column C = the "Task" for the body of the email
Column G = the "Date" the review is due for the body of the email
Column D = the "Description" for the body of the email

The code is called function sendEmails from line 339 to 367 per below: 

function sendEmails() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("H&S Reviews").activate();
  var lastRow = sheet.getLastRow();
  var message = spreadsheet.getSheetByName("Email Alerts").getRange(1,1).getValue();

  // I modified below script.
  var values = sheet.getRange(4, 1, lastRow - 3, 11).getValues();
  var todaysDate = sheet.getRange(1, 11).getValue();
  for (var i = 0; i < values.length; i++) {
    var [,,task,description,,,date,,emailAddress,firstName,sendDate] = values[i];
    var messageBody = message.replace("{name}",firstName).replace("{Task}",task).replace("{Description}",description).replace("{Date}",date);
    var subject = "Health & Safety Review Task"; 
    var sheetDate = new Date(sendDate);
    Sdate = Utilities.formatDate(todaysDate,"GMT+0200","dd-MM-yyyy");
    SsheetDate = Utilities.formatDate(sheetDate,"GMT+0200", "dd-MM-yyyy");
    if (Sdate == SsheetDate){
      var subject = "Health & Safety Review Task";
      emailAddress = "[email protected]";
      MailApp.sendEmail(emailAddress, subject, messageBody);
    }
  }
}

Also when I run the code in the Script Editor I get the following message:

"Method SpreadsheetApp.Range.getValue is heavily used by the script.Collapse File: Code Line: 344 The script uses a method which is considered expensive. Each invocation generates a time consuming call to a remote server. That may have critical impact on the execution time of the script, especially on large data. If performance is an issue for the script, you should consider using another method, e.g. Range.getValues()."

I would really appreciate some help to get this working.

Upvotes: 0

Views: 155

Answers (1)

Tanaike
Tanaike

Reputation: 201348

How about this modification? Please think of this as just one of several possible answers.

Modification point:

  • In your script, getValue() is used in the for loop. By this, I think that this occurs such issue. So in this modification, getValues() is used instead of getValue() as mentioned by the error message.

Modified script:

function sendEmails() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("H&S Reviews").activate();
  var lastRow = sheet.getLastRow();
  var message = spreadsheet.getSheetByName("Email Alerts").getRange(1,1).getValue();

  // I modified below script.
  var values = sheet.getRange(4, 1, lastRow - 3, 11).getValues();
  var todaysDate = sheet.getRange(1, 11).getValue();
  for (var i = 0; i < values.length; i++) {
    var [,,task,description,,,date,,emailAddress,firstName,sendDate] = values[i];
    var messageBody = message.replace("{name}",firstName).replace("{Task}",task).replace("{Description}",description).replace("{Date}",date);
    var subject = "Health & Safety Review Task"; 
    var sheetDate = new Date(sendDate);
    Sdate = Utilities.formatDate(todaysDate,"GMT+0200","dd-MM-yyyy");
    SsheetDate = Utilities.formatDate(sheetDate,"GMT+0200", "dd-MM-yyyy");
    if (Sdate == SsheetDate){
      var subject = "Health & Safety Review Task";
      MailApp.sendEmail(emailAddress, subject, messageBody);
    }
  }
}
  • Before the for loop, the values are retrieved from the Spreadsheet. And the retrieved values are used in the for loop.

Note:

  • In your script, as the range of H&S Reviews, the values are retrieved from the row 4. So I reflected this to the modified script. If you want to retrieve the values from the row 3, please modify var values = sheet.getRange(4, 1, lastRow - 3, 11).getValues(); to var values = sheet.getRange(3, 1, lastRow - 2, 11).getValues();.
  • I think that in the if statement of your script works. The value of the cell "K1" and the values of the cells `K4:K" of "K3:K" are compared. But if you want to modify this, please tell me.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 2

Related Questions