[SOLVED] Google Sheets to Excel Power Query - Sheet will download, but won't load to query.

Question

I'm wanting to create a connection with Google sheets to Excel with Power Query so it refreshes with updated data in Google Sheets.

I select Get Data> From Other Sources > From Web

I Input the google sheet url and replace the last part with "export?format=xlsx"

My connection seems to be working.

Excel will Download the Google Sheet into Excel instead of loading it into Power Query. This doesn't help because it doesn't refresh with changes made in Google sheets. Is there a setting somewhere that I'm missing?

Here is what my navigator shows. I'm unable to load the google sheets data into power query. It seems that the download of the file cancels the navigation to the webpage. Any suggestions on what I'm doing wrong?

PII is mask by MSFT

Answer 1

Hi James Croxford,

First, my pleasure to assist you.

As per your description, please don’t worry, we can work together to narrow down and resolve the situation.

It seems you create a connection between Google sheets to Excel 365 with Power Query and when you use select Get Data> From Other Sources > From Web feature and Excel will Download the Google Sheet into Excel instead of loading it into Power Query from your side.

If my understanding is right, for the situation you encountered, we do understand the inconvenience caused and apologize for it. Based on my test result, when I use the Web connector (URL + "/export", or "export?format=xlsx" and it will not download Excle file but i cannot see loading table in Excel Power Query.

Here are my steps you may try and check if there is any difference:

1.Open your google sheet and click on share button and select Anyone link.

2.Select Copy link button.

3.Open Excel 365 blank workbook and click on Data tab> Get Data> From Other Sources > From Web and past the link here

4.Follow the prompt instruction. And It will load into Power Query but without getting data table.

In the meantime, please provide us the version information. To check version: Open Excel>File>Account>Product Information and capture a full screenshot and share here (Please mask your private message) and we will be tested on that version and verify the result with you.

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Best regards

Waqas Muhammad

Answer 2

Thank you for taking a look. I don't know what is causing the download.

This is my version of Office.

The steps you included mimicked the steps I already had. They at least confirmed that I'm on the right track.

The only difference seems to be in the last part of the URL string that I copied from google.

In place of the "edit#gid=0" or "edit?usp=sharing" that is at the end of the string I tried the following;

I had included "export?format=xlsx"

Your image showed "&sd=trueexport?format=xlsx"

I have also tried "&output?format=xlsx"

Neither of these options worked. I don't know if I'm placing them in the correct part of the string.

Answer 3

Hi James

What you're looking for is documented in article Power Query / Get & Transform – Get data from GoogleSheet

Some pics might be outdated but the overall process should still work - Please confirm

Answer 4

Thank you for the help. I went through the link you send and see that downloading and getting the download link is a different way than what I have tried.

Question: If I download and use the download link to build a query, I assume I would have to download the google sheet each time. I'm assuming that the query won't updated if the original google sheet is updated?

It didn't work. The download link only gave me an error of "Unable to connect".

Do you have any suggestions on how I can fix this?

Answer 5

Hi James

I got the same error after testing with a new file. Obviously something changed and it's definitively in the link generated. Fortunately I still had the workbook used to write the article and after comparing the old and the new link I could determine where the issue was + found another approach:

1/ In Notepad prep. the following string/link:

https://docs.google.com/spreadsheets/d/[ID]/export?format=xlsx&id=[ID]

2/ Open the sheet in Google Sheets

3/ Go to File > Share > Share with others. At the botton click on "Copy link"

4/ Close the Google sheet

5/ Open Notepad > Ctrl+V. You should get something like:

https://docs.google.com/spreadsheets**/d/**1hYOzch3ohgShR0FfWpbTOTIjWKJIu-7Z\_X1GZjV3aak**/edit**?usp=sharing

where the [ID] is after /d/ and before /edit?

6/ Copy that [ID] and replace it in the string/link prep. at #1. With the above [ID], the string/link should now be:

https://docs.google.com/spreadsheets/d/1hYOzch3ohgShR0FfWpbTOTIjWKJIu-7Z\_X1GZjV3aak/export?format=xlsx&id=1hYOzch3ohgShR0FfWpbTOTIjWKJIu-7Z\_X1GZjV3aak

7/ Copy that link

8/ In Excel > Data > From Web > Paste the above string/link...

This works fine here. What about you?