Power Query from Google Drive

These days, it is common to use online services from many providers. I’m sure that I use services from a dozen different providers every day. One of the services I use is Google Drive, and I have a variety of documents stored there. One day, I was in Excel and thinking to myself, “I wonder if I could use Power Query to retrieve data from an Excel file stored in Google Drive?” Although Google Drive isn’t listed in Power Query’s Get Data options, this post demonstrates how to use Power Query to pull data from an Excel (or csv) file stored in Google Drive.

Objective

Before we get to the mechanics, let’s be clear on our goal here.

Let’s say that we have stored an Excel (or CSV) file in Google Drive. When we open our browser, the Excel file looks like this:

We would like to use Power Query to pull data stored in that file. This is what we’ll accomplish, but first, we need to chat about file types.

This post demonstrates how to use Power Query to retrieve data from an Excel file stored in Google Drive, which is different than pulling data from a Google Sheets document stored in Google Drive. A Google Sheets document has a slightly different icon, and looks like this:

In Google Drive, you can store all types of documents, including both Excel files and Google sheets. This technique applies to Excel files, not Google Sheets files. But, if you have a Google Sheets file, you can convert it to an Excel file. To do so, you can open the Google Sheets document and use the Download as Excel option, and then upload the resulting Excel file back into Google Drive.

Alright, with that background out of the way, let’s get to the solution.

Google Drive File Stream

Google provides a free utility called Google Drive File Stream that essentially maps a drive letter to Google Drive. In other words, your computer will see a mapped drive (G:) that actually points to your Google drive files.

After you download and install File Stream, you’ll see Google drive mapped in your file explorer, like this:

Once File Steam is installed, we are set. We can use Google Drive like a normal mapped drive. This includes copy/pasting files, opening files, and using Power Query!

Note: at the time of this post, there are two Google Drive Utilities available, depending on how your Google drive account was provisioned. They are Google Drive File Stream (for teams) and Google Drive Backup and Sync (for individuals).

Power Query

To retrieve data from an Excel file stored in Google drive, we simply point Power Query to the G: drive.

From within Excel, we head to Get Data > From File > From Workbook (or From Text/CSV). In the resulting dialog, we browse to the G: drive and locate our Excel file:

We click Import and are presented with the Navigator dialog where we can pick the desired sheet or table:

If we wanted to clean the data, we could use the Transform Data command to open the Power Query editor. If the data is ready to go, we can use the Load command.

We can then load it to the location of our choice, for example we could send it into a table or on to the data model:

We click OK, and bam:

If the data in the Google Drive file is updated, perhaps a staff person enters additional rows, we can simply refresh our results table. Power Query will then retrieve the data from within that file and update any related reports or PivotTables we’ve built.

If you have any other Power Query or Google Drive tips, please share by posting a comment below … thanks!

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

6 Comments

  1. Timmie on December 3, 2020 at 9:17 am

    Hi Jeff,

    This is brilliant! Thank you for sharing.

  2. Willard Stevenson on December 3, 2020 at 11:29 am

    I had an issue with Power Query retrieving data from a website (crashes Excel) so I used =IMPORTHTML function in Google Sheets and shared the sheet with all (gid=0), used the URL in Power Query where I cleaned the data for analysis. I made a hyperlink to the Google Sheet URL in my Excel workbook so I can jump to the Google Sheet, make changes then refresh the query in Excel. For details, email me.

  3. Kurt on December 3, 2020 at 12:17 pm

    Wow… That is really cool

  4. Ang on January 18, 2021 at 4:31 pm

    Thanks Jeff – what about if you want to bring data into power query from the results sheet of a google form, so that each time there is new data added via the google form, refreshing the power query query will update with the new data from the google form, without having to open the results google sheet and download to the excel file – is this possible?

Leave a Comment