Dashboard/Google Sheets Reporting

dashboard
googlesheets

#1

Hello! I’m trying to optimize the Google Sheets integration for use in a weekly meeting where we report out on all projects. That being said, I had some questions about the integration that I wasn’t sure if anyone could answer!

  • Can you add columns to a spreadsheet with the live source data without it affecting those live columns? For example, adding a “Notes” column.

  • After pulling a column from the live source data, can you change the name of that column without affecting the live source data? For instance, I want to change “Name” to “Projects” for better visibility/clarity in reporting.

  • Is there any way to manually update the sheet versus having to wait every hour?

Any help is much appreciated!


#2

Hi @kbuchanio! We’ll be happy to help. I’ve reached out to my colleagues who are informed about the integration. I’ll get back to you with more information shortly!


#3

Hi! Jerrin from Asana here.

Can you add columns to a spreadsheet with the live source data without it affecting those live columns? For example, adding a “Notes” column.

This is currently not possible - the live source data tab is really just a single cell that populates others. There is a hacky workaround where you make a new tab that utilizes Google Sheet’s IMPORTRANGE function (https://support.google.com/docs/answer/3093340).

After pulling a column from the live source data, can you change the name of that column without affecting the live source data? For instance, I want to change “Name” to “Projects” for better visibility/clarity in reporting.

Because the live source data tab is normally not changeable, you’d have to make a new tab that copies data from the old tab.

Is there any way to manually update the sheet versus having to wait every hour?

Unfortunately not - the only way to really force an update is to completely delete your report and then start a new one (sorry!)

Let us know if you have any questions!


#4

Thanks! Are there any plans to optimize the Google Sheets reporting in the future? We’d love to get more granular with what data is pulling. Thanks!


#5

Glad to hear this is a useful feature for you, @kbuchanio. If you have specific suggestions for the feature, we’d love to hear them in the #productfeedback category!


#6

Having another issue with this reporting. I’ve added an entirely new sheet where I am simply referencing the live source data. I’ve added additional columns in that sheet (ex. Priority column), which I’d like to sort the data by—but the data simply keeps rearranging and this new column isn’t matching up with it’s appropriate row. Can someone please assist?


#7

Hi @kbuchanio I am only seeing this thread now. I love the spreadsheet integration as well and here are the work around I built to make it useful to me and my team:

  1. I created a new google spreadsheet that is not linked to the synced Dashboard spreadsheet. In there I dynamically import the live data using the IMPORTRANGE function. I locked that tab and hid it so that no one can modify or break it.
  2. Depending on my use cases I have another protected and hidden tab with additional info that I am pulling from other places. A database that lists specific additional info like team members of a project or specific resources that are not dynamic and unique to a specific asana project
  3. I have the main tab where I used specific formulas like FILTER or QUERY to pull in data from the live tab (my initial IMPORTRANGE tab) and my second database. Then I can add conditional formatting for dynamic colors, filters, create charts…

To go even further I also created a Zapier integration (free if you have less than 5 integration running at once) and I automatically publish asana progress updates in a specific slack channel. So I get very detailed reports in my spreadsheet and I automatically push the progress update for those who only need high level updates. That allows me to have a determined version of one dashboard that is relevant to the entire company that is different from my personal dashboard. It’s a workaround to fake create a company-wide dashboard rather than have everyone subscribe to each relevant project at the company level in addition to the projects relevant to them.

Finally to make the spreadsheet integration even more powerful, I created Asana templates with numbered sections. These sections become the most used sections across the projects in my dashboard and then I get even more granular data reported in the live spreadsheet that I can base my charts on. If you have very specific needs you are not able to implement I would be happy to provide more detailed feedback if I can help.


#8

Thank you so much for the in-depth response. I’m going to give your suggestions above a go. If I have any additional questions I’ll be sure to let you know!