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

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.

• 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. v

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

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"
                  }
              }
          }
        ]
      
      ];
}

/**
 * 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 );
}

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;

}

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

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();

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"}
      }
    ] ];
}

/**
 * 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 );
}

<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') ?>

<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 snippets like this see Google Apps Scripts snippets