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

This script remove duplicates from a sheet based on one or more key columns.

function main( 
  workbook: ExcelScript.Workbook, 
  sheetName?: string, 
  headerRowNumber?: number, 
  keyColumnHeadersCsv: string = "Id" 
) { 
  const worksheet = sheetName 
    ? workbook.getWorksheet(sheetName) 
    : workbook.getActiveWorksheet(); 

  const usedRange = worksheet.getUsedRange(); 
  if (!usedRange) return; 

  const allValues = usedRange.getValues() as (string | number | boolean)[][]; 
  const totalRowCount = allValues.length; 
  const totalColCount = allValues[0].length; 

  const headerRowIndex = Math.max(0, headerRowNumber - 1); 

  if (headerRowIndex >= totalRowCount) { 
    throw new Error("headerRowNumber is outside the used range."); 
  } 

  const headerValues = allValues[headerRowIndex].map(v => String(v ?? "").trim()); 

  const keyHeaders = keyColumnHeadersCsv 
    .split(",") 
    .map(h => h.trim()); 

  if (keyHeaders.length === 0) { 
    throw new Error("Please provide at least one header name in keyColumnHeadersCsv."); 
  } 

  const keyColumnIndexes = keyHeaders.map(headerName => { 
    const colIndex = headerValues.indexOf(headerName); 
    if (colIndex < 0) { 
      throw new Error(`Key column not found in header row: "${headerName}"`); 
    } 
    return colIndex; 
  }); 

  const seenKeys = new Set<string>(); 

  const outputValues: (string | number | boolean)[][] = []; 
  outputValues.push(allValues[headerRowIndex]); 

  const firstDataRowIndex = headerRowIndex + 1; 

  for (let rowIndex = firstDataRowIndex; rowIndex < totalRowCount; rowIndex++) { 
    const rowValues = allValues[rowIndex]; 

    const uniqueKey = keyColumnIndexes 
      .map(colIndex => String(rowValues[colIndex] ?? "").trim()) 
      .join("||"); 

    if (isEmptyKey(uniqueKey, keyColumnIndexes.length)) { 
      outputValues.push(rowValues); 
      continue; 
    } 

    if (!seenKeys.has(uniqueKey)) { 
      seenKeys.add(uniqueKey); 
      outputValues.push(rowValues); 
    } 
  } 

  usedRange.clear(ExcelScript.ClearApplyTo.contents); 

  const startRow = usedRange.getRowIndex(); 
  const startCol = usedRange.getColumnIndex(); 

  const outputRange = worksheet.getRangeByIndexes( 
    startRow + headerRowIndex, 
    startCol, 
    outputValues.length, 
    totalColCount 
  ); 

  outputRange.setValues(outputValues); 
} 

function isEmptyKey(uniqueKey: string, keyColumnCount: number): boolean { 
  if (keyColumnCount <= 1) return uniqueKey.trim() === ""; 
  return uniqueKey === "||".repeat(keyColumnCount - 1); 
}

This script also remove duplicates with more advanced options

function main( 
  workbook: ExcelScript.Workbook, 
  sheetName: string = "", 
  headerRowNumber: number = 1, 

  // Key columns define what counts as "duplicate" 
  keyColumnHeadersCsv: string = "Id", // e.g. "EmployeeId,Region" 

  // Choose how to decide which duplicate row to keep 
  keepStrategy: "byRowOrder" | "byDate" = "byRowOrder", 

  // Only applies to keepStrategy="byRowOrder" 
  scanDirection: "topToBottom" | "bottomToTop" = "topToBottom", 

  // Only applies to keepStrategy="byDate" 
  dateColumnHeader: string = "" // e.g. "ModifiedOn" or "UpdatedAt" 
) { 
  const worksheet = sheetName 
    ? workbook.getWorksheet(sheetName) 
    : workbook.getActiveWorksheet(); 

  const usedRange = worksheet.getUsedRange(); 
  if (!usedRange) return; 

  const allValues = usedRange.getValues(); 
  const totalRowCount = allValues.length; 
  const totalColCount = allValues[0].length; 

  const headerRowIndex = Math.max(0, headerRowNumber - 1); 

  const headerValues = allValues[headerRowIndex].map(v => String(v ?? "").trim()); 

  const keyHeaders = keyColumnHeadersCsv 
    .split(",") 
    .map(h => h.trim()); 

  if (keyHeaders.length === 0) { 
    throw new Error("Please provide at least one key column header (keyColumnHeadersCsv)."); 
  } 

  const keyColumnIndexes: number[] = keyHeaders.map(headerName => { 
    const colIndex = headerValues.indexOf(headerName); 
    if (colIndex < 0) throw new Error(`Key column not found in header row: "${headerName}"`); 
    return colIndex; 
  }); 

  let dateColumnIndex = -1; 
  if (keepStrategy === "byDate") { 
    if (!dateColumnHeader) { 
      throw new Error(`keepStrategy="byDate" requires a dateColumnHeader.`); 
    } 
    dateColumnIndex = headerValues.indexOf(dateColumnHeader); 
    if (dateColumnIndex < 0) { 
      throw new Error(`Date column not found in header row: "${dateColumnHeader}"`); 
    } 
  } 

  const keptRowIndexByKey = new Map<string, number>(); 

  const firstDataRowIndex = headerRowIndex + 1; 
  const lastDataRowIndex = totalRowCount - 1; 

  const rowScanOrder: number[] = []; 
  if (scanDirection === "topToBottom") { 
    for (let rowIndex = firstDataRowIndex; rowIndex <= lastDataRowIndex; rowIndex++) { 
      rowScanOrder.push(rowIndex); 
    } 
  } else { 
    for (let rowIndex = lastDataRowIndex; rowIndex >= firstDataRowIndex; rowIndex--) { 
      rowScanOrder.push(rowIndex); 
    } 
  } 

  for (const rowIndex of rowScanOrder) { 
    const rowValues = allValues[rowIndex]; 

    const uniqueKey = keyColumnIndexes 
      .map(colIndex => String(rowValues[colIndex] ?? "").trim()) 
      .join("||"); 

    if (!uniqueKey || uniqueKey === "||".repeat(Math.max(0, keyColumnIndexes.length - 1))) { 
      continue; 
    } 

    if (!keptRowIndexByKey.has(uniqueKey)) { 
      keptRowIndexByKey.set(uniqueKey, rowIndex); 
      continue; 
    } 

    if (keepStrategy === "byRowOrder") { 
      continue; 
    } 

    const currentlyKeptRowIndex = keptRowIndexByKey.get(uniqueKey)!; 

    const keptTime = toComparableTime(allValues[currentlyKeptRowIndex][dateColumnIndex] as any); 
    const candidateTime = toComparableTime(rowValues[dateColumnIndex] as any); 

    if (candidateTime > keptTime) { 
      keptRowIndexByKey.set(uniqueKey, rowIndex); 
    } 
  } 

  const keptRowIndicesSorted = Array.from(keptRowIndexByKey.values()).sort((a, b) => a - b); 

  const outputValues: (string | number | boolean)[][] = []; 
  outputValues.push(allValues[headerRowIndex] as (string | number | boolean)[]); 
  for (const rowIndex of keptRowIndicesSorted) { 
    outputValues.push(allValues[rowIndex] as (string | number | boolean)[]); 
  } 

  usedRange.clear(ExcelScript.ClearApplyTo.contents); 

  const outputRange = worksheet.getRangeByIndexes( 
    headerRowIndex, // write starting at header row 
    0, 
    outputValues.length, 
    totalColCount 
  ); 
  outputRange.setValues(outputValues); 
} 

function toComparableTime(value: string | number | boolean): number { 
  if (typeof value === "number" && Number.isFinite(value)) { 
    return value; // Excel serial date or numeric timestamp-like value 
  } 

  if (typeof value === "string") { 
    const parsed = Date.parse(value); 
    return Number.isFinite(parsed) ? parsed : Number.NEGATIVE_INFINITY; 
  } 

  return Number.NEGATIVE_INFINITY; 
}