Using CiviCRM with Google Script/Google Drive with the new CiviService Google Script Library

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’ image button).

Google Form

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.

Adding custom columns

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:

Adding custom UI

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:

2d array Object array

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:

Adding a script file

Step 5 – Adding the CiviService Library

To add CiviService to your script project in the script editor:

  1. Click on the menu item “Resources > Libraries…”
  2. In the “Find a Library” text box, enter the script ID 1rlzPrAGx2acG1DHAMqZ1egJh6Y9OqUq3cy8e8AwDjLFGfqmr-hQR0YLq and click the “Select” button.
  3. Choose a version in the dropdown box (usually best to pick the latest version).
  4. Click the “Save” button.

Adding the CiviService Library

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

image

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.

API Explorer

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]

Share