Log In
Register
Home
March 10, 2016
Offline
I need to connect Excel to a file on the drive.
In the drive, I accessed the Internet advertising panel and copied the URL in Power Query imported from the Internet,
but I got the following error described in the screenshot.
There are other files that did work for me and since I have Google Business, I ask what the solution is, thanks!!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Lea,
Hard to say why. Try opening that link in a different browser in private mode, make sure that link is accessible without errors.
Use a simple url, the one you posted points to accounts.google.com instead of the file directly, should be just:
https://doc-10-44.....output=xlsx
Excel does not have yet a google sheets connector where you can sign in to google, it's available only in PBI for the moment.
Active Member
January 19, 2023
Offline
This YouTube video should help: Get Google Sheet Data in Excel with Power Query:
If you have Excel 2021/365, you can try this LAMBDA function:
=LAMBDA(SURL,
LET(base,"https://docs.google.com/spreadsheets/d/",
tail, RIGHT(SURL,LEN(SURL)-SEARCH("#",SUBSTITUTE(SURL,"/","#",LEN(SURL)-LEN(SUBSTITUTE(SURL,"/",""))))+1),
ls, LEN(SURL),
lb, LEN(base),
lt, LEN(tail),
stail, "/export?format=xlsx&id=",
ID, MID( SURL, lb+1, ls - lb - lt),
base & ID & stail & ID
)
)
The only parameter is the Share URL you get as per the Video. I love Power Query, but this is good for smaller data that doesn't need to be cleaned.
Hope that helps.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Jerry,
The video you shared is for getting data from public sheets, they are shared with anyone with the link, sign in is not necessary.
Lea's sheet is not public, users need to login in order to see data, but PQ does not have yet a google sheets connector to allow login.
Thank you for sharing.
Active Member
January 19, 2023
Offline
I did a search for "power query authentication for google docs" and found this doc: Power Query Google Sheets connector - Power Query
https://learn.microsoft.com/en.....oglesheets
The bad news is it's not part of Excel (yet?). The good news is that it IS available in Power BI (Desktop {Version: 2.112.1161.0 64-bit (December 2022)}), and it took a couple of times to get it to work, I got it to work on the 2nd try. The link needed is the one you get when you open the file in Google Sheets itself.
Once it's in Power BI (Desktop), you'll need DAX Studio to get the data out, and then only as a CSV file. You can get it here:
The small test I did worked fine, but it was just two small worksheets that got exported as two CSV files, but after closing and reopening the Power BI file, it was able to refresh without any problem.
It looks like there MIGHT be a way to use an API to connect, but that's way beyond me!
1 Guest(s)
