question

PolkBrian-4129 avatar image
0 Votes"
PolkBrian-4129 asked NancyfromtheOffice answered

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

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-scripts-excel-dev
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

worksheets are named
CV-1, CV-2, CVC-3 through CV-40
FAM-1, FAM-2,FAM-3 through FAM-20
CRIM-1, CRIM-2,CRIM-3 through CRIM-20
PRO-1, PRO-2,PRO-3 through PRO-20

0 Votes 0 ·

Attached is screen shot of worksheet names

0 Votes 0 ·

1 Answer

NancyfromtheOffice avatar image
0 Votes"
NancyfromtheOffice answered

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://docs.microsoft.com/en-us/office/dev/scripts/resources/excel-samples. I hope this helps!

-Nancy from the Office Scripts team

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.