Picking up where the script ended when time limit is reached

Question

I'm trying to implement the solution that Anton Soradoi proposed on 12/22/11 to enable a mail merge script that will exceed its maximum execution time terminate gracefully, wait a period of time, then pick up where it left off and continue doing so until its run is complete. Here is the link to the referenced post.

The script runs fine for the aloted time (5 min), then throws a "Execution failed: Invalid argument: value (line 80). Also, I'm not sure what the "else" part of the script that Anton Soradoi discussed is supposed to do (run my menuItem1 function again?). I feel like I'm pretty close, and any help would be greatly appreciated. My code is below:

//Creates the custom menu in the spreadsheet "Run Script"
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Script')
      .addItem('Create Certs', 'menuItem1')
      .addToUi();
}//Ends the custom menu in the spreadsheet

//Runs the menuItem 1 operation (Create Certs)
function menuItem1() {
//Defines the start row and calculates the number of rows to be processed
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = Browser.inputBox("Enter Start Row");
  var endRow = Browser.inputBox("Enter End Row");
  var numRows = (endRow - startRow) + 1; //this is the row height
  var dataRange = sheet.getRange(startRow, 1, numRows, 7);
  var counter =0;   
  var data = dataRange.getValues();   
  var templateDoc = DriveApp.getFileById("1baxSUxfSdzcVheR3Y2qgieWeSAqNybPfWct1913uRIc");   
  var templateCopy = templateDoc.makeCopy();
  var templateCopyId = templateCopy.getId();
  var dateOld;   
  var courseOld;   
  var fullNameOld;
  var mailFrom = GmailApp.getAliases()
  var team = "NWC Online PME Help Desk"
  var startTime= (new Date()).getTime();
  
for (var i = 0; i < data.length; ++i) {
        var doc = DocumentApp.openById(templateCopyId);
        var body = doc.getActiveSection();
        var row = data[i];
        var date = row[0];
        var nic = row[1];
        var course = row[2];
        var lastname = row[3];
        var firstname = row[4];
        var middle = row[5]
        var email = row[6];
        var subjectTxt = "NWC Online PME Course Certificate";
        var fullBody = "PME COURSE COMPLETION CERTIFICATE" + "\n\n";
           fullBody += "Your course completion certificate is attached." + "\n\n";
           fullBody += "NOTES:" + "\n";
           fullBody += "1. DO NOT telephone NWC to resolve PME certificate issues, email our Help Desk: pmecerthelp@usnwc.edu." + "\n";
           fullBody += "2. NWC does NOT mail hardcopy certificates." + "\n";
           fullBody += "3. NWC does not award certificates for the SNCO JPME courses." + "\n";
           fullBody += "4. NWC course completion certificates are not automatically entered into your electronic training or service records." + "\n\n";
           fullBody += "Regards," + "\n\n";
           fullBody += "U.S. Naval War College Online PME Program Team"+ "\n\n";
           fullBody += "Learn more about NWC's Online PME Program at the link below:" + "\n";
           fullBody += "http://www.usnwc.edu/Academics/College-of-Distance-Education/PME-(1).aspx" + "\n";
        var fullName = firstname+' '+middle+''+lastname
        var fdate = Utilities.formatDate(new Date(date), "UTC", "d MMMM yyyy"); //converts UTC date
           if(counter ==0){
             body.replaceText('fullName',fullName);  
             body.replaceText('course', course);
             body.replaceText('date', fdate); 
        }//Ends the if condition
           else {   
             body.replaceText(fullNameOld,fullName);
             body.replaceText(courseOld, course);
             body.replaceText(dateOld, fdate); 
        }//Ends the else condition
           dateOld = fdate; 
           courseOld = course;
           fullNameOld = fullName;
           counter ++

   doc.saveAndClose();
      var attachment = doc.getAs('application/pdf');
      GmailApp.sendEmail(email, subjectTxt, fullBody, {name: team, attachments: attachment, from: mailFrom[1]});
 
      var scriptProperties = PropertiesService.getScriptProperties();
      var newStartRow= scriptProperties.getProperty('row');

   for(var ii = newStartRow; ii <= data.length; ii++) {
      var currTime = (new Date()).getTime();
    
    if(currTime - startTime >= 300000) {
      scriptProperties.setProperty("row", ii);
      ScriptApp.newTrigger("menuItem1")
               .timeBased()
               .at(new Date(currTime+30000))
               .create();
      break;
    }//Ends the if loop
  }//Ends the second for loop

   }//Ends the first for loop 
}//Ends menuItem1

Jack Brown · Answer 1 · 2017-04-08T14:06:45.897

The below code should do the trick, you can not use the code as is. Please read below:

//Creates the custom menu in the spreadsheet "Run Script"
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Script')
      .addItem('Create Certs', 'menuItem1')
      .addToUi();
}//Ends the custom menu in the spreadsheet

//Runs the menuItem 1 operation (Create Certs)
function menuItem1() {
//Defines the start row and calculates the number of rows to be processed
  var sheet = SpreadsheetApp.getActiveSheet();
  var scriptProperties = PropertiesService.getScriptProperties();
  var startRow= scriptProperties.getProperty('StartRow');
  var endRow = scriptProperties.getProperty('EndRow');
  // Check to see if any property called startRow is present
  if (startRow == null || endRow == null){                        //If not present ask for the values from user
  startRow = Number(Browser.inputBox("Enter Start Row"));
  endRow = Number(Browser.inputBox("Enter End Row"));
  scriptProperties.setProperty("EndRow", endRow)
  } else {                                     // if present ues those values for this run 
   startRow = Number(startRow)                 // Convert String to numbers
   endRow = Number(endRow)

  }

  var numRows = (endRow - startRow) + 1; //this is the row height
  var dataRange = sheet.getRange(startRow, 1, numRows, 7);
  var counter =0;   
  var data = dataRange.getValues();   
  var templateDoc = DriveApp.getFileById("1baxSUxfSdzcVheR3Y2qgieWeSAqNybPfWct1913uRIc");   
  var templateCopy = templateDoc.makeCopy();
  var templateCopyId = templateCopy.getId();
  var dateOld;   
  var courseOld;   
  var fullNameOld;
  var mailFrom = GmailApp.getAliases()
  var team = "NWC Online PME Help Desk"
  var startTime= (new Date()).getTime();             //set Start time

for (var i = 0; i < data.length; ++i) {
        var doc = DocumentApp.openById(templateCopyId);
        var body = doc.getActiveSection();
        var row = data[i];
        var date = row[0];
        var nic = row[1];
        var course = row[2];
        var lastname = row[3];
        var firstname = row[4];
        var middle = row[5]
        var email = row[6];
        var subjectTxt = "NWC Online PME Course Certificate";
        var fullBody = "PME COURSE COMPLETION CERTIFICATE" + "\n\n";
           fullBody += "Your course completion certificate is attached." + "\n\n";
           fullBody += "NOTES:" + "\n";
           fullBody += "1. DO NOT telephone NWC to resolve PME certificate issues, email our Help Desk: pmecerthelp@usnwc.edu." + "\n";
           fullBody += "2. NWC does NOT mail hardcopy certificates." + "\n";
           fullBody += "3. NWC does not award certificates for the SNCO JPME courses." + "\n";
           fullBody += "4. NWC course completion certificates are not automatically entered into your electronic training or service records." + "\n\n";
           fullBody += "Regards," + "\n\n";
           fullBody += "U.S. Naval War College Online PME Program Team"+ "\n\n";
           fullBody += "Learn more about NWC's Online PME Program at the link below:" + "\n";
           fullBody += "http://www.usnwc.edu/Academics/College-of-Distance-Education/PME-(1).aspx" + "\n";
        var fullName = firstname+' '+middle+''+lastname
        var fdate = Utilities.formatDate(new Date(date), "UTC", "d MMMM yyyy"); //converts UTC date
           if(counter ==0){
             body.replaceText('fullName',fullName);  
             body.replaceText('course', course);
             body.replaceText('date', fdate); 
        }//Ends the if condition
           else {   
             body.replaceText(fullNameOld,fullName);
             body.replaceText(courseOld, course);
             body.replaceText(dateOld, fdate); 
        }//Ends the else condition
           dateOld = fdate; 
           courseOld = course;
           fullNameOld = fullName;
           counter ++

   doc.saveAndClose();
      var attachment = doc.getAs('application/pdf');
     //GmailApp.sendEmail(email, subjectTxt, fullBody, {name: team, attachments: attachment, from: mailFrom[1]});
     Utilities.sleep(30000)


    var currTime = (new Date()).getTime();

    if(currTime - startTime >= 240000) {                   //Check if the script run is over 4minutes , at 5 min the excution might as well might have been terminated
      scriptProperties.setProperty("StartRow", startRow + i+1);   //The new start, just number of iteration done plus 1 to start from row after that 
      ScriptApp.newTrigger("menuItem1")
               .timeBased() 
               .at(new Date(currTime+30000))               //restart in 30 secs! 
               .create();
      Logger.log("Last email sent to: " + email)
      Logger.log("Next Run start at row: " + startRow + i+1)
      return;                                // End current run. 
    }//Ends the if Block


   }//Ends the first for loop 
}//Ends menu

The way this works is to use the scriptProperties to store your lastRow that was processed and your endRow. If it cannot find those values it will ask the user to enter it!

var scriptProperties = PropertiesService.getScriptProperties();
  var startRow= scriptProperties.getProperty('StartRow');
  var endRow = scriptProperties.getProperty('EndRow');
  // Check to see if any property called startRow is present
  if (startRow == null || endRow == null){                        //If not present ask for the values from user
  startRow = Browser.inputBox("Enter Start Row");
  endRow = Browser.inputBox("Enter End Row");
  scriptProperties.setProperty("EndRow", endRow)
  } else {                                     // if present ues those values for this run 
   startRow = Number(startRow)                 // Convert String to numbers
   endRow = Number(endRow)
  }

The below code will check to see if it has gone past the 4-minute mark if so, set up a time trigger and modify the startrow property to the new row after the last processed one. Then exit the function using return.

var currTime = (new Date()).getTime();

    if(currTime - startTime >= 240000) {                   //Check if the script run is over 4minutes , at 5 min the excution might as well might have been terminated
      scriptProperties.setProperty("StartRow", startRow + i+1);   //The new start, just number of iteration done plus 1 to start from row after that 
      ScriptApp.newTrigger("menuItem1")
               .timeBased() 
               .at(new Date(currTime+30000))               //restart in 30 secs! 
               .create();
      Logger.log("Last email sent to: " + email)
      Logger.log("Next Run start at row: " + startRow + i+1)
      return;                                // End current run. 
    }//Ends the if Block

Debugging: You will notice these lines near your GmailApp Lines:

//GmailApp.sendEmail(email, subjectTxt, fullBody, {name: team, attachments: attachment, from: mailFrom[1]});
     Utilities.sleep(30000)

Commented out your GamilApp line and instead put a sleep for 30 seconds, this is will help test the code. Once you run the code find the execution transcript under "View". And find these values:

Logger.log("Last email sent to: " + email)
Logger.log("Next Run start at row: " + startRow + i+1)

That way you can manually match the last email sent and next startRow to make sure everything works fine before you go live.

Edit

Finally, to reset your script properties if you run into any issues, run the below function.

function resetScript(){
var scriptProperties = PropertiesService.getScriptProperties();
Logger.log(scriptProperties.getProperties())
scriptProperties.deleteAllProperties()
}

Hope that helps

Could you point out which line is 28, and what is the exact error? — Jack Brown, Apr 08 '17 at 13:43
Thanks so much for your help. I think I understand what you are saying. While debugging, I ran into an issue where the script is throwing an error at line 28. When I entered the startRow as 3 and the endRow as 10, the Sheet.getRange is reporting the values as ([371, 1, -360, 7]), which is an invalid range. I'm not sure how that translation is happening. Also, when I attempt to run the script again, it reports: The coordinates or dimensions of the range are invalid. This tells me that the erroneous range data persists in the scriptProperties, so I will need to purge that data. — Steve P, Apr 08 '17 at 13:50
Line 28 reads: var dataRange = sheet.getRange(startRow, 1, numRows, 7); — Steve P, Apr 08 '17 at 13:51
Error reads (from the Execution Transcript): Execution failed: The coordinates or dimensions of the range are invalid. (line 28, file "Cert Merge 10") [0.168 seconds total runtime] — Steve P, Apr 08 '17 at 13:52
I have added a code to clear properties and made a small edit to the code. Run the resetScript() then run the modified code — Jack Brown, Apr 08 '17 at 14:12
I'm still at a loss for what's causing Sheet.getRange([371, 1, -360, 7]) to return those values when I input 3 and 10. Clearing the scriptProperties store had no effect as the script returns the same error. — Steve P, Apr 08 '17 at 14:36
Ok then run the code in debug mode, click on line number 28. A red dot will mark the line, then click on the bug button on the toolbar. — Jack Brown, Apr 08 '17 at 14:41
Lastly, if all else fails, create a new spreadsheet, add your code there and try to replicate the error and share that sheet here will edit privileges. I will take a look at and sheet whats going on... Remember to remove any data that could violate any Ferpa rules! — Jack Brown, Apr 08 '17 at 14:50
[Here](https://docs.google.com/spreadsheets/d/1sowe0piKdIeutEPoHT03GnkutMnDFiOe6ouX4Lw0AGI/edit?usp=sharing) is the link. You should be able to edit. All of the data in the spreadsheet is randomized, so it should be okay. — Steve P, Apr 08 '17 at 16:39

Steve P · Answer 2 · 2017-04-09T22:23:50.973

Working with Jack Brown for most of the day, here is the final answer:

//Creates the custom menu in the spreadsheet "Run Script"
function onOpen() {
   var ui = SpreadsheetApp.getUi();
   ui.createMenu('Script')
      .addItem('Create Certs', 'menuItem1')
      .addToUi();
}//Ends the custom menu in the spreadsheet

//Runs the menuItem 1 operation (Create Certs)
function menuItem1() { //Defines the start row and calculates the number of rows to be processed
   var sheet = SpreadsheetApp.getActiveSheet();
   var scriptProperties = PropertiesService.getScriptProperties(); // starts the Script Properties service for storing the last row completed
   var startRow= scriptProperties.getProperty('StartRow');
   var endRow = scriptProperties.getProperty('EndRow');

// Check to see if any property called startRow is present
   if (startRow == null || endRow == null){ //If not present ask for the startRow and endRow values from user
      startRow = Number(Browser.inputBox("Enter Start Row"));
      endRow = Number(Browser.inputBox("Enter End Row"));
      scriptProperties.setProperty("EndRow", endRow)
   }// ends the if condition

   else {  // if present ues those values for this run 
      startRow = Number(startRow) // Convert startRow string to a number
      endRow = Number(endRow)     // Convert endRow string to a number
   } //end the else condition

   var numRows = (endRow - startRow) + 1; //this is the row height

   if(numRows < 1){
      scriptProperties.deleteAllProperties()
      return; 
   } //ends the if condition

   var dataRange = sheet.getRange(startRow, 1, numRows, 7);
   var counter =0;   
   var data = dataRange.getValues();   
   var templateDoc = DriveApp.getFileById("1baxSUxfSdzcVheR3Y2qgieWeSAqNybPfWct1913uRIc");   
   var templateCopy = templateDoc.makeCopy();
   var templateCopyId = templateCopy.getId();
   var dateOld;   
   var courseOld;   
   var fullNameOld;
   var mailFrom = GmailApp.getAliases() //gets the alias address the email is sent from
   var team = "NWC Online PME Help Desk"
   var startTime= (new Date()).getTime(); //sets the script Start time

   for (var i = 0; i < data.length; ++i) { // Populates the certificate with row data and builds the custom email
      var doc = DocumentApp.openById(templateCopyId);
      var body = doc.getActiveSection();
      var row = data[i]; // specifies the active row [i]
      var date = row[0]; // date on the cert for row [i] (column 0)
      var nic = row[1]; // course nickname for row [i] (column 1)
      var course = row[2]; // course name for row [i] (column 2)
      var lastname = row[3]; // learner's last name for row [i] (column 3)
      var firstname = row[4]; // learner's first name for row [i] (column 4)
      var middle = row[5]; // learner's middle initial for row [i] (column 5)
      var email = row[6]; // learner's email address for row [i] (column 6)
      var subjectTxt = "NWC Online PME Course Certificate"; // email's subject line
      var fullBody = "PME COURSE COMPLETION CERTIFICATE" + "\n\n"; //email's header
         fullBody += "Your course completion certificate is attached." + "\n\n";
         fullBody += "NOTES:" + "\n";
         fullBody += "1. DO NOT telephone NWC to resolve PME certificate issues, email our Help Desk: pmecerthelp@usnwc.edu." + "\n";
         fullBody += "2. NWC does NOT mail hardcopy certificates." + "\n";
         fullBody += "3. NWC does not award certificates for the SNCO JPME courses." + "\n";
         fullBody += "4. NWC course completion certificates are not automatically entered into your electronic training or service records." + "\n\n";
         fullBody += "Regards," + "\n\n";
         fullBody += "U.S. Naval War College Online PME Program Team"+ "\n\n";
         fullBody += "Learn more about NWC's Online PME Program at the link below:" + "\n";
         fullBody += "http://www.usnwc.edu/Academics/College-of-Distance-Education/PME-(1).aspx" + "\n";
      var fullName = firstname+' '+middle+''+lastname 
      var fdate = Utilities.formatDate(new Date(date), "UTC", "d MMMM yyyy"); //converts the UTC date to the desired format

   if(counter == 0){
      body.replaceText('fullName',fullName);  
      body.replaceText('course', course);
      body.replaceText('date', fdate); 
   }//Ends the if condition
   
   else {   
      body.replaceText(fullNameOld,fullName);
      body.replaceText(courseOld, course);
      body.replaceText(dateOld, fdate); 
   }//Ends the else condition

       dateOld = fdate; 
       courseOld = course;
       fullNameOld = fullName;
       counter ++

   doc.saveAndClose(); //creates the learner's certificate

   var attachment = doc.getAs('application/pdf'); // converts the learner's certificate from a doc file to a PDF

   GmailApp.sendEmail(email, subjectTxt, fullBody, {name: team, attachments: attachment, from: mailFrom[1]}); // sends the learner's certificate as an attachment to the email

// Utilities.sleep(2500) // When sleeping the GmailApp line, enables testing of the script without impact on Gmail's email quota

   sheet.getRange(startRow + i , 8).setValue("Done at: "+ new Date()) // puts the sent date/time in column 8 of the spreadsheet
   var currTime = (new Date()).getTime(); //gets the current date/time for the script to determine run time

   if(currTime - startTime >= 240000) { //Checks if the script run is over 4 minutes
      clearTriggers() //clears the script's triggers
      scriptProperties.setProperty("StartRow", startRow + i+1); // The new startRow, just number of iterations done + 1 to start with the next row 
      ScriptApp.newTrigger("menuItem1")
         .timeBased() 
         .at(new Date(currTime+60000)) //restarts the script in 60 sec to finish cleanly
         .create();
             Logger.log("Last email sent to: " + email)
             Logger.log("Next Run start at row: " + (startRow + i+1))
      return; // Ends the current run. 
   }//Ends the if condition

   }//Ends the first for loop 

//  Logger.log("This shouldnt run") was used in testing, not needed now.
   DriveApp.getFileById(templateCopyId).setTrashed(true); // deletes the working copy of the document template
   clearTriggers() // function defined in Reset Script
   resetScript() // function defined in Reset Script

}//Ends menuItem1

//Comments
//Jagannathan
//Ok this seems to work, also note i modified this:
//.at(new Date(currTime+60000))
//To run after minute, the trigger are not sensititve to 30 sec, so they where not triggering properply.
//Again copy this as is run it your spreadsheet when you get a chance.
//Once you are satisfied with the mock run, remove the comment form Gmail and comment sleep and run it
//Leave the sheet.getRange() line to so that you keep on eye on how it triggers. And in case it fails you know where to start from.

And here is the additional script file that defines resetScript() and clearTriggers()

function resetScript(){
var scriptProperties = PropertiesService.getScriptProperties();
Logger.log(scriptProperties.getProperties())
scriptProperties.deleteAllProperties()
}

function clearTriggers(){
 var triggers = ScriptApp.getUserTriggers(SpreadsheetApp.getActive())
 for (var i =0 ; i< triggers.length ; i++)
 {
   if(triggers[i].getHandlerFunction() == "menuItem1") {
     ScriptApp.deleteTrigger(triggers[i]) 
   }
 }
 
}

Picking up where the script ended when time limit is reached

2 Answers2