How I used the Asana API and Google Sheets for top-level projects view and status updates


Hey, first time posting. I just wanted to share this script I wrote to grab Asana projects, write them to a Google Sheet, and then write new status updates back from a little form in a custom sidebar.

We use Asana to store information about a zillion clients and projects and we’ve always had trouble getting a good top-level view of everything that’s going on.

I started using the API to pull project data out, but without being able to sort projects in any meaningful way, the value of a long list of active and inactive projects was pretty limited. Plus, I still had to go back into Asana, search for a project, and go to the progress tab to update a project status.

This script sorts Asana projects into categories based on the color assigned to the project for all users, feeds in only the data that we really need for management team meetings, and lets us run down the list, updating where we need to update. I have the Chrome extension to add tasks, so I didn’t include that in the script, but it wouldn’t be hard.

One reason I’m sharing this, even though I’m a little nervous, is that I had a really hard time getting my return JSON formatted correctly. Turns out I hadn’t nested that data correctly, so even though it looked like JSON to me, it wasn’t recognized by Asana on the other end. Since that’s been rectified and we’ve used the sheet successfully now for a month or so, I wanted to make it available to anyone else trying to accomplish the same thing.

I’ll check back and try to answer any questions you might have about how it all works, but I may not be able to respond immediately.

Check out the code over at this Gist:

1 Like


Welcome to the Community Forum @Janine_Paris and thanks for sharing! :slight_smile:



Thanks Janine! For JSON format I always use to check the format. It is very useful when you have the correct structure, but missing a few } or have an extra coma.



Thank you, Marie!

1 Like


My problem wasn’t actually typos. It was trying to reconcile how Google Apps Script handles JSON with the technical documentation for JSON against the Asana endpoint I wanted to talk to. Here’s the way I ended up formatting the request that posts statuses back to Asana:

var options = {
  “method”: “post”,
  “headers”: {
    “Authorization”: bearer
  “contentType”: “application/json”,
  “payload”: JSON.stringify({
    “data”: {
      “text”: text,
      “color”: color
UrlFetchApp.fetch(“” + id + “/project_statuses”, options);

It doesn’t look that exotic, but there are just a few tiny pieces that were really hard to get my head around.

1 Like