Registering Help Requests in Trello from Google Sheets

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)[http://trello.com/app-key]. 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:

https://trello.com/1/authorize?key=YOUR_APP_KEY&name=APP_NAME&scope=read,write&expiration=never&response_type=token

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.

Leave a Reply

Your email address will not be published. Required fields are marked *