Toper Turor

Editor’s Note: Guest author Ashraf Chohan works at the Government Digital Service (GDS), part of the UK Cabinet Office. -- Arun Nagarajan

Recently, when we were preparing the launch of GOV.UK, my team was tasked with creating a series of high-level metrics reports which could be quickly compiled and presented to managers without technical or analytical backgrounds. These reports would be sent daily to ministers and senior civil servants of several government departments, with the data customised for each department.

We decided to use Adobe InDesign to manage the visual appearance of the reports. InDesign’s data-merge functionality, which can automatically import external data into the layout, made it easy to create custom departmental reports. The challenge was to automate the data collection using the Google Analytics API, then organize the data in an appropriate format for InDesign’s importer.

In a previous post on this blog, Nick Mihailovski introduced a tool which allows automation of Google Analytics Reporting using Google Apps Script. This seemed an ideal solution because the team only had basic developer knowledge, much of the data we needed was not accessible from the Google Analytics UI, and some of the data required specific formatting prior to being exported.

We started by building the core reports in a Google spreadsheet that pulls in all of the required raw data. Because we wanted to create daily reports, the start and end dates for our queries referenced a cell which defaulted to yesterday’s date [=(TODAY())-1].

These queries were dynamically fed into the Google Analytics API through Apps Script:

// All variables read from each of the “query” cells  
var optArgs = {
    dimensions: dimensions,              
    sort: sort
    segment: segment
    filters: filters,         
    start-index: 1,
    max-results: 250                    
  };

  // Make a request to the API.
  var results = Analytics.Data.Ga.get(
      tableId,                  // Table id (format ga:xxxxxx).
      startDate,               // Start-date (format yyyy-MM-dd).
      endDate,                 // End-date (format yyyy-MM-dd).
      endDate,                 // Comma seperated list of metrics.
      optArgs);

Next, we created additional worksheets that referenced the raw data so that we could apply the first stage of formatting. This is where storing the data in a spreadsheet really helps, as data formatting is not really possible in the Google Analytics UI.

For example, the final report had a 47-character limit for page titles, so we restricted the cells in the spreadsheet to 44 characters and automatically truncated long URLs by appending “...”.

Once the initial formatting was complete, we used formulas to copy the data into a summary sheet specially laid out so it could be exported as a CSV file that merges seamlessly into InDesign.

Below is an example of how a report looks on publication. Nearly everything on the page was extracted from the API tool, including the department name and the day number. Because most of the data was automated, it required minimal effort on our part to assemble these reports each morning.

We discovered that an added bonus of pulling data into a Google spreadsheet was that it also allowed us to publish the data to a Google site. This helped us display data to stakeholders without adding lots of users to our Google Analytics account.

The tools let us present Google Analytics data in deeper, more creative ways. That’s really important as we share information with more and more non-technical people, whether they’re inside GDS or beyond.

Ashraf Chohan Guest author Ashraf Chohan works at the Government Digital Service (GDS), part of the UK Cabinet Office. GDS’s role is to deliver digital transformation of government services. Ashraf is a Product Analyst for GOV.UK, a new site for public services and information.

The Apps Script team held a hackathon in Washington DC on March 7. Over 80 developers attended and we had some great demos at the end of the evening. One of the demos was from Rusty Mellinger, who explains his script in this blog post. If you missed the DC hackathon, sign up for our next one in Chicago on April 19. -Jan Kleinert

I was lucky enough to attend Google’s Apps Script Hackathon at their office in DC, recently, and got a chance to play with Apps Script. After a quick walk-through tutorial, Jan gave us a couple of hours to hack around with it.

Scripts in Apps Script are written in JavaScript and stored, edited, and run on Googles servers, interfacing with a big list of included services. You can call the scripts from spreadsheets, Google Sites, or from hits to a generated URL.

Roommate Payment Spreadsheet

My roommates and I keep a spreadsheet on Google Docs to track who owes what, but since we’re a house full of software engineers, it’s gotten pretty complicated. Each row records the details of a single transaction: who paid, the total, and what percentages of the payment are on behalf of which roommates. All these interpersonal debts are added up into the (J5:M8) matrix, cancelled out across the diagonal into (P5:S8) to get a single debt for each roommate pairing, and then those are totalled into the final "Shake Out", (F4:F7), which says whether you owe or are owed. Maybe Apps Script could make my life simpler here?

Automatic Emails

First, I’m currently owed a fair amount of money, so I set up automated reminder emails to the roommates who are behind:

// Send emails to everybody with their current status.
function emailDebtors() {
  var ss       = SpreadsheetApp.getActiveSpreadsheet();
  var results  = ss.getRange( "G4:G7" ).getValues();
  var emails   = ss.getRange( "O3:R3" ).getValues();
  var numUsers = 4;
  
  for(var i = 0; i != numUsers; i++) {
    var val = Math.round(results[i][0]);
    
    if (val > 0) {
      // This guy owes money in the shake-out.
      MailApp.sendEmail(
        emails[0][i], "Youre a deadbeat!",
        "You owe $" + val + ". Please pay it!");
    }
  }
}

This just pulls the current totals from the (G4:G7) "Shake Out", as well as their respective email addresses from (O3:R3). When this function is called, if any of them owe more than $0, they get a friendly reminder!

Custom Menus

I could set that up to trigger daily or weekly, but it only really needs to happen when somebody needs to collect what they’re owed, so I’ve added it as an option to the sheet’s menu on start-up.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ { name: "Email debtors", 
                        functionName: "emailDebtors"}];
  ss.addMenu( "SkyCastle", menuEntries );
}

Easy! Now when somebody wants to collect, they just click the “SkyCastle -> Email debtors” option and the appropriate reminder emails are sent out, from their own Gmail address.

Historical Charting

I still had a couple of hours, and wanted to play with the UI and Google Charts services, so I decided to chart the “Shake Out” values over the history of the spreadsheet. The existing cells are hard-coded to operate on the total sums from the full sheet, so I had to re-implement the math to track it line-by-line. (This isn’t all bad, because I can use it to double check the existing arithmetic, which was sorely needed.)

The basic sketch is as follows:

var data = Charts.newDataTable()
                 .addColumn(Charts.ColumnType.NUMBER, "Row");
for (var i = 0; i != 4; i++) {
  data.addColumn(Charts.ColumnType.NUMBER, names[i]);
}

for (var i = 0; i != NUMROWS; i++) {
  var row = Array(5);
  // …
  // Process the current line here, and compute the shake-out.
  // …
  data.addRow(row);
}
data.build();

I’ve omitted the actual calculation, because it’s just a bunch of hacks specific to our spreadsheet formulas. Each row contains the row number, and the accumulated shake-out thus far, and gets added to the `data` table. I break out of the loop once I go off the end of my data and start hitting `NaNs`.

To create the line chart and add it to a new UI window:

var chart = Charts.newLineChart()
      .setDataTable(data)
      .setDimensions(700, 400)
      .setTitle("Debt History")
      .build();

var uiApp = UiApp.createApplication()
                 .setWidth(700)
                 .setHeight(400)
                 .setTitle("Payment History");
uiApp.add(chart);
ss.show(uiApp);
return uiApp;

After adding this function as another option in our custom `SkyCastle` menu and clicking it, we see a nice graph. (I’m almost always on the bottom, but that’s because I make the actual rent and utility payments.) The final entries are equal to the original "Shake Out" cells, so our old arithmetic seems correct, too.

Lessons Learned

The built-in debugger isn’t bad; use the `Select function` dropdown and click the bug icon. I also used Logger.log() liberally while trying to get things working right. (Go to `View -> Logs` in the Script Editor to view that output.)

Apps Script seems to work well, overall, and hooks into a nice and expanding array of Google products and data sources. The GWT-backed UI service is a clever idea, though I barely had a chance to touch it.

Thanks again to Jan and Google for hosting this Hackathon; I can’t wait for the next one!

Rusty Mellinger Rusty Mellinger co-founded Illogic Inc, making heavy use of Google Apps and GWT.