3

I followed Hugo Fierro's tutorial on adding a Google Apps Script to send emails from Google Sheets. The tut is at https://developers.google.com/apps-script/articles/sending_emails.

I customised the script:

1) I replaced the "MailApp.sendEmail" API with "GmailApp.sendEmail" because I was getting authentication errors and the emails were not sending. The Gmail API has worked fine.

2) I added an option to send a PDF attachment with each mail using "DriveApp.getFileById".

3) I added a second condition to the IF statement to check that the PDF document is available before sending (by referencing a column in the sheet).

The problem is that if the script references only 5 rows, then it processes in under 30 seconds. When I try to process 10 rows or more, the processing time goes up significantly.

I replaced "sheet.getRange" with "sheet.getLastRow()" in an attempt to reduce how many rows the script is referencing.

var READY = 'READY';
var SENT = 'SENT';

function sendEmails() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email'); // Get the active spreadsheet, then get the "Email" sheet
  var startRow = 2; // Select data row to start at
  var endRow = sheet.getLastRow(); // Get the last row in the sheet
  var data = sheet.getRange(startRow, 1, endRow, 6).getValues(); // Get the range of cells, then get the values

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var email = row[0]; // Column 1
    var subject = row[1]; // Column 2
    var message = row[2]; // Column 3
    var attachment = DriveApp.getFileById(row[3]); // Returns the attachment file ID
    var emailReady = row[4]; // Column 5
    var emailSent = row[5]; // Column 6
    var name = 'VFISA'; // Set "from" name in email
    var bcc = 'myaddress@gmail.com'; // Blind carbon copy this email address
    if (emailReady==READY && emailSent!==SENT) { // Prevents sending duplicates, waits for attachment cell to confirm available
      GmailApp.sendEmail(email, subject, message,{
        name: name,
        bcc: bcc,
        htmlBody: message,
        attachments: attachment
      });
      sheet.getRange(startRow + i, 6).setValue(SENT); // Set the cell in column F to "SENT"
      SpreadsheetApp.flush(); // Make sure the cell is updated right away in case the script is interrupted
    }
  }
}

I expected the script to run much faster. The error I get is "Service using too much computer time for one day". When I reference 20 rows it takes up to 4 minutes to run.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Sheldon Kennedy
  • 31
  • 2
  • I have a similar script which is very slow as well, it is able to process at most 40 emails before running out of time, even without attachment. You can check the time each operation takes by selecting "display" then "execution script" in the script editor. – esandier May 26 '19 at 14:17
  • Obligatory link to the [best practices](https://developers.google.com/apps-script/guides/support/best-practices) guide aside, probably the best thing to do in this situation would be to something like the last suggestion it makes--split up your executions. Calling spreadsheets, drive, and sending mail can all take a while. I assume you're executing this on a time trigger--what about trying `var n = data.length > 5 ? 5 : data.length; ` with `i < n` as the condition in your for-loop? – sinaraheneba May 26 '19 at 14:19
  • 2
    As a test, I would remove these two lines: `sheet.getRange(startRow + i, 6).setValue(SENT); // Set the cell in column F to "SENT" SpreadsheetApp.flush();` If it makes enough difference to be worthwhile to you, then I'd remove the `flush()` statement, and replace the individual cell setValue with something else. You could update the data, and then write the data in one operation back to the spreadsheet. Also, you might add a link to the file instead of an attachment. – Alan Wells May 26 '19 at 15:01
  • Thank you esandier, Altigraph and Alan for your responses. I appreciate your time and super fast replies. esandier, your suggestion to check "View" then "Execution transcript" shows that each request to "DriveApp.getFileById" takes about 5 seconds! When I removed this the entire script executed in under 1 second. – Sheldon Kennedy May 26 '19 at 16:27
  • 1
    UPDATE: Issue resolved. Thanks again. I have removed ```DriveApp.getFileById``` and instead used an inline link (based on Alan Wells' suggestion). I added 500 rows and the script executes <1s :) – Sheldon Kennedy May 26 '19 at 16:59
  • 1
    @SheldonKennedy Please post the solution that you applied as an answer. – Rubén May 26 '19 at 17:31

1 Answers1

0

DriveApp.getFileById was causing each row to take about 5 seconds to execute. I removed this and now the entire script executes in under 1 second.

Instead of adding the PDF as an attachment, I used an inline link based on the file's ID (based on Alan Wells' suggestion).

Sheldon Kennedy
  • 31
  • 2