Running things in parallel using HTML service was a brief intro on how to run a number of things at once, orchestrating executing using Google Apps Script HTML service. Here's how to set it up. You'll need to run this as a container bound script, since we are going to visualize the activity in a spreadsheet sidebar. Orchestration profileThe action is controlled by an orchestration profile. Here's the one for the video in Running things in parallel using HTML service function demoProfile() { return [ [ { name: "a1", functionName:"functionA", skip:false, debug:true, options:{something:"a1run"} }, { name: "b1", functionName:"functionB", skip:false, options:{something:"b1run"} }, { name: "a2", functionName:"functionA", skip:false, options:{something:"a2run"} } ], [ { name:"reduction", skip: false, functionName:"reduceTheResults", options:{something:"reductions"} } ] ]; } Explanation
Here's the code for this demo - in fact it doesn't do anything - just demonstrates the principle. The first block - the map functions function functionA (options) { start = new Date().getTime(); //simulate some activity Utilities.sleep(Math.random()*10000); return ['some data from function A', options.something ] ; } function functionB (options) { start = new Date().getTime(); //simulate some activity Utilities.sleep(Math.random()*10000); return ['some data from function A', options.something ] ; } The second block - a reduce function. This is a very common operation - so the same reduce function can probably be re-used for most applications. Note that it get passed any options from its profile, along with all the data from each of the map operations executed in the first block. It's job is simply to consolidate each of these results into one./** * reduce the results from a previous mapping excercise * @param {object} options describes what to do * @param {object} mapResults this would contain results from a previous stage if present * @return {array.*} test data to pass on to next stage */ function reduceTheResults(options, mapResults) { // we'll have all the results here so consolidate var results = mapResults.reduce ( function (p,c) { (Array.isArray (c.results) ? c.results : [c.results]).forEach (function(d) { p.push (d); }) return p; },[]); return results; } Here's what a completed progress sidebar looks like. Note that each task shows how long it took to complete - This example shows that we got 23 seconds of compute time done in 10 seconds through parallel running. All the bars are green so nothing failed. You'll notice the bars changing colors at different stages of execution.A more complicated exampleIf you use Database abstraction with google apps script libraries from this site you'll know that they present a common interface to a number of back ends. This means that testing them against the same data set and queries is complicated and I usually hit quota limitations - so I have to break them up and so on. Using this method automates all that, runs all the tests at the same time, and also helps simulates multiuser access. Her's a video of a test of 5 back ends simultaneously. You'll notice that there were multiple map and reduce steps in this example. Here's what happened
In this example, we got 123 seconds worth of computing done in 56 seconds. You'll also notice that I also recorded one of the database update threads (SHEET) in action in the video as it used the in focus spreadsheets as its back end database. The only difference between the demo example and this one is the profile and the map modules. I used the same reduction function throughout. Here's the result of a chunkier test. This one would have failed if I'd done the operations sequentially as it breaks the Apps Script 6 minute quota, but with parallel running it all got done in a couple of minutes Here's the profile for running this. Most of the content is the various parameters for setting up different kind of database access. function dbProfile () { // need this for api keys var userStore = PropertiesService.getScriptProperties(); // these would run paralell database soak tests return [ [ { "name": "TEST DATA", "functionName": "prepareTheData", "skip": false, "options": { "scale": 20 } }, { "name": "TEST DATA", "functionName": "prepareTheData", "skip": false, "options": { "scale":20 } } ], [ { "name": "REDUCE TEST DATA", "functionName": "reduceTheResults", "skip": false, "options": {} } ], [ { "name": "SHEET", "functionName": "bigTest", "skip": false, "options": { "driver": "cDriverSheet", "parameters": { "siloid": "polymerdbab", "dbid": "1yTQFdN_O2nFb9obm7AHCTmPKpf5cwAd78uNQJiCcjPk", "peanut": "bruce", "disablecache": 1 } } }, { "name": "MEMORY", "functionName": "bigTest", "skip": false, "options": { "driver": "cDriverMemory", "parameters": { "siloid": "polymerdbab", "dbid": "memory", "peanut": "bruce", "disablecache": 1 } } }, { "name": "MONGOLAB", "functionName": "bigTest", "skip": false, "options": { "driver": "cDriverMongoLab", "parameters": { "siloid": "polymerdbab", "dbid": "xliberation", "peanut": "bruce", "disablecache": 1, "driverob": JSON.parse(userStore.getProperty('mongoLabKeys')) } } }, { "name": "PARSE", "functionName": "bigTest", "skip": false, "options": { "driver": "cDriverParse", "parameters": { "siloid": "polymerdbab", "dbid": "mp", "peanut": "bruce", "disablecache": 1, "driverob": JSON.parse(userStore.getProperty('parseKeys')) } } }, { "name": "DRIVE", "functionName": "bigTest", "skip": false, "options": { "driver": "cDriverDrive", "skip": false, "parameters": { "siloid": "polymerdbab.json", "dbid": "/scratch", "peanut": "bruce", "disablecache": 1 } } } ], [ { "name": "FINALREDUCTION", "functionName": "reduceTheResults", "skip": false, "options": {} } ], [ { "name": "LOG", "functionName": "logTheResults", "skip": false, "options": { "driver": "cDriverSheet", "clear": true, "parameters": { "siloid": "log", "dbid": "1yTQFdN_O2nFb9obm7AHCTmPKpf5cwAd78uNQJiCcjPk", "peanut": "bruce" } } } ] ]; } and the functions that are called block 0 /** * set up some test data to apply to all tests - is intended to be executed from htmlservice * @param {object} options describes what to do * @param {array.*} optStageResults this would contain results from a previous stage if present * @return {array.*} test data to pass on to next stage */ function prepareTheData (options, optStageResults) { return getSomeTestData (options.scale || 5 ); } block 1function reduceTheResults(options, mapResults) { // we'll have all the results here so consolidate var results = mapResults.reduce ( function (p,c) { (Array.isArray (c.results) ? c.results : [c.results]).forEach (function(d) { p.push (d); }) return p; },[]); return results; } block 2/** * do a database test * @param {object} options describes what to do * @param {object} testData this would contain results from a previous stage if present * @return {object} test data to pass on to next stage */ function bigTest ( options, testData) { //write the results to a log var handler = new cDbAbstraction.DbAbstraction ( eval(options.driver), options.parameters ); assert(handler.isHappy(), 'unable to get sheet handler','handler'); return testCases (handler,undefined, undefined, testData ? testData[0].results : null ); } block 3repeat the reduction function block 4 /** * Log the results of the orchestration * @param {object} options describes what to do * @param {object} reduceResults this would contain results from a previous stage if present * @return {object} test data to pass on to next stage */ function logTheResults (options,reduceResults) { var handler = new cDbAbstraction.DbAbstraction ( eval(options.driver), options.parameters ); assert(handler.isHappy(), 'unable to get handler',options.driver); if (options.clear) { var result = handler.remove(); if (result.handleCode < 0) { throw result.handleError; } } var result = handler.save(reduceResults[0].results); if (result.handleCode < 0) { throw result.handleError; } return reduceResults.results; } Setting up your own version of thisI recommend a structure like this Code.gs This is all about setting up the sidebar and kicking of execution. The only change you need to make here is to call the function that creates the profile for your job. var ADDONNAME = "async"; function onInstall() { onOpen(); } function onOpen() { SpreadsheetApp.getUi().createMenu('async') .addItem('async', 'showSidebar') .addToUi(); } /** * Shows a custom HTML user interface in a sidebar */ function showSidebar() { // kicking off the sidebar executes the orchestration libSidebar('asyncService',ADDONNAME, demoProfile () ); } /** * called onopen * @param {string} htmlName name of html file * @param {string} addonName name of addon for the sidebar title * @param {object} profile object */ function libSidebar (htmlName, addonName, profiles) { var html = HtmlService .createTemplateFromFile(htmlName) .evaluate() .getContent(); // add the function names html += "<script>\n" + "doSomeThings( " + JSON.stringify(profiles) + ");\n</script>"; return SpreadsheetApp.getUi().showSidebar( HtmlService.createTemplate(html).evaluate() .setSandboxMode(HtmlService.SandboxMode.NATIVE) .setTitle(addonName)); } /** * Returns the contents of an HTML file. * @param {string} file The name of the file to retrieve. * @return {string} The content of the file. */ function include (file) { return HtmlService.createTemplateFromFile(file).evaluate().getContent(); } profiles.gs These are the profiles for whatever you want to run. The demo profile looks like this. My dbProfile() example was shown earlier. function demoProfile() { return [ [ { name: "a1", functionName:"functionA", options:{something:"a1run"} }, { name: "b1", functionName:"functionB", options:{something:"b1run"} }, { name: "a2", functionName:"functionA", options:{something:"a2run"} } ], [ { name:"reduction", functionName:"reduceTheResults", options:{something:"reductions"} } ] ]; } process.gs App script functions that are referenced in your profile. For the demo example, you'll need the first 3 functions /** * reduce the results from a previous mapping excercise * @param {object} options describes what to do * @param {object} mapResults this would contain results from a previous stage if present * @return {array.*} test data to pass on to next stage */ function reduceTheResults(options, mapResults) { // we'll have all the results here so consolidate var results = mapResults.reduce ( function (p,c) { (Array.isArray (c.results) ? c.results : [c.results]).forEach (function(d) { p.push (d); }) return p; },[]); return results; } function functionA (options) { start = new Date().getTime(); //simulate some activity Utilities.sleep(Math.random()*10000); return ['some data from function A', options.something ] ; } function functionB (options) { start = new Date().getTime(); //simulate some activity Utilities.sleep(Math.random()*10000); return ['some data from function A', options.something ] ; } //--- below here are the functions specific to my db test profile /** * do a database test * @param {object} options describes what to do * @param {object} testData this would contain results from a previous stage if present * @return {object} test data to pass on to next stage */ function bigTest ( options, testData) { //write the results to a log var handler = new cDbAbstraction.DbAbstraction ( eval(options.driver), options.parameters ); assert(handler.isHappy(), 'unable to get sheet handler','handler'); return testCases (handler,undefined, undefined, testData ? testData[0].results : null ); } /** * Log the results of the orchestration * @param {object} options describes what to do * @param {object} reduceResults this would contain results from a previous stage if present * @return {object} test data to pass on to next stage */ function logTheResults (options,reduceResults) { var handler = new cDbAbstraction.DbAbstraction ( eval(options.driver), options.parameters ); assert(handler.isHappy(), 'unable to get handler',options.driver); Logger.log(reduceResults); if (options.clear) { var result = handler.remove(); if (result.handleCode < 0) { throw result.handleError; } } var result = handler.save(reduceResults[0].results); if (result.handleCode < 0) { throw result.handleError; } return reduceResults.results; } /** * set up some test data to apply to all tests - is intended to be executed from htmlservice * @param {object} options describes what to do * @param {array.*} optStageResults this would contain results from a previous stage if present * @return {array.*} test data to pass on to next stage */ function prepareTheData (options, optStageResults) { return getSomeTestData (options.scale || 5 ); } This is the sidebar template. You may want to play around with this, but the results and canvas divs are needed as are the two include references <link href='http://fonts.googleapis.com/css?family=Roboto' rel='stylesheet' type='text/css'> <style> .intro { font-family: 'Roboto', sans-serif; font-size: 14px; color:white; background-color:#bf360c; margin:4px; padding:4px; width:100%; } .error { font-family: 'Roboto', sans-serif; font-size: 10px; color:red; margin:4px; padding:4px; width:100%; } </style> <div style = "width:95%;"> <div class="intro">Script orchestration and parallel execution</div> <div id="results" class="error"></div> <canvas id="canvas" style="border:0px;" width="280" height="600"></canvas> </div> <?!= include('async.js') ?> <?!= include('canvas.js') ?> canvas.js.html This is the template Javascript for rendering the progress bars. You shouldn't need to change this at all unless you want to play with the colors or bar sizes. <script> var options = { BARHEIGHT:21, TEXTPX:12, BARCOLORS: { fail: '#E57373', success: '#81C784', run:'#FFEB3B', void:'#ECEFF1', anythingElse: '#263238', summary:'#FFEB3B', text:'Black' }, BARLENGTH:260, XSTART:10, YSTART:10, TEXTPAD:6, VERTICALSPACE:2 }; var canvas, cx , summaryBarTime = 0, summaryColor = options.BARCOLORS.summary; window.onload = function () { canvas = document.getElementById("canvas"); if (canvas.getContext) { cx = canvas.getContext('2d'); } }; /** * update the summary bar * @param {object} theGlobals contains profiles of whats running * @param {boolean} finished whether we are finished running */ function summaryBar (theGlobals, finished) { if (cx) { var now = new Date().getTime() ; if (summaryBarTime < now - theGlobals.startedAt) { summaryBarTime = (Math.floor((now - theGlobals.startedAt)/theGlobals.STEP) +1) * theGlobals.STEP; } // if we're finished, then readjust the progress bar to be 100% of time elapsed if (finished) { summaryBarTime = now - theGlobals.startedAt; if (summaryColor === options.BARCOLORS.summary) { summaryColor = options.BARCOLORS.success; } } recalibrate (theGlobals.startedAt,theGlobals.functions); } } /** * recalibrates all the bars and replots them * @param {number} startedAt a timestamp of when it all started * @param {array.object} functions the profiles of the functions to recalibrate */ function recalibrate (startedAt, functions) { var now = new Date().getTime() ; // plot progress of each function functions.forEach (function (d,i) { // but only if its started if (d.start) { var elapsed = d.end ? d.end - d.start : now - d.start; doBar ( options.YSTART + (i+1)*(options.BARHEIGHT + options.VERTICALSPACE) , options.XSTART, elapsed / summaryBarTime, d.start > startedAt ? (d.start - startedAt) / summaryBarTime : 0, d.name + ' Compute: ' + Math.round((elapsed)/1000), options.BARCOLORS[d.status] ); if (d.status === 'fail') { summaryColor = options.BARCOLORS.fail; } } }); doBar ( options.YSTART, options.XSTART, (now-startedAt) / summaryBarTime, 0, 'SUMMARY: Elapsed: ' + Math.round((now-startedAt)/1000) + ' Compute: ' + Math.round(functions.reduce(function(p,c){ return p+ (c.end ? c.end-c.start : now-c.start); },0)/1000) + ' seconds', summaryColor ); } /** * create a bar line * @param {number} t top * @param {number} l left * @param {number} ratio the ratio of the bar to plot * @param {number} offset the offset of the bar to start at * @param {number} optColor the color to use */ function doBar(t, l, ratio, offset, value, optColor) { var barColor = optColor || options.BARCOLORS.anythingElse ; if (ratio < 1) { filler (t,l,options.BARLENGTH, options.BARCOLORS.void); } if (ratio > 0) { filler (t,l + (offset || 0) * options.BARLENGTH ,options.BARLENGTH * ratio, barColor); } if (value) { fillText (t + options.BARHEIGHT *.67 ,l+options.TEXTPAD,value.toString(),options.BARCOLORS.text); } } /** * fill a rect * @param {number} top top * @param {number} left left * @param {number} width the width of the bar to plot * @param {string} color the color to use */ function filler (top,left,width,color) { if(width) { cx.fillStyle = color; cx.fillRect (left ,top,width, options.BARHEIGHT); } return width; } /** * fill a rect with text * @param {number} top top * @param {number} left left * @param {string} text the text to plot * @param {string} color the color to use * @param {string} align the text alignment * @return {number} the width of the text */ function fillText (top,left, text,color,align) { return canvasText (top,left,text,color,align,options.TEXTPX); } /** * fill a rect with text * @param {number} top top * @param {number} left left * @param {string} text the text to plot * @param {string} color the color to use * @param {string} align the text alignment * @param {string} textPx the text size * @return {number} the width of the text */ function canvasText (top,left,text,color,align,textPx) { cx.font = textPx; cx.fillStyle = color; cx.textAlign = align || 'left'; cx.fillText(text, left, top); return cx.measureText (text); } </script>
<script> "use strict"; var globals = { startedAt:null, finishedAt:null, functions:[], REPEATEVERY:2000, STEP:60000, blockExecuting: -1 }; /** * do a replot of estimated end point */ function reSchedule() { // when the number of results equals the number of things to do we are done // update progress bar summaryBar(globals ,globals.finishedAt); // if there's more to do reschedule another timer update event if(!globals.finishedAt) { setTimeout (function () { reSchedule(); } , globals.REPEATEVERY); } } /** * this is called by the htmlservice to kick everything off * @param {array.[object]} functions an array of array of GAS function objects to execute * @return */ function doSomeThings (functions) { // kick off the first block globals.startedAt = new Date().getTime(); globals.thingsToDo = functions; executeNextBlock(); // scheduke a status update reSchedule(); } /** * this is called to execute the next block of things that need to be done */ function executeNextBlock () { if ( globals.blockExecuting < globals.thingsToDo.length -1 ) { globals.blockExecuting++; globals.thingsToDo[globals.blockExecuting].forEach (function(f) { if (!f.skip)globals.functions.push(doAThing(f)); }); } else { globals.finishedAt = new Date().getTime(); } } /** * check to see if everything in this block is completed * cant use promises, because caja doesnt support */ function isBlockCompleted () { return globals.functions.filter ( function(d) { return d.block === globals.blockExecuting && d.end; }).length === globals.thingsToDo[globals.blockExecuting].filter(function(d) { return !d.skip; }).length; } /** * previous stages results */ function getPreviousResults () { return globals.functions.filter ( function (d) { return d.block === globals.blockExecuting -1; }) .reduce (function (p,c) { p.push(c); return p; },[]); } /** * execute a single GAS function * @param {object} funcOb the GAS function to execute */ function doAThing(funcOb) { var runOb = { start:new Date().getTime(), end:null, status:'run', gas: funcOb, name:funcOb.name || funcOb.functionName, results:null, block:globals.blockExecuting }; try { google.script.run.withFailureHandler(function(error) { // the funciton progress bar will reflect failed status complete('fail',error); document.getElementById ("results").innerHTML += ('<br>' + error); }) .withSuccessHandler(function(result) { // all was good complete('success',result); }) [runOb.gas.functionName](runOb.gas.options, getPreviousResults()); if (runOb.gas.debug) { console.log('running ' + runOb.gas.functionName); console.log('properties'); console.log(JSON.stringify(runOb)); console.log('data from previous stage'); console.log(JSON.stringify(getPreviousResults())); } } catch (err) { complete ('fail',[]); } function complete (status,result) { runOb.status = status; runOb.end = new Date().getTime(); runOb.results = result; if (runOb.gas.debug) { console.log('finished ' + runOb.gas.functionName); console.log('properties'); console.log(JSON.stringify(runOb)); } // move to next phase? if (isBlockCompleted()) { executeNextBlock(); } } return runOb; } </script> For more on this topic, see Running things in parallel using HTML service. For more snippets like this see Google Apps Scripts snippetsFor help and more information join our forum,follow the blog or follow me on twitter .
|
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Google Apps Script HTML service > Running things in parallel using HTML service >