=GSHEETS(SURL)

SURL
URL copied from "Anyone with the link" Share URL from a Google Sheets file.

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

jdellasala

Board Regular
Joined
Dec 11, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.

Excel Formula:
=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
AB
1Anyone 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
RangeFormula
A2A2=FORMULATEXT(B2)&": "
B2B2=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

1669227393060.png


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.


1669227451605.png


The initial Navigator window looks like an Excel Workbook.

1669227536098.png


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!
 
Last edited by a moderator:
Upvote 0

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,978
Perhaps an alternative version by using the TEXTSPLIT function since the position of the sheet id won't change:

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

Forum statistics

Threads
1,181,171
Messages
5,928,518
Members
436,606
Latest member
ppainter1958

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top