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. http://stackoverflow.com/q/35289183/1027723

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

I was delighted to recently be invited to speak at the University of Edinburgh to students of their PgCert in Academic Practice. The original ‘pitch’ was:

Academic practice continues to evolve to reflect the needs and opportunities of various stakeholders including the learner, employers and the institution. Some would argue that university education isn't changing fast enough given the pace of change within society and technology. We will explore strategies for developing an agile approach to academic practice, looking at how education can be 'hacked' to creatively overcome the limitations of the system. ... We conclude taking a wider view exploring emerging peadagogies and technologies and how these might be used too to make education better.

...continue reading

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 (http://www.online-educa.com/) 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

I’m usually very protective of my family life and in particular pictures and information about my wife and daughter. I’m breaking this rule with the permission of my daughter to ask you to show your support for the continuation of cleft surgical services in both Edinburgh and Glasgow. My daughter was born with a cleft lip and palate and within hours of her birth we were receiving support from the Edinburgh cleft team. Clefts are the most common facial defect affecting 1 in 700 births. Currently in Scotland there are two cleft teams in Scotland, on in Glasgow and the other in Edinburgh. It’s worth stressing how important these teams are including plastic surgeons, speech therapists, orthodontists, dentists, audiologist, clinical nurse specialists and more. For the east of Scotland the Clinical Lead of Cleft Lip and Palate Services is the world renowned Dr Felicity Mehendale.

Whilst many hard decisions have to be made in funding the NHS given the number of children affected by clefts I think it’s important that in Scotland we maintain both the high level of treatment as well as invaluable research and teaching in this area. With this in mind please:

Sign this petition

Share on Facebook

Send a message to friends

Tweet to your followers

Thank you!

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