Finished Roadmapper Script - Desktop Liberation

I pretty much started here. Since the objective was to migrate from a VBA project to a Google Apps script version of the same, my approach was to write the skeleton, and emulate the underlying classes, modules and libraries. If I was writing this in Apps Script from scratch, I would have written this differently, but using the converted classes from VBA shows just how similar you can make VBA and Apps Script in structure.

The main things I needed to implement in google script to get this going were the data abstraction classes, some kind of GAS alternative to Excel shapes, Google charts, various roadmap specific classes, and of course the finished roadmapper script.

The roadmapper module is implemented in the mcpher library. You can find a workbook with test data in VBA to Google Apps Script Roadmapper project download

Roadmapper in Google Apps Script

The calling script – this would be in your workbook, is just a one liner specifying where to find your parameters and your data.

function testRoadMapper() {
  return mcpher.actRoadMapper("RoadmapParameters","InputData");
}

function testRoadMapper() {

return mcpher.actRoadMapper("RoadmapParameters","InputData");

}

The rest is implemented in mcpher library in the roadmapper module and looks like this ( or at least it did when I wrote it – latest code will be in mcpher library

function actRoadMapper(wp,wd){
//where the parameters are

    var rParam = wholeSheet (fixOptional (wp, "Parameters"));
// automatically find where the data is

    var rData = getLikelyColumnRange(Sheets(wd));

// get the data and the parameters

    var dSets = new cDataSets();
    dSets.create();

    dSets.init(rData,undefined , "data");

    dSets.init(rParam,undefined ,undefined , true, "roadmap colors");
    dSets.init(rParam,undefined ,undefined , true, "containers");
    dSets.init(rParam,undefined ,undefined , true, "options");
    dSets.init(rParam,undefined ,undefined , true, "custom bars");
    var ds = dSets.dataSet("data");
    if (!ds.where()) 
      MsgBox ("No data to process");
    else {
      //check we have fields we need
      if(ds.headingRow().validate(true, "Activate", "Deactivate", "ID", "Target", "Description")){ 
        var rplot = rangeExists(dSets.dataSet("options").cell("frameplot", "value").toString());
        if (rplot) doTheMap(dSets, rplot);
      }
    }

    
}

function doTheMap(dSets, rplot) {
  var scRoot = new cShapeContainer();
  scRoot.create (scRoot,undefined,rplot,dSets);
  // create s node for each data row
  dSets.dataSet("data").rows().forEach(
    function (dr,drItem) {
      var sc = scRoot.find(dr.toString("ID"));
      if (!sc) {
        sc = new cShapeContainer();
        sc.create (scRoot, dr);
        scRoot.children().add (sc, sc.id());
      }
      else {
        MsgBox (sc.id() + " is a duplicate - skipping");
      }
    }
  );
  // clean up the structure and associate ids to targets

  scRoot.springClean();
  scRoot.createScale();
  scRoot.sortChildren();

  // make the shapes 

  scRoot.makeShape();

  // do the chart if there is one
  scRoot.doShapeCallouts();
  scRoot.makeChart();
  // add traceability data to each shape
  scRoot.traceability();
  // group everything

  scRoot.groupContainers();

}

function actRoadMapper(wp,wd){

//where the parameters are

var rParam = wholeSheet (fixOptional (wp, "Parameters"));

// automatically find where the data is

var rData = getLikelyColumnRange(Sheets(wd));

// get the data and the parameters

var dSets = new cDataSets();

dSets.create();

dSets.init(rData,undefined , "data");

dSets.init(rParam,undefined ,undefined , true, "roadmap colors");

dSets.init(rParam,undefined ,undefined , true, "containers");

dSets.init(rParam,undefined ,undefined , true, "options");

dSets.init(rParam,undefined ,undefined , true, "custom bars");

var ds = dSets.dataSet("data");

if (!ds.where())

MsgBox ("No data to process");

else {

//check we have fields we need

if(ds.headingRow().validate(true, "Activate", "Deactivate", "ID", "Target", "Description")){

var rplot = rangeExists(dSets.dataSet("options").cell("frameplot", "value").toString());

if (rplot) doTheMap(dSets, rplot);

}

function doTheMap(dSets, rplot) {

var scRoot = new cShapeContainer();

scRoot.create (scRoot,undefined,rplot,dSets);

// create s node for each data row

dSets.dataSet("data").rows().forEach(

function (dr,drItem) {

var sc = scRoot.find(dr.toString("ID"));

if (!sc) {

sc = new cShapeContainer();

sc.create (scRoot, dr);

scRoot.children().add (sc, sc.id());

}

else {

MsgBox (sc.id() + " is a duplicate - skipping");

}

);

// clean up the structure and associate ids to targets

scRoot.springClean();

scRoot.createScale();

scRoot.sortChildren();

// make the shapes

scRoot.makeShape();

// do the chart if there is one

scRoot.doShapeCallouts();

scRoot.makeChart();

// add traceability data to each shape

scRoot.traceability();

// group everything

scRoot.groupContainers();

}

Take a look at how the From VBA to Google Apps Script to follow along with the Roadmap Generation migration project. Why not join our forum,follow the blog or follow me on Twitter to ensure you get updates when they are available. You can find the code and test data in the VBA to Google Apps Script Roadmapper project download.

Transitioning is covered more 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.