performance - Picking up where the script ended when time limit is reached -

i'm trying implement solution anton soradoi proposed on 12/22/11 enable mail merge script exceed maximum execution time terminate gracefully, wait period of time, pick left off , continue doing until run complete. here link referenced post.

the script runs fine aloted time (5 min), throws "execution failed: invalid argument: value (line 80). also, i'm not sure "else" part of script anton soradoi discussed supposed (run menuitem1 function again?). feel i'm pretty close, , appreciated. code below:

//creates custom menu in spreadsheet "run script"  function onopen() {    var ui = spreadsheetapp.getui();    ui.createmenu('script')        .additem('create certs', 'menuitem1')        .addtoui();  }//ends custom menu in spreadsheet    //runs menuitem 1 operation (create certs)  function menuitem1() {  //defines start row , calculates number of rows 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 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 desk"    var starttime= (new date()).gettime();      (var = 0; < 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 attached." + "\n\n";             fullbody += "notes:" + "\n";             fullbody += "1. not telephone nwc resolve pme certificate issues, email our desk: pmecerthelp@usnwc.edu." + "\n";             fullbody += "2. nwc not mail hardcopy certificates." + "\n";             fullbody += "3. nwc not award certificates snco jpme courses." + "\n";             fullbody += "4. nwc course completion certificates not automatically entered 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 nwc's online pme program @ 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 if condition             else {                  body.replacetext(fullnameold,fullname);               body.replacetext(courseold, course);               body.replacetext(dateold, fdate);           }//ends 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 if loop    }//ends second loop       }//ends first loop   }//ends menuitem1

the below code should trick, can not use code is. please read below:

//creates custom menu in spreadsheet "run script" function onopen() {   var ui = spreadsheetapp.getui();   ui.createmenu('script')       .additem('create certs', 'menuitem1')       .addtoui(); }//ends custom menu in spreadsheet  //runs menuitem 1 operation (create certs) function menuitem1() { //defines start row , calculates number of rows processed   var sheet = spreadsheetapp.getactivesheet();   var scriptproperties = propertiesservice.getscriptproperties();   var startrow= scriptproperties.getproperty('startrow');   var endrow = scriptproperties.getproperty('endrow');   // check see if property called startrow present   if (startrow == null || endrow == null){                        //if not present ask values user   startrow = number(browser.inputbox("enter start row"));   endrow = number(browser.inputbox("enter end row"));   scriptproperties.setproperty("endrow", endrow)   } else {                                     // if present ues values run     startrow = number(startrow)                 // convert string numbers    endrow = number(endrow)    }    var numrows = (endrow - startrow) + 1; //this 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 desk"   var starttime= (new date()).gettime();             //set start time  (var = 0; < 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 attached." + "\n\n";            fullbody += "notes:" + "\n";            fullbody += "1. not telephone nwc resolve pme certificate issues, email our desk: pmecerthelp@usnwc.edu." + "\n";            fullbody += "2. nwc not mail hardcopy certificates." + "\n";            fullbody += "3. nwc not award certificates snco jpme courses." + "\n";            fullbody += "4. nwc course completion certificates not automatically entered 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 nwc's online pme program @ 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 if condition            else {                 body.replacetext(fullnameold,fullname);              body.replacetext(courseold, course);              body.replacetext(dateold, fdate);          }//ends 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 script run on 4minutes , @ 5 min excution might might have been terminated       scriptproperties.setproperty("startrow", startrow + i+1);   //the new start, number of iteration done plus 1 start row after        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 @ row: " + startrow + i+1)       return;                                // end current run.      }//ends if block      }//ends first loop  }//ends menu 

the way works use scriptproperties store lastrow processed , endrow. if cannot find values ask user enter it!

var scriptproperties = propertiesservice.getscriptproperties();   var startrow= scriptproperties.getproperty('startrow');   var endrow = scriptproperties.getproperty('endrow');   // check see if property called startrow present   if (startrow == null || endrow == null){                        //if not present ask values user   startrow = browser.inputbox("enter start row");   endrow = browser.inputbox("enter end row");   scriptproperties.setproperty("endrow", endrow)   } else {                                     // if present ues values run     startrow = number(startrow)                 // convert string numbers    endrow = number(endrow)   } 

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

var currtime = (new date()).gettime();      if(currtime - starttime >= 240000) {                   //check if script run on 4minutes , @ 5 min excution might might have been terminated       scriptproperties.setproperty("startrow", startrow + i+1);   //the new start, number of iteration done plus 1 start row after        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 @ row: " + startrow + i+1)       return;                                // end current run.      }//ends if block 

debugging: notice these lines near gmailapp lines:

//gmailapp.sendemail(email, subjecttxt, fullbody, {name: team, attachments: attachment, from: mailfrom[1]});      utilities.sleep(30000) 

commented out gamilapp line , instead put sleep 30 seconds, test code. once run code find execution transcript under "view". , find these values:

logger.log("last email sent to: " + email) logger.log("next run start @ row: " + startrow + i+1) 

that way can manually match last email sent , next startrow make sure works fine before go live.

edit

finally, reset script properties if run issues, run below function.

function resetscript(){ var scriptproperties = propertiesservice.getscriptproperties(); logger.log(scriptproperties.getproperties()) scriptproperties.deleteallproperties() } 

hope helps