Organizing parallel streams of server calls with google.script.run promises

In Organizing asynchronous calls to google.script.run I showed how to avoid spaghetti with google.script.run callbacks to server side functions that needed to run sequentially. You should take a look at that first if you are unfamiliar with promises or google.script.run. One of the useful things you can do with HTMLService is to provoke multiple server side calls in parallel (since each server call is a separate instance). Using callbacks could get messy, so in this example I'll use Promises again.

The task is to, in parallel, get the names sheets in a number of spreadsheets, and then get the heading row from each. In a real life example, you might use the names of the sheets to construct a series of drop downs for the user to select from.

Server side functions

We can use the same server side functions as in Organizing asynchronous calls to google.script.run

Get the names of all the sheets in a book
function getSheetsInBook (id) {

  return SpreadsheetApp
  .openById(id)
  .getSheets()
  .map(function (d) {
    return d.getName();
  });
}

Get a single row of data from a given sheet
// get a single row of data
function getDataFromRow (id , sheetName, rowOffset) {

  return SpreadsheetApp
  .openById(id)
  .getSheetByName(sheetName)
  .getDataRange()
  .offset(rowOffset, 0, 1)
  .getValues()[0];
}

Client side calls


Here's the final function, driven by an array of spreadsheet ids. 
    var ids = [
      '1wC0zA-CkBu78VbYWZ4tMe0iVj5Buu6dJegXvD1DhnZk',
      '1T4ZF2ChuOYiDf2eExDSeON2TiFRpsMQYWF674rDAz14'
    ];
     
    function getMultipleSheets() {
      
      return Promise.all (ids.map (function (id,i) {
        return promiseRun ('getSheetsInBook' , id)
        .then (function (result) {
          showResult (result, "sheets",i);
          return promiseRun ('getDataFromRow',  id , result[1] , 0);
        })
        .then (function (result) {
          showResult (result, "headings",i);
        });
        
      }));
      
    }

Promise.all is a function that takes an array of promises as an argument, and returns a new promise which consolidates all the promises in the array and their results. This promise is resolved when EACH of the promises it is handling is resolved. That means I can construct an array of chained promised that sequentially get the sheet list, pick one, then get the heading row. Each set of chained promises will execute simultaneously so we get the benefit of parallel running.


Putting it together


I covered the promiseRun function in Organizing asynchronous calls to google.script.run
    // using promises instead
    function promiseRun (func) {
      
      // this is a trick to convert the arguments array into an array, and drop the first one
      var runArgs = Array.prototype.slice.call(arguments).slice(1);
      
      return new Promise (function (resolve, reject) {
        google.script.run
        .withSuccessHandler (function (result) {
          resolve (result);
        })
        .withFailureHandler (function (error) {
          reject (error);
        })
        [func].apply (this , runArgs) ;
        
      })
    
    }

I'll associate the getMultiple sheets function with a button, and report a finish message or an error message if anything went wrong
      prom.addEventListener ("click" , function (e) {
        getMultipleSheets()
        .then (function () {
          showError ("all done by promises");
        })
        .catch (function(error) {
          showError (error);
        });
      });


and here's the result



You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.




For more like this, see Google Apps Scripts snippets. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available. 





Comments