Toper Turor: A First Attempt at Apps Script with Spreadsheets

Tuesday, March 10, 2015

A First Attempt at Apps Script with Spreadsheets

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.

Toper Turor