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.
Roadmapper in Google Apps Script
1 2 3 |
function testRoadMapper() { return mcpher.actRoadMapper("RoadmapParameters","InputData"); } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
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(); } |
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.