Javascript regex for extracting spreadsheet ID and sheet ID from google sheets URL

3
votes

I want Javascript regex for extracting spreadsheet ID and sheet ID from google sheets URL. The URL for sheets.google.com spreadsheet looks like: https://docs.google.com/spreadsheets/d/1QOKbrUz7AWUwT-hNfWd1Pxf__QObYCZ1rD2CKMjpnGw/edit#gid=0.

This Link describes what regex to use for extracting spreadsheet ID and sheet ID. I tried them in Javascript but it is not working. Here is code that I tried:

var spreadsheetId = new RegExp("/spreadsheets/d/([a-zA-Z0-9-_]+)").exec(resourceUrl);
var sheetId = new RegExp("[#&]gid=([0-9]+)").exec(resourceUrl);
3
javascriptgoogle-sheetsgoogle-docs

3 Answers

4
votes

If it succeeds, the exec() method places the matching characters in its indexes. Index 0 is the full string of matched characters. Other indexes (1, 2, ...) are the characters matched by parts of the regular expression delimited by parentheses, in the order they occur.

Your regular expressions match a larger part of the URL string, but the IDs are the parts delimited by parentheses. In both cases you have just one pair of parentheses hence they are in index 1. So the values you want are in

 spreadsheetId = new RegExp("/spreadsheets/d/([a-zA-Z0-9-_]+)").exec(resourceUrl)[1];
 sheetId = new RegExp("[#&]gid=([0-9]+)").exec(resourceUrl)[1];

(note the [1] at the end of both instructions)

2
votes

Assuming that the Google Spreadsheet ID has more than 15 characters, the regex would be like this. The GID may or may not be available in a URL.

var resourceUrl = "https://docs.google.com/spreadsheets/d/1QOKbrUz7AWUwT-hNfWd1Pxf__QObYCZ1rD2CKMjpnGw/edit#gid=0";

var matches = /\/([\w-_]{15,})\/(.*?gid=(\d+))?/.exec(resourceUrl);

if (matches) {
  console.log("Spreadsheet: " + matches[1]);
  console.log("Sheet: " + matches[3]);
}
0
votes

Something wrong with just looking for the segment following "/d/" ?

/.+\/d\/([^/]+)/

The capture group is the id