Script times out in a loop containing 750 rows and 16 columns

| | August 7, 2015

I have a Google Apps Script that I’m using to Bulk email recipients on the same domain. The Script essentially sends them a link to a Google Doc containing a monthly report. The values for the report come from a Spreadsheet:

  • Access a spreadsheet containing 16 columns and 750 rows
  • Read the email address from the EMAIL column
  • Access a Google Doc which is essentially a template of the monthly
    report, make a copy of the template for each email address retrieved
    from the spreadsheet and using values from the columns essentially do
    a find a replace on the Doc. In my app I allow the user to select the template and then I store that templateId in ScriptDB
  • Send an email to each of the recipients with a link to the monthly
    report – each report is unique since the columns values represent a
    user.

The biggest problem with this script is that it times out after 5 minutes of execution. Last time I tried to run it, it sent out 145 emails out of the 750 email target.

Template Selector Code

function selectTemplate() {
 var app = UiApp.createApplication().setTitle("Select Template").setHeight(400).setWidth(500);
 var doclisthandler = app.createServerHandler('templateSelectionHandler');
 var closeHandler = app.createServerHandler('closeSelectionHandler');
 app.createDocsListDialog().showDocsPicker().addSelectionHandler(doclisthandler).addCloseHandler(closeHandler);
 SpreadsheetApp.getActiveSpreadsheet().show(app);

}

/**
Function to retrieve the template ID from ScriptDb
**/

function setTemplateId(){
  var db = ScriptDb.getMyDb();
  /**we dont query for a specific ID because by default we only store one template so we will
  always have one record
 **/
  var results = db.query({});
  while (results.hasNext()) {
      var result = results.next();
      var jsonResults = Utilities.jsonStringify(result);

  }
  Logger.log(jsonResults);
  var jsonTemplate = Utilities.jsonParse(jsonResults);
  var templateId = jsonTemplate.template_id;
  return templateId;
}

This is the code that sends the email:

function sendEmail(){


  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
  var range = mySheet.getDataRange();

  var myRange = range.offset(1, 0, range.getNumRows()-1);

  var curFeatures = range.getValues();
  var curCols = new ColNumbers(curFeatures[0], COLS_KEYAPPCOLS);

  var currentTime = new Date();
  var payrollYear = currentTime.getYear();
  var payMonth = mySheet.getSheetName();
  var hours = currentTime.getHours();
  var minutes = currentTime.getMinutes();

  try{
    //check if folder exists before you create one
    var monthFolder = DocsList.getFolder("Report-" + mySheet.getSheetName() + "," + payrollYear);
  }catch(e){
    //create a folder to hold the current pay slips
    var monthFolder = DocsList.createFolder("Report-" + mySheet.getSheetName() + "," + payrollYear);
    Logger.log(monthFolder.getName());

  }


  myRange.getValues().forEach( function( recipient, index, data ){

    var staffNumber = recipient[curCols.staffNumber];
    Logger.log("staff Number " + staffNumber);
    var staffName = recipient[curCols.name];
    Logger.log("staffName " + staffName);
    var subject = "Report - " + staffName + ", " + payMonth + "-" + payrollYear;
    var emailAddress = recipient[curCols.email];
    var adminAllowance = recipient[curCols.admin];
    var respAllowance = recipient[curCols.resp];
    var topUpAllowance = recipient[curCols.topup];
    var arrears = recipient[curCols.arrears];
    var overtime = recipient[curCols.overtime];
    var grossPay = recipient[curCols.gross];
    var paye = recipient[curCols.paye];
    var mubasa = recipient[curCols.mubasa];
    var loan = recipient[curCols.loan];
    var rent = recipient[curCols.rent];
    var nssf = recipient[curCols.nssf];
    var net = recipient[curCols.net];
    var totalDed = recipient[curCols.totalded];

    var templateid = setTemplateId(); // get template file id
    if(templateid == ""){
      Browser.msgBox("No template has been selected. Please select the correct template");
      return;
    }

    var docName = "Report details - " + staffNumber;
    //if email address is not empty do all the cool stuff like sending the data
    if(emailAddress != ""){
      var copyDoc = DocsList.getFileById(templateid).makeCopy(docName);      
      copyDoc.addToFolder(monthFolder);


      var docid = copyDoc.getId();
      Logger.log("Document ID " + docid);

      var doc = DocumentApp.openById(docid);

      //set permissions for the doc
      Logger.log("permission to view doc " + docid + " assigned to " + emailAddress);
      doc.addViewer(emailAddress);
      var docURL = doc.getUrl();
      var body = doc.getActiveSection();
      body.replaceText("%MONTH%", payMonth);
      body.replaceText("%YEAR%", payrollYear);
      body.replaceText("%STAFFNAME%", staffName);
      body.replaceText("%PAYE%", paye);  
      body.replaceText("%OVERTIME%", overtime); 
      body.replaceText("%GROSS%", grossPay);  
      body.replaceText("%NSSF%", nssf); 
      body.replaceText("%MUBASA%", mubasa);  
      body.replaceText("%NET%", net);  
      body.replaceText("%ARREARS%", arrears); 
      body.replaceText("%TOTALDED%", totalDed); 
      body.replaceText("%LOAN%", loan);
      body.replaceText("%RENT%", rent);
      body.replaceText("%ADMIN%", adminAllowance);
      body.replaceText("%RESP%", respAllowance);
      body.replaceText('%TOPUP%', topUpAllowance);

      //email message

      doc.saveAndClose();


       Logger.log("Sending email to " + emailAddress + " at " + hours + ":" + minutes);
      try{
         MailApp.sendEmail(emailAddress, 
                      subject, "", 
                      {htmlBody: message
                       },
                       attachment: docName,
                       name: "Report"
                      });
      }catch(e){
       Logger.log(e); 
      }

    }else{
         Logger.log("no email address found for staff member " + staffName);                  

        }


  });


}

I’d really appreciate some pointers on how I can optimize this script inorder for it not to timeout.

One Response to “Script times out in a loop containing 750 rows and 16 columns”

  1. I’d suggest you modify your script to process say 75 or 100 items at a time, keep track where you are in the script properties (or somewhere else) and continue a moment later until it has processed the 750 items.
    This should be started on a timer trigger every 5 minutes or so.

    when everything is sent then shut down the trigger and restart it when you need. Triggers can be easily set programmatically.

Leave a Reply