Connecting Strava and Google Sheets using IFTTT

Update (2016-06-19)

Bad news: My cheeky hack to get Strava data into Google Sheets is no longer necessary.

Good news: Things got a whole lot simpler last month when IFTTT officially started supporting Strava! Instead of following the instructions in this article, I strongly recommend using this IFTTT recipe.


Last year (2014) I made a goal of cycling or running to work at least 100 times. I ended up doing 172!

This year, I moved from Canada to London, but not before doing over 252 commutes! (Note: I stretched the definition of commute to include any time I saved at least 5km of driving or transit)

I won’t reach 300 this year (my original goal) but instead of going sans goal until 2016 (I am very motivated by goals, even if they are completely arbitrary), I decided to set a new goal.

Cycle 1000km on my “new” bike before the end of the year.

So I made a quick chart in Excel.

newgoal excel

But then I realized that this would be a great opportunity for some tinkering. You see, the Excel chart above requires the dreaded MANUAL DATA ENTRY. If there was a way for me to use my beloved Strava to automatically update a similar chart, I’d be set.

So I tried to plug into the Strava API. But then got a little overwhelmed when it started asking me about authorization codes and application names.

I then went to my favorite “automate everything” site, IFTTT. While they don’t support Strava directly, they do support RSS feeds, and FeedMyRide allows you to create an RSS feed from your Strava activities. IFTTT allowed me to easily create a recipe for hooking up RSS to Google Sheets.

Add in some parsing, some array formulas, and a nice chart et voila: As you can see here, I now have automatic updating of my progress toward my goal!

1000km complete!

I’m a little behind on my goal as I write this(November 24), so stay tuned to see if I manage to catch up!

Update (01/03/2016)

I did it! I ran into a few hiccups along the way, such as four (mostly) unrelated flat tires in two days, an unexpected house move closer to work, and a cold/flu that lingered for a couple weeks, but my mileage at midnight on December 31st, 2015 was 1011.9km*.

mission accomplished!

Mission Accomplished! Me celebrating hitting the 1000km mark on a sunny New Years Eve day at Tower Bridge

*One little disclaimer: I had to cheat a little by including the approximately 50km I cycled in Copenhagen while on vacation there, so technically my new bike’s 2016 mileage was just under 1000km.

Anyway, I found it a very powerful motivator to have my goal out in public for all to see (even though, if we’re being honest, I’d be surprised if more than two people saw said goal). Also, I really enjoyed trying to keep the blue line above the red line in real time. And I absolutely loved exploring the sights of London. I may not keep up the pace of 500 km per month in 2016 but I can see myself continuing to cycle to work most days.

Customizing Google Forms and Sheets

The Client

Alberta Science Network (ASN) is a charity that is very close to my heart. They have several educational initiatives, including “Scientists And Engineers in the Classroom”. Through this program, from 2011 to 2015, I made over 40 presentations at local schools, teaching over 1500 students about Chemistry using magic and hands-on activities.
Here’s a quick video I whipped up when ASN wanted their volunteers to talk about what they do for the organization.
ASN approached me asking if I knew of any technologies or people who could help smooth out some of their processes. I initially declined because it was a busy time at work, but later approached the organization to see if I could help out.

Before

Here’s a screenshot of the member form that was in use originally.
Original Form Screenshot
More importantly, here’s the original process:
  1. Member fills out form
  2. ASN staff gets an email with form data
  3. ASN staff copies form data into a local spreadsheet
  4. At end of year, all members are sent an email, asking them to fill out the form again

This is quite a labor-intensive process for staff, the data is not backed up, and it does not make it easy for members to renew.

After

As you can see below, the form was remade using Google Forms. The look and feel wasn’t significantly changed but a few unnecessary fields were removed and the fields were aligned and spaced further apart from each other.
New Form Partial Screenshot

The Process

Going into the project with ASN, I was hoping to be able to use Ruby on Rails (RoR) to make a custom solution because I had recently done the fantastic Rails Tutorial by Michael Hartl. After considering RoR and looking into Customer Relationship Management tools such as the open-source platform CiviCRM, I realized that Google Forms and Sheets could do the trick in considerably less time.
Google automatically inserts form data into a spreadsheet, but there were a few things that weren’t automatically supported. For example, ASN has annual memberships, so every June, a script goes through and sets all members to inactive then invites them to renew their membership. In order to improve the renewal rate, I used the following script to pre-fill the membership form for these renewal emails.
/**
* Use Form API to generate pre-filled form URLs
* Based on http://stackoverflow.com/a/26395487/1704355
*
* Note: This script can either be run manually or linked to a trigger such as a time-based trigger.
*/

function GeneratePrefilledURLs()
{
  var range = GetSpreadsheetDataRange();
  var data = range.getValues();
  var headerRow = data[0];

  // Use form attached to sheet
  var formUrl = SpreadsheetApp.getActive().getFormUrl();
  var form = FormApp.openByUrl(formUrl);
  var formItems = form.getItems();
  
  // Grab column numbers for later use.
  var urlCol = headerRow.indexOf("Prefilled URL");
  var activeMemberColumn = headerRow.indexOf("Approved Active Member");
  
  // Skip headers, then build URLs for each row in this sheet.
  for (var row = 1; row < data.length; row++)
  {
    var activeMember = data[row][activeMemberColumn];
    
    // Only update for active members.
    if (activeMember == "y" || activeMember == "")
    {
      // Create a form response object to prefill.
      var formResponse = form.createResponse();

      // Cycle through all form items that have matching columns.
      for (var i = 0; i < formItems.length; i++) { // Get text of question for item var ques = formItems[i].getTitle(); // Get col index that contains this question var quesCol = headerRow.indexOf(ques); var resp = ques ? data[row][quesCol] : ""; var type = formItems[i].getType().toString(); //Logger.log("Question='"+ques+"', resp='"+resp+"' type:"+type); // Treat Membership Type separately because we always prepopulate the same response (Renewal) regardless of what the data says. if (ques == "Membership Type") { var item = formItems[i].asMultipleChoiceItem(); resp = "Renewal"; } else { // Need to treat every type of answer as its specific type. switch (formItems[i].getType()) { case FormApp.ItemType.TEXT: var item = formItems[i].asTextItem(); break; case FormApp.ItemType.PARAGRAPH_TEXT: item = formItems[i].asParagraphTextItem(); break; case FormApp.ItemType.LIST: item = formItems[i].asListItem(); break; case FormApp.ItemType.MULTIPLE_CHOICE: item = formItems[i].asMultipleChoiceItem(); break; case FormApp.ItemType.CHECKBOX: item = formItems[i].asCheckboxItem(); // Resp is a CSV string. Convert to array of separate choices, ready for createResponse(). resp = resp.split(/ *, */);   // Convert CSV to array break; case FormApp.ItemType.DATE: item = formItems[i].asDateItem(); resp = new Date( resp ); break; case FormApp.ItemType.DATETIME: item = formItems[i].asDateTimeItem(); resp = new Date( resp ); break; default: item = null;  // Not handling DURATION, GRID, IMAGE, PAGE_BREAK, SCALE, SECTION_HEADER, TIME break; } } // Add this answer to our pre-filled URL if (item && resp != "") { try { // Store the item response in formResponse. If it doesn't work, just log the exception and move on. var response = item.createResponse(resp); formResponse.withItemResponse(response); } catch (e) { Logger.log(e.toString()); } } // else if we have any other type of response, we'll skip it else { Logger.log("Skipping i = " + i + ", question = " + ques + " type: " + type); } } // Generate the pre-filled URL for this row. var editResponseUrl = formResponse.toPrefilledUrl(); var shortUrl = UrlShortener.Url.insert({longUrl: editResponseUrl}).id; // If there is a "Prefilled URL" column, update it. if (urlCol >= 0)
      {
        var urlCell = range.getCell(row+1,urlCol+1);
        urlCell.setValue(shortUrl);
      }
    }
  }
};


// Helper function that returns the size of an object.
// From http://stackoverflow.com/a/6700/1704355
Object.size = function(obj)
{
  var size = 0, key;
  for (key in obj)
  {
    if (obj.hasOwnProperty(key)) size++;
  }

  return size;
};
I had never worked with Google Apps Script (GAS) or JavaScript (which GAS is based on) before, so it took a lot of reading, searching, and trial and error, but I made sure to document my code to make it easy for myself or others to be able to modify the code in the future. I’m sure that there are some “rookie mistakes” in there but so far, no problems have been reported and on-time member renewals almost tripled from 2014 to 2015. ASN was quite pleased with my work!