Google Sheets & Asana

Available on Asana Advanced, Enterprise, and Enterprise+ tiers, as well as legacy tiers Business and Legacy Enterprise.

Visit our pricing page for more information.

When you’re handling many projects, understanding how well they're going can be a challenge. With reporting in Google Sheets, you can use project data to create custom reports and visualizations to see what’s on track and what needs attention. Google sheet exports are available for portfolios, projects and advanced search results.

Preparing to use Google Sheet reporting

Before opening a Google Sheet from your portfolio, you should make sure you have the appropriate projects in your portfolio.

If you haven’t used Google Sheets before, we recommend consulting Google’s Help Center and Google Sheets Help Forum. This particular article will explain how to run some basic reports with your Asana portfolio data, but is not intended to explain all the ways you can use Google Sheets.

Reporting with Google Sheets gives you countless ways to sort and visualize the work you track in Asana. This article will help you get started and provide some inspiration for reports you might want to create.

To get started:

  1. Install the Asana Exports add on in Google Drive using this link. 
  2. Copy your Asana export link to sync with Google Sheets.
    • In Asana, simply click Export/Print from the drop down menu next to your project/portfolio header and then select Sync to Google Sheets. Your Google Sheet will open in a new tab. 
    • Copy the URL provided in the modal that follows.
  3. Sync your Asana data to a new sheet

  • Create a new, blank Google sheet.
  • Navigate to the Extensions tab > Asana Exports > Sync with Asana
  • Paste the URL from step 2b into the input box and click Ok. Your sheet will then be synced with your Asana data.

Getting started with portfolio reporting

First time users will need to authorize Asana to connect with their Google account.

Once created, the data in your Google Sheet will automatically update every hour.

What’s in the report

Out of the box, the Google Sheet includes three tabs:

  1. Overview—serves as a reference on how to use the Sheet, and contains two example charts created from your data.
  2. Asana project data—shows the most important data from each project. Explore this data by sorting or filtering by the columns.
  3. Live source data—pulls live data directly from Asana. This tab is locked to protect the connection to Asana.

You can hover over column headers to learn more about the data in the column.

Building custom reports

The Sheet comes standard with two example reports and two data tabs, but you will likely want to create custom reports for your team’s projects. With the data supplied in the Sheet, and by reading through the examples below, you should be able to achieve many of your reporting needs.

Reports you create are saved into the Sheet automatically, and they will appear the next time you open the Sheet from Asana.

Here are some examples of the kinds of reports you could create in your Google Sheet. We’ll dive into two of these below to show you how.

  • Which projects are at risk and due soon? A scatter chart of due date and status color (example below)
  • Which project owners are pushing work forward quickly? A bar chart of recent tasks completed by project owner (example below)
  • What portion of your projects are on track? A pie chart of status colors
  • Is work focused or spread across projects? A histogram of tasks completed in the past week in each project
  • Which teams have projects that are close to completion? A stacked bar chart of projects at % completion, by team

Example 1: A scatter chart of due date and status color

This example shows some of the techniques you’ll use to build a report to find the individual projects that most need your attention. We’ll build a report to identify projects that are due soon where the last status update is red, since those projects probably need the most urgent attention.

Create a new tab

Click the + on the bottom left of your sheet to create a new tab, and choose a name for your report.

Add columns to your report by referencing the Live source data tab

  1. Select the top left cell of your new blank tab
  2. Type =
  3. Navigate to the Live source data tab. Click the header of the column you want to copy into the other tab. In this case, we will reference the Name column. Press Enter.

  4. After pressing enter, you’ll be taken back to your report tab.
  5. Select the next cell to the right, and repeat steps 2 to 4 for the Due Date and Status Color columns.
  6. Now that we’ve referenced the header cell of each column, we need to copy across the data itself. Select all three headers, and use the autofill handle on the bottom right corner of your selection to drag down.

We’ve referenced the data in the Live source data tab, rather than just copying it. That means when the data in the Live source data tab automatically updates every hour, your report will also automatically update.

Calculate how long until each project is due, using a formula

  1. Type DAYS TILL DUE as a header in the fourth column
  2. Under it, enter the formula =DATEDIF(NOW(), B2, "D"), which calculates the number of days between now and the value in the Due Date column.

  3. Drag the autofill handle to populate the rest of your new column.

Create a scatter chart

  1. First we need to sort the report by Status Color. The easiest way is to open the Data menu and choose Filter. Then, use the menu on the Status Color column header to Sort A → Z.

  2. Select the Status Color, Days Till Due, and Name columns, in that order, by holding ⌘ (on Mac) or CTRL (on PC) and clicking the letter above each column (C, D and A). These are the three columns we need in the scatter chart, and the Name column must be chosen last because it will be the label for each datapoint.

  3. Create a chart, by opening the Insert menu and choosing Chart.
  4. Choose a Scatter chart and uncheck Aggregate column C.

With this chart, you can visualize which of the near-due projects are in trouble. In the screenshot below, we’ve marked that “danger zone”, projects that are both red and due in less than 60 days. As the data is updated hourly from Asana, you can always check this chart to understand where to focus team efforts.

Example 2: Task completion rate by project owner

This example shows some of the techniques you’ll use to build a report where projects are grouped according to some property, in this case by project owner. We’ll build a report to find out how many tasks were completed this week in projects driven by each project owner. We can identify high-throughput teams, or a Project Owner who is encouraging their team to capture all tasks in Asana.

Create a pivot table to group the projects by owner

  1. Navigate to the Live source data tab.
  2. Select rows from row 2 onwards. We skip row 1 because a pivot table works best when the first row contains the headers.

  3. Open the Data menu and choose Pivot table. A new tab will open titled Pivot table.
  4. From the Pivot table tab, in the Report Editor sidebar, under Rows, choose Add field. Select the column you’d like to group your projects by, in this case Owner. Uncheck Show totals, since we don’t need that data in this example.

  5. Under Values, choose Add field. Select the column you’d like to understand for each group of projects—in this case, Tasks Completed.

  6. Check that the values for the group are summarized in the way you need. In this example, we want to see the total task completion rate for each group of projects, so we choose Sum.

Visualize the data

In many cases, an easier alternative to manually creating a chart is to use the Explore sidebar of Google Sheets, which generates relevant charts using artificial intelligence.

  1. Click Explore in the bottom right hand of the Sheet. In this example, a bar chart is a useful visualization of the task completion rate.
  2. To see a larger version of the chart, you can drag it into the main area of the Sheet. Right click the chart to customize it.
 

Create your own custom reports in Google Sheets

Managing multiple projects at once is never easy, but reporting with Google Sheets gives you the flexibility to visualize information and create the reports you need with ease. Use project data from your Portfolio to build reports and visualizations to see what’s on track, and what needs your attention.