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!