Get Google Sheet Data in Excel with Power Query

Where does that first link come from in the text editor? Copy it from the screen?

can we authenticate any how as we dont want to use the public share link option?

Fantastic. Thank you so much.

you are a savior darling.

Great explanation! Thank you!

Great video! How about applying the same process for a private workbook?

Hi it didn't work for me as I get an HTML data. I followe the exact instructions and yet....
I saw a blog on this issue and there was an example link to try and it works!!!
do not understand what is the different between those two

Excel does not seem to have this web feature you are using on the Microsoft essentials basic plan. Is that correct? I can't see all the options you see

The tailored link does not get the file tabs, no

Is this just a snapshot or by modifying the Excel table it will modify the Google sheets?

Absolutely genius!!!! Gets around the whole publish on change issue. This answer should be in so many threads.
Changes can be refreshed instantly. Thank you sooooo much!

Supper helpfull, Thank you from Indonesia 🙏🙏🙏

Didn't work for me, using excel 2016, keeps giving: an error has occurred in the script on this page, syntax error in regular expression

Thank you very much.
But I must add that, on the Google Spreadsheet, you must delete all the Rows and Columns that are blank/unused since now the Excel includes them by marking as "null", which takes too much time and resources to periodically update or load initially.

My only hesitation is, as we are juggling the links around to use directly from Google Drive, I am sure that Google is way aware of this problem/limitation and I'm afraid to rely on the Google Drive and the Google Documents to use on Excel in the long run; Google may do something in the future to render all the years of work go in vain in a second since the Google Document were not originally intended to use as data source for Excel documents...

I was requested to do the same dashboard in both excel and google data studio. This will help me to have one single raw data file :')

This has just made my week. This solved all my problems I was having!! Thank you!!!

Hello this helped me alot thank you! i now have a new problem - i added a new column in my google sheet and the data is not able to refresh now. what should i do?

It feels like magic... thank you.

@How To excel, Excellent advice! But how to make reverse operation - Get Excel data in Google Sheet?

Thank you so much. For about 2 weeks I've been having issues with web queries that worked fine until then. So it's been a nightmare to deal with. And now after seeing your video I just bring them to a new place in Gsheets, and then bring them into my normal excel from there. Thanks again. So good!!! :-)

Godlike!

It has a BIG security flaw. Making documents public is something that should never be done.

how to import it with excel 2016? my excel new web query just show YAHOO only

I have a crazy situation where I can only share the Google sheet within my organization. Is there a workaround for this situation? I was able to publish the sheet as a webpage and then connect to the webpage but it's messy.

great job

Thank you for the video.
I managed to create everything and it worked fine. But when I close and open the table (excel), the link is lost and I have to re-link it to be able to sync the data from Google Sheet. Would you have a solution to that so that when I close and open the excel the next day, I can just refresh and the data is imported from Google Sheet to excel?

My google drive is in a company group. My share option is: "Anyone in this group with this link can edit". If it is stored as a xlsx in Google Drive, it will work. If it is stored as a google sheet, it won't. Any suggestions?

great! very useful

Thanks you John .awesome..one question though : will it import the macros from the original Google sheet?

IF WE EDIT THE IMPORTED DATA IN EXCEL AND DO REFRESH, WILL IT UPDATE IN SHEETS TOO? IF NOT, IS THERE ANY WAY TO DO LIKE THAT?

thanks! this is so helpful... i wonder if there is a way to do this with a whole folder from google drive, which contains several gsheets

Hi John.. discovered another way to do this. Change the end portion of the shared link from: edit?usp=sharing to: export?format=xlsx. Tried it a few times and it works even if multiple sheets and named ranges in the Google Sheets document. It is an interesting option! Hope you find it useful :))

Hi John. Cool trick! Thanks for sharing :)) Thumbs up!!

You taught very simply

wow, many thanks, so easy to get data from Sheets to Excel =))

you're a life, time and life saver SIR!

Thank you helped a lot man !

Hello, thx for the video. What if the google sheets is protected by a password? It seems that power query is unfortunately not able to connect. Thanks for the answer?

Thank you a lot, that was very helpful!

Jon, is there any equivalent feature to power query from Google sheets?

is it possible to extract those data if the view is restricted?

How can do in opposite direction by export data from excel to google sheet with power query?

Hi! This was working so well in September and now it isn't. My Google sheet columns A-F are populated but Excel PQ pulls null values into Excel columns A-F when I refresh in PQ. Can you please help me?

Can I do the same with Excel online?

This was incredibly helpful and super concise. Thank you!

Thank You Very Much