Registering Help Requests in Trello from Google Sheets

Published: 2016-08-04 08:06 |

Category: Technology |

I’m on a new team with the school district (I don’t think I’ve written about that change yet…). I’m teaching one section of AP Biology and working the rest of the day with an Instructional Technology Specialist team for the whole district. The goal is to build out a program to support anything to do with…well…instructional technology.

Part of our big “here we are” message is a Google Form available to anyone in the district. We’re in week 1 of school and we’re already getting requests for classroom visits, app support, and staff PD across the district. These spreadsheets, if you’re using something similar, can get really big, really fast. Puzzle piece number one.

To help organize our projects and day to day tasks, we’re using [Trello]( as a team. We can create lists to categorize cards with specific tasks. As they change status, drag a card from one list to another. We can also comment, assign team members, and attach files to the tasks to keep our email from exploding all over the room. There’s a web app and a mobile app so we’re all in touch. Puzzle piece number two.

Now, I don’t want to check a spreadsheet to go and manually put a PD request into Trello. Luckily, Google Sheets allow you to `POST` data to other apps through APIs on the web. Doubly luckily, Trello has an API.

Long story short, I had an _awful_ time getting this started because Trello required a particular form of authorization that Google no long supports in Apps Script (go figure). So, I Googled and Googled until I came across an [old post by Riley Pannkuk]( who was doing something similar for bug tracking for his app. So, I sent him an email. He wrote back with a solution; I’m not sure how he found it because it isn’t in the Trello docs (shame shame), but it works.

The problem was that no matter how I authenticated, Trello didn’t recognize my permissions in the app. Riley pointed out that there was a missing parameter on my authorization request: the `response_type` field needed to be set to `token`. Again: not in the docs.

So, step one was to set up a Trello board and then grab my API key from (the developer options page)[]. Then, I sent an authentication request via URL with the following information:

– key = My app key

– name = arbitrary name to identify the app by in my settings

– scope = level of access needed by the app (in this case, read & write)

– expiration = how long should the app have access?

– response_type = token

The authorization request looks like this when you’re ready to send it:


This returns a webpage with a long string of letters and numbers that need to be included in the script (see below) to post when the sheet is updated.

Here’s the script with comments so you can see what’s going on:

Rather than sharing a pretty team-specific Trello board, the last step in the function is to change the Google sheet status to “In Progress” so others can check up on what needs to be done.

The next step is to add something called a **webhook** to watch the cards as they move through the Trello board and update things like the team member assigned, completed, etc. I’ll add another post when that’s done, complete with code.

Comments are always open. You can get in touch by sending me an email at