Office script update each hyperlink on that worksheet with the worksheet name

Question

I have 111 tabs on my workbook. I want to freeze the same area on each sheet without running the script on each sheet. How do I do this? Below is my script.

function main(workbook: ExcelScript.Workbook)
{
let selectedSheet = workbook.getActiveWorksheet();
// Set the frozen cells on selectedSheet
selectedSheet.getFreezePanes()
.freezeAt(selectedSheet.getRange("A1:A11"));
}

Answer 1

Hi there!

This is a great question - thanks so much for asking! Could you let me know if the following code works for your workbook?

function main(workbook: ExcelScript.Workbook) {
// Get all the worksheets in the workbook.
let sheets = workbook.getWorksheets();

// Get a list of all the worksheet names.
let names = sheets.map((sheet) => sheet.getName());

// Write in the console all the worksheet names and the total count.
console.log(names);
console.log(Total worksheets inside of this workbook: ${sheets.length});

// Set the tab color each worksheet to a random color
for (let sheet of sheets) {
// Generate a random color hex-code.
sheet.getFreezePanes()
.freezeAt(sheet.getRange("A1:A11"));
}
}

As an added note, I adapted most of this code from the "Iterating over collections" section of the Office Scripts documentation: https://learn.microsoft.com/en-us/office/dev/scripts/resources/excel-samples. I hope this helps!

-Nancy from the Office Scripts team