Open By URL Or Id

Paste a whole browser URL into SpreadsheetApp.openById and the run dies. openByUrlOrId(urlOrId) accepts a Spreadsheet URL or raw id, extracts /spreadsheets/d/<id> when present, then opens with SpreadsheetApp.openById — so operators can paste whatever the address bar gave them.

When to use

  • Menu prompts / Script Properties that store “the spreadsheet”
  • Handoffs where someone copies the full Chrome URL
  • Any helper that used to assume a clean 44-char id

How to use this snippet

/** * Open a spreadsheet from a full Sheets URL or a raw spreadsheet id. * Extracts /spreadsheets/d/<id> when present; otherwise treats input as id. * @param {string} urlOrId * @return {SpreadsheetApp.Spreadsheet} */ function openByUrlOrId(urlOrId) { var s = String(urlOrId == null ? '' : urlOrId).trim(); if (!s) { throw new Error('openByUrlOrId: empty urlOrId'); } var m = s.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/); var id = m ? m[1] : s; // Guard obvious “still a URL but not a Sheets URL” pastes if (/^https?:\/\//i.test(id)) { throw new Error('openByUrlOrId: not a Google Sheets URL or id: ' + s.slice(0, 80)); } return SpreadsheetApp.openById(id); }

Example

function openTargetFromProperty_() { var raw = PropertiesService.getScriptProperties().getProperty('targetSpreadsheet'); var ss = openByUrlOrId(raw); Logger.log('Opened %s (%s)', ss.getName(), ss.getId()); return ss; } function promptAndOpen() { var ui = SpreadsheetApp.getUi(); var res = ui.prompt('Spreadsheet URL or id'); if (res.getSelectedButton() !== ui.Button.OK) return; var ss = openByUrlOrId(res.getResponseText()); ui.alert('Opened: ' + ss.getName()); }

Tips:

  • Prefer storing the id in Script Properties after the first successful open — URLs get edit/share query junk appended.
  • openById still needs the caller to have access; this helper only fixes parsing.
  • Docs / Slides URLs won’t match — fail loud instead of feeding https://… to openById.

Tip: NitroGAS Co-Pilot can swap brittle openById(Browser.inputBox(…)) calls for openByUrlOrId so pasted address-bar URLs stop blowing up mid-run.

Happy Coding!

Tags

NitroGAS Chrome Extension

Want this snippet handy inside the Apps Script editor? NitroGAS gives you free themes and snippets — plus optional Co-Pilot when you want a boost (1-week, 1-month, or yearly passes — no subscription). Happy Coding!

Get the Extension

Other Helpful Code Snippets:

Fill Html Template

function fillHtmlTemplate(html, values, opts) { opts = opts || {}; var onMissing = opts.onMissing || 'blank'; // 'blank' | 'throw' values = values || {}; function escapeHtml(s) { return String(s == null ? '' : s) .replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;') .replace(/"/g, '&quot;').replace(/'/g, '&#39;'); } // {{{token}}} = raw HTML; {{token}} = escaped return String(html).replace(/\{\{\{([^{}]+)\}\}\}|\{\{([^{}]+)\}\}/g, function (_, rawKey, escKey) { var key = String(rawKey || escKey).trim(); var has = Object.prototype.hasOwnProperty.call(values, key); if (!has) { if (onMissing === 'throw') throw new Error('fillHtmlTemplate: missing key "' + key + '"'); return ''; } var val = values[key]; if (rawKey) return String(val == null ? '' : val); // raw / unescaped return escapeHtml(val); }); } // fillHtmlTemplate('<p>Hi {{name}}</p>', { name: 'Ada <3' });

Group Objects By Key

function groupObjectsByKey(rows, keyOrFn) { var out = {}; if (!rows || !rows.length) return out; var getKey = typeof keyOrFn === 'function' ? keyOrFn : function (row) { return row[keyOrFn]; }; rows.forEach(function (row) { var key = getKey(row); // Blank / missing keys become '' so they still land in one bucket if (key == null) key = ''; key = String(key); if (!out[key]) out[key] = []; out[key].push(row); }); return out; } // var byClient = groupObjectsByKey(rows, 'Client');

Is Within Business Hours

function isWithinBusinessHours(startHHmm, endHHmm, opts) { opts = opts || {}; var ss = opts.spreadsheet || (typeof SpreadsheetApp !== 'undefined' ? SpreadsheetApp.getActive() : null); var tz = (ss && ss.getSpreadsheetTimeZone && ss.getSpreadsheetTimeZone()) || Session.getScriptTimeZone() || 'America/New_York'; var when = opts.when || new Date(); // Weekday: 1=Mon … 7=Sun in spreadsheet timezone (Utilities 'u') var weekday = Number(Utilities.formatDate(when, tz, 'u')); var allowed = opts.weekdays || [1, 2, 3, 4, 5]; if (allowed.indexOf(weekday) === -1) return false; var mins = function (hhmm) { var p = String(hhmm).split(':'); return Number(p[0]) * 60 + Number(p[1] || 0); }; var nowMins = Number(Utilities.formatDate(when, tz, 'H')) * 60 + Number(Utilities.formatDate(when, tz, 'm')); var start = mins(startHHmm); var end = mins(endHHmm); // Half-open window [start, end) so 17:00 is "closed" if (end <= start) return nowMins >= start || nowMins < end; // overnight window return nowMins >= start && nowMins < end; } // if (!isWithinBusinessHours('09:00', '17:00')) return;