Extract Google Sheet ID with REGEXTRACT

3
votes

Although the Google Sheet ID can be referenced in Apps Script with the following getSheetId(), I find the need to extract the ID within a Google Sheets cell.

I have extracted the first part of the URL but not the ID. Using:

=REGEXEXTRACT(F2,"^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)")

Luckily the URLs imported will always have the same format, so no need for a variable regex to handle different forms on the URL

Sheet URL

https://docs.google.com/spreadsheets/d/1ZNU46cVFMebj8t-kFXySuZyTTTupCo8EMrHr1exXr6Zg/edit#gid=757856732

Sheet ID (always occurs after 5th forward slash and before the last forward slash)

1ZNU46cVFMebj8t-kFXySuZyTTTupCo8EMrHr1exXr6Zg

How can I specifically get the Google Sheet ID from the URL referenced in another cell?

3
regexgoogle-sheetssplitgoogle-sheets-formula

3 Answers

3
votes

try like this:

=INDEX(SPLIT(A1, "/"),, 5)

enter image description here

2
votes

If the ID is always between the last but one and the last / you may use

=REGEXEXTRACT(A1, ".*/(.*)/")

See the regex demo

Details

  • .* - 0 or more chars other than line break chars as many as possible
  • / - a backslash (the last but one backslash)
  • (.*) - Capturing group 1 (the value returned by REGEXEXTRACT will be this one): 0 or more chars other than line break chars as many as possible
  • / - a backslash (it will be the last backslash in the string).
0
votes

If any body is interested on getting that in PHP here is the code for it

$sheetlink = "https://docs.google.com/spreadsheets/d/HERE IS THE GOOGLE SHEET ID/edit#gid=123"    
$sheetlink = preg_split('/(d\/|\/edit)/', $sheetlink);

echo $sheetlink['1'];