Import data from a Google Sheets file into Excel with Power Query without making the file public
HTML-код
- Опубликовано: 9 июл 2021
- We can connect Excel to a Google Sheets file and import data from there with Power Query. Watch to learn the details you need to account for to make that connection happen.
-________________________________________________________
Learn how to AUTOMATE YOUR EXCEL REPORTS in minutes instead of hours without copy-paste or coding: snapreportschamp.com/course
Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists:
solveandexcel.ca/dynamic-depe...
___________________________________________________________________
Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer
LIVE CLASSES on RUclips every week: https:www// / celiaalvessolveexcel
Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at t.me/celiaalvessolveexcel
Subscribe to my NEWSLETTER for exciting news about the Excel world: bit.ly/learnfromcelia
Solve & Excel Consulting - solveandexcel.ca
LinkedIn: / celiajordaoalves
Facebook: / solveandexcel
Twitter: @celia_excel
Instagram: @solveandexcel
#excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA
-------------------------
#Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada 
Наука
![XO (Only If You Say Yes) - ENHYPEN エンハイプン 엔하이픈 [Music Bank] | KBS WORLD TV 240712](http://i.ytimg.com/vi/wBT1bKKwPTo/mqdefault.jpg)
Hi Celia. This is such an awesome tip! Works like a charm. Thanks for sharing :)) Thumbs up!!
Just adding this in case anyone is facing issues:
Change the option in Google Sheets to allow anyone with the link to be an *editor*, and it will work.
If you don´t want anyone to randomly edit your Google File, then you can block the actual Sheet by going to Data - Protect Sheets and Ranges. And even though anyone with the link can access it nobody will be able to actually edit it.
Interesting strategy. Thank you for sharing
Hey does this still work? I tried with no luck, anyone with the link can be an editor, and change the last portion of the text but the url only gets recognized as html :( even after in the query settings telling it is an excel workbook it does not work
Great content! This solution is going to help me a lot. Tks Celia!!!
Awesome, Múcio. This video was extracted from a live class. Make sure to check the full lesson if you think it might help you too. The link appears at the end of the video.
Interesting! I’ll have to try this as I’ve been looking for the solution for a while
Let me know how it goes.
Thank you! this was really useful tip. Saved me so much time
Awesome! Glad to hear that. :)
Thanks Celia, this is very useful!
Awesome! Glad that it helped, Lee.
Thank you very much, Madam. I was inquiring as to why data from a Google Sheet cannot be accessed via Power Query. Your instruction on the final portion of the Google Sheet address was extremely helpful. I express my sincere gratitude and offer my utmost respect from Pakistan.
You're very welcome, Imran. Thank you so much for letting me know that the video was helpful to you. It makes my day happier! :)
Hi Celia, it's very interesting but u know people Can easily get your data i fond new users ( anonyme ....) Connecting in m'y documents ( it is business documents very confidential )
There is an option to publish on the web. We are not doing that in this case. Only sharing the link with authorized people.
When the data is highly confidential, companies may choose to keep it in their servers, highly protected, and not in the cloud.
Yes. I did the same approach as u explaind it. But people Can hack the link because as I mentioned above I found strange profils ( used in general by hackers)
Nice Tutorial could u pls share your insights on how do i tackle same with pdf data ..if u can help celia..
Some versions of Excel have a connector in Power Query to input from PDF. Check if your version has it here: Power Query data sources in Excel versions - Microsoft Support
prod.support.services.microsoft.com/en-us/office/power-query-data-sources-in-excel-versions-e9332067-8e49-46fc-97ff-f2e1bfa0cb16?WT.mc_id=M365-MVP-5003849
@@CeliaAlvesSolveExcel I created my own connector but required copy and pasting in excel.. because I'm using office 2016 or 2013 Only thanks you give me the new dimension to think ...
Hi Celia, I am using Excel 16.77.1 on a MacBook M1 and the "Other Sources" option is not available. I go to Data/Get Data (Power Query)/????
There is no option to choose Other Sources, any thoughts?
Unfortunately, Power Query for Excel for Mac is behind the Windows version. Not all data source types are available.
If the video shows the code for the connection, you can try to create a blank query and type in the same M code I have and see if it works. I know this trick works to make some connectors work in Excel for Mac even without having a button for them in the ribbon.
support.microsoft.com/en-gb/office/import-and-shape-data-in-excel-for-mac-power-query-7b2f337d-e7d2-4fdf-bf00-3dfbb1e5e9c5#:~:text=If%20you%20are%20a%20Microsoft,the%20latest%20version%20of%20Office.&text=Select%20Data%20%3E%20Get%20Data%20(Power,source%2C%20and%20then%20clicking%20Next.
@@CeliaAlvesSolveExcel Thank you VERY much for the fast reply, you are awesome. I will try the above - fingers crossed
@@autodifessa_per_donne 🤞😊
can the link be edited so that it contains the login data? e-mail and password
Not sure. Not that I know. This technique requires that the file is shared as "Anyone with the link can access" (not sure if these are the gift words)
This does not make the file public. But if the link is shared by one of the users to someone outside the organization, that person will have access to the file.
Hi Celia, I managed to do this for one of the files but upon retrying for a different file I'm only getting the html result. Any ideas on how to troubleshoot? I am already an editor for the google sheet
But sure what could be the problem. Is the file shared as "Anyone with the link can edit"?
@@CeliaAlvesSolveExcel thanks Celia that was definitely it! my Org had placed additional privacy to limit organizational content. I find the workaround would be to have the file reuploaded with xlsx extension instead of gsheet and linking via file path instead of weblink. For my case since I'm not the owner I am requesting the owner to reupload as xlsx and adding a shortcut to my drive for me to be able to access via file path. Thanks again for all your content and work that you do!
@@theresa9213 glad to help :)
wich are the differences between: "anyone with the link" and "making the file public" thank you
Making the file public makes it available to be found by Google searches. "Anyone with the link" does not make the file public so that it can be found by anyone - only people who know that specific URL will be able to access the file.
I tried this several times, and it didn't work
Sorry to hear that. It may have to do with the permissions on Google. Is it a Google business account? Make sure the file access permissions is set to anyone with the link. I hope this helps.
I tried and didn´t work at first. Just change the option in Google Sheets to allow anyone with the link to be an *editor*, and it will work. If you don´t want anyone to randomly edit your Google File, then you can block the actual Sheet going to Data - Protect Sheets and Ranges. And even though anyone with the link can access it nobody will be able to actually edit it.
can the link be edited so that it contains the login data? e-mail or password
@@AcademiaExcel thank you so much for this 🤗