Parallel implementation and getting started

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 profile

The 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
  • Tasks are ordered into arrays of things that can be run in parallel. So the example above is allowing a1,b1,a2 to be run together followed by 'reduction' when they are completed.
  • Each task should have a name (to identify it on the progress chart), and a functionName that points to a Google Apps Script function to be run. The skip property can be set to true if you want to omit anything from actually running. This allows you to run subsets of the profile without having to make a new one.
  • You'll find the debug property invaluable. It's very difficult to debug html service since it is sanitized by caja. The code you see chrome developer tools is nothing like the code you wrote. Also its hard to get visibility into what's happening on the GAS side since the execution log gets overwritten. If you turn debug on, you'll see lots of stuff in the console window of the developer tools showing you whats running, using what options, and what data it's using.
  • Options can be anything you want. This is passed over to the Google Apps Function that is to be run. In addition to options, the data from the previous function block is automatically passed over too. This is the mechanism for passing results between one stage and another
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 example

If 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
  • Multiple threads to create a sets of test data
  • A reduce operation to combine testdata into one dataset
  • Multiple threads to execute the same test on each of the database backends
  • A reduce operation to combine the test results into one dataset
  • A logging operation to output the test results to a spreadsheet. 
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 1

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;

}

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 3

repeat 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 this

I 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 );
}

asyncService.html
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>


async.js.html
This orchestrates everything

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

dbTests.gs
These are specific to my use case for database testing. If you want to see the code for that let me know and I'll share it with you. In this module you would write the functions specific to your application.

For more on this topic, see Running things in parallel using HTML service. For more snippets like this see Google Apps Scripts snippets

For help and more information join our forum,follow the blog or follow me on twitter .
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.





Comments