Get Google Sheet Data in Excel with Power Query
HTML-код
- Опубликовано: 12 фев 2020
- Learn how to import Google Sheet data into Excel using power query. You'll always have the latest Google sheet data with the click of a refresh button in Excel. 👇
Check out my full courses and ebooks here
👉 www.howtoexcel.org/courses/
DOWNLOAD the example workbook here
📖 www.howtoexcel.org/downloads/
SUBSCRIBE & get my 3 FREE eBooks
📧 www.howtoexcel.org/newsletter/
CONNECT with me on social
Facebook: / howtoexcelblog
Twitter: / howtoexcelblog
LinkedIn: / john-macdougall
Thanks for all your support!
In case anyone needs the link
docs.google.com/spreadsheets/d/{ID}/export?format=xlsx&id={ID}
and then a hero comes along
Thanks
@@keysersoze4330
Absolutely brilliant!! Saved me a massive headache. Great clear and working tutorial thanks!
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!!! :-)
This is brilliant, I had tried one from another video and figured you couldn't do it any more. this will save me about 3 hours a week in scheduling. thanks a million!!!
Glad to hear! There's usually a better way to be found. 👍
This has just made my week. This solved all my problems I was having!! Thank you!!!
Damn!! you made it so simple for me to understand the steps.. Thanks a ton, really helpful, John.
Glad to hear it helped you out Nelson. 😀
This was incredibly helpful and super concise. Thank you!
No problem, glad it helped.
Great explanation! Thank you!
you're a life, time and life saver SIR!
wow, many thanks, so easy to get data from Sheets to Excel =))
Hi John. Cool trick! Thanks for sharing :)) Thumbs up!!
Wow!I Just Learned Something New To Me!Awesome Tutorial Thank You John :):):)
Glad you enjoyed it Darryl!
Thank you for doing this ! 🎉
It feels like magic... thank you.
Supper helpfull, Thank you from Indonesia 🙏🙏🙏
Fantastic. Thank you so much.
great! very useful
Thank you a lot, that was very helpful!
You're welcome David! Glad it helped.
you are a savior darling.
Thank you helped a lot man !
Good to hear!
Good job. It was that what I looked for
Eye see you found it 😂
You taught very simply
Many thanks!
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
Thanks you John .awesome..one question though : will it import the macros from the original Google sheet?
Thank You Very Much
Welcome. Glad it helped!
Great video! How about applying the same process for a private workbook?
Godlike!
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 :))
THANK YOUUUUU
It worked! thanks a lot
@@marynayakovenko5539 Awesome! Good luck :))
thanks!!
Easy, thank you
@How To excel, Excellent advice! But how to make reverse operation - Get Excel data in Google Sheet?
Woooow fantastic.
Thanks alot.
Could you please tell me how does it work if I want to get data from folder exist in google drive or drop box?
Not sure that's possible with power query alone.
Thanks for sharing this usefully trick.
it worked when google sheet contains one tab.
it did not worked with more tab.. I am using 2019 EXCEL version .. thanks so much
it definitely works in excel 365
If you use more than one tab, in gsheet choose file then publish it to web. And choose xlsx. Then the rest is same in power query
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?
Hi, I've copied and replaced {ID} as per your instructions but i keep on getting the Document option as per your first example and not the sheet names listed in the PQ "landing page". Any suggestions?
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?
can we authenticate any how as we dont want to use the public share link option?
At 2:10, But where did you get that magic string from, Johnny? Every time I need to import a Google sheet, what should I do? Where it is available?
Where does that first link come from in the text editor? Copy it from the screen?
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?
I did all the steps mentioned still power query is loading the table and html format like shown first. My company uses Microsoft 2013
Could it work excel to spreadsheet?
Is this just a snapshot or by modifying the Excel table it will modify the Google sheets?
It has a BIG security flaw. Making documents public is something that should never be done.
Jon, is there any equivalent feature to power query from Google sheets?
You can try the QUERY function in google sheets. It's not equivalent, but does allow you to get data by writing your own SQL.
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?
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
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.
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...
How can do in opposite direction by export data from excel to google sheet with power query?
You can't
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?
Yes, I think so. I have not explored any other option.
Thanks for the info. Exactly what I need. But... I got a message during connection saying - "Relationship tag contains incorrect attribute. Line 2, position 86." Can you help on that?
Was that a power query message? Does anything load?
@@HowToExcelBlog Nothing is downloaded. It starts the connection and PQ message only says "Relationship tag contains incorrect attribute. Line 2, position 86." However... I noticed that the (ID) you mentioned is the same for a Google file with several workbooks in the same file. I believe PQ/Excel/Google is getting lost when there are more than just one workbook.
Interesting. That might be the case, I never tried getting data from multiple google sheets.
how to import it with excel 2016? my excel new web query just show YAHOO only
The tailored link does not get the file tabs, no
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
not working in excel 2016 massage"The command you selected is not available from this shortcut menu."appears
How to get a 4 mini charts (at the headers) at 3:01
pls show me
There's an option you can check in the View tab in power query editor.
Can I do the same with Excel online?
No power query is only available in desktop Excel.
is it possible to extract those data if the view is restricted?
Hmm, not sure.
In 0:42 you have selected "Anyone with the link". What if the document belongs to a user who has restricted sharing permissions outside the domain? Will it work in that case?
same question :) I have also this situation
error: we couldn't authenticate with the credentials provided , how to resolve this ?
Is this possible with anyone at "X" with this link ?
Not sure I understand what you mean 🤔
@@HowToExcelBlog You have instructed to share the link which is accessible by anyone. I want to know is this possible where I want to share to only selective set of people.
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
Pretty nifty
Nifty indeed.
Power query is not updating after few days, it says operation timed out while connecting to google sheet.
Hmm 🤔