[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

Thank you for the research in helping me resolve this. The changes seem to bypass the error. I'm back to my original challenge. Instead of loading the data into my query, it is trying to download the entire sheet. Regardless of me saving or canceling the download, the Navigator for power query will return "document" and won't populate the sheet names or tables. I must have the link correct, because it downloads the correct file. My permissions must be good as well. I've been searching for settings that cause the download instead of loading the data.

Answer 2

Hi James

I tried many things but couldn't repro the issue (file downloading). Could use the following URL and let us know the result:

https://docs.google.com/spreadsheets/d/1pMnEef7osurGSZAa7o31kpRyTyMS3y-V/export?format=xlsx&id=1pMnEef7osurGSZAa7o31kpRyTyMS3y-V

Answer 3

Hi James

Marking possible solution(s) helps those who search this site. If, by any chance, one of the proposals you got solved your issue it would be appreciated your mark it - Thanks

(In the meantime I updated the article I initially shared with you)