MS Excel Integration for Portfolios

I’m a HUGE proponent of the Google Sheets Integration. While Google Sheets is a fantastic tool that is top notch when it comes to creating a collaborative workspace for many users, some prefer the good ole MS Excel experience…

… bummer… there isn’t a Microsoft integration for tools like Excel…

… but wait… there is! :wink:

Did you know that you can dynamically link your Asana Portfolio to Microsoft Excel by creating a “From Web” data connection as shown below?


Here are the steps to produce this result above:

  1. From the Portfolio Actions menu you first need to Export to Google Sheets


  1. Once your Google Sheet is created, navigate to the :lock: Live source data tab and copy the full https address within the IMPORTDATA () function of cell A2


  1. Navigate to the Data tab in the MS Excel Ribbon and choose “From Web” then paste the URL copied from the Google Sheet formula and click OK then Load from the next prompt.


  1. Finally, you can modify your Connection Properties by selecting the dropdown arrow on the “Refresh All” button on the Data tab. Here you can adjust how your connection refreshes, allowing for a more regular sync with Asana than the typical hourly sync with Google Sheets.

Asana Excel Connection Properties


Now that you have data connected in Excel, how might you cross reference with other data sources outside Asana for enhanced visualization and reporting? I would love to hear how this gives you :muscle: super powers in terms of contextual reporting!

4 Likes

Thanks for the tutorial, ping @Julien_RENAUD in case he is interested.

A client of mine is using the integration between portfolios and Google Sheets and told me it was really unreliable, with many times a connection failing for example. Did you experience the same thing @Jerod_Hillard ?

@Bastien_Siebman it depends on what the client means by the connection failing. I have had issues with connections when not all systems are operational and the Asana Status page reports there are incidents that might interfere with the experience. This was only a couple times since heavily utilizing this integration so it has been quite rare.

Aside from that, there are instances where it seems Google Sheets doesn’t properly update. Usually a refresh of my browser tab kickstarts the re-sync and gets everything updated. The Asana data doesn’t seem to sync right on the hour either. Sometimes it is a bit over an hour before new information is retrieved. For me, that is expected at this point. Again, usually a quick refresh of my browser tab forces a re-sync if it has been over 2 hours since data was last dynamically updated.

If you have more specific client experiences, PM me and I’ll let you know if those jog my memory. We have some robust syncing going on where data is being shared across numerous Portfolios, Saved Searches and Projects within a single Google Sheet (which also happens to sync with other Google Sheets). Quite the database of information flowing so I would suspect I have seen some of what your client is experiencing. In short, once the timing is understood it would seem the data connections have been strong and reliable. I’ve even written scripts to expedite the sync process when I get impatient! :wink:

2 Likes

Thanks for the information. Unfortunately this does not work for MAC Excel. MS does not provide the Power Query tools for MS Excel for Mac.