Auto-generate a clickable workbook index

Northwind’s master workbook has grown to twenty tabs, and the row of tiny tab labels at the bottom of the screen is no longer a usable index. People scroll sideways hunting for the right sheet, or worse, give up and rebuild a tab that already exists somewhere.

This script keeps a Contents tab at the front of the workbook with a clickable link to every other sheet. It rebuilds the list from scratch each time it runs, so when a tab is added, removed or renamed the index simply catches up. One glance at the first tab and you can jump straight to anything.

What you’ll need

• A Google Sheet (the master workbook) with multiple tabs.
• Edit access, so the script can create the Contents tab and install a trigger.
• Nothing else — the script creates the Contents tab itself if it is missing.

The script

// The name of the index tab the script builds and keeps in sync.
const CONTENTS_TAB = 'Contents';

// Row where the list of links starts (row 1 holds the heading).
const FIRST_LINK_ROW = 3;

/**
 * Rebuilds the Contents tab so it holds one clickable link per sheet
 * in the workbook. Safe to run repeatedly — it clears and redraws.
 */
function rebuildContents() {
  const ss = SpreadsheetApp.getActive();

  // 1. Find the Contents tab, or create it as the first tab if missing.
  const contents = ss.getSheetByName(CONTENTS_TAB) ||
    ss.insertSheet(CONTENTS_TAB, 0);

  // 2. Wipe it clean so old links never linger after a tab is removed.
  contents.clear();

  // 3. Write the heading.
  contents.getRange(1, 1).setValue('Contents').setFontSize(18);

  // 4. Build one HYPERLINK formula per sheet, skipping the Contents tab.
  //    "#gid=<id>" jumps straight to that sheet inside this workbook.
  const rows = ss.getSheets()
    .filter((sheet) => sheet.getName() !== CONTENTS_TAB)
    .map((sheet) => [
      `=HYPERLINK("#gid=${sheet.getSheetId()}", "${sheet.getName()}")`,
    ]);

  // 5. Drop the formulas in as a single column, starting below the heading.
  if (rows.length) {
    contents.getRange(FIRST_LINK_ROW, 1, rows.length, 1).setFormulas(rows);
  }

  Logger.log('Rebuilt Contents with ' + rows.length + ' links.');
}

How it works

1. rebuildContents opens the active workbook and looks for a tab named Contents. If it does not exist, it inserts one at position 0 so the index is always the first tab.
2. It clears the Contents tab completely. Rebuilding from a blank slate is simpler and safer than trying to patch the existing list — a renamed or deleted sheet just disappears on the next run.
3. It writes the word “Contents” in row 1 at a larger font size as a heading.
4. It reads every sheet in the workbook, filters out the Contents tab itself, and maps each remaining sheet to a HYPERLINK formula. The #gid= fragment uses the sheet’s stable numeric ID, so the link keeps working even if the tab is later renamed.
5. It writes the whole column of formulas in one call, starting at FIRST_LINK_ROW. If the workbook only has the Contents tab, the list is empty and nothing is written.

Example run

A workbook with these tabs:

Contents · Q1 Budget · Q2 Budget · Vendors · Staff roster

After a run, the Contents tab reads:

Contents
Q1 Budget
Q2 Budget
Vendors
Staff roster

Each name is a live link — clicking Vendors jumps straight to that tab. Add a Q3 Budget sheet, let the script run, and a new link appears in the list without anyone editing it.

Trigger it

Run it once by hand from the editor to create the first index. To keep it in sync, install an onChange trigger so it fires when tabs are added or removed:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose function rebuildContents, event source From spreadsheet, and event type On change.
4. Save and approve the authorisation prompt.

Watch out for

• An onChange trigger fires on many change types, not just tab insertions, so rebuildContents will run more often than strictly needed. The function is cheap and idempotent, so this is harmless — but it is not free if the workbook is enormous.
• Sheet names go straight into the HYPERLINK formula. A tab name containing a double quote will break the formula; rename such tabs or escape the quotes before relying on this.
• The index lists tabs in their current left-to-right order. If you want it sorted alphabetically, sort the rows array before writing it.
• Renaming a tab keeps the link working because #gid= uses the numeric ID, but the visible label only updates on the next rebuild.
• The script clears the whole Contents tab on every run, so do not store anything else on it — notes or extra columns there will be wiped.