Set Freeze Panes.(Microsoft Excel Office Scripts)
Introduction to setting Freeze Panes using Office scripts.
Operations
Specify range of cells
The following syntax is used (Red text indicates variable parts)
// Set sheet to variable
let variable = workbook.getWorksheet("Sheet name");
// Freeze
variable.getFreezePanes().freezeAt(variable.getRange("Cell address"));The cell address specifies the range to be fixed.
For example, in normal operation, if you select cell "D6" and fix it, "A1:C5" will be fixed. To do the same thing, specify "A1:C5".
Specify rows
The following syntax is used.
workbook.getWorksheet("Sheet name").getFreezePanes().freezeRows(Number of rows);If 3 is specified for the Number of rows, rows 1-3 are fixed.
Specify columns
The following syntax is used.
workbook.getWorksheet("Sheet name").getFreezePanes().freezeColumns(Number of columns);If 3 is specified for the Number of columns, columns A through C are fixed.
Release
The following syntax is used.It is released on a sheet-by-sheet basis.
workbook.getWorksheet("Sheet name").getFreezePanes().unfreeze();
Get the range that is currently frozen
The following syntax is used.
let variable = workbook.getWorksheet("Sheet name").getFreezePanes().getLocation();
console.log(variable.getAddress());
However, if there is no frozen range, the result of getLocation() will be undefined and getAddress() will error.
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.