Techtalk: #CiviCon London: Automatic data extraction and custom email reports from CiviCRM using Google Apps Script

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:

Schedule CiviReports from a Google Sheet

The script is then able to search the recipients Gmail inbox, find the .csv attachment and write the data to a target Google Sheet. Using a Google Sheet interface we can reshape the data using built-in and custom formula to generate charts which another script can automatically send at set intervals.

I’m sure many hard core Civi developers/users will be perplexed as why we’ve chosen this route given the abundance of well documented alternatives such as using the CiviCRM API, Extensions or even reading files created on the server.  The answer is we were constrained by time and resource. With a bit more free time I’ve started thinking about other ways we could have integrated with our Civi data from Google Drive. For example, for developers I’ve started looking at a Google Apps Script client library to access the CiviCRM REST API. This would provide a way to make REST calls from different script projects whilst using User Properties to globally store API keys and access. Another angle could be to look at how new Apps Script Execution API could be used to provide easy data integration/export options for Google Drive (see recent post on Execution API).

CiviService Library for Apps Script

One final other option would be to create a Google Add-on (created with Google Apps Script) along the lines of the other analytics add-ons available like Supermetrics. This could allow anyone to start querying their CiviCRM data from the comfort of a Google Sheet and achieve similar results to our current custom Civi reporting without some of the messiness.

Supermetrics Add-on for Google Sheets

My talk slides are embedded below (I’ll try and make a recording):