GSHEETS

[jdellasala]

GSHEETS(SURL) - Given the "Anyone with the link" Share URL from Google Sheets (SURL), provides URL to use in a Web Power Query to retrieve data from the Google Sheet file.

=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 
    )
)

Book1
	A	B
1	Anyone Share Link:	https://docs.google.com/spreadsheets/d/1EXNH3rKprSzAx922CH3gSGmdQifj18u9/edit?usp=sharing&ouid=102379200906293412742&rtpof=true&sd=true
2	=GSHEETS(B1):	https://docs.google.com/spreadsheets/d/1EXNH3rKprSzAx922CH3gSGmdQifj18u9/export?format=xlsx&id=1EXNH3rKprSzAx922CH3gSGmdQifj18u9
Sheet1

Cell Formulas
Range	Formula
A2	A2	=FORMULATEXT(B2)&": "
B2	B2	=GSHEETS(B1)

I got this from the following YouTube video - Get Google Sheet Data in Excel with Power Query.

With an open Google Sheet, click the [Share] button in the top right corner of the page, select Anyone with the link from the drop down, and then click Copy Link

and paste it either into a cell for future use or into a From the Web query under Data -> Get & Transform Data.
This will work with an Anonymous connection. I haven't been able to get it to work with a Restricted link reliably. Please post if you have any insight to using a Restricted link.

The initial Navigator window looks like an Excel Workbook.

When imported, the Column Headers are in the top row and have to be promoted if needed. I have not tried this with formulas in the Google Sheet yet. Let me know how it works if you do.
Also, the name of the file does not appear anywhere in the Query. You may want to include that in the name of the Query.

Feedback is welcome!

 

[smozgur]

Perhaps an alternative version by using the TEXTSPLIT function since the position of the sheet id won't change:

=LAMBDA(SURL,
    LET(base,"https://docs.google.com/spreadsheets/d/",
        id,INDEX(TEXTSPLIT(SURL,"/"),6),
        base & id & "/export?format=xlsx&id=" & id
    )
)(B1)