Problem with Google Apps Script maximum execution time

Question

I'm new to coding and recently I've created a Google script (based on two other scripts) which does the following:

1. Searches for a Gmail draft by its subject line
2. Gets the Gmail draft and uses it as a template to create multiple drafts with unique attachments
3. Puts a confirmation phrase after drafts are created.

Here is the code:

//Change these to match the column names you are using for email recepient addresses and merge status column//
var RECIPIENT_COL  = "Email";
var MERGE_STATUS_COL = "M";

//Creates the menu item "Mail Merge" for user to run scripts on drop-down//
function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Mail Merge')
  .addItem(' Create Drafts', 'createDrafts').addToUi(); 
}

function createDrafts() {
  // search for the draft Gmail message to merge with by its subject line
  var subjectLine = Browser.inputBox("Select draft " + "to merge with:", "Paste the subject line:", Browser.Buttons.OK_CANCEL);

  if (subjectLine === "cancel" || subjectLine == ""){ 
   // if no subject line finish up
   return;
  }

  // get the draft Gmail message to use as a template
  var emailTemplate = getGmailTemplateFromDrafts_(subjectLine);
  emailTemplate.subject = subjectLine;

  // get the data from the active sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  // fetch values for each row in the Range.
  var data = dataRange.getValues();
  // assuming row 1 contains our column headings
  var header = data.shift(); 

  // get the index of column named 'M' (Assume header names are unique)
  var draftCreatedColIdx = header.indexOf(MERGE_STATUS_COL);

  var object = data.map(function(row) {  
    // create a new object for next row using the header as a key
    var nextRowObject = header.reduce(function(accumulator, currentValue, currentIndex) {
      accumulator[currentValue] = row[currentIndex];      
      return accumulator;
    }, {}) // Use {} here rather than initialAccumulatorValue
    return nextRowObject;
  });

  // loop through all the rows of data
  object.forEach(function(row, rowIdx){

    // only create drafts if mail merge status cell is blank
    if (row[MERGE_STATUS_COL] === ''){

        var msgObj = fillInTemplateFromObject_(emailTemplate, row);
        var attachment_id = "File Name";

        // split the values taken from cell into array
        var pdfName = row[attachment_id].split(', ');
          // initialize files as empty array
          var files = []; 

          // run through cell values and perform search
          for(var j in pdfName){ 
            // perform the search,results is a FileIterator
            var results = DriveApp.getFilesByName(pdfName[j]); 
            // interate through files found and add to attachment results
            while(results.hasNext()) {
        // add files to array
        files.push(results.next());
            }
          }     

        // @see https://developers.google.com/apps-script/reference/gmail/gmail-app#sendemailrecipient-subject-body-options
        GmailApp.createDraft(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {htmlBody: msgObj.html, attachments: files});
        // create a confirmation phrase in the first column
        sheet.getRange("A" + (rowIdx + 2)).setValue("DRAFT");    
     }
  }); 
}

/**
 * Get a Gmail draft message by matching the subject line.
 * @param {string} subject_line to search for draft message
 * @return {object} containing the plain and html message body
*/
function getGmailTemplateFromDrafts_(subject_line) {
  try {
    // get drafts
    var drafts = GmailApp.getDrafts();
    // filter the drafts that match subject line
    var draft = drafts.filter(subjectFilter_(subject_line))[0];
    // get the message object
    var msg = draft.getMessage();
    return {text: msg.getPlainBody(), html:msg.getBody()};
  } catch(e) {
    throw new Error("Oops - can't find Gmail draft");
  }
}

/**
 * Filter draft objects with the matching subject linemessage by matching the subject line.
 * @param {string} subject_line to search for draft message
 * @return {object} GmailDraft object
*/
function subjectFilter_(subject_line){
  return function(element) {
    if (element.getMessage().getSubject() === subject_line) {
      return element;
    }
  }
}

/**
 * Fill HTML string with data object.
 * @param {string} template string containing {{}} markers which are replaced with data
 * @param {object} data object used to replace {{}} markers
 * @return {object} message replaced with data
 * H/T https://developers.google.com/apps-script/articles/mail_merge
*/
function fillInTemplateFromObject_(template, data) {
  // convert object to string for simple find and replace
  template = JSON.stringify(template);
  // Search for all the variables to be replaced, for instance {{Column name}}
  var templateVars = template.match(/{{([^}]+)}}/g);

  // Replace variables from the template with the actual values from the data object.
  // If no value is available, replace with the empty string.
  for (var i = 0; i < templateVars.length; ++i) {
    // strip out {{ }} 
    var variableData = data[templateVars[i].substring(2, templateVars[i].length - 2)];
    template = template.replace(templateVars[i], variableData || "");
  }
  // convert back to object
  return JSON.parse(template);
}

The script works as expected but when I'm trying to process too many rows with too many attachments it exceeds a 6-minute Google Script maximum execution time.

While trying to solve this problem I found a simple script that uses a continuationToken and by doing so never exceeds the limit. My goal is to try to use the same principle in my own script and to process rows by tens. Unfortunatelly, I haven't had any luck so far and need some help. Here's the code of the script that I found:

Code.gs

function onOpen() {
  SpreadsheetApp.getUi().createMenu("List Drive files").addItem('Start', 'start').addToUi();
}

function start() {
  var ui = HtmlService.createHtmlOutputFromFile('ui');
  return SpreadsheetApp.getUi().showSidebar(ui);
}

function getDriveFiles(continuationToken) {
  if(continuationToken) {
    var files = DriveApp.continueFileIterator(continuationToken);
  }
  else {
    var files = DriveApp.getFiles();
  }
  var i = 0;
  while (files.hasNext() && i < 10) {
    var file = files.next();
    SpreadsheetApp.getActiveSheet().appendRow([file.getName(), file.getUrl()]);
    i++;
    if(i == 10) {
      return files.getContinuationToken();
    }
  }
} 

ui.html

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<div style="text-align:center; margin-top:10px">
<div>Files processed:</div>
<div id="nbOfFilesProcessed">0</div>
<br>
<button id="startButton" class="blue" onclick="start()">Start</button>
<div class="secondary">Close the sidebar to stop the script.</div>
</div>

<script>
function start() {
  document.getElementById("startButton").disabled = true;
  google.script.run.withSuccessHandler(onSuccess).getDriveFiles();
}
function onSuccess(continuationToken){
  // If server function returned a continuationToken it means the task is not complete
  // so ask the server to process a new batch.
  if(continuationToken) {
    var nbOfFilesProcessedEl = document.getElementById("nbOfFilesProcessed");
    nbOfFilesProcessedEl.innerHTML = parseInt(nbOfFilesProcessedEl.innerHTML) + 10;
    google.script.run.withSuccessHandler(onSuccess).getDriveFiles(continuationToken);
  }
}
</script> 

Answer 1

From what I see in the code you posted you will have to edit your createDrafts function in this way:

• Edit how the function is triggered: you will have to use an HTML ui element to run javascript inside it.
• Edit the while loop so that it has a return statement when you hit the limit of your batch.
• Create a Javascript function in the HTML ui element that handles the success of the createDrafts function and recursively calls it in case that the continuationToken is returned.

Snippets

UI Component

You can keep your custom menu and on click add this HTML to a UI dialog.

- code.gs -
//Creates the menu item "Mail Merge" for user to run scripts on drop-down//
function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Mail Merge')
  .addItem(' Create Drafts', 'openDialog').addToUi(); 
}
function openDialog() {
  // Display a modal dialog box with custom HtmlService content.
  var htmlOutput = HtmlService
      .createHtmlOutputFromFile('Dialog')
      .setWidth(250)
      .setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Create Drafts');
}

- Dialog.html -
<!-- The UI will be very similar to the one you found, I will keep only the strictly necessary statements for this example -->
<div>
<button id="startButton" onclick="startBatch()">Start</button>
</div>

<script>
function startBatch() {
  google.script.run.withSuccessHandler(onSuccess).createDrafts();
}
function onSuccess(continuationToken){
  // If server function returned a continuationToken it means the task is not complete
  // so ask the server to process a new batch.
  if(continuationToken) {
  google.script.run.withSuccessHandler(onSuccess).createDrafts(continuationToken);
  }
}
</script> 

Apps Script Component

function createDrafts(continuationToken) {
 var batchLimit = 10;
 // ...
 // run through cell values and perform search
 for(var j in pdfName){ 
     // perform the search,results is a FileIterator
     if (continuationToken) {
         var results = DriveApp.continueFileIterator(continuationToken);
     } else {
         var results = DriveApp.getFilesByName(pdfName[j]);
     } 
     // interate through files found and add to attachment results
     let i = 0;
     while(results.hasNext() && i<batchLimit) {
         // add files to array
         files.push(results.next());
         i++;
         if (i === batchLimit) {
             return results.getContinuationToken();
         }
     }
 }     

Final considerations

As an improvement to your batch operation, I would save all the user inputs so that you will be able to continue the script without prompting for it again. You can either pass these values to the return function on a javascript object or save them in the cache with the CacheService utility.

Moreover, try to find the correct trade off between execution time and batch limit: A small batch limit will never hit the time limit but will consume your quota very fast.

References:

Client Side API

Cache Service

Apps Script UI