javascript - Google Apps Script, fastest way to retrieve data from external spreadsheets -

i'm trying load data multiple spreadsheets(~100) single spreadsheet, when try script times out. appears opening each spreadsheet takes long time. there way can speed or work around?

here's use open each spreadsheet

// set current spreadsheet master , current date. var master = spreadsheetapp.getactive(); var mastersheet = master.getsheetbyname('master'); var users = master.geteditors(); var today = new date();  // adds menu spreadsheet function onopen() {   var spreadsheet = spreadsheetapp.getactivespreadsheet();   var entries = [{     name : "update data",     functionname : "retrievepartnerdata"   }];   spreadsheet.addmenu("submissions menu", entries); };   // first data partner sheets function retrievepartnerdata() {    mastersheet.getrange(2, 1, mastersheet.getlastrow(), mastersheet.getlastcolumn()).clear(); //clear our master sheet aka sheet   mastersheet.hidesheet();    //get's promo outline internal sheet , store it's values in promorange array   var promorange = master.getsheetbyname("promotional outline").getrange("a1:z100").getvalues();    var sheetpartnerarray = [];    // row array contaings url's external spreadsheets   var row = master.getsheetbyname('partner sheet collection').getrange("b:b").getvalues();    row.map(function(e){     if(e[0] != "" && e[0] != "url"){       var ss = spreadsheetapp.openbyurl(e[0]);        var studioname = ss.getsheets()[0].getrange("a1").getvalue();        //updates promotional outline sheet in partner sheet       var promosheet = ss.getsheetbyname("promotional outline");          promosheet.getrange("a1:z100").setvalues(promorange);        //hide columns k z       promosheet.hidecolumns(11,4);        var sheet = ss.getsheets();        sheet.map(function(f){         var sheetname = f.getsheetname(); // retrieves sheetname of each sheet          var lastrow = 0;         if(f.getlastrow() == 1) {           lastrow = 1;         } else {           lastrow = f.getlastrow() - 1;         }         var datarange = f.getrange(2, 1, lastrow, f.getlastcolumn());         var data = datarange.getvalues();          (var j = 0; j < data.length; j++) {           if (data[j][0].length != 0 && (data[j][5] > today || data[j][5] == "[please enter]")) { // check if promo end date after current day             var sheetrow = data[j];             sheetrow[1] = studioname;             sheetrow.unshift(sheetname); //adds country beginning of row using sheet name spreadsheets              sheetpartnerarray.push(sheetrow);           }         }       })     }   })   mastersheet.getrange(2, 1, sheetpartnerarray.length , sheetpartnerarray[0].length ).setvalues(sheetpartnerarray); }; 

thanks!

one common approach set trigger restart big job @ time in future (just beyond maximum execution time). big job as can (or stops nicely @ logical point), , either gets killed or quietly exits. either way, gets restarted shortly after, , resumes work.

patt0 has taken idea elegant end, providing library can add script. few adaptations, should able turn retrievepartnerdata() batch job.

since have menu already, , retrievepartnerdata() involves iterating on many spreadsheets, have opportunity break barrier way, completing each iteration (or better, set of iterations) in separate server script instance.

this technique appears in what happens when "sleep" in gas ? (execution time limit workaround)

and there similar in how poll google doc add-on. in answer, ui client uses timer repeatedly execute server function. here, though, iterations work-based, rather time-based. client-side function, running in browser, keep calling server until there no more work done:

  /**    * call server-side 'serverprocess' function until there's no more work.    */   function dispatchwork(){     if (window.runningprocess) {     }     google.script.run      .withsuccesshandler(                //<<<< if last call        // after each interval, decide next        function(workis) {          if (!workis.done) {             //<<<<< check if we're done            // there's more work do, keep going.            dispatchwork();          }          else {            // done. stop timer            stoptimer();            $('#start-process').hide();            $("#final").html(' <h2>processing complete!</h2>');          }        })      .withfailurehandler(        function(msg, element) {         //<<<<<< if error          showerror(msg, $('#button-bar'));          element.disabled = false;        })      .serverprocess();                  //<<<<< call server function   }; 

in case, first need refactor retrievepartnerdata() can called client process single spreadsheet (or set of them). no doubt have put considerable time making map loop work cleanly, , taking apart painful, worth it.

the following spreadsheet-bound script can adapted use. consists of menu item, simple ui, , scripts on client (javascript + jquery) , server (google apps script), control work in intervals.

the control data in "sourcesheets" tab, , results copied "master".

code.gs

var properties = propertiesservice.getscriptproperties();  // adds menu spreadsheet function onopen() {   var spreadsheet = spreadsheetapp.getactivespreadsheet();   var entries = [{     name : "update data",     functionname : "updatedata"   }];   spreadsheet.addmenu("big job", entries); };  /**  * presents ui user.  */ function updatedata () {   var userinterface = htmlservice.createhtmloutputfromfile("conductor")                                  .setheight(150)                                  .setwidth(250)                                  .settitle("what 5 minute limit?");   var ss = spreadsheetapp.getactivespreadsheet();   ss.show(userinterface) }  /**  * called client, function performs server work in  * intervals. exit when processing time has exceeded max_interval,  * 3.5 minutes. every time function exits, client provided  * current status object, done=true when work queue has  * been emptied.  *  * @returns {object}      status { done: boolean }  */ function serverprocess() {   var max_interval = (3.5 * 60);  // minutes * seconds   var intervalstart = math.round(new date() / 1000);    // persisted work queue, if there 1   var queueprop = properties.getproperty('work-queue') || '[]';   var queue = json.parse(queueprop);    if (queue.length == 0) {     queue = preparework();   }    // work interval, until we're out of time   while ((math.round(new date() / 1000) - intervalstart) < max_interval) {     if (queue.length > 0) {       var ssid = queue.shift();       processsheet(ssid);       properties.setproperty('work-queue', json.stringify(queue));     }     else break;   }    // report result of interval client   var result = { done : (queue.length == 0) };   return( result ); }  /**  * set work queue & clear master sheet, ready import data source sheets.  *  * @return {string[]}             work queue  */ function preparework() {   // no work yet, set work   var ss = spreadsheetapp.getactive();   var mastersheet = ss.getsheetbyname('master');   var rowstodelete = mastersheet.getmaxrows()-1;   if (rowstodelete)     mastersheet.deleterows(2, rowstodelete);  //clear our master sheet aka sheet    // build work queue   var queue = [];   var data = ss.getsheetbyname('sourcesheets')      // data                .getdatarange().getvalues();   var headers = data.splice(0,1)[0];                // take headers off   var ssidcol = headers.indexof('spreadsheet id');  // find column work   (var i=0; i<data.length; i++) {     queue.push(data[i][ssidcol]);                   // queue work   }   // persist work queue scriptproperty   properties.setproperty('work-queue', json.stringify(queue));   return queue; }  /**  * whatever work item need. in example, we'll import data  * source sheet , append our master.  *  * @param {string}  ssid       source spreadsheet id  */ function processsheet(ssid) {   var mastersheet = spreadsheetapp.getactive().getsheetbyname('master');   var sourcesheet = spreadsheetapp.openbyid(ssid).getsheetbyname('sheet1');    utilities.sleep(60000);   // don't want this... wasting time.    var masterlastrow = mastersheet.getlastrow();   var sourcerows = sourcesheet.getlastrow();   mastersheet.insertrowsafter(mastersheet.getlastrow(), sourcesheet.getlastrow());    var sourcedata = sourcesheet.getdatarange().getvalues().slice(1);   var destrange = mastersheet.getrange(masterlastrow+1, 1, sourcedata.length, sourcedata[0].length);   destrange.setvalues(sourcedata); } 

conductor.html

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css"> <!-- css package above applies google styling buttons , other elements. -->   <div id="form-div" class="sidebar branding-below">   <span id="final"></span>   <form>     <div class="block" id="button-bar">       <button class="blue" id="start-process">start processing</button>     </div>   </form> </div>  <div class="bottom">   elapsed processing time: <span id="elapsed">--:--:--</span> </div>  <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"> </script>  <script>   /**    * on document load, assign click handlers button(s), add    * elements should start hidden (avoids "flashing"), ,    * start polling document selections.    */   $(function() {     // assign click handler(s)     $('#start-process').click(startprocess);    });    /**    * call server-side 'serverprocess' function until there's no more work.    */   function dispatchwork(){     if (window.runningprocess) {     }     google.script.run      .withsuccesshandler(        // after each interval, decide next        function(workis) {          if (!workis.done) {            // there's more work do, keep going.            dispatchwork();          }          else {            // done. stop timer            stoptimer();            $('#start-process').hide();            $("#final").html(' <h2>processing complete!</h2>');          }        })      .withfailurehandler(        function(msg, element) {          showerror(msg, $('#button-bar'));          element.disabled = false;        })      .serverprocess();   };    /**    * runs server-side function retrieve    * selected text.    */   function startprocess() {     this.disabled = true;  // disable button     $('#error').remove();  // clear previous error messages, if     starttimer();          // start work timer, display user     window.runningprocess = true;     dispatchwork();        // start our work on server   }     // timer adapted http://codingforums.com/javascript-programming/159873-displaying-elapsed-time.html    /**     * kicks off tick function.     */   function starttimer( )   {     window.seconds = null;     window.ticker = null;     window.seconds = -1;     window.ticker = setinterval(tick, 1000);     tick( );   }    /**    * stop ticking    */   function stoptimer()   {     clearinterval(window.ticker);   }    /*    * updates timer display, between sleeps.    */   function tick( )   {     ++window.seconds;     var secs = window.seconds;     var hrs = math.floor( secs / 3600 );     secs %= 3600;     var mns = math.floor( secs / 60 );     secs %= 60;     var pretty = ( hrs < 10 ? "0" : "" ) + hrs                + ":" + ( mns < 10 ? "0" : "" ) + mns                + ":" + ( secs < 10 ? "0" : "" ) + secs;     $("#elapsed").text(pretty);   }    /**    * inserts div contains error message after given element.    *    * @param msg error message display.    * @param element element after display error.    */   function showerror(msg, element) {     var div = $('<div id="error" class="error">' + msg + '</div>');     $(element).after(div);   } </script>