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 functionsWe 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 callsHere'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.
|
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. |