Scripting and Macros

Office Scripts

link - learn.microsoft.com/en-us/office/dev/scripts/resources/samples/samples-overview 
link - learn.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset 
link - learn.microsoft.com/en-us/office/dev/scripts/resources/samples/external-fetch-calls 
link - learn.microsoft.com/en-us/office/dev/scripts/resources/samples/filter-table-get-visible-range 
link - learn.microsoft.com/en-us/office/dev/scripts/resources/samples/get-table-data 

var _range = context.workbook.names.getItem("myNamedRange").getRange(); 
var _rangeValues = _range.getRow(1).getBoundingRect(_range.getLastCell()); 
var _myCol = _range.getColumn(0); 

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

getCell(0,0) = A1 
getRange 
getWorksheet 
getAddress 
getValue - only 1 value, doesn't return an array from a larger range  
getValues - returns an array 
ExcelScript.Range ? 
getRange.getSurroundingRegion 

let myArray = wshName.getValues() 
myArray.shift() // removes the header / first row 

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