Cells & Ranges


Get the value in cell A1.

let range = selectedSheet.getRange("A1"); 
console.log(range.getValue());

Get the current active cell 
let cell = workbook.getActiveCell();
console.log(`The current cell's value is ${cell.getValue()}`);

This script applies some formatting to the cell to its right and the cell above.

function main(workbook: ExcelScript.Workbook) { 
  let activeCell = workbook.getActiveCell();
  console.log(`The active cell's address is: ${activeCell.getAddress()}`);

  let rightCell = activeCell.getOffsetRange(0,1);
  rightCell.setValue("Right cell");
  console.log(`The right cell's address is: ${rightCell.getAddress()}`);
  rightCell.getFormat().getFont().setColor("Magenta");
  rightCell.getFormat().getFill().setColor("Cyan");

  let aboveCell = activeCell.getOffsetRange(-1, 0);
  aboveCell.setValue("Above cell");
  console.log(`The above cell's address is: ${aboveCell.getAddress()}`);
  aboveCell.getFormat().getFont().setColor("White");
  aboveCell.getFormat().getFill().setColor("Black");
}

This script copies the formatting from the active cell to the 9 neighbouring cells.
Note that this script only works when the active cell isn't on an edge of the worksheet.

function main(workbook: ExcelScript.Workbook) { 
  let activeCell = workbook.getActiveCell();
  let cornerCell = activeCell.getOffsetRange(-1,-1);
  let surroundingRange = cornerCell.getResizedRange(2, 2)
  surroundingRange.copyFrom(activeCell, ExcelScript.RangeCopyType.formats);
}

This script clears the formatting from the selected range, and loops through every cell changing the fill color to a random color.

function main(workbook: ExcelScript.Workbook) { 
  let range = workbook.getSelectedRange();
  let rows = range.getRowCount();
  let cols = range.getColumnCount();
  range.clear(ExcelScript.ClearApplyTo.formats);

  for (let row = 0; row < rows; row++) {
    for (let col = 0; col < cols; col++) {
      let colorString = `#${Math.random().toString(16).substr(-6)}`;
      range.getCell(row, col).getFormat().getFill().setColor(colorString);
    }
  }
}

This script gets the current used range and shades any blank cells with a yellow background.

function main(workbook: ExcelScript.Workbook) { 
    let range = workbook.getActiveWorksheet().getUsedRange();
    let blankCells = range.getSpecialCells(ExcelScript.SpecialCellType.blanks);
    blankCells.getFormat().getFill().setColor("yellow");
}

This script removes hyperlinks

function main(workbook: ExcelScript.Workbook, sheetName: string = 'Sheet1') {  
  // Get the active worksheet.
  let sheet = workbook.getWorksheet(sheetName);

  // Get the used range to operate on.
  // For large ranges (over 10000 entries), consider splitting the operation into batches for performance.
  const targetRange = sheet.getUsedRange(true);
  console.log(`Target Range to clear hyperlinks from: ${targetRange.getAddress()}`);

  const rowCount = targetRange.getRowCount();
  const colCount = targetRange.getColumnCount();
  console.log(`Searching for hyperlinks in ${targetRange.getAddress()} which contains ${(rowCount * colCount)} cells`);

  // Go through each individual cell looking for a hyperlink.
  // This allows us to limit the formatting changes to only the cells with hyperlink formatting.
  let clearedCount = 0;
  for (let i = 0; i < rowCount; i++) {
    for (let j = 0; j < colCount; j++) {
      const cell = targetRange.getCell(i, j);
      const hyperlink = cell.getHyperlink();
      if (hyperlink) {
        cell.clear(ExcelScript.ClearApplyTo.hyperlinks);
        cell.getFormat().getFont().setUnderline(ExcelScript.RangeUnderlineStyle.none);
        cell.getFormat().getFont().setColor('Black');
        clearedCount++;
      }
    }
  }
  console.log(`Done. Cleared hyperlinks from ${clearedCount} cells`);
}


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext