How do I reduce the "computer time" that my Google Apps Script is using?

Question

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 = '[email protected]'; // 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.

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
Obligatory link to the 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
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
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
@SheldonKennedy Please post the solution that you applied as an answer. — Rubén

Sheldon Kennedy Sheldon Kennedy · Accepted Answer · 2019-05-27T18:15:11

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).

How do I reduce the "computer time" that my Google Apps Script is using?

1 Answers