Data manipulation Classes in Google Apps Script

These Data Manipulation Classes have been migrated from VBA so that the Google Docs spreadsheet data can be abstracted  from its physical location in the same way as most of the projects on this site abstract data from Excel. This is not the best way to deal with data in Apps Script, but is provided in case you want to migrate from their equivalent in Excel.

Once these have been migrated, then I have a platform to both migrate projects from Excel, but also I can feasibly dual maintain an Excel and a VBA version going forward.  The key thing is that the specifics of data access and navigation is abstracted away from the main project code and dealt with by the respective classes. You’ll see from the examples below that this abstraction allows the javaScript and VBA to be almost the same as each other. You can find the code (its a work in progress) and test data in the VBA to Google Apps Script Roadmapper project download

Page Content hide
1 Examples
2 Populating a dataset
3 Create a collection of datasets with various attributes
4 Enumerating each row in a dataset
5 Summary
6 Share with your network

Examples

You may already be familiar with these classes if you are a regular visitor to this site. You can find the VBA details here. The Google Script versions are pretty much the same, with a few minor differences where I couldn’t get javaScript and VBA behavior to be exactly the same. The complete code (work in progress) can be found here – Google Apps Script Roadmapper code The best way to illustrate is to compare javaScript and VBA ways of doing the same thing

Populating a dataset

javaScript

var ds = new cDataSet();
  ds.populateData(wholeSheet("inputdata"));

VBA

dim ds as new cDataSet;
  ds.populateData wholeSheet("inputdata")

Create a collection of datasets with various attributes

javaScript

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

VBA

Set dSets = New cDataSets
    With dSets
        .create
        .init rData, , "data"
        .init rParam, , , True, "roadmap colors"
        .init rParam, , , True, "containers"
        .init rParam, , , True, "options"
        .init rParam, , , True, "custom bars"
    End With

Enumerating each row in a dataset

javaScript

dSets.dataSet("data").rows().forEach(
    function (dr,drItem) {
        // do something with each dr (the cDataRow) or drItem (the item number of each row)
    }
  );

VBA

With dSets.dataSet("data")
    ' create for each datarow
        For Each dr In .rows
            ' do seomthing with dr ( the cDataRow)
        Next dr
    End With

Accessing a single cell in a dataset by key and converting it to a string javaScript

dSets.dataSet("options").cell("frameplot", "value").toString();

VBA

dSets.dataSet("options").cell("frameplot", "value").toString

Transitioning is covered more comprehensively in my book, Going Gas – from VBA to Apps script.

You can find the latest complete code for cDataSet and related classes in the mcpher library

Summary

Take a look at how the From VBA to Google Apps Script for more like this. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.