back to top

Using The Pivotal Tracker API To Update A Google Spreadsheet

Who tracks the trackers?

Posted on

One of the tools we use at BuzzFeed to manage projects and products is Pivotal Tracker. For the unfamiliar, Tracker is a widely-used and complex piece of software that organizes all the stuff you need to get done into a series of "stories" that look like cards and get displayed in expandable stacks of columns, like this:

I believe this is what people call "high information density."

I believe this is what people call "high information density."

Whether due to shortcomings in the application, how we're using it, or some combination of the two, many of us have found it necessary to use Google Spreadsheets in order to keep track of our Tracker cards. But then we need to spend a lot of time manually keeping spreadsheets and Tracker in sync, and that is not an amazing way to spend time.

I figured there must be a better way, and started playing around with the Tracker API and some custom Google Spreadsheet scripts, blissfully ignorant of this prescient xkcd comic my boss Alice would later share with me.

"'Automating' comes from the roots 'auto-' meaning 'self-', and 'mating', meaning 'screwing'."
CC BY NC xkcd.com

"'Automating' comes from the roots 'auto-' meaning 'self-', and 'mating', meaning 'screwing'."

After consulting with my colleagues to learn about their workflows and workarounds (all of us had independently developed remarkably similar systems), I was able to develop a spreadsheet template that saves us time and headaches. The spreadsheet is tailored to our particular way of using Tracker and managing projects, so I don't think sharing it would be helpful. However, I do want to share the components I used so that if you're using Pivotal Tracker in combination with Google Spreadsheets you can incorporate these formulas into your own situations.

Importing a Tracker story's name, state and owner

By way of prerequisite knowledge, it will help you if you're already pretty comfortable with spreadsheets. You don't need to know much of anything about APIs or JavaScript (I don't!), but the more you know the more you'll be able to customize and improve upon what I've done.

My objective was to a create a spreadsheet that would allow me to enter only a Tracker story ID in the A column and pull this other information into subsequent columns in the same row: story name, status, scheduled release number (which in our workflow is a label), and story owner (the person doing the work).

I started by looking at the Tracker API documentation, and discovered a pretty straightforward endpoint (URL) for grabbing most of the data associated with a Tracker story:

https://www.pivotaltracker.com/services/v5/projects/[projectID]/stories/[storyID]?token=[APItoken]

If you replace my three placeholders with your own project number, story number, and Tracker API token (you can find that on your profile page) and then paste the resulting URL into a browser, you should see some JSON text containing all of the information about the given story.

The easiest way to suck all of that data into Google Spreadsheets is to use the IMPORTDATA function, which would look like this (using your own URL):

===IMPORTDATA("https://www.pivotaltracker.com/services/v5/projects/[projectID]/stories/[storyID]?token=[APItoken]")==

To make that more reusable (especially if you want to share it with other people), you might want to point the project ID, story ID and API tokens to other cells so that they can be easily changed. In my case, I put the ID of all my stories in the A column and my API token and project ID on another sheet called settings, so my formula builds the URL using those values and looks like this:

===IMPORTDATA("https://www.pivotaltracker.com/services/v5/projects/"&settings!$B$2&"/stories/"&A2&"?token="&settings!$B$1)==

That pulls all of the info into the spreadsheet, but it doesn't parse the JSON, which is a problem if you want to just cleanly put each value (name, status, owner) into its own column. So for that I used this script (there's a short guide for installing it here) that adds a new ImportJSON function to Spreadsheets. That new function also lets you select just one value in Tracker's JSON to display in a cell, so that you can do something like this to import just the story name into a cell:

===ImportJSON("https://www.pivotaltracker.com/services/v5/projects/"&settings!$B$2&"/stories/"&A2&"?token="&settings!$B$1,"/name","noInherit,noTruncate,noHeaders")==

I duplicated that into new columns across the row, replacing "/name" with the different fields I wanted, like /current_state and /owned_by_id.

Changing owner IDs into names

Ok, so that gets us a lot of the way towards something that's usable and mostly automated. But there were a few more things I wanted to do. First, the stories endpoint only tells me the ID of the person who owns a card (e.g. "112742") instead of their actual name, username or initials.

In order to grab that information, I created a new spreadsheet called "people" and used the Project Memberships endpoint to pull in all the people in our project. (We put everything into one project, so if you have multiple projects you might have to do this for each one, or maybe use the Account Memberships endpoint instead.) That formula looks like this, again pointing to the project number and API token in my settings sheet:

===importJSON("https://www.pivotaltracker.com/services/v5/projects/"&settings!$B$2&"/memberships?token="&settings!$B$1)==

Then, over on the sheet that lists my stories, I used the VLOOKUP function to look up a project member by ID and return their full name. This formula looks a little crazy since there are three nested functions, but it just builds upon what we've already done. On the inside, it uses ImportJSON to grab the ID of the owner of this story. Then, it uses the TEXT function to convert that value to text (I found I needed to do this in order for it to work). Finally, VLOOKUP searches the "people" sheet for the owner ID and returns the next column, which contains that person's actual name. (If you wanted to return the person's initials or username instead, you could replace the ",2," in this formula with either ",4," or ",5," to return those different columns.)

===VLOOKUP(text(ImportJSON("https://www.pivotaltracker.com/services/v5/projects/"&settings!$B$2&"/stories/"&A13&"?token="&settings!$B$1,"/owned_by_id","noInherit,noTruncate,noHeaders"),"#"),people!$D$2:$H$200,2,FALSE)==

Use regular expressions to search Tracker tags

The next thing I wanted to do was add a column telling me if the story was scheduled for a release. In our case, we add release numbers in the format 8.42, or 8.45.2, as tags. That's a very specific workflow that probably won't apply to other situations, but I'll explain how I extracted this information because you may find yourself wanting to search for your own Tracker tags and display them in their own column.

The challenge is that ImportJSON doesn't do a great job of importing Tracker tags, because it puts each tag on a new row, whereas I wanted all of the data associated with each story to stay on the same row. That's why I'm also using IMPORTDATA (as described above) to suck in all of the Tracker JSON to one row. The problem with that, though, is that each story ends up with a different number of columns, so there's no way to know specifically in which columns tags will appear.

In my case, I got around this by concatenating (combining) all of the columns in which tag data might appear, and then searching that concatenated text to see if it contained a release number. Because our release numbers are always in the same format and we don't use any other tags that match that same format, I was able to use a regular expression (\d\.\d{1,2}\.?\d?) with the REGEXEXTRACT function to pull out a release number when present.

===(REGEXEXTRACT(CONCATENATE(M2:AR2),"\d\.\d{1,2}\.?\d?")==

So, if you want to find a tag that can also be matched by a regular expression you might be able to use a similar formula, or maybe another lookup function.

Conditional formatting and final touches

Behold my finished spreadsheet in all its glory, feast on some of the funny little bugs and projects that have been on my mind this week.

Behold my finished spreadsheet in all its glory, feast on some of the funny little bugs and projects that have been on my mind this week.

After that, everything else was tinkering. I used conditional formatting with custom formulas and ranges to give each row a different color depending on the story's status. Then I wrapped a lot of the above functions with IFERROR functions (to suppress error messages) and in other cases used IF functions (to only execute the formula if there's a story ID in the first column, and otherwise leave the cell blank).

I shared the final template with the rest of the team and a number of people are now using it, saving time and making it easier to track the Tracker. The biggest challenge we've found so far is that once you have a lot of these formulas on a page a few of them tend to error out due to either timeouts or API limits, so our workaround is to copy and paste-values-only those fields that aren't likely to change (replacing the API calls with actual text), and to only use this for cards that we're actively thinking about while still keeping a list of other cards on a separate sheet.

I know that's a lot to take in! But if this post helps only one other Tracker user then... well then it probably won't have been worth writing. Hopefully it helps at least, like, three people.

If you have any questions or comments, leave them below or find me on popular social media website twitter dot com.