At the Association for Learning Technology (ALT) we use the open source CiviCRM for membership and events management. I’ve previously described how we are able to do Custom regular reporting from #CiviCRM with Google Sheets and Google Apps Script which is able to trigger reports from Civi which are emailed to a Gmail account before being saved to Google Sheets. I premiered this work at CiviCon London 2015, at the time hinting that there were other ways of integrating Google Drive with CiviCRM. I’m back at CiviCon London again showcasing some of my latest work. This time I’ll be highlighting the CiviService Google Script Library I’ve written. This allows you to easily create custom workflows in Google Drive with your CiviCRM installation via it’s API. To give you an idea how you might use this I’ve created a demonstration project that uses Google Forms for ALT Members to submit events. Responses are validated in a Google Sheet and with a couple of clicks can automatically be used to create a CiviEvent on our site.
If you haven’t used Google Script (formerly known as Google Apps Script) before there is a lot to get your head around so in this post I thought I’d go through our event solution as a demonstration. If you are just interested in using the CiviService Google Script Library the id is 1rlzPrAGx2acG1DHAMqZ1egJh6Y9OqUq3cy8e8AwDjLFGfqmr-hQR0YLq
and you can jump down to step 5-6 to see how to use it or checkout the README on Github. If you’d just like a version of the final project detailed below here is a copy of the Google Sheet/Form
Step 1 – Creating a Google Form
I’m not going to go into detail about how to create your Google Form. The main thing is you include fields for event title, start/end dates. Google Forms has some nice build in features for data validation you can use and special input types for dates and times. The copy of the shared Google Sheet/Form will give you an idea of some of these (if you are creating a From from scratch you can add a Google Sheet by clicking on the Responses tab and click the ‘Create spreasheet’ button).
Step 2 – Add some extra columns to your form responses
In the Google Sheet collecting the Form responses you can start building your custom workflow. In this example I’ve added two extra columns called Action and Link. In the Action column I’ve used the build in List of Items Data Validation to create an in-cell dropdown list. There might be other ways you tweak your workflow for approving submitted events. If you want to skip any manual validation you could add some code to automatically add events when they are submitted. You might want to look at free add-ons like Form Notifications to send emails when the form is submitted.
Step 3 – Adding some custom interface
The idea with this project is that once an event is submitted someone checks it before deciding if it should be added as a CiviEvent. To give the user a menu item to process any decisions we’re going to add a custom menu to our Google Sheet like this one:
To do this from your Google Sheet collecting the form responses open Tools > Script editor.. and add the following code:
// Adding some basic UI function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('CiviCRM') .addItem('Add events', 'processEvents') // menu item name and function .addToUi(); }
If you now Run > onOpen you’ll be prompted for permissions and in your Google Sheet you should see a CiviCRM menu option. The menu doesn’t do anything yet as we haven’t included the porcessEvents
function. If you are interested you can find out more about customizing Google Sheets, Docs and Forms interfaces.
Step 4 – Processing the Google Sheet data
Back in the Script Editor add the following code to your project:
// function we call to process the events submitted function processEvents(){ var doc = SpreadsheetApp.getActiveSheet(); var data = doc.getRange(1, 1, doc.getLastRow(), doc.getLastColumn()).getValues(); var dataObj = rangeToObjects(data); for (i in dataObj){ // if action is 'Add to Civi' and no link then process if (dataObj[i].action === "Add to Civi" && dataObj[i].link === ""){ addEvent(dataObj[i]); // Civi API call to add event } } }
Lines 3-4 of this code load the data from our response collection sheet the .getValues()
returning a 2d array of the sheet. On line 3 we call a custom function .rangeToObjects()
which turns the 2d array into an object array using the column headings as key values illustrated below:
To use the .rangeToObjects()
function we need to add this functionality to our project. The .rangeToObjects()
function has been written by a third party developer James Ferreira and there are a couple of ways we can add this to our project. For the purposes of this project we are going to copy/paste the source code but here you can read more about ObjService. To do this we are going to add another script file to our project to make it easier to navigate in our project. This is done in the Script Editor by clicking File > New > Script file. Give your new script file the name ObjApp. When you’ve pasted the code from here in your script editor should look like this:
Step 5 – Adding the CiviService Library
To add CiviService to your script project in the script editor:
- Click on the menu item “Resources > Libraries…”
- In the “Find a Library” text box, enter the script ID
1rlzPrAGx2acG1DHAMqZ1egJh6Y9OqUq3cy8e8AwDjLFGfqmr-hQR0YLq
and click the “Select” button. - Choose a version in the dropdown box (usually best to pick the latest version).
- Click the “Save” button.
Step 6 – Initializing the CiviService Library
To start making API calls you need to setup your CiviCRM installation to use the REST API. The three pieces of information you need are a site key (key
), user api key (api_key
) and your api base url (api_base
= [this_bit]/civicrm/extern/rest.php
). There are a couple of ways you can store/use these details. In this example I suggest adding them to the Script Properties dialog. You can do this in the Script Editor > Project properties and then opening the Script tab and store the values with the property names: api_base
, api_key
and key
To pass these values to the CiviService library you can add the following code to your script project:
var config = PropertiesService.getScriptProperties().getProperties(); CRM.init(config);
Step 7 – Making calls to your CiviCRM API
The final piece of code you need is the addEvent()
function. The easiest way I’ve found to experiment and build CiviCRM API calls in the API Explorer. Your Civi installation should have this built in at [your_site]/civicrm/api#explorer or you can use the one on the demo installation at http://dmaster.demo.civicrm.org/civicrm/api#explorer (username/password: demo). Using this let you find the entities and parameters you need generating code snippets you can copy and paste.
Hopefully the example Event create call looks familiar in the final code snippet we need in our project:
// add event to civi with row details function addEvent(evnt){ CRM.api3('Event', 'create', { "sequential": 1, "title": evnt.eventTitle, "description": evnt.eventDescription, "start_date": combineDateTime(evnt.startDate, evnt.startTime), // As Google Forms have seperate date and time form elements combine these e.g. 201610050930 "end_date": combineDateTime(evnt.startDate, evnt.endTime), // "event_type_id": getEventTypeValue(evnt.eventType), // BONUS if our event labels and values don't match to addition lookup "is_active": 1, "is_public": 0 }).done(function(result) { if(!result.is_error){ // if event added put url in the sheet var doc = SpreadsheetApp.getActiveSheet(); // assuming Link column is 17th along... doc.getRange(parseInt(evnt.rowNum)+1, 17).setValue("https://www.alt.ac.uk/" + "civicrm/event/info?reset=1&id=" + result.values[0].id); }; }); } /** * combine a date and time into a string of yyyyMMddhhmmss * * @param {Date} date. * @param {Date} time. * @return {string} of date+time. */ function combineDateTime(date, time){ return Utilities.formatDate(new Date(date.getFullYear(), date.getMonth(), date.getDate(), time.getHours(), time.getMinutes(), time.getSeconds()), Session.getScriptTimeZone(), "yyyyMMddhhmmss"); }
Your script editor should be similar to the code here. Once you have saved your script and added an event via the Google Form, when you add the ‘Add to Civi’ in the action column in the responses sheet and run the CiviCRM > ‘Add events’ menu option if everything goes well you should see the Link column populated with a link to the event created on your Civi site. If it’s not working for you try a copy of the Google Sheet/Form and completing step 6 from above.
Easter eggs
In the code repository there are a couple of bonus features. In our case we wanted to include an Event Type. As our Event Type labels and values don’t match we make another call to our API to return the option value. Also as are Event Types might change we have another bonus function we can call that can dynamically update the Event Type in the Google Form.
[This project was presented at CiviCon London 2016 6th/7th October – Slides available on Slideshare. Thanks goes to Bruce Mcpherson for Promise Polyfill]