Problem With Google Apps Script Maximum Execution Time

February 28, 2024 Post a Comment

I'm new to coding and recently I've created a Google script (based on two other scripts) which does the following: Searches for a Gmail draft by its subject line Gets the Gmail d

Solution 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//functiononOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Mail Merge')
  .addItem('📌 Create Drafts', 'openDialog').addToUi(); 
}
functionopenDialog() {
  // 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><buttonid="startButton"onclick="startBatch()">Start</button></div><script>functionstartBatch() {
  google.script.run.withSuccessHandler(onSuccess).createDrafts();
}
functiononSuccess(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

functioncreateDrafts(continuationToken) {
 var batchLimit = 10;
 // ...// run through cell values and perform searchfor(var j in pdfName){ 
     // perform the search,results is a FileIteratorif (continuationToken) {
         var results = DriveApp.continueFileIterator(continuationToken);
     } else {
         var results = DriveApp.getFilesByName(pdfName[j]);
     } 
     // interate through files found and add to attachment resultslet 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.