Site icon Kaizen.Personal computer work.

Get all Worksheets in the Workbook.(Microsoft Excel Office Scripts)

Japanese version.

Introducing a method to retrieve all worksheets in a workbook.

This is useful if you want to retrieve or change worksheet names for all worksheets.

Operations

The following syntax is used (Red text indicates variable parts)

Array variable = workbook.getWorksheets();

Performs some operation on the Array variable obtained.

Getting the number of worksheets in a workbook

In the following code example, the .length property is used to obtain 3, the number of arrays (number of worksheets in the workbook).

let sheets = workbook.getWorksheets();
console.log(sheets.length);

Display names for all worksheets

let sheets = workbook.getWorksheets();
  
for (let sheet of sheets){
	console.log(sheet.getName());
}

Change all worksheet names

The changed sheet names must be set so that there are no duplicate sheet names.

let sheets = workbook.getWorksheets();
 
for(let i=0; i<sheets.length; i++){
	sheets[i].setName("sheet_" + (i+1).toString());
}

When this script is executed, the sheet names will be changed to "sheet_1", "sheet_2", and "sheet_3".

Change Tab color for all worksheets

let sheets = workbook.getWorksheets();
  
for(let i=0; i<sheets.length; i++){
	sheets[i].setTabColor("green");
}

When this script is executed, the tab color of all sheets will be green.

For those who want to learn Office script effectively

The information on this site is now available in an easy-to-read e-book format.

Or Kindle Unlimited (unlimited reading).

You willl discover how to about basic operations.

By the end of this book, you will be equipped with the knowledge you need to use Excel Office Script to streamline your workflow.

Links

Office Scripts Articles

Exit mobile version