MS Excel — Connecting PowerQuery To An Excel File In The Cloud (Google Drive) — Part 2

Next Up on My Cloud Excel File Data Acquisition…

Don Tomoff

Follow

Published in

Let’s Excel

4 min readJan 12, 2019

--

As a finance and accounting professional, I spend a lot of time working with data.

Increasingly, I also get to deal with cloud data across many various cloud storage services — primarily Box.com, Dropbox, OneDrive and GoogleDrive. So knowing how to retrieve data directly Excel files from these cloud services comes in very handy!

In Part 1, I explored accessing Excel files in Box.com.

MS Excel — Connecting PowerQuery To An Excel File In The Cloud (Box.com) — Part 1

The More You Work With Data, You Will Want This!

medium.com

Here’s The Process for Excel Files in Google Drive

Locate the Excel file in Google Drive. Open the spreadsheet.

Share file from Google Drive to “Anyone With Link” (I would recommend NOT to do this with sensitive information!).

**Spreadsheet in Google Drive with “Share” Icon**

**Sharing Set at “Anyone with Link can view”**

Next, we need to access the data source link for use in Power Query. To do this we use “File >> Download as >> Microsoft Excel (.xlsx)”.

Although the file will download, what we care about is the download LINK that is generated.

**Download Google Spreadsheet as a Microsoft Excel file**

**Downloaded file — this can be deleted later!**

Now, access the Downloads information for your Web Browser (I am using Chrome here — which is “CTRL + J”).

**Downloads page of the Chrome Browser — Copy associated Link**

This is what we use in Excel Power Query to access the data directly on the Internet.

Open a new Excel workbook. Using Excel 2016, I select “Data >> Get & Transform Data >> From Web”.

Excel 2016 “***Data >> Get & Transform Data >> From Web”***

Paste the link into the Dialog box that appears.

**Paste Download link (Web URL) Into the Dialog Box**

Connect using “Anonymous” and click “Connect”.

**Select “Anonymous” and Click “Connect”**

Select the worksheet tab that you want to Import and click Edit — do not immediately Load the query.

**Select Data source and Click Edit in Query Navigator window**

Edit Query Settings to change the query name.

**Power Query Edit Mode — change Query Name**

Finally, this is where we edit the query and shape the data set. For purposes of this discussion, I am not going to do anything other than “Load” the data.

Editing and shaping this data is for another discussion!

I load the data into a Table in Excel.

**Load Data — select “File >> Close & Load To…”**

**Google Drive sourced data loaded into the Excel Spreadsheet!**

That’s it. Now we can start working with and shaping our data set. Whenever the file in Google Drive is modified, the changes will be reflected in the Excel file data set!

If you are a Google Drive user, this capability can have quite and impact on your data access and management process.

Think about how you can take advantage of this capability…and go!