Get Sheet By Gid

A sheet URL ends in #gid=123456789. That number points at one tab and stays the same when someone renames it. getSheetByGid(ss, gid) finds the tab from that number, so a script keeps working after "Sheet1" becomes "Orders 2027".

When to use

  • Scripts that take a pasted sheet link as input
  • Config that should survive tab renames
  • Matching a link in an email or doc to the right tab

How to use this snippet

/** * Find a tab by its gid (the number after #gid= in the URL). * * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} ss * @param {number|string} gid * @return {GoogleAppsScript.Spreadsheet.Sheet|null} */ function getSheetByGid(ss, gid) { var target = Number(gid); var sheets = ss.getSheets(); for (var i = 0; i < sheets.length; i++) { if (sheets[i].getSheetId() === target) return sheets[i]; } return null; }

Example

function openFromLink(url) { var ss = SpreadsheetApp.openByUrl(url); var gid = (url.match(/[#&]gid=(\d+)/) || [])[1] || 0; var sheet = getSheetByGid(ss, gid); if (!sheet) throw new Error('No tab with gid ' + gid); return sheet; }

Notes

  • The first tab of a new spreadsheet is usually gid 0.
  • Copying a spreadsheet keeps the gids; duplicating a tab gives the copy a new one.

Happy Coding!

Tags

NitroGAS Chrome Extension

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

Get the Extension

Other Helpful Code Snippets:

Close Form At Limit

function closeFormAtLimit(form, opts) { opts = opts || {}; if (!form.isAcceptingResponses()) return false; var count = form.getResponses().length; var full = opts.maxResponses && count >= opts.maxResponses; var late = opts.closeAt && new Date() >= new Date(opts.closeAt); if (!full && !late) return false; form.setCustomClosedFormMessage( opts.message || (full ? 'This form has reached its response limit. Thank you!' : 'This form is now closed. Thank you!') ); form.setAcceptingResponses(false); return true; }

Parse Loose Date

function parseLooseDate(value, opts) { opts = opts || {}; if (value instanceof Date) return isNaN(value.getTime()) ? null : value; if (typeof value === 'number') { // Sheets serial number (days since 1899-12-30) return new Date(Math.round((value - 25569) * 86400000) + new Date().getTimezoneOffset() * 60000); } var s = String(value || '').trim(); if (!s) return null; var MONTHS = { jan: 0, feb: 1, mar: 2, apr: 3, may: 4, jun: 5, jul: 6, aug: 7, sep: 8, oct: 9, nov: 10, dec: 11 }; var y, m, d, mt; if ((mt = s.match(/^(\d{4})[-\/.](\d{1,2})[-\/.](\d{1,2})/))) { y = +mt[1]; m = +mt[2] - 1; d = +mt[3]; } else if ((mt = s.match(/^(\d{1,2})[-\/.](\d{1,2})[-\/.](\d{2,4})$/))) { var a = +mt[1], b = +mt[2]; if (opts.dayFirst === undefined && a <= 12 && b <= 12 && a !== b) { throw new Error('Ambiguous date "' + s + '": pass {dayFirst: true} or {dayFirst: false}'); } var dayFirst = opts.dayFirst === undefined ? a > 12 : opts.dayFirst; d = dayFirst ? a : b; m = (dayFirst ? b : a) - 1; y = +mt[3]; if (y < 100) y += 2000; } else if ((mt = s.match(/^(\d{1,2})\s+([a-z]{3})[a-z]*\.?,?\s+(\d{4})$/i))) { d = +mt[1]; m = MONTHS[mt[2].toLowerCase()]; y = +mt[3]; } else if ((mt = s.match(/^([a-z]{3})[a-z]*\.?\s+(\d{1,2})(?:st|nd|rd|th)?,?\s+(\d{4})$/i))) { m = MONTHS[mt[1].toLowerCase()]; d = +mt[2]; y = +mt[3]; } else { return null; } if (m === undefined) return null; var out = new Date(y, m, d); if (out.getFullYear() !== y || out.getMonth() !== m || out.getDate() !== d) return null; return out; }

Get Latest Csv Attachment

function getLatestCsvAttachment(query) { var threads = GmailApp.search(query + ' has:attachment filename:csv', 0, 10); var best = null; threads.forEach(function (t) { t.getMessages().forEach(function (m) { m.getAttachments().forEach(function (a) { if (!/\.csv$/i.test(a.getName())) return; if (!best || m.getDate() > best.date) best = { date: m.getDate(), att: a }; }); }); }); if (!best) return null; return { name: best.att.getName(), date: best.date, rows: Utilities.parseCsv(best.att.getDataAsString()) }; }