As of yesterday TED have uploaded 1,903 videos totaling 1,622,120 seconds of playtime which have been viewed 428,117,012 times and received 4,360,903 likes.

If you’d like to play with the data you can find it in this YouTube Channel Summary – TEDtalksDirector Google Sheet … and if you would like similar data for your or someone else's channel make a copy of this YouTube Channel Summary Google Sheet and follow the setup instructions. ...continue reading

Last year I wrote about how you can  use Google Apps Script to integrate with If you are not familiar with the service lets you:

transform any website into a table of data or an API in minutes without even writing any code

As part of my work at ALT we recently needed to extract data from our hosted Open Conference Systems (OCS). OCS has some data export options but none that fitted our exact need. As a hosted solution we don’t have access to the backend so I turned to to liberate our own data <sigh>. OCS uses a basic authentication but the great thing about is you can train it to enter your username and password and extract the data from the pages you need.  Getting data behind an authentication layer with the API is a two step process:

Make sure you check out the docs before integrating authenticated sources!
Every time you pass in credentials you will be logged in; pass in credentials once or via a login call and subsequently pass through cookies.

I took a while to get my head around the process because the two links in the support message just take you to the generic API docs. This is a better url to the queryLogin methods. It’s clear that have put a lot of work into the developer experience, but unfortunately I struggled testing the queryLogin method. Using a valid id and model schema for the input just gave an ‘UnexpectedErrorException’. So I then turned to’s own dataset tools. This was another dead end as I was struggling to get it to recognise my OCS login. Peeking under the hood I discovered:

Looking for another ‘in’ a quick search came up with this post on Using authenticated data sources with PHP and Go. Given I do a lot of coding in PHP translating to Javascript/Google Apps Script is relatively straight forward. I was still struggling however with the ‘shape’ of the login payload and the $connectorDomain. The breakthrough came remembering that looked like they were dog fooding their own API in their dataset tool.

Luke use the log

With this I could see what the $connectorDomain should have been and can now happily go off and liberate our data. Here’s my translation of the PHP example in Google Apps Script also available as a gist:

function getResults() {
  var connector = {'username':'YOUR_SITE_USERNAME',
  var creds = {};
  creds[connector.connectorDomain] = {
    "username": connector.username,
    "password": connector.password
  var additionalInput = {};
  additionalInput[connector.connectorGuid] = {'domainCredentials':creds};              
  //get cookies
  var login = query(connector.connectorGuid, false, connector.userGuid, connector.apiKey, additionalInput, false);
  additionalInput[connector.connectorGuid].cookies = login.cookies;  
  var result = query(connector.connectorGuid, {"webpage/url":""}, connector.userGuid, connector.apiKey, additionalInput, false);
  // do something with results like write to Google Sheet
function query(connectorGuid, input, userGuid, apiKey, additionalInput, login) {
  var url = "" + connectorGuid + "/_query?_user=" + userGuid + "&_apikey=" + apiKey;
  var data = {};
  if (input) {
    data["input"] = input;
  if (additionalInput) {
    data["additionalInput"] = additionalInput;
  if (login) {
    data["loginOnly"] = true;
  var ch = UrlFetchApp.fetch(url, {'method':'POST', 'payload': JSON.stringify(data)});
  var result = ch.getContentText();
  return JSON.parse(result);

At the Association for Learning Technology (ALT) one of the core tools we use for membership management is CiviCRM. CiviCRM has a number of ‘out-of-the-box’ reports you can use and run to summaries and analyse memberships and contributions. Given the flexibility of Civi you can also with a bit of know how create custom extensions and reporting options enabling so very sophisticated analytics. At ALT we are keen to make more use of the data we have on memberships but at the same time have limited resources and time to implement these. In this post I’ll highlight how using Google Sheets we’ve developed a framework that allows us to rapidly develop custom reporting.


If you are already a CiviCRM user you are probably already aware of CiviReports and the ability create basic custom reports which allow you to view and download data. As part of this you can also schedule reports to land in your inbox. This is great but has it’s limitations. In particular, additional setup is required if you don’t want to just report on a daily basis; you end up with tables of data, with no graphical summaries; and combining current and historic data isn’t possible.


Scheduling reports at custom intervals

CiviCRM provides an interface to schedule a mail_report. The issue many people discover is this will send reports on set intervals usually hourly or daily. You can schedule individual jobs to run a specific periods but you quickly find yourself in the world of command lines and CRON jobs. Crons are scheduled tasks run by a web server. If you have dedicated admin support this is a fairly standard task and the instructions are easy to follow. At ALT we have the option to open a support ticket with our host but this seems like a waste on time and money.

Our solution is to use a Google Sheet… well a Google Sheet with a bit of ‘juice’. The sheet is shared with our team and anyone can add a CiviReport id to Column A and choose how often it runs in Column B using a data validation list option.

deciding what reports run when

But how does this trigger our civi install to run the job? The juice is Google Apps Script, a cloud based scripting language native to Google Drive. Apps Script is a free service from Google and fortunately for us has the ability to run scripts on configured time intervals. It also has the ability to call specific urls using the build-in UrlFetchApp (similar to CURL). I’ll give you a link to this Sheet so you can setup your own later and when you do you’ll see the entire process is managed with a couple of lines of code included below:

function doTasks() {
  var doc = SpreadsheetApp.getActiveSpreadsheet(); // get spreadsheet
  var sheet = doc.getSheetByName("Tasks"); // get sheet
  var data = sheet.getRange(3, 1, sheet.getLastRow(),; // get values
  var now = new Date(); // time now
  // for each row of the sheet interate accross
  for (var i = 0; i < data.length; i++){
    if (data[i][COL.report_id] != ""){ // if there is instance id do something
      // collect row values
      var report_id = data[i][COL.report_id]
      var type = data[i][COL.type];
      var next_run = data[i][COL.next_run] || 0; 
      // check if it's time to run the report again
      if (next_run < now && type != "never"){
        // if it is ping the report trigger
        var new_next_run = callUrl(report_id, type, {format: data[i][COL.format], ss_id: data[i][COL.ss_id], ss_sht: data[i][COL.ss_sht]} );
        // ..and record when to run again
        sheet.getRange(parseInt(i)+3, 3, 1, 2).setValues([[now, new_next_run]]);

What this does is read the sheet data and then iterate across each row. If the report is overdue to be run again it calls a another custom function callUrl which will run the CiviReport and return/write when next to run.

Creating graphical summaries and combining data

By this point you may be sensing that I’m partial to solving problems with Google Sheets. With Sheets it’s fairly straight forward to manually export different reports from Civi and analyse using formula and Charts. The manual export of CiviReports can get tiresome so how can we automate this? Again we return to Google Apps Script. One of the options in CiviReports is to attach the data to the emailed report as a .csv file. From the previous example we can see it is possible to read and write data to a Google Sheet. So if we can get the .csv file from our emailed report we can write it to the Sheet … right?

This is actually more straight forward than you may think as another feature of Google Apps Script is to interact with the script owner’s Gmail.  As part of this we can search for messages and get associated attachments. Using this we can read the latest report from Civi, write the data to a sheet and with a bit of clever formula building automatically get the latest summary or custom chart. As Apps Script runs in a pre authenticated environment, no oAuth handshakes here, the code is relatively straight forward:

function processInbox(){
  var PS = PropertiesService.getScriptProperties();
  var data = PS.getProperties();
  for (var key in data) {
    if (key.substring(0, 10) == "search_str"){
      var param_raw = data[key];
      var param = JSON.parse(param_raw);
      // get last 20 message threads using serach term
      var threads =, 0, 20); 
      // assume last thread has our latest data
      var last_thread = threads.length-1;
      if (last_thread > -1){
        // get message in the last thread        
        var msg =  threads[last_thread].getMessages()[0];
        // get the attachments
        var attachments = msg.getAttachments();
        for (var k = 0; k < attachments.length; k++) {
          // get the attachment as a string
          var csv_str = attachments[k].getDataAsString();
          // parse string as csv
          var csv = Utilities.parseCsv(csv_str);
          // create destination object
          var doc = SpreadsheetApp.openById(param.ss_id);
          var sheet = doc.getSheetByName(param.ss_sht);
          // clear any old data
          // write new data
          sheet.getRange(1, 1,  csv.length, csv[0].length).setValues(csv);
          // mark message are read and archive (you could also label or delete)


Data protection

There are a couple of things worth noting here. Google Sheets are a fantastic collaborative environment and with this solution we can still share spreadsheets to selected people in our organisation and beyond. Something to remember though is this script runs as the sheet owner so when configuring the CiviReport it needs to go to the email address of the owner. At ALT we benefit from being a Google for Education user so our email and Drive access comes as part of the Google Apps suite. This solution could also be setup to run on a regular Google account but there are data protection issues to consider sending reports to a non-organisation contact. As such you might only want to re-use this solution as an easy way to schedule reports rather than schedule and process the data.

ARRAYFORMULA, FILTER, QUERY functions are your friends

As our solution dumps .csv data in a sheet, clearing any previous data, any formulas you have in the sheet will also be lost. We get around this by doing all data manipulations in a separate sheet which references the imported data. To save copy and pasting lots of formulas we make extensive use of the ARRAYFORMULA, FILTER, QUERY functions available in Google Sheets.

Comparing CiviEvent registrations

One scenario we have is monitoring the number of registrations to an annual conference. It’s easy for us to export registrations for previous years as static data into a spreadsheet. For the analysis we want to group the number of registrations by week number. To get a week number for the live data we create a second sheet that references it. Using the ARRAYFORMULA you can enter the reference once which is then applied to all the values in the range. For example, we get all the registration dates in column A using =ArrayFormula('2015LiveData'!B:B) in cell A1 and then extract the week numbers in column C by using the formula =ARRAYFORMULA(IF(ISBLANK(A2:A),"",WEEKNUM(A2:A))) in cell C2.

ArrayFormula is your friend

Setting up your own copy of Schedule CiviCRM Reports

If this is a project you’d like to use and/or build upon you can copy a blank version of our template below:

Schedule CiviCRM Reports
[While signed in File > Make a copy]

Once you have a copy open Tools > Script editor to see all of the project code and instructions for setting up.

I’ve hopefully given you enough to go on to setup but feel free to leave a comment if you get stuck or have any questions.


Example of pdf version from altc 2014At ALT we use Google Sheets as an easy way to share and collaborate on draft event timetables. Recent examples are the ALT Annual Conference 2014  and the OER15 timetables. One of the reasons for publishing draft timetables using Google Sheets is  we can get a static url for people to download it as PDFs but the contents can be dynamically updated (see recent post on doing this). The template we use for conferences is continually evolving which isn’t an issue as it’s easy to copy the last version. One headache is that our theme colour usually changes. This can be a bit fiddly change as we use empty cells to create a thicker grid:

Thicker borders

Faced with another cell background switch it made sense to actually do this with code rather than clicks and thanks to Google Apps Script possible in 19 lines of code and a couple of minutes:

function colorReplace() {
  var doc = SpreadsheetApp.getActiveSheet();
  // get all the existing active sheet background colours
  var cells = doc.getRange(1, 1, doc.getLastRow(), doc.getLastColumn()).getBackgrounds();
  var rows = cells.length;
  var cols = cells[0].length;
  // iterate accross
  for (var i = 0; i < rows; i++){
    for (var j = 0; j < cols; j++){
      if (cells[i][j] == '#feeff8'){ // first color to change
        cells[i][j] = '#f3f3f3'; // first color change to
      } else if (cells[i][j] == '#bf0875'){ // second color to change
        cells[i][j] = '#079948'; // second color to change
  // update backgound colours
  doc.getRange(1, 1, doc.getLastRow(), doc.getLastColumn()).setBackgrounds(cells);


To get the existing cell background colour I used the debugger setting a breakpoint before the loop to see the existing cell colour HEX codes:

debugger to inspect cell colours

When I fly it generally becomes a session in staring into space and letting my mind wonder. It’s not that I haven’t got a long list of Google Apps Script ideas I want to explore, but as a ‘cloud’ based scripting environment a data connection is required (insert pun about in the clouds/above the clouds). For my latest trip I decided to set myself a challenge – create a watch face for Android Wear only using my mobile. This is a lot easier than it sounds thanks to the WatchMaker Watch Face app for Android. Here’s how I got on (or you can just download my watch face)

My watch face on the right displaying the time 9:42 and on the left TokyoFlash Twelve 9-5 B it was inspired by
My watch face on the right displaying the time 9:42 and on the left TokyoFlash Twelve 5-9 B it was inspired by

WatchMaker is one of a number of apps that allows you to do WYSIWYG (what you see is what you get) editing. Making my own watch face was something I started thinking about at the weekend and I’d already scoped out WatchMaker, downloaded some other watch faces and had a peek under the hood. As part of this I discovered WatchMaker integrates with the Lau Programming Language making it possible to do some amazing stuff.

Some of the watch faces I've been playing withAnother great feature of WatchMaker is reuse is baked in. Every watch face you import can be duplicated and customised. This liberal approach makes it easy to see how other people are using WatchMaker features.

As part of my weekend tinkering I’d already taken a picture of my well loved TokyoFlash Twelve 5-9 B watch, shown above. The 1259 B uses LEDs to light up a binary display of the time (see video here), hours shown on the left and minutes equalling the sum of the top (ones) and bottom (tens) rows.

I wasn’t planning on making a watch when I sat down on the plane, but just before the aircraft doors closed grabbed an image of a 1259 B with LEDs on. As I wanted to use a 1259 B LED in my watch the first challenge was cropping the image. I’ve limited image editing apps on my phone but discovered if I successively used the Snapseed cropping tool I could get down to a 16x16px image.

Screenshot_2015-05-19-07-54-15[1] Screenshot_2015-05-19-07-54-09[1]

Adding an image to WatchMaker lets you position the image and appearance using a number of build in WatchMaker variables. For example, {dmo} returns the current minute in the hour in ones. So to progress a minutes led one step from left to right I set the x-position using –90+{dmo}*18. Rotating the hour LED around the bezel was a little more tricky and during the flight my basic trig. maths failed me but the cos/sin rule quickly came back to me once the aircraft doors opened.

There a lot more you can do in WatchMaker with sensor and external data and even animation effects and whilst my first foray in this area is basic it’s mine and my maker journey continues ;)

At ALT we are a Google Apps for Education user and make extensive use of Google Drive for creating and publishing documents. Some of our documents, such as member lists or timetables, get regularly updated and updating links in our websites can be a chore. One of the nice features of documents in Google Drive is you have a couple of ways of publishing documents so anyone can view (no log in required). For Google Sheets the main options are getting a shareable link:

share with others  ... because sharing is good ;)

Or using the ‘publish to the web’ option:

publish to web new school

In the ‘old’ version of Google Sheets the ‘Publish to the web’ option included lots of file types, not just as a web page:

publish-to-web old skool

You can still get new Google Sheets to generate download links for other formats … it’s just a bit complicated. The complicated bit is working out which url tweaks you need. For example, with your magic goggles on you might start spotting a pattern in this url which I’ve line breaked to make easier to see:

Remembering all these switches isn’t easy so for our team I’ve shared a simple template with lets us drop in a shared Google Sheet link, choose options for layout and get a url. Here’s a copy in case you find useful:

PDF Link for Google Sheet Template
PDF Link for Google Sheet Template


My old colleague at Cetis, David Sherlock, posted a nice little  ‘Twitter Question/Revision Bot’. This uses a .csv file of questions and multiple choice answers which get randomly tweeted out using a Python script. David designed the project with a Raspberry Pi in mind but also highlights it can be easily run on any Unix like environment such as Mac OS X or Linux. As not everyone is going to have easy access to this here’s how you can do something similar with Google Sheets (if you don’t want to play copy and paste coding make a copy of this sheet).

1. Setting up a Google Sheets environment to handle it

  1. Start with a new Google Sheet and like David have six columns in each row with question, answer, three options (one of which the correct one) and an extra row to record if the question has been asked.
  2. In your spreadsheet open Tools > Script editor and when asked start a ‘Blank Project’
  3. In the new editor window select Resources > Libraries (this will first prompt you to give your project a name, I called mine TwitBot.
  4. In the ‘Find a Library’ box enter MarIlVOhstkJA6QjPgCWAHIq9hSqx7jwh and click Select
  5. You should now see ‘TwtrService’ listed as one of the libraries. In the ‘Version’ dropdown select the latest version (at time of writing 14), and click Save

TwtrService is a library I’ve written so interact with the Twitter API. You can read more about it here.

In the code window add the following code and click save:

function setup() {
  if (TwtrService.isUserConnectedToTwitter()){
   var result = Browser.msgBox("Twitter Authorisation",
                   "You appear to already be connected to Twitter.\\n\\nWould you like to run the setup again?",
    // Process the user's response.
    if (result == 'yes') {
      // User clicked "Yes".
  } else {

The above code uses the TwtrService to help you set up Twitter access if required

Your script window should look like this:

script editor

2. Create a Twitter App

If you’ve used my other TAGS templates you can reuse your details for this. To see if Twitter App details are required from the script window select Run > setup (if setup isn’t listed you need to first save your code). Running setup will start the first part of the authentication process. Click continue and review the authentication required and ‘Accept’ if you are happy.

Auth required

Going back to the spreadsheet you started there should now be a dialog window asking you to do something. If you haven’t setup a Twitter App before it should look like this:

Twitter app creation

Follow the instructions onscreen to create your app.

Important: The Twitter account you use to authorise access is the one that will send out the tweets

3. Write a Python Google Apps Script

Add the code below to your existing script project and save:

var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName('Sheet1');

function tweetQuestion(){
  var ran = Math.floor(Math.random() * (sheet.getLastRow()-1)) + 2;
  var row = sheet.getRange(ran, 1, 1, sheet.getLastColumn()).getValues()[0];
  if (row[5] !== ""){
    tweetQuestion(); // if already asked pulls another random row
  } else {
    var tweet =  "Q: " + row[0];
    var tweet2 =  row[2];
    var tweet3 =  row[3];
    var tweet4 =  row[4];'statuses/update', {status: tweet});
    var options = [tweet2,tweet3,tweet4];
    shuffle(options);'statuses/update', {status: "A: " + options[0]);'statuses/update', {status: "B: " + options[1]);'statuses/update', {status: "C: " + options[2]);
    sheet.getRange(ran, 6).setValue(new Date());

function shuffle(a,b,c,d){//array,placeholder,placeholder,placeholder

4. Time the script to run every hour or so

In the script editor select Resources > Current project’s triggers and click ‘No triggers set up. Click here to add one now.’. Select to run tweetQuestion every hour (or your preference), and also click ‘notification’ so you can get an email if the script fails. Finally click ‘Save’

Timed triggers

What will happen now is the function even if you don’t  have the spreadsheet or script editor open or even your browser.

Important: when this script runs out of questions it will go into an infinite loop. You can go back into the trigger window to remove the function at any point. If you don’t you’ll end up using all of your script runtime quota. You homework is to figure a way to get the script to bail if there are no questions left.

My homework...

function tweetQuestion(){
  var asked_col = sheet.getRange(2, 6, sheet.getLastRow()-1).getValues();
  // get unasked q's
  var unasked = [];
  for(var i=0; i < asked_col.length; i++) {
    if(asked_col[i][0] == "") {
  // randomly pick one
  var ran = Math.floor(Math.random() * unasked.length);
  if (unasked[ran]){
    var row = sheet.getRange(unasked[ran], 1, 1, sheet.getLastColumn()).getValues()[0];
    var tweet =  "Q: " + row[0];
    var tweet2 =  row[2];
    var tweet3 =  row[3];
    var tweet4 =  row[4];'statuses/update', {status: tweet});
    var options = [tweet2,tweet3,tweet4];
    shuffle(options);'statuses/update', {status: "A: " + options[0]});'statuses/update', {status: "B: " + options[1]});'statuses/update', {status: "C: " + options[2]});
    sheet.getRange(unasked[ran], 6).setValue(new Date());
  } else {
    // no questions left - do something else

Kin Lane has done some great work in highlighting the importance of APIs in education. If you are unfamiliar with APIs they are a way for separate programs to communicate and share information or perform actions.  With the growing usage of data in education I believe APIs are the only way to use data effectively and efficiently. Kin’s University of API white paper  is a great starting point to get more context.

Reading the white paper reminded me how important it is to get people to think beyond the webpage and consider the underlying data used to generate it.

Luke, view the source

Back in the day when I discovered the work of Tony Hirst this was a real threshold concept for me. Five years ago unpicking data powering the web felt a lot easier there was usually only basic authentication required, if any. Now you usually have to do some sort of authentication handshake. This additional step often immediately lands you in codeland. Even if you don't do code there are still opportunities to explore APIs. Any decent API service will usually have interactive documentation for developers or API consoles. In a recent talk, which you can see the fragments of here, I highlighted what data is behind a tweet. If you'd like to explore the Twitter API in a non-cody way here's how:

Interactively exploring the Twitter API

1. Go to Twitter API console Make sure you logged in (should see your avatar top right)

2. In the Authentication drop down select OAuth 1 - this will prompt you to sign in with twitter


3. When bumped back to the pack select /status/show/{id}.json

image (3) 

4. After it prefills some details switch to the Template tab. In the id box enter a twitter id number e.g. in my tweet you'd just enter 591156241969319936 and hit the orange send button

 image (2)

5. In the pane you should get a response. The main data bit starts:


6. To get details of other tweets click the Template tab and enter another id.

image (1)

7. If you are interested in other API calls you can make click the Service box and select another



Later today (2.30pmUTC) I’ll be presenting at #oer15 about Twitter in open education (tune in here). As I wanted to highlight the network effect of Twitter I wanted to engage not just the room, but leave ‘footprints’ as for others to follow. I know people like Alex Couros and Alan Levine have done cool stuff live tweeting from Keynote. I’ve dabbled with doing stuff with Microsoft PowerPoint but was never fully satisfied. Given Twitter now supports a number of embedded media formats I thought rather than trying to fit Twitter into another presentation tool, to turn my live tweets into my slides.

And so TAGSPresenter is born! Using a Google Sheet as an editor, Google Drive to host images and a bit of Google Apps Script to glue it together I’ve got my own Twitter based presentation tool. I don’t have time to write about how it was technically achieved but if you want to peak under the hood of the hack here are my ‘slides’ which are published here.

Tune in at 2.30 to see how it goes ;)

In a couple of weeks I’ll be talking about TAGS at OER15 (14:30-15:00, 15th April). Whilst parallel sessions aren’t going to be streamed I’ve got a couple of ideas for broadcasting my session. If I pull this off I’ll be co-tagging my presentation #oer15 #740.

My notes for structuring the session so far is:

  • Networks – shape, strength and characteristics
  • Networked education – making the connection
  • Footprints – seeing the connection
  • Twitter in Ed – activities Adoption Matrix (Mark Sample) and examples
    ”anyone to become an active participant in the conversation” Ross, 2012.
  • APIs – me speaky code
  • Twitter Search and the Twitter Search API
  • Anatomy of a tweet
  • TAGS/TAGSExplorer
  • TAGS in the wild
  • Context – SNA, ethics, vulnerability

You’ll see I’ve highlighted two items in that list and this is where you can help. If you have used TAGS to support your class/course I’d like to know:

  1. how does Twitter generally fit in to the course? Are you using directed activities or is there a more organically;
  2. how TAGS is used to support this? Post/ongoing SNA, situational awareness, …

As the session will mostly be me talking, preferably some video or audio would be great. If you’ve previously talked about Twitter/TAGS in education and a recording is available I’ll happily look at this and see if there is something I can use.