We have too many tasks in a project to export to a CSV (tasks are truncated over a certain amount) so it was suggested that we export to JSON or export to Google Sheets. However, in a project’s export options (inside the dropdown next to the project’s title) I’m only seeing options for exporting to JSON, CSV, and printing. When I’ve Google’ed around for answers, some people have posted screenshot of “Google Sheets” being on their export menu, but I can’t figure out how to add that option.
I’ve tried parsing the project’s JSON feed from the API with a Google Script inside a Google Spreadsheet and I can parse out a task’s name and notes, but can’t figure out how to grab all of the custom field values and subtasks. If anyone can a tool or code they’ve used in the past (either Javascript for a Google Script or Python) to parse the JSON and export to a CSV, I’d love to see it!
Summary: We are trying to export a very large project with many tasks and custom fields within each task to a CSV, but can’t figure out a way to do it. Asana’s default CSV export seems to have a limit to what it can export by default, and it was suggested we could get more using the JSON export or through the API.
And just for more context, here is the code I’m currently using in a Google Script to pull the JSON from the API and throw into a Google Sheet. This code works and I can pull all tasks in the project, but can only figure out how to get tasks by ID, name, and notes. I can’t figure out how to get any of the custom fields or subtasks inside a task.
// Based on https://gist.github.com/superstrong/b8d7413724ce311d11e672ad5d2c57c7
var options = {
"headers" : {"Authorization": "Bearer "+ AUTH_TOKEN},
}
var response = UrlFetchApp.fetch("https://app.asana.com/api/1.0/projects/"+ PROJECT_ID +"/tasks?opt_pretty&opt_expand=(this%7Csubtasks%2B)", options);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var sheet = ss.getSheetByName("Dashboard"); // specific sheet name to use; alternatively use ss.getActiveSheet()
var dataAll = JSON.parse(response.getContentText());
var dataSet = dataAll.data; // "data" is the key containing the relevant objects
var rows = [],
data;
for (i = 0; i < dataSet.length; i++) {
data = dataSet[i];
//Each JSON entity we want to pull goes below
rows.push([
data.id,
data.created_at,
data.name,
data.notes,
]);
}
// Export the data to rows in the spreadsheet: [row to start on], [column to start on], [number of rows], [number of entities]
dataRange = sheet.getRange(2, 1, rows.length, 4);
dataRange.setValues(rows);
Thanks for reaching out. Sorry you are having issues converting your large project to CSV. Have you tried the Google Sheets integration outlined on the /apps page: Google Sheets + Asana: Create custom reporting • Asana
If this doesn’t fit your use case, you could split up the project in Asana into smaller projects and then export.
It is not advisable to try to get all of the data from a huge project in a single API call. Doing so can result in causing latency in the API, which may result in you getting banned from the API.
@Jeff_Schneider, thanks for the reply! I added opt_fields to the “options” in the original request. Thanks for that suggestion.
I would love to use the Google Sheets integration, but can’t find a way to activate it. As I mentioned in the original post, I don’t see a “Google Sheets” export option on a project. The only place I see anything about Google Sheets is in the Dashboard, and when I click “Open Report in Google Sheets” on the Dashboard page it opens a spreadsheet that just shows me the name of the project and some metrics about it, but not individual tasks. Am I missing something and/or can you tell me how to export projects + the tasks/subtasks/customs fields into a Google Sheet?
I know in your original post, you said that this option was not available to you. Can you confirm. This export will hopefully solve your use case. It shows tasks, subtasks and custom fields.
If this doesn’t work, you would need to continue to build out your custom script. We are planning to write an example script that uses the Asana API to export to Google Sheets, but it likely won’t be for a few months.
@Jeff_Schneider I see it now, but it was not available before yesterday. People on my team said it is not available on their end. How can we make it show up for everyone on my team?
I don’t see the export to google sheets option under my project.
If I export to JSON i’m not sure how to plug it into Excel and the make a report that will give me all completed tasks in a project that can be filtered by custom fields and then for the tasks that are not completed I want to know a count of subtasks and which ones are completed.
Any tips or guidance would be highly appreciated.
I’m trying to get reporting standardized for the whole department and the goal would be to report on a per sprint level but roll up by quarter and the year.
I did some digging and you actually need to have Asana support manually add this integration to your domain (you must also be premium). You can have support add it by filling out this form. (cc: @keeeith, @James_Carl)
Another option is to see if any of the reporting apps will solve your use case.
You could also have a developer write a script to create your custom report via the Asana API. All of the data you need for your report is accessible through the API. (It’s also accessible via a saved advanced search in the product, though getting a count isn’t always perfectly seamless re: Simple task total report)
Ask for the integration to be turned on to allow projects to be exported and synced to Google Sheets.
To provide a bit more context, this is a one-way sync to Google Sheets. Changes in Asana will be reflected in the Sheet, but changes in the Sheet will not go back to Asana. A popular use case is doing calculations (beyond summing which can be done in the product) on custom field values.
1. Use Power BI desktop (this won’t work just on Power BI service you have to start on desktop). 2. Share Google Sheet and get link from sharing. 3. Paste Google Sheet shared link and it will end in something like “adfe/edit?usp=sharing” 4. Remove the /edit?usp=sharing off the url 5. then add export?format=xlsx&id= where the edit/? had previously started 6. then copy and paste the long id from the first part of your url 7. the long, final URL you should use for Power BI get from web will be something like: “https://docs.google.com/spreadsheets/d/1nWV8adkjfadkfHWDIAa3ad/export?format=xlsx&id=1nWV8adkjfadkfHWDIAa3ad” NOTE: id after equals sign matches id from Google for share sheet. (BTW this isn’t a real link just demonstration).
That’s it - now you can design in Power BI desktop and publish to Power BI service on web (if needed). Only downside is there’s no automatic refresh. Folks can edit / enter on Google Sheet but change won’t appear in Power BI Desktop until you click refresh and won’t appear in Power BI Service until you republish and overwrite.
I’ve been trying to set this up for ages, and then I finally found this answer and it works! So now I can set up an advanced search report for multiple projects, sync the report with Google Sheets, then build a flashy, management-pleasing Power BI report that refreshes at a click.
Thanks for suggesting how to use output options. I’m very new to Asana and have little technical background so I’m having trouble getting this to work. When I tried it in terminal, the outputs for curl -H "Authorization: Bearer https://app.asana.com/api/1.0/projects/<project_id>/tasks?opt_pretty&opt_fields=(thissubtasks%2B),name,id,assignee,custom_fields is the same as curl -H "Authorization: Bearer https://app.asana.com/api/1.0/projects/<project_id>/tasks?opt_pretty
The first call works fine in browser so I think there must be some simple, fundamental thing that I’m missing. Can you please explain why I can’t seen the fields that I specified when I combined opt_pretty and opt_fields?
