Connect to Google Sheets through Power Query with LAMBDA function

[jdellasala]

Watching this video, I figured out how to set up a live connection to a Google Workbook through Power Query.

Basically, you copy the URL for sharing, and then set up a URL with an ID gleaned from that URL. The ID is between the /d/ and the last / of the URL provided. The video is 2 years old, and the sharing URL in sheets has changed which is why the (hopefully) static parts of the needed URL are hard coded (but still needed to know the tail string of the URL provided!).

The "https:" has been removed from any pseudo links below to prevent them from working!

Basically what you need to do is build the link: "//docs.google.com/spreadsheets/d/" & ID & "/export?format=xlsx&id=" & ID
Doing that manually is the pits, so I wrote the following LAMBDA which takes the URL provided by using the [Share] button in the Workbook, and pass that URL to the 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  )
)

I call the function _GSheets (I always preface LAMBDA functions with an underscore), but you can obviously call it whatever you want! Assuming the URL "//docs.google.com/spreadsheets/d/XYZ/edit?usp=sharing" is the URL provided by Sheets and in cell A1, _GSheets(A1) would return the URL "//docs.google.com/spreadsheets/d/XYZ/export?format=xlsx&id=XYZ" which can then be used in a Web Power Query.

Side note - the latest beta of Power BI has a connector for Google Sheets, but one has to log in with a Google account through the browser first, and that connection is not retained. It does work fine, and doesn't require sharing, but the file will have to be shared for other users anyway. This function can share with a link available to anyone. My Google credentials had been saved on my first attempt to use Power Query with the browser URL, however clearing those credentials allowed me to connect anonymously. I assume if you share and restrict it to specific users, they would have to use their credentials to connect.

Once imported, changes in the Sheets workbook will be reflected in the refreshed Table in Excel.

My testing is very limited, so I'm anxious to hear if it works for you. Hope it works for you!