MASHe Review 2013: Google Apps Script and Google Sheets

It’s been a while since I’ve done a review post but as this year has been a blur if for no one else I wanted to look at my posts from 2013. Rather than a look at everything I wanted to extract some themes and for this first review I wanted to look back at some of the work I’ve done around Google Apps Script and Google Spreadsheets/Sheets.

Keeping your Twitter Archive Fresh on Google Drive

Twitter archive in Google DriveOne of my favourite posts of the year was made in January – Keep your Twitter Archive fresh on Google Drive using a bit of Google Apps Script. Like many others I was pleased to see Twitter announce the ability to request an archive of all the tweets from your account, the issue being as soon as you download this the data is out of date. Using a combination of Google Apps Script and Google Drive I published a way for users to not only keep the data fresh but host it for free on Google Drive (my archive is here). The powerful features of this solution are: reading JSON from Drive; evaluating as Javascript (used as the Google Apps Script base language); and then rewriting JSON back to Drive.
By coincidence 2013 actually began with the Analysing WordPress post velocity and momentum stats with Google Sheets (Spreadsheet) post which highlighted a quick way of exporting WordPress Stats for analyse in a Google Sheets. This uses nothing more than a little knowledge of the WordPress Stats API and Google Apps Script to import the data into a sheet. Almost 250k post views later it’s interesting to see some of my old faithful posts are generating traffic (in part benefiting from a full years worth of view stats). The top 10 is shown below and you can see ‘keep your twitter archive fresh’ sneaks in at number 10.


This conveniently takes us to February and the top post of the year with the release of version 5 of Twitter Archiving Google Spreadsheet TAGS. TAGS has been a long standing project, version 5 emerging in anticipation to changes to the Twitter API. One of the nice things about TAGS is the way the community has taken to this tool to perform a number of Twitter analyses. One example is the work published by Richard Baxter on How To Use Twitter Data for Really Targeted Outreach.
To explore some of the other uses I’ve extracted the pingbacks from my Google Analytics data for the post using the Using Google Spreadsheets as a Google Analytics Data Bridge solution I published in July. I’m disappointed that this post hasn’t done better. I think it’s a great solution for selectively publishing GA data and a halfway house to Google’s more complicated superProxy but with barely 1,000 views it sits in 41st spot. Answers for where this post misses the mark on a postcard.

Learning Analytics & MOOC web greasing

February also saw the first of a series of posts using Google Sheets as part of a tool chain for some light ‘Learning Analytic’. This started with the #LAK13: Recipes in capturing and analyzing data – Twitter and went on to explore how the Canavs LMS discussion boards could be queried using IMPORTFEED. The Canvas example was then extended by integrating Google Sheets with the Canvas API. This work resulted in an invite to present a Canvas Geek Out Webinar on Using Google App Scripts and Spreadsheets to Analyze Canvas Usage Data. Even better Brian Whitmer (co-counder Instructure Canvas) went on to develop the idea further turning it into a Google Sheet template for Canvas – impact, tick.
As the year unfolded my attention focused on MOOCs and the use of WordPress as part of an open course infrastructure. This area will hopefully also get the MASHe review treatment but even in here I turned to my faithful Google Script skills for some quick web greasing. As I was interested in data aggregation using FeedWordPress this usually meant flipping JSON APIs into RSS. Two of these included: Creating an RSS search feed for posts; and Creating a ‘full fat’ RSS feed for Google Gmail labels. The last one is particularly interesting as it was development to turn email announcements into a data feed. The idea being you could turn a Twitter emailed ‘summary of activity on your network’ into and information source which might support the entire class.


The last two post I’d like to highlight are ‘challenges’ that came from Twitter or my contact form. Both of these come from one person, Marjolein Hoekstra, so you’ll need to ask her what’s the magic to stir me from my slumber ;). The first was How to open a Google Spreadsheet at a specific row for editing, which was a case knowing a bit about how Google Sheets works and poking around the address bar. Jacob Jan Tuinstra has gone on and explained it far more concisely than I did in this stack exchange response. The second was How to compare two strings and highlight the differences. Originally I wanted to solve this just with existing Google Sheet formula. I eventually got there but Bruce Mcpherson (who has been coming up with some insane Script solutions) gazumped me with, I think, a better solution. A useful reminder or the power of Google Sheet formula (whilst also remembering the dangers of Spreadsheet Addiction).

The future

So what delights behold 2014? Well with the New Google Sheets which brings various performance gains including ‘millions of cells’ TAGS v6.0 is definitely on the cards. Beyond this who knows but given todays announcement of additional Google Apps Script advanced services including YouTube Analytics I’m sure Google Apps Script and Google Sheets will feature in my work for ALT.
Update: Not sure how I forgot I also did a guest post of the official Google Developers blog on How Apps Script Makes Classroom Observation Quicker and Easier by automating Google Form handling


Join the conversation

comment 1 comment

Comments are closed.