Excel Table of Contents for Sheet Tabs with Hyperlinks | Create Automatically | Office Script Macro

In this Microsoft Excel video tutorial I demonstrate how to use an Office Script to automatically create a table of contents including hyperlinks for each worksheet tab.

Download the featured file here.

Here is the Office Script code:

function main(workbook: ExcelScript.Workbook) {

    //If TOC already exists, delete it
    if (workbook.getWorksheet("Table of Contents")) {
        let oldTOC = workbook.getWorksheet("Table of Contents")
        oldTOC.delete();
    }

    //Insert a TOC worksheet as the first worksheet in the workbook
    let tocWsheet = workbook.addWorksheet();
    tocWsheet.setPosition(0);
    tocWsheet.setName("Table of Contents");

    //Enter a title for the TOC worksheet and format it
    tocWsheet.getRange("A1").setValue("Table of Contents");
    tocWsheet.getRange("A1").getFormat().getFont().setBold(true)
    tocWsheet.getRange("A1").getFormat().getFont().setName("Calibri");
    tocWsheet.getRange("A1").getFormat().getFont().setSize(14);
    tocWsheet.getRange("A1:D1").getFormat().getFill().setColor("#E7E6E6");

    //Enter column headings for the TOC
    let tocRange = tocWsheet.getRange("A2:B2")
    tocRange.setValues([["Sheet No.", "Sheet Name"]]);
    tocRange.getFormat().getFont().setItalic(true)

    //Calculate the size of the TOC range (based on the number of sheets)
    let worksheets = workbook.getWorksheets();
    tocRange = tocRange.getResizedRange(worksheets.length, 0);

    //Loop through every worksheet except the TOC worksheet
    for (let tocEntry = 1; tocEntry < worksheets.length; tocEntry++) {
        //Enter the position of the worksheet
        tocRange.getCell(tocEntry, 0).setValue(tocEntry);
        //Create a hyperlink to the worksheet
        tocRange.getCell(tocEntry, 1).setHyperlink({
            textToDisplay: worksheets[tocEntry].getName(),
            documentReference: `'${worksheets[tocEntry].getName()}'!A1`
        });
    };

    //Loop through each worksheet and remove existing 'Back to TOC' hyperlinks

    // Loop over each worksheet
    for (let sheet of worksheets) {
        // Get the used range
        let usedRange = sheet.getUsedRange();
        if (!usedRange) {
            continue; // If no used range, skip to next sheet
        }

        // Get the values of the used range
        let values = usedRange.getValues();

        // Go through each cell in the used range
        for (let row = 0; row < values.length; row++) {
            for (let col = 0; col < values[row].length; col++) {
                if (values[row][col] === "Back to TOC") {
                    // Clear the cell if it matches "Back to TOC"
                    usedRange.getCell(row, col).clear(ExcelScript.ClearApplyTo.contents);
                }
            }
        }
    }


    //Loop through each worksheet and add 'Back to TOC' hyperlink
    for (let tocNav = 1; tocNav < worksheets.length; tocNav++) {
        //Find the column to the right of the last used column
        let usedrange = worksheets[tocNav].getUsedRange()
        let lastcol: number = usedrange.getLastColumn().getColumnIndex()
        //Add the hyperlink 2 columns to the right of the last used column
        worksheets[tocNav].getCell(0, lastcol + 2).setHyperlink({
            textToDisplay: "Back to TOC",
            documentReference: `'${worksheets[0].getName()}'!A1`
        });
    };


    //Activate the TOC worksheet
    tocWsheet.activate();
    //Autofit column B
    tocWsheet.getRange("B:B").getFormat().autofitColumns();
    //Turn gridlines off
    tocWsheet.setShowGridlines(false);
}