ExcelScript.RangeFormat interface

Package:

ExcelScript

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

adjustIndent(amount)	Adjusts the indentation of the range formatting. The indent value ranges from 0 to 250 and is measured in characters.
autofitColumns()	Changes the width of the columns of the current range to achieve the best fit, based on the current data in the columns.
autofitRows()	Changes the height of the rows of the current range to achieve the best fit, based on the current data in the columns.
getAutoIndent()	Specifies if text is automatically indented when text alignment is set to equal distribution.
getBorders()	Collection of border objects that apply to the overall range.
getColumnWidth()	Specifies the width of all columns within the range. If the column widths are not uniform, null will be returned.
getFill()	Returns the fill object defined on the overall range.
getFont()	Returns the font object defined on the overall range.
getHorizontalAlignment()	Represents the horizontal alignment for the specified object. See ExcelScript.HorizontalAlignment for details.
getIndentLevel()	An integer from 0 to 250 that indicates the indent level.
getProtection()	Returns the format protection object for a range.
getRangeBorder(index)	Gets a border object using its name.
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.
getReadingOrder()	The reading order for the range.
getRowHeight()	The height of all rows in the range. If the row heights are not uniform, null will be returned.
getShrinkToFit()	Specifies if text automatically shrinks to fit in the available column width.
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.
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.
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.
getVerticalAlignment()	Represents the vertical alignment for the specified object. See ExcelScript.VerticalAlignment for details.
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
setAutoIndent(autoIndent)	Specifies if text is automatically indented when text alignment is set to equal distribution.
setColumnWidth(columnWidth)	Specifies the width of all columns within the range.
setHorizontalAlignment(horizontalAlignment)	Represents the horizontal alignment for the specified object. See ExcelScript.HorizontalAlignment for details.
setIndentLevel(indentLevel)	An integer from 0 to 250 that indicates the indent level.
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.
setReadingOrder(readingOrder)	The reading order for the range.
setRowHeight(rowHeight)	Specifies the height of all rows in the range.
setShrinkToFit(shrinkToFit)	Specifies if text automatically shrinks to fit in the available column width.
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.
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.
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.
setVerticalAlignment(verticalAlignment)	Represents the vertical alignment for the specified object. See ExcelScript.VerticalAlignment for details.
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

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

ExcelScript.RangeBorder[]

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

ExcelScript.RangeFill

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

ExcelScript.RangeFont

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

ExcelScript.HorizontalAlignment

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

ExcelScript.FormatProtection

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

ExcelScript.RangeBorder

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()

The reading order for the range.

getReadingOrder(): ReadingOrder;

Returns

ExcelScript.ReadingOrder

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

ExcelScript.VerticalAlignment

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