Operators paste Share links, "Open in new tab" URLs, and bare ids into the same column. extractDriveFileId normalizes that mess into a Drive id string (or '' if nothing matches) so DriveApp.getFileById / SpreadsheetApp.openById stop blowing up on /view?usp=sharing. Distinct from folder-path helpers — this is string parsing only.
What you'll need
- A cell or form field that might contain a URL or a raw id
- Downstream code that accepts an empty string as "skip / invalid"
- No Drive scopes required for the parse itself
How to use this snippet
function extractDriveFileId(input) {
if (input == null) return '';
var s = String(input).trim();
if (!s) return '';
// Already looks like a bare Drive id
if (/^[a-zA-Z0-9_-]{25,}$/.test(s) && s.indexOf('/') === -1 && s.indexOf('?') === -1) {
return s;
}
var patterns = [
/\/file\/d\/([a-zA-Z0-9_-]+)/,
/\/folders\/([a-zA-Z0-9_-]+)/,
/\/spreadsheets\/d\/([a-zA-Z0-9_-]+)/,
/\/document\/d\/([a-zA-Z0-9_-]+)/,
/\/presentation\/d\/([a-zA-Z0-9_-]+)/,
/[?&]id=([a-zA-Z0-9_-]+)/,
/open\?id=([a-zA-Z0-9_-]+)/
];
for (var i = 0; i < patterns.length; i++) {
var m = s.match(patterns[i]);
if (m && m[1]) return m[1];
}
return '';
}
Example
function openLinkedSpreadsheet(rowObj) {
var id = extractDriveFileId(rowObj.workbookUrl);
if (!id) {
throw new Error('Row ' + rowObj._row + ': could not parse Drive id from workbookUrl');
}
return SpreadsheetApp.openById(id);
}
// Custom function friendly (returns blank on miss)
function DRIVE_ID(cell) {
return extractDriveFileId(cell);
}
Tips:
- Empty string on miss — throw at the call site with a row-aware message.
- Folder links and file links both work; confirm you wanted a file before
getFileById. - Shortcut / Shared drive UI URLs change over time — extend
patternswhen a new shape shows up in the wild. - Complements
getOrCreateFolder/getOrCreateFolderPathwhich need an already-valid id.
Happy Coding!
