A question recently came up on Stackoverflow:

I've just written my first google apps scripts, ported from VBA, which formats a column of customer order information … The issue is processing time. It cannot handle longer columns of data. I am warned that Method Range.getValue is heavily used by the script.

The question is well presented with the code they are using, example data and things they have tried.

existing data
Existing Data

Reformatted data
Reformatted data

One of the things to remember when moving from the Excel to the Google Sheets is that every interaction you make with a spreadsheet comes with a performance hit. Also Apps Script has a 6 minute execution limit and other quotas to consider. This requires a slightly different mindset and instead you need to think about things in batches rather than row by row operations.

The Apps Script Developer site has some guidance on batch operations. Basically best practice is to grab all the data you need using .getValues(), which returns  a two-dimensional array of values, iterate across and reshape as required and then if needed output using .setValues(). The performance gain when you do this is staggering. Executing the original code on 1,000 rows of data takes 56.822 seconds total runtime, doing as a batch and you get 0.301 seconds total runtime. You can see the code I suggest using here and if you want to have a play yourself here is some test data with both functions loaded in the script editor

Whilst recently in Berlin I was invited to GDG Berlin to give a presentation on Google Apps Script. My experience when talking to developer groups is there is usually low awareness of Google Apps Script so the challenge is usually to provide enough information to entice developers into finding out more and using this tool. Last year I did a similar talk for the London #DevFest on Google Apps Script: The authentic{ated} playground. This talk focused on how Apps Script is integrated into the Google ecosystem, for me, one of the big selling points of this product. For GDG Berlin I decided to revisit my original slidedeck thinking I could add some minor tweaks. In the end it turned into a major rewrite in part to reflect changes in Apps Script over the last year. Here is how I initially pitched the session: ...continue reading

Last week I was fortunate to attend OEB 2015 ( in Berlin. There were many great presentations and sessions at OEB and I particularly enjoyed Jenny Fisher and Chrissi Nerantzi’s Playing for Change – the Role of Creative Activities session, and I look forward to more similar sessions at the ALT Annual Conference next year. My contribution to OEB15 was a workshop on ‘Creating personal tutoring environments with Google Apps Script’. As it happened this workshop followed the opening plenary in which David Price was encouraging delegates to find their inner hacker and take a more autonomous approaches to education (recording here). Below is my submitted session abstract followed by slides: ...continue reading

A while ago I was approached by staff at RMIT University to give a session on the use of Google Analytics for Learning Analytics. This is something I’ve touched upon in past presentations and blog posts. One of the advantages of being on the Google Experts programme is rubbing shoulders with experts in other products like Google Analytics. One such expert is Nico Miceli who got his Google Experts badge around the same time as me. Nico has taken Google Analytics far beyond pageviews using GA  to track the physical world and even for textual analysis. Given Nico’s expertise I was keen to get him involved in the sessions and fortunately for me he was up for it.

If you’d like to see what we talked about the below is the session abstract, our slide deck and recording of the session co-produced with Nico ...continue reading

Later today I’ll be presenting at CiviCon London, the largest gathering in Europe of CiviCRM community. CiviCRM is a big part of my day job at the Association for Learning Technology and this event is the perfect opportunity to hear about the latest developments and get some inspiration for what we can do with our own Civi system.

The talk I’ll be giving is based around some earlier work I’ve already published about  Custom regular reporting from #CiviCRM with Google Sheets and Google Apps Script and Tips on emailing inline Google Charts from Sheets using Apps Script. These outline the method we use to trigger the sending of .csv data attached to an email sent from CiviReport using a Google Sheet: ...continue reading


Google recently announced a new Google Apps Script Service called Execution API. The pitch:

Have you ever wanted a server API that modifies cells in a Google Sheet, to execute a Google Apps Script app from outside of Google Apps, or a way to use Apps Script as an API platform? Today, we’re excited to announce you can do all that and more with the Google Apps Script Execution API. The Execution API allows developers to execute scripts from any client (browser, server, mobile, or any device). You provide the authorization, and the Execution API will run your script.

Getting data into Google Sheets from external sources has been a long held interest of mine. Whilst the Execution API can do much more than just this in this post I want to focus on how you can setup the Execution API to create an authenticated pipeline. Before I go into this it’s worth reminding ourselves of the other ways you can get data into Google Sheets, in particular, publishing a script as a web app. ...continue reading

Photo credit : Chris Bull CC-BY-NC-SA ALT week was #altc, once of the largest annual edtech events in the UK calendar. Each year we’ve seen a growth in the use of Twitter at the event and it makes sense for us to have the live Twitter stream displayed throughout the venue. In previous years we’ve relied on 3rd party services but this year I wanted to bring this in-house.  Having looked around I came across a nice example by Remy Sharp for a ‘A conference twitter wall with built in schedule & announcements’. This looked ideal as the schedule and announcements was a nice additional feature, plus it was coded in HTML/Javascript making it easier for me to modify. ...continue reading

Previously I’ve written about how we use Google Apps Script to extract data from one of the Association’s online systems. We use this technique to generate charts which give us some insight to our current standing. One limitation we had was to view the charts we had to open the Google Sheet with them in. There are various techniques for automatically distributing these using Apps Script and in this post I’m going to highlight one method for emailing inline charts. Below is what the result looks like, an email with an embedded chart, no need to download or preview attachments. ...continue reading

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