Help with Excel (Online) script that creates a table of contents, but only for visible sheets

Im.DavidJ 0 Reputation points
2024-06-14T06:59:45.0566667+00:00

Hello.

I want to use the Excel online script found here: https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/table-of-contents

However I'd like for it to only work for visible sheets within the workbook.

I've tried to do this myself, but am not having much luck.

function main(workbook: ExcelScript.Workbook) {

// Insert a new worksheet at the beginning of the workbook.

let tocSheet = workbook.addWorksheet();

tocSheet.setPosition(0);

tocSheet.setName("Table of Contents");

// Give the worksheet a title in the sheet.

tocSheet.getRange("A1").setValue("Table of Contents");

tocSheet.getRange("A1").getFormat().getFont().setBold(true);

// Create the table of contents headers.

let tocRange = tocSheet.getRange("A2:B2")

tocRange.setValues([["#", "Name"]]);

// Get the range for the table of contents entries.

let worksheets = workbook.getWorksheets();

tocRange = tocRange.getResizedRange(worksheets.length, 0);

// Loop through all worksheets in the workbook, except the first one.

for (let i = 1; i < worksheets.length; i++) {

// Create a row for each worksheet with its index and linked name.

tocRange.getCell(i, 0).setValue(i);

tocRange.getCell(i, 1).setHyperlink({

textToDisplay: worksheets[i].getName(),

documentReference: `'${worksheets[i].getName()}'!A1`

});

};

// Activate the table of contents worksheet.

tocSheet.activate();

}

Office
Office
A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.
1,451 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,671 questions
JavaScript API
JavaScript API
An Office service that supports add-ins to interact with objects in Office client applications.
935 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,699 questions
0 comments No comments
{count} votes