Google Apps Script

At the Association for Learning Technology (ALT) one of the core tools we use for membership management is CiviCRM. CiviCRM has a number of ‘out-of-the-box’ reports you can use and run to summaries and analyse memberships and contributions. Given the flexibility of Civi you can also with a bit of know how create custom extensions and reporting options enabling so very sophisticated analytics. At ALT we are keen to make more use of the data we have on memberships but at the same time have limited resources and time to implement these. In this post I’ll highlight how using Google Sheets we’ve developed a framework that allows us to rapidly develop custom reporting.

Problem

If you are already a CiviCRM user you are probably already aware of CiviReports and the ability create basic custom reports which allow you to view and download data. As part of this you can also schedule reports to land in your inbox. This is great but has it’s limitations. In particular, additional setup is required if you don’t want to just report on a daily basis; you end up with tables of data, with no graphical summaries; and combining current and historic data isn’t possible.

Solution

Scheduling reports at custom intervals

CiviCRM provides an interface to schedule a mail_report. The issue many people discover is this will send reports on set intervals usually hourly or daily. You can schedule individual jobs to run a specific periods but you quickly find yourself in the world of command lines and CRON jobs. Crons are scheduled tasks run by a web server. If you have dedicated admin support this is a fairly standard task and the instructions are easy to follow. At ALT we have the option to open a support ticket with our host but this seems like a waste on time and money.

Our solution is to use a Google Sheet… well a Google Sheet with a bit of ‘juice’. The sheet is shared with our team and anyone can add a CiviReport id to Column A and choose how often it runs in Column B using a data validation list option.

deciding what reports run when

But how does this trigger our civi install to run the job? The juice is Google Apps Script, a cloud based scripting language native to Google Drive. Apps Script is a free service from Google and fortunately for us has the ability to run scripts on configured time intervals. It also has the ability to call specific urls using the build-in UrlFetchApp (similar to CURL). I’ll give you a link to this Sheet so you can setup your own later and when you do you’ll see the entire process is managed with a couple of lines of code included below:

function doTasks() {
  var doc = SpreadsheetApp.getActiveSpreadsheet(); // get spreadsheet
  var sheet = doc.getSheetByName("Tasks"); // get sheet
  var data = sheet.getRange(3, 1, sheet.getLastRow(), COL.total).getValues(); // get values
  var now = new Date(); // time now
  // for each row of the sheet interate accross
  for (var i = 0; i < data.length; i++){
    if (data[i][COL.report_id] != ""){ // if there is instance id do something
      // collect row values
      var report_id = data[i][COL.report_id]
      var type = data[i][COL.type];
      var next_run = data[i][COL.next_run] || 0; 
      // check if it's time to run the report again
      if (next_run < now && type != "never"){
        // if it is ping the report trigger
        var new_next_run = callUrl(report_id, type, {format: data[i][COL.format], ss_id: data[i][COL.ss_id], ss_sht: data[i][COL.ss_sht]} );
        // ..and record when to run again
        sheet.getRange(parseInt(i)+3, 3, 1, 2).setValues([[now, new_next_run]]);
      }
    }
  }
}

What this does is read the sheet data and then iterate across each row. If the report is overdue to be run again it calls a another custom function callUrl which will run the CiviReport and return/write when next to run.

Creating graphical summaries and combining data

By this point you may be sensing that I’m partial to solving problems with Google Sheets. With Sheets it’s fairly straight forward to manually export different reports from Civi and analyse using formula and Charts. The manual export of CiviReports can get tiresome so how can we automate this? Again we return to Google Apps Script. One of the options in CiviReports is to attach the data to the emailed report as a .csv file. From the previous example we can see it is possible to read and write data to a Google Sheet. So if we can get the .csv file from our emailed report we can write it to the Sheet … right?

This is actually more straight forward than you may think as another feature of Google Apps Script is to interact with the script owner’s Gmail.  As part of this we can search for messages and get associated attachments. Using this we can read the latest report from Civi, write the data to a sheet and with a bit of clever formula building automatically get the latest summary or custom chart. As Apps Script runs in a pre authenticated environment, no oAuth handshakes here, the code is relatively straight forward:

function processInbox(){
  var PS = PropertiesService.getScriptProperties();
  var data = PS.getProperties();
  for (var key in data) {
    if (key.substring(0, 10) == "search_str"){
      var param_raw = data[key];
      var param = JSON.parse(param_raw);
      // get last 20 message threads using serach term
      var threads = GmailApp.search(param.search_str, 0, 20); 
      // assume last thread has our latest data
      var last_thread = threads.length-1;
      if (last_thread > -1){
        // get message in the last thread        
        var msg =  threads[last_thread].getMessages()[0];
        // get the attachments
        var attachments = msg.getAttachments();
        for (var k = 0; k < attachments.length; k++) {
          // get the attachment as a string
          var csv_str = attachments[k].getDataAsString();
          // parse string as csv
          var csv = Utilities.parseCsv(csv_str);
          // create destination object
          var doc = SpreadsheetApp.openById(param.ss_id);
          var sheet = doc.getSheetByName(param.ss_sht);
          // clear any old data
          sheet.clear();
          // write new data
          sheet.getRange(1, 1,  csv.length, csv[0].length).setValues(csv);
          // mark message are read and archive (you could also label or delete)
          threads[last_thread].moveToArchive().markRead();
          PS.deleteProperty(key);
        }
      }
    }
  }
}

NB

Data protection

There are a couple of things worth noting here. Google Sheets are a fantastic collaborative environment and with this solution we can still share spreadsheets to selected people in our organisation and beyond. Something to remember though is this script runs as the sheet owner so when configuring the CiviReport it needs to go to the email address of the owner. At ALT we benefit from being a Google for Education user so our email and Drive access comes as part of the Google Apps suite. This solution could also be setup to run on a regular Google account but there are data protection issues to consider sending reports to a non-organisation contact. As such you might only want to re-use this solution as an easy way to schedule reports rather than schedule and process the data.

ARRAYFORMULA, FILTER, QUERY functions are your friends

As our solution dumps .csv data in a sheet, clearing any previous data, any formulas you have in the sheet will also be lost. We get around this by doing all data manipulations in a separate sheet which references the imported data. To save copy and pasting lots of formulas we make extensive use of the ARRAYFORMULA, FILTER, QUERY functions available in Google Sheets.

Comparing CiviEvent registrations

One scenario we have is monitoring the number of registrations to an annual conference. It’s easy for us to export registrations for previous years as static data into a spreadsheet. For the analysis we want to group the number of registrations by week number. To get a week number for the live data we create a second sheet that references it. Using the ARRAYFORMULA you can enter the reference once which is then applied to all the values in the range. For example, we get all the registration dates in column A using =ArrayFormula('2015LiveData'!B:B) in cell A1 and then extract the week numbers in column C by using the formula =ARRAYFORMULA(IF(ISBLANK(A2:A),"",WEEKNUM(A2:A))) in cell C2.

ArrayFormula is your friend

Setting up your own copy of Schedule CiviCRM Reports

If this is a project you’d like to use and/or build upon you can copy a blank version of our template below:

Schedule CiviCRM Reports
[While signed in File > Make a copy]

Once you have a copy open Tools > Script editor to see all of the project code and instructions for setting up.

I’ve hopefully given you enough to go on to setup but feel free to leave a comment if you get stuck or have any questions.

Enjoy!

Example of pdf version from altc 2014At ALT we use Google Sheets as an easy way to share and collaborate on draft event timetables. Recent examples are the ALT Annual Conference 2014  and the OER15 timetables. One of the reasons for publishing draft timetables using Google Sheets is  we can get a static url for people to download it as PDFs but the contents can be dynamically updated (see recent post on doing this). The template we use for conferences is continually evolving which isn’t an issue as it’s easy to copy the last version. One headache is that our theme colour usually changes. This can be a bit fiddly change as we use empty cells to create a thicker grid:

Thicker borders

Faced with another cell background switch it made sense to actually do this with code rather than clicks and thanks to Google Apps Script possible in 19 lines of code and a couple of minutes:

function colorReplace() {
  var doc = SpreadsheetApp.getActiveSheet();
  // get all the existing active sheet background colours
  var cells = doc.getRange(1, 1, doc.getLastRow(), doc.getLastColumn()).getBackgrounds();
  var rows = cells.length;
  var cols = cells[0].length;
  // iterate accross
  for (var i = 0; i < rows; i++){
    for (var j = 0; j < cols; j++){
      if (cells[i][j] == '#feeff8'){ // first color to change
        cells[i][j] = '#f3f3f3'; // first color change to
      } else if (cells[i][j] == '#bf0875'){ // second color to change
        cells[i][j] = '#079948'; // second color to change
      } 
    }
  }
  // update backgound colours
  doc.getRange(1, 1, doc.getLastRow(), doc.getLastColumn()).setBackgrounds(cells);
}

Tip

To get the existing cell background colour I used the debugger setting a breakpoint before the loop to see the existing cell colour HEX codes:

debugger to inspect cell colours

3 Comments

My old colleague at Cetis, David Sherlock, posted a nice little  ‘Twitter Question/Revision Bot’. This uses a .csv file of questions and multiple choice answers which get randomly tweeted out using a Python script. David designed the project with a Raspberry Pi in mind but also highlights it can be easily run on any Unix like environment such as Mac OS X or Linux. As not everyone is going to have easy access to this here’s how you can do something similar with Google Sheets (if you don’t want to play copy and paste coding make a copy of this sheet).

1. Setting up a Google Sheets environment to handle it

  1. Start with a new Google Sheet and like David have six columns in each row with question, answer, three options (one of which the correct one) and an extra row to record if the question has been asked.
  2. In your spreadsheet open Tools > Script editor and when asked start a ‘Blank Project’
  3. In the new editor window select Resources > Libraries (this will first prompt you to give your project a name, I called mine TwitBot.
  4. In the ‘Find a Library’ box enter MarIlVOhstkJA6QjPgCWAHIq9hSqx7jwh and click Select
  5. You should now see ‘TwtrService’ listed as one of the libraries. In the ‘Version’ dropdown select the latest version (at time of writing 14), and click Save

TwtrService is a library I’ve written so interact with the Twitter API. You can read more about it here.

In the code window add the following code and click save:

function setup() {
  if (TwtrService.isUserConnectedToTwitter()){
   var result = Browser.msgBox("Twitter Authorisation",
                   "You appear to already be connected to Twitter.\\n\\nWould you like to run the setup again?",
                   Browser.Buttons.YES_NO);
    // Process the user's response.
    if (result == 'yes') {
      // User clicked "Yes".
      TwtrService.showTwitterKeySecret(SpreadsheetApp);
    }
  } else {
    TwtrService.showTwitterKeySecret(SpreadsheetApp);
  }
}

The above code uses the TwtrService to help you set up Twitter access if required

Your script window should look like this:

script editor

2. Create a Twitter App

If you’ve used my other TAGS templates you can reuse your details for this. To see if Twitter App details are required from the script window select Run > setup (if setup isn’t listed you need to first save your code). Running setup will start the first part of the authentication process. Click continue and review the authentication required and ‘Accept’ if you are happy.

Auth required

Going back to the spreadsheet you started there should now be a dialog window asking you to do something. If you haven’t setup a Twitter App before it should look like this:

Twitter app creation

Follow the instructions onscreen to create your app.

Important: The Twitter account you use to authorise access is the one that will send out the tweets

3. Write a Python Google Apps Script

Add the code below to your existing script project and save:

var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName('Sheet1');

function tweetQuestion(){
  var ran = Math.floor(Math.random() * (sheet.getLastRow()-1)) + 2;
  var row = sheet.getRange(ran, 1, 1, sheet.getLastColumn()).getValues()[0];
  if (row[5] !== ""){
    tweetQuestion(); // if already asked pulls another random row
  } else {
    var tweet =  "Q: " + row[0];
    var tweet2 =  row[2];
    var tweet3 =  row[3];
    var tweet4 =  row[4];
    
    TwtrService.post('statuses/update', {status: tweet});
    var options = [tweet2,tweet3,tweet4];
    shuffle(options); 
    TwtrService.post('statuses/update', {status: "A: " + options[0]);
    TwtrService.post('statuses/update', {status: "B: " + options[1]);
    TwtrService.post('statuses/update', {status: "C: " + options[2]);
    sheet.getRange(ran, 6).setValue(new Date());
  }
}

// http://stackoverflow.com/a/25984542/1027723
function shuffle(a,b,c,d){//array,placeholder,placeholder,placeholder
 c=a.length;while(c)b=Math.random()*c--|0,d=a[c],a[c]=a[b],a[b]=d
}

4. Time the script to run every hour or so

In the script editor select Resources > Current project’s triggers and click ‘No triggers set up. Click here to add one now.’. Select to run tweetQuestion every hour (or your preference), and also click ‘notification’ so you can get an email if the script fails. Finally click ‘Save’

Timed triggers

What will happen now is the function even if you don’t  have the spreadsheet or script editor open or even your browser.

Important: when this script runs out of questions it will go into an infinite loop. You can go back into the trigger window to remove the function at any point. If you don’t you’ll end up using all of your script runtime quota. You homework is to figure a way to get the script to bail if there are no questions left.

TAGSPresenter

Later today (2.30pmUTC) I’ll be presenting at #oer15 about Twitter in open education (tune in here). As I wanted to highlight the network effect of Twitter I wanted to engage not just the room, but leave ‘footprints’ as for others to follow. I know people like Alex Couros and Alan Levine have done cool stuff live tweeting from Keynote. I’ve dabbled with doing stuff with Microsoft PowerPoint but was never fully satisfied. Given Twitter now supports a number of embedded media formats I thought rather than trying to fit Twitter into another presentation tool, to turn my live tweets into my slides.

And so TAGSPresenter is born! Using a Google Sheet as an editor, Google Drive to host images and a bit of Google Apps Script to glue it together I’ve got my own Twitter based presentation tool. I don’t have time to write about how it was technically achieved but if you want to peak under the hood of the hack here are my ‘slides’ which are published here.

Tune in at 2.30 to see how it goes ;)

3 Comments

There was a question that came up in the Google Apps Script G+ community about moving a row of data to another sheet. The person was reusing some code posted by Victor Yee back in 2012 which hooks into the onEdit event in Google Sheets. The idea is a Google Form is used to collect data into a Google Sheet. Someone then looks at the data entered and decides if it should be actioned. If yes then the data is moved to an appropriate sheet within the spreadsheet. The route of the problem appeared to be not only has Google Apps Script changed a lot since then but so has Google Sheets and Forms. In particular it looks like new Sheets “Cannot cut from form data. Use copy instead.”:

Cannot cut from form. Use copy instead

To use ‘copy’ instead in Victor’s code you would replace moveTo with:

s.getRange(rowIndex, 1, 1, colNumber).copyTo(target);

and add the line afterwards of

s.deleteRow(rowIndex);

which will delete the row just changed. I’m not sure why moveTo doesn’t work. Perhaps there is conflict between the onSubmit and onEdit events. Looking through Victor’s code I was surprised he didn’t use the onEdit fields available. For example:

FieldExampleNotes
e.sourceSpreadsheetA Spreadsheet object, representing the Google Sheets file to which the script is bound
e.rangeRangeA Range object, representing the cell or range of cells that were edited
e.value10Only available if the edited range is a single cell

.. so I've reworked into:

/**
 * Moves row of data to another spreadsheet based on criteria in column 6 to sheet with same name as the value in column 4.
*/

function onEdit(e) {
  // see Sheet event objects docs
  // https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
  var ss = e.source;
  var s = ss.getActiveSheet();
  var r = e.range;
  
  // to let you modify where the action and move columns are in the form responses sheet
  var actionCol = 6;
  var nameCol = 4;

  // Get the row and column of the active cell.
  var rowIndex = r.getRowIndex();
  var colIndex = r.getColumnIndex();
  
  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
  var colNumber = s.getLastColumn()-1;
  
  // if our action/status col is changed to ok do stuff
  if (e.value == "ok" && colIndex == actionCol) {
    // get our target sheet name - in this example we are using the priority column
    var targetSheet = s.getRange(rowIndex, nameCol).getValue();
    // if the sheet exists do more stuff
    if (ss.getSheetByName(targetSheet)) { 
      // set our target sheet and target range
      var targetSheet = ss.getSheetByName(targetSheet);
      var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);
      // get our source range/row
      var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
      // new sheets says: 'Cannot cut from form data. Use copy instead.' 
      sourceRange.copyTo(targetRange);
      // ..but we can still delete the row after
      s.deleteRow(rowIndex);
      // or you might want to keep but note move e.g. r.setValue("moved");
    }
  }
}

which you can also get by making a copy of this sheet (Update: remember to open Tool > Script editor and then click Resource > Current project triggers to add the onEdit event to the function). See also Michael's comment about using var s = e.range.getSheet();

A question came in on the Google Apps Script Google+ Community which in part was asking about parsing .csv files into a Google Sheet. I saw the question come in over my phone and knowing it was very achievable directed the person to the Interacting With Your Docs List tutorial on the Google Developers site as a starting point. In particular this tutorial included a function to parse a .csv file using regular expressions. Shortly after +Andrew Roberts kindly pointed out  that Apps Script includes a parseCsv method … doh. +Marcos Gomes then pointed out that the tutorial uses the Docs List Service that was deprecated on December 11, 2014 … doh. Given the tutorial I referenced was written in May 2010 it’s not surprising given the updates in Apps Script that it’s now out of date and the tutorial itself should probably be carrying a deprecated notice. One of the really nice things about the Google Developers site is most, if not all, the documentation is released under Creative Commons Attribution 3.0 License. So for my penance below is a reworking of Tutorial: Interacting With Your Docs List CC-BY Google Inc. (Jan Kleinert)

...continue reading

Back in 2012 I shared some of the exploratory work I did issuing Mozilla Open Badges with using a combination of Google Apps Script and Google Sites. This solution had bit of a fudge which required the badge Assertion (the JSON data file behind each individual badge), to be proxied via a web host. It was great to see this post was followed up by David Mullet who was able to add some additional functionality as well as avoiding the badge proxying. Since then I've also revisited Open Badges, this time as an add-on to issue badges from self-hosted WordPress sites. As part of this I learned more about issuing badges and in particular the Mozilla Issuer API. The Issuer API is a JavaScript library which makes it easy for you to let people add badges they've achieved to their badge portfolio in the Mozilla Backpack. Late last year I decided to revisit Open Badges and Apps Script, this time integrating the Issuer API into a Script powered web app, removing the need send people to a Google Site. This wasn't as straight forward as I had anticipated and but in this post I'll cover what is possible.

Why?

Survey participant emailSo why issue badges directly from Google Apps Script? This was inspired by feedback from a survey we were running at ALT. This was distributed as a Google Form. To help improve the response rate we included a feature to receive an email confirming the respondent's participation in the survey. The confirmation, sent using Apps Script, included a suggested tweet and a digital badge for them to display highlighting participation. Because the badge wasn't in the Open Badge format respondent's couldn't add it to their Mozilla Backpack. I could have of course reused the earlier work I and David have done on issuing badges via Google Sites, but wanted to see if it was possible to remove that dependency and issue Open Badges from a single container bound script or by embedding an Apps Script in Google Sites.

What’s possible?

Looking through the source code of the Issuer API you can see it has two methods for launching a badge claim interface. The default, OpenBadges.issue (code here) is a full screen modal interface created by injecting a iframe. The fallback, OpenBadges.issue_no_modal (code here), adds the badge data to a web form which is automatically submitted to the Backpack to handle. At the time I was exploring this problem, the method for creating html based interfaces in Apps Script was restricted to a sanitised version of HTML which didn’t support the modal/iframe way of doing things. That all changed in December when Google announced an alternative way of serving HTML in Apps Script that skipped most on the sanitisation (see Speeding up HtmlService in Apps Script).

This all sounds promising, directly wrapping the Issuer API JavaScript library in a Apps Script powered web app. Trying this out I hit my first issue:

I'm using Google Apps Script Content Service to generate a badge assertion (1.0.0 spec), example here https://script.google.com/macros/s/AKfycbx5uvVcmmHweZwhIxzO0IAUrUtY_cW88Sz1B-MpquZxopvfyIY/exec?type=assertion&uid=54354354354-2

When trying to issue the badge using the Issuer API the issuer returns a 504 bad gateway example header response. Using Google Apps Script Content Service there are 302 redirects are in place for the badge assertion which end in 200 OK (this is how Google configure their service). Here is example 302 redirect headers and example eventual 200 OK.

Using Google Apps Script Content Service to create badge assertions appears to only affect assertions that include verify.url (a 0.5.0 spec assertion is issued without issue).

A 302 redirect 1.0.0 spec assertion was issued okay e.g. http://labs.hawksey.info/badges/1_0_spec_redirect.json which suggests something else in the headers is causing the 504 bad gateway

If you fill in this form with your Backpack email it sent a link to a Google Site where you should be able to claim a badge associated to your email https://docs.google.com/a/hawksey.info/forms/d/1IKfJQGu1spJyTpkPWYL8TlXcRFQxm8gv8ZMATXcVQWU/viewform

I'm still getting a 504

A lot to take in here. Basically Open Badges can use a JSON object for each badge also known as the Assertion.  The Assertion spec is at 1.0 which requires  a verification url for the badge. The verfication url is the same as the url where the Assertion lives. Here is an example badge generated using Apps Script:

Now if you hit the verify.url: "https://script.google.com/macros/s/AKfycbx5uvVcmmHweZwhIxzO0IAUrUtY_cW88Sz1B-MpquZxopvfyIY/exec?type=assertion&uid=54354354354-2" you get passed through a 302 redirect:

302 redirect

In terms of Badge Verification “eventual 200 OK” is permitted:

The use of the term "eventual 200 OK" is meant to mean that 3xx redirects are allowed, as long as the request eventually terminates on a resource that returns a 200 OK.

Using the Open Badges Validator throwing in the verify.url returns a valid Assertion

valid Assertion

So the Mozilla Backpack issue ticket remains open. Currently, as far as I can see, a version 1.0 spec Assertion generated in Apps Script 504s in the Issuer API. If you are desperate to issue Open Badges in Apps Script NOW!!! the fix ain’t pretty … the Open Badges 0.5 spec assertion (bloody nightmare to find this reference …). This is basically where I was in 2012, a badge assertion that doesn’t use a verify.url.

Lets however look at a couple of example workflows for issuing Open Badges using Google Apps Script.

Example: Using a Google Form to Issue Open Badges via a Google Site

Back to the original scenario of giving someone an open badge for completing a Google Form.  Here’s an example Form/Sheet you are free to copy/reuse. Things to note are:

  • an extra sheet called IssuedBadges; and
  • some code in Tools > Script editor (source extracted into a gist) which needs you to go to Resources > Current project’s triggers and add a onSubmit trigger .

The code is hooked into the form submission and records a new badge in the IssuedBadges sheet and generates an email to the recipient to collect their badge (note current issue with duplicate emails from Apps Script). You’ll also see from the code it includes a BASE_URL, this is the place we’ll send people to  collect their badge. A Google Site can happily have a container bound Apps Script, but in this example I want to highlight how you can have a standalone script embedded in a Google Site. So our next bit of code is a standalone Google Apps Script which handles the Open Badges Assertion creation and issuing. You can copy and then modify the standalone script here or browse as a gist).

Hopefully the source has enough in-line documentation to make sense of it. Basically the code looks for the type of data it’s returning. If it’s another machine it throws JSON and for humans shows the Mozilla Issuer API which will help the person collect their badge in the url.  To deploy this code we need to publish it as a web app. If you haven’t done this before from your copy of the script in the Script Editor select Publish > Deploy as web app which should give a similar dialog to the one below:

Deploy as web app

A couple of neat things here. For this to work you need to allow the app to ‘execute as me’. This lets the script access the Google Sheets your account can access. This means the script will selectively be able to read the Google Form responses you get. We also allow the script to run for anyone, even anonymously. This doesn’t mean that anyone can start reading all of your data, the script is programmed only to ready specific data and only return a specific interface designed by us.

When you hit ‘Deploy’ this gives you a ‘service url’. You can try this url and you’ll get a browser window with something like:

image

Back at the script attached to your Sheet/Form you could replace the BASE_URL with ‘service url’ (this line of code). So when someone fills in your form they will get an email to your deployed script which will have the extra bits needed in the link to issue them a badge. This isn’t great UX so instead lets see how you can use the same url in a Google Site, the advantage being you can provide additional information about collecting a badge in a Google Site. Fortunately the Google Developers site has some great guidance on Embedding your web app in Google Sites which should allow you to paste in your ‘service url’.

You can adjust the gadget settings like size and border, and of course add extra information. To see all of this in action enter your email in the form below and you should get an email with a ‘claim link’ to the ‘sandbox’ site (emails won’t be shared or used for any other purpose than sending the claim url).

Wrapping up

So if you’ve made it this far !!!WELL DONE!!! (#epic). Some things to note. This example currently only works with 0.5 spec Open Badge Assertions. Hopefully the 504 issue with Backpack/Issuer API is resolved. Some of you may have noticed that in this example I opted to use the issue_no_modal of the Issuer API rather than take full advantage of the new Apps Script IFRAME/modal mode. The reason I didn’t is there are some open issues with IFRAME and Caja’d version using no_modal Issuer API works perfectly fine (I’ve left the iframe version in the code if you prefer this way).

Enjoy and as always comments to clarify any of this are welcome

It’s been a while since I've had a chance to blog about a failed attempt to do something. Recently I was trying to get full screen access in Google Drive using Google Apps Script and I couldn't get it to work resulting in this feature request (star to vote ;). In this post I want to share some of my thought processes and highlight why this would be a useful feature.

In the middle…

One of the nice features of Apps Script is access to custom dialogs and interfaces in Docs, Sheets and Forms. These can be generated using the HtmlService, which enables you to write very rich and interactive interfaces. Until very recently all the Html you created went through Google’s Caja Compilier. Caja created a lot of headaches for Apps Script developers striping and reformatting html tags and restricting what you could do. For example you could forget using SVG (scalable vector graphics) making it impossible to use well established visualisation libraries like D3.js.

Román Cortés 1kb Christmas Tree in Apps ScriptHowever, in December the Google App Script team announced Speeding up HtmlService with the IFRAME sandbox mode. With this there is now the option to skip Caja sanitisation and a whole host of modern browser-based application deployment. For example here’s  Román Cortés 1kb Christmas Tree in Apps Script (I've tipped it over the 1k mark as it was throwing an error in Chrome. - the source code for this project is here). Huh turns out with blocks can't be used anonymously, as script owner this works fine :(

The IFRAME mode should not be confused with the ability to use <iframe> to embed your published scripts. This isn't currently possible for security reasons and there is an issue ticket for it.

In the beginning…

My interest in using custom visualisations in Google Sheets stems back to 2011, my first attempt wrapping Protovis SVG graphs, the precursor to D3.js, as a Google Spreadsheet Gadget. Alas Spreadsheet Gadgets got deprecated in the ‘spring clean of 2012’. One of the big advantages of Spreadsheet Gadgets was you didn’t have to publish the data to be able to generate a visualisation, (a route I use for my TAGSExplorer tool). Unfortunately I don’t have a screen shot of how the Protovis Gadget rendered so you’ll have to make do with a later version which switched to D3.js:

Google Spreadsheet Gadget Example

With the new IFRAME  mode there is an opportunity to re-explore how custom visualisations and analytical tools could be integrated in Sheets as well as Docs and Forms. Bruce Macpherson has already shown an example of D3.js in the Sheet sidebar:

CC-BY-SA brucemcpherson
CC-BY-SA Bruce Mcpherson - D3.js in the Sheet sidebar

With this we've come almost full-circle with the ability to create custom visualisations around data in Google Drive without the need to publish it. One limitation here is the amount of screen space you can play with. Sidebars are restricted to 300px wide. You can use dialog windows but these would have to be a fixed size and as far as I'm aware there are no browser detection methods for factor in the available screen size. But lets not forget that I'm predominately talking about scalable vector graphics here and most modern browsers now support the Fullscreen API (Fullscreen API spec). To see this in action embedded below is an iframe of this page, which if supported by your browser, will let you to pop it to fullscreen using the button (I'm just using a raster image to keep with the Xmas theme ;).

So using Apps Script we could have fixed size interfaces with the ability to stay in Docs, Sheets or Forms interface but pop to fullscreen … Well as you have probably already noticed from the post title nope. Here’s a Sheet where I’ve used the same full screen code in a custom dialog. Clicking the ‘Image Test’ link in the spreadsheet opens the dialog but while the fullscreen script runs nothing happens. Initially I thought it was something wrong with my code but trying a YouTube embed within a custom dialog resulted in now full screen option being included.

YoutTube Embed - no full screen

In the future…

Having access to Fullscreen API would be one way to get us back to 2011 and there may be other feature requests around embedding custom interactive graphs in Google Drive. If you’d like access to the Fullscreen API in HtmlService here’s the feature request.

Oh and happy holidays ;)

1 Comment

+Tom Smith kindly reminded me that there are existing Google Form Add-ons to achieve a similar result without having to mess with code. So you might want to have a look at Choice Eliminator by Bjorn Behrendt and formLimiter by Andrew Stillman

You can imagine the scenario, you’ve got a Google Form perhaps setup as a booking form and a select option for picking timeslots. As the slots fill up you’d like the option to be removed. I was surprised not to find an example of this so here is a rough sketch of some code that might do the job. We start with a basic form with a ‘choose from list’ option:

Select option

and in the linked Google Sheet we have an extra sheet to keep tally:

tally

In this sheet I’ve got a column with quotas for each of my select options and a calculation for how many are left =B2-COUNTIF(responses!B:B,"="&A2)

To dynamically modify the Google Form we need to add some code. Here’s a basic snippet that reads our options and quotas and rewrites the select options (note the inline comments for bits you’ll need to edit):

function availableSlots(){
  var form = FormApp.openByUrl('URL_OF_YOUR_FORM'); // TODO add your form url
  // need to read what slots are available
  var slots = SpreadsheetApp
                .getActiveSpreadsheet()
                .getRange("slots!A2:C10")
                .getValues(); // TODO make sure getRange matches your quota range
  var choice = [];
  // loop through our available slots
  for (s in slots){
    // test if slot still available
    if (slots[s][0] != "" && slots[s][2] > 0){ 
      choice.push(slots[s][0]); // if so we add to temp array
    }
  }
  var formItems = form.getItems(FormApp.ItemType.LIST); // our form list items
  // TODO assumption that first select list is the one you want to change  
  // change formItems[n] if you have more than one select list
  // and we just rewrite all the options to ones that are free
  formItems[0].asListItem().setChoiceValues(choice); 
}

To enable this you need to use the Tools > Script editor in you Google Sheet and then Resources > Current project’s triggers. At this point to may be prompted for authorisation to read the data and change the form. Once that’s done add the availableSlots function to trigger on form submit.

Current project’s triggers

If you’d like to shortcut all but this last step make a copy of this template.

Notes

Some things to be aware of. If you have concurrent users submitting the form someone may sneak in. Apps Script has ways of preventing this but not as far as I’m aware for Google Forms. It’s worth remembering that do don’t need to use Google Forms at all for getting data into a Google Sheet and if you can host your own HTML form you can make POST/GET requests. If you develop this example further I’d be interested to hear about it. Enjoy!

1 Comment

In the Apps Script G+ Community Scott Marquardt asked:

Youtube API question.

Argh, I can't find a single example of anything like this. With my current knowledge, I have a hard time inferring from the API reference documents alone, to Apps Script.

My goal is a script to add an item to a Youtube playlist.  My intended workflow is a Google form to enter video IDs into a Google sheet, with a triggered script adding each new video ID to a hard-coded playlist.

I'm unashamedly panhandling here, on behalf of what would be a really valuable use case in a dozen of our special education schools.

I agree with Scott that the Apps Script Advanced Services documents (here’s YouTube) don’t give you much to go on. The solution is actually relatively simple once you get your head around the shape of the data required. So here is a form linked to this playlist (I’ll turn this off as soon as it starts  getting spammed). In the spreadsheet receiving responses the Tools > Script editor has the following code (in the script you’ll see where you need to hard code the playlist you want to add videos to):

Update: Scott asked about adding notes/comments to playlist items and I've updated the code to do this to the gist

To get this working there are a couple of hoops to jump through. As the YouTube API is an Advanced Service and it must be enabled before use. For this project to do this you need to be in the Script Editor then:

  1. Click Resources > Advanced Google Services…
  2. Scroll down to YouTube Data API to turn it on then click the ‘Google Developers Console link:
    Enabling Advanced Services
  3. In the Google Developers Console find and turn on the YouTube API. After it’s enabled you can close the Console window

You can only interact with YouTube Channels the account authorised to run the script is the owner of (see related post by me on this).

The final step is to setup the script to trigger when the form is submitted. To do this while still in the Script Editor  select Resources > Current project’s triggers and add a run addVideoToPlaylist event on form submit.

Current project triggers

If you would prefer to not do all the code copy and pasting you can File > Make a copy of this sheet (you’ll still need to enable Advanced Services and the form submit.

There’s obviously lots more you could do with this script like putting submissions into a moderation queue but I’ll let you go and play with those ideas.