ExcelScript.RangeFormat interface
A format object encapsulating the range's font, fill, borders, alignment, and other properties.
Remarks
Examples
/**
* This script applies some simple formatting to the top row of the used range.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the top row of the used range in the current worksheet.
const selectedSheet = workbook.getActiveWorksheet();
const topRow = selectedSheet.getUsedRange().getRow(0);
// For the top row, set the fill to black, the font color to white, and the font to be bold.
const format: ExcelScript.RangeFormat = topRow.getFormat();
format.getFill().setColor("black");
format.getFont().setColor("white");
format.getFont().setBold(true);
}
Methods
| adjust |
Adjusts the indentation of the range formatting. The indent value ranges from 0 to 250 and is measured in characters. |
| autofit |
Changes the width of the columns of the current range to achieve the best fit, based on the current data in the columns. |
| autofit |
Changes the height of the rows of the current range to achieve the best fit, based on the current data in the columns. |
| get |
Specifies if text is automatically indented when text alignment is set to equal distribution. |
| get |
Collection of border objects that apply to the overall range. |
| get |
Specifies the width of all columns within the range. If the column widths are not uniform, |
| get |
Returns the fill object defined on the overall range. |
| get |
Returns the font object defined on the overall range. |
| get |
Represents the horizontal alignment for the specified object. See |
| get |
An integer from 0 to 250 that indicates the indent level. |
| get |
Returns the format protection object for a range. |
| get |
Gets a border object using its name. |
| get |
Specifies a double that lightens or darkens a color for range borders. The value is between -1 (darkest) and 1 (brightest), with 0 for the original color. A |
| get |
The reading order for the range. |
| get |
The height of all rows in the range. If the row heights are not uniform, |
| get |
Specifies if text automatically shrinks to fit in the available column width. |
| get |
The text orientation of all the cells within the range. The text orientation should be an integer either from -90 to 90, or 180 for vertically-oriented text. If the orientation within a range are not uniform, then |
| get |
Determines if the row height of the |
| get |
Specifies if the column width of the |
| get |
Represents the vertical alignment for the specified object. See |
| get |
Specifies if Excel wraps the text in the object. A |
| set |
Specifies if text is automatically indented when text alignment is set to equal distribution. |
| set |
Specifies the width of all columns within the range. |
| set |
Represents the horizontal alignment for the specified object. See |
| set |
An integer from 0 to 250 that indicates the indent level. |
| set |
Specifies a double that lightens or darkens a color for range borders. The value is between -1 (darkest) and 1 (brightest), with 0 for the original color. A |
| set |
The reading order for the range. |
| set |
Specifies the height of all rows in the range. |
| set |
Specifies if text automatically shrinks to fit in the available column width. |
| set |
The text orientation of all the cells within the range. The text orientation should be an integer either from -90 to 90, or 180 for vertically-oriented text. If the orientation within a range are not uniform, then |
| set |
Determines if the row height of the |
| set |
Specifies if the column width of the |
| set |
Represents the vertical alignment for the specified object. See |
| set |
Specifies if Excel wraps the text in the object. A |
Method Details
adjustIndent(amount)
Adjusts the indentation of the range formatting. The indent value ranges from 0 to 250 and is measured in characters.
adjustIndent(amount: number): void;Parameters
- amount
-
number
The number of character spaces by which the current indent is adjusted. This value should be between -250 and 250. Note: If the amount would raise the indent level above 250, the indent level stays with 250. Similarly, if the amount would lower the indent level below 0, the indent level stays 0.
Returns
void
Examples
/**
* This script adjusts the indentation of a specific table column.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the first table in the current worksheet.
const selectedSheet = workbook.getActiveWorksheet();
const table = selectedSheet.getTables()[0];
// Get the data range of the second column.
const secondColumn = table.getColumn(2);
const data = secondColumn.getRangeBetweenHeaderAndTotal();
// Add an indentation of 1 character space to the data range.
data.getFormat().adjustIndent(1);
}
autofitColumns()
Changes the width of the columns of the current range to achieve the best fit, based on the current data in the columns.
autofitColumns(): void;Returns
void
Examples
/**
* This script creates a new table from existing data and autofits the columns.
*/
function main(workbook: ExcelScript.Workbook) {
const currentSheet = workbook.getActiveWorksheet();
const usedRange = currentSheet.getUsedRange();
// Create the table.
const table = currentSheet.addTable(usedRange, true);
// Format the table columns.
table.getRange().getFormat().autofitColumns();
}
autofitRows()
Changes the height of the rows of the current range to achieve the best fit, based on the current data in the columns.
autofitRows(): void;Returns
void
Examples
/**
* This script creates a new table from existing data and autofits the rows.
*/
function main(workbook: ExcelScript.Workbook) {
const currentSheet = workbook.getActiveWorksheet();
const usedRange = currentSheet.getUsedRange();
// Create the table.
const table = currentSheet.addTable(usedRange, true);
// Format the table rows.
table.getRange().getFormat().autofitRows();
}
getAutoIndent()
Specifies if text is automatically indented when text alignment is set to equal distribution.
getAutoIndent(): boolean;Returns
boolean
getBorders()
Collection of border objects that apply to the overall range.
getBorders(): RangeBorder[];Returns
getColumnWidth()
Specifies the width of all columns within the range. If the column widths are not uniform, null will be returned.
getColumnWidth(): number;Returns
number
Examples
/**
* This script doubles the column width for every column in the active worksheet's used range.
*/
function main(workbook: ExcelScript.Workbook) {
const currentSheet = workbook.getActiveWorksheet();
const usedRange = currentSheet.getUsedRange();
// To optimize performance, get all the current row heights before setting them.
let currentWidths = Array<number>(usedRange.getColumnCount());
for (let column = 0; column < currentWidths.length; column++) {
currentWidths[column] = usedRange.getColumn(column).getFormat().getColumnWidth();
}
// Set the new column widths.
for (let column = 0; column < currentWidths.length; column++) {
usedRange.getFormat().setColumnWidth(currentWidths[column] * 2);
}
getFill()
Returns the fill object defined on the overall range.
getFill(): RangeFill;Returns
Examples
/**
* This script gives the total row of a table a green color fill.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the first table in the workbook.
let table = workbook.getTables()[0];
// Get the range for the total row of the table.
let totalRange = table.getTotalRowRange();
// Set the fill color to green.
totalRange.getFormat().getFill().setColor("green");
}
getFont()
Returns the font object defined on the overall range.
getFont(): RangeFont;Returns
Examples
/**
* This script bolds the text of cell A1.
*/
function main(workbook: ExcelScript.Workbook) {
// Get A1 on the current worksheet.
const cell = workbook.getActiveWorksheet().getCell(0,0);
// Bold the font for that cell
cell.getFormat().getFont().setBold(true);
}
getHorizontalAlignment()
Represents the horizontal alignment for the specified object. See ExcelScript.HorizontalAlignment for details.
getHorizontalAlignment(): HorizontalAlignment;Returns
getIndentLevel()
An integer from 0 to 250 that indicates the indent level.
getIndentLevel(): number;Returns
number
getProtection()
Returns the format protection object for a range.
getProtection(): FormatProtection;Returns
getRangeBorder(index)
Gets a border object using its name.
getRangeBorder(index: BorderIndex): RangeBorder;Parameters
- index
- ExcelScript.BorderIndex
Index value of the border object to be retrieved. See ExcelScript.BorderIndex for details.
Returns
Examples
/**
* This script adds a border around the outside of a range.
*/
function main(workbook: ExcelScript.Workbook) {
// Get a range from the current worksheet.
let range = workbook.getActiveWorksheet().getRange("B2:E15");
// Add a border around the whole bounding range.
let format = range.getFormat();
format.getRangeBorder(ExcelScript.BorderIndex.edgeTop).setStyle(ExcelScript.BorderLineStyle.continuous); // Top border
format.getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setStyle(ExcelScript.BorderLineStyle.continuous); // Bottom border
format.getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setStyle(ExcelScript.BorderLineStyle.continuous); // Left border
format.getRangeBorder(ExcelScript.BorderIndex.edgeRight).setStyle(ExcelScript.BorderLineStyle.continuous); // Right border
}
getRangeBorderTintAndShade()
Specifies a double that lightens or darkens a color for range borders. The value is between -1 (darkest) and 1 (brightest), with 0 for the original color. A null value indicates that the entire border collection doesn't have a uniform tintAndShade setting.
getRangeBorderTintAndShade(): number;Returns
number
getReadingOrder()
getRowHeight()
The height of all rows in the range. If the row heights are not uniform, null will be returned.
getRowHeight(): number;Returns
number
Examples
/**
* This script doubles the row height for every row in the active worksheet's used range.
*/
function main(workbook: ExcelScript.Workbook) {
const currentSheet = workbook.getActiveWorksheet();
const usedRange = currentSheet.getUsedRange();
// To optimize performance, get all the current row heights before setting them.
let currentHeights = Array<number>(usedRange.getRowCount());
for (let row = 0; row < currentHeights.length; row++) {
currentHeights[row] = usedRange.getRow(row).getFormat().getRowHeight();
}
// Set the new row heights.
for (let row = 0; row < currentHeights.length; row++) {
usedRange.getFormat().setRowHeight(currentHeights[row] * 2);
}
}
getShrinkToFit()
Specifies if text automatically shrinks to fit in the available column width.
getShrinkToFit(): boolean;Returns
boolean
getTextOrientation()
The text orientation of all the cells within the range. The text orientation should be an integer either from -90 to 90, or 180 for vertically-oriented text. If the orientation within a range are not uniform, then null will be returned.
getTextOrientation(): number;Returns
number
getUseStandardHeight()
Determines if the row height of the Range object equals the standard height of the sheet. Returns true if the row height of the Range object equals the standard height of the sheet. Returns null if the range contains more than one row and the rows aren't all the same height. Returns false otherwise.
getUseStandardHeight(): boolean;Returns
boolean
getUseStandardWidth()
Specifies if the column width of the Range object equals the standard width of the sheet. Returns true if the column width of the Range object equals the standard width of the sheet. Returns null if the range contains more than one column and the columns aren't all the same height. Returns false otherwise.
getUseStandardWidth(): boolean;Returns
boolean
getVerticalAlignment()
Represents the vertical alignment for the specified object. See ExcelScript.VerticalAlignment for details.
getVerticalAlignment(): VerticalAlignment;Returns
getWrapText()
Specifies if Excel wraps the text in the object. A null value indicates that the entire range doesn't have a uniform wrap setting
getWrapText(): boolean;Returns
boolean
setAutoIndent(autoIndent)
Specifies if text is automatically indented when text alignment is set to equal distribution.
setAutoIndent(autoIndent: boolean): void;Parameters
- autoIndent
-
boolean
Returns
void
setColumnWidth(columnWidth)
Specifies the width of all columns within the range.
setColumnWidth(columnWidth: number): void;Parameters
- columnWidth
-
number
Returns
void
Examples
/**
* This script inserts a new column and sets that column's width to 100 pixels wide.
*/
function main(workbook: ExcelScript.Workbook) {
const currentSheet = workbook.getActiveWorksheet();
// Insert a new column between the current B and C columns.
const bcRange = currentSheet.getRange("C:C");
const newColumn = bcRange.insert(ExcelScript.InsertShiftDirection.right);
// Set the column width of the new column to 100 pixels.
newColumn.getFormat().setColumnWidth(100);
}
setHorizontalAlignment(horizontalAlignment)
Represents the horizontal alignment for the specified object. See ExcelScript.HorizontalAlignment for details.
setHorizontalAlignment(horizontalAlignment: HorizontalAlignment): void;Parameters
- horizontalAlignment
- ExcelScript.HorizontalAlignment
Returns
void
Examples
/**
* This script centers the text in a table's header row cells.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the first table on the current worksheet.
const currentSheet = workbook.getActiveWorksheet();
const table = currentSheet.getTables()[0];
// Get the header range.
const headerRange = table.getHeaderRowRange();
// Set the horizontal text alignment to `center`.
headerRange.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
}
setIndentLevel(indentLevel)
An integer from 0 to 250 that indicates the indent level.
setIndentLevel(indentLevel: number): void;Parameters
- indentLevel
-
number
Returns
void
setRangeBorderTintAndShade(rangeBorderTintAndShade)
Specifies a double that lightens or darkens a color for range borders. The value is between -1 (darkest) and 1 (brightest), with 0 for the original color. A null value indicates that the entire border collection doesn't have a uniform tintAndShade setting.
setRangeBorderTintAndShade(rangeBorderTintAndShade: number): void;Parameters
- rangeBorderTintAndShade
-
number
Returns
void
setReadingOrder(readingOrder)
The reading order for the range.
setReadingOrder(readingOrder: ReadingOrder): void;Parameters
- readingOrder
- ExcelScript.ReadingOrder
Returns
void
setRowHeight(rowHeight)
Specifies the height of all rows in the range.
setRowHeight(rowHeight: number): void;Parameters
- rowHeight
-
number
Returns
void
Examples
/**
* This script inserts a new row and sets that row's width to 100 pixels tall.
*/
function main(workbook: ExcelScript.Workbook) {
const currentSheet = workbook.getActiveWorksheet();
// Insert a new row between the current 2 and 3 rows.
const bcRange = currentSheet.getRange("3:3");
const newRow = bcRange.insert(ExcelScript.InsertShiftDirection.down);
// Set the row height of the new row to 100 pixels.
newRow.getFormat().setRowHeight(100);
}
setShrinkToFit(shrinkToFit)
Specifies if text automatically shrinks to fit in the available column width.
setShrinkToFit(shrinkToFit: boolean): void;Parameters
- shrinkToFit
-
boolean
Returns
void
setTextOrientation(textOrientation)
The text orientation of all the cells within the range. The text orientation should be an integer either from -90 to 90, or 180 for vertically-oriented text. If the orientation within a range are not uniform, then null will be returned.
setTextOrientation(textOrientation: number): void;Parameters
- textOrientation
-
number
Returns
void
setUseStandardHeight(useStandardHeight)
Determines if the row height of the Range object equals the standard height of the sheet. Note: This property is only intended to be set to true. Setting it to false has no effect.
setUseStandardHeight(useStandardHeight: boolean): void;Parameters
- useStandardHeight
-
boolean
Returns
void
setUseStandardWidth(useStandardWidth)
Specifies if the column width of the Range object equals the standard width of the sheet. Note: This property is only intended to be set to true. Setting it to false has no effect.
setUseStandardWidth(useStandardWidth: boolean): void;Parameters
- useStandardWidth
-
boolean
Returns
void
setVerticalAlignment(verticalAlignment)
Represents the vertical alignment for the specified object. See ExcelScript.VerticalAlignment for details.
setVerticalAlignment(verticalAlignment: VerticalAlignment): void;Parameters
- verticalAlignment
- ExcelScript.VerticalAlignment
Returns
void
Examples
/**
* This script sets the vertical alignment formatting to "top"
* for every cell in the row.
*/
function main(workbook: ExcelScript.Workbook) {
// Get row 1 for the current worksheet.
const sheet = workbook.getActiveWorksheet();
const firstRow = sheet.getRange("1:1");
// Set the vertical alignment formatting on the row.
firstRow.getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.top);
}
setWrapText(wrapText)
Specifies if Excel wraps the text in the object. A null value indicates that the entire range doesn't have a uniform wrap setting
setWrapText(wrapText: boolean): void;Parameters
- wrapText
-
boolean
Returns
void
Examples
/**
* This script turns on the text wrapping for a column.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the E column in current worksheet.
const column = workbook.getActiveWorksheet().getRange("E:E");
// Set wrap text to true for the column.
column.getFormat().setWrapText(true);
}
Office Scripts
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for