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

English Forum Developers & API
,
1

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:
http://bit.ly/AsanaDataHandlerGS

1 Like
2

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

3

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

4

Thank you, Marie!

1 Like
6

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(“https://app.asana.com/api/1.0/projects/” + 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.

2 Likes
7

I got an error in the

// GET/SHOW USERS
function storeAsanaUsers() {
var response = UrlFetchApp.fetch(“https://app.asana.com/api/1.0/workspaces/WorkspaceID/users”, getAsanaAuth()),
dataAll = JSON.parse(response.getContentText()),
dataSet = dataAll.data,
users = ,
data;

for (i = 0; i < dataSet.length; i++) {
data = dataSet[i];
users.push({name: data.name, id: data.id.toString().replace(/[pr[^]]*]/,“”)});
}
return(users);
}

It said it was undefined. Just to be clear, If I am copy-paste your code, I only need to change the secret key, workspace ID, name, and sheet?

1 Like
8

Hey Andrew! Looks like I need to do a little digging. It may be a day or two before I can get back to you.

9

Good evening,

Have you found out the problem?

Looking forward to hearing from you!

Andrew Setyawan

10

Thank you SO MUCH! It was exactly this (getting the format of the POST request right in Apps Script) that I couldn’t get. Your post fixed my problem immediately. Thanks again for taking the time to share!

11

What is the logic you use for the colors? Per team member, per project type, per status, etc…?