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 dropdowns 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
