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

Polk, Brian 1 Reputation point
2021-04-11T17:10:16.613+00:00

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"));
}

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.
1,964 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Nancy Wang 106 Reputation points Microsoft Employee
    2021-04-30T20:55:22.9+00:00

    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

    No comments