Scripting and Macros

use direct property assignments without setter methoods

Split values from one column into multiple destination columns (table-based).

function main( 
  workbook: ExcelScript.Workbook, 
  originColumnHeader: string = "FullName", 
  destinationColumnHeadersCsv: string = "FirstName,LastName", 
  tableName: string = "Table1", 
  delimiter: string = " " 
) { 
  const tbl = workbook.getTable(tableName); 

  const tblHeaders = tbl.getColumns().map(col => col.getName()); 

  const dataRange = tbl.getRangeBetweenHeaderAndTotal(); 
  const values = dataRange.getValues(); 
  const rowCount = values.length; 

  const originHeaderIndex = tblHeaders.indexOf(originColumnHeader); 

  const destinationHeaderIndexes = destinationColumnHeadersCsv 
    .split(",") 
    .map(colName => tblHeaders.indexOf(colName.trim())); 

  for (let row = 0; row < rowCount; row++) { 
    // Read the origin value (e.g., "John Smith"). 
    const originValue = values[row][originHeaderIndex] as string; 

    if (!originValue) { 
      continue; // nothing to split 
    } 

    const parts = originValue 
      .split(delimiter) 
      .map(p => normalize(p)) 
      .filter(p => p.length > 0); 

    if (parts.length === 0) continue; 

    const destCount = destinationHeaderIndexes.length; 

    for (let i = 0; i < Math.min(parts.length, destCount); i++) { 
      values[row][destinationHeaderIndexes[i]] = parts[i]; 
    } 

    if (parts.length > destCount) { 
      const overflow = parts.slice(destCount).join(" "); 
      const lastDestIdx = destinationHeaderIndexes[destCount - 1]; 

      const existing = normalize(String(values[row][lastDestIdx] ?? "")); 
      values[row][lastDestIdx] = existing ? `${existing} ${overflow}` : overflow; 
    } 
  } 

  dataRange.setValues(values); 
} 

function normalize(input: string): string { 
  return input 
    .replace(/\u00A0/g, " ") 
    .replace(/\s+/g, " ") 
    .trim(); 
} 

This script is a simple column split (Text-to-Columns style) - Range/UsedRange version

function main( 
  workbook: ExcelScript.Workbook, 
  originColumnHeader: string = "FullName", 
  destinationColumnHeadersCsv: string = "FirstName,LastName", 
  delimiter: string = " ", 
  sheetName?: string, 
  headerRowNumber: number = 1 
) { 
  const worksheet = getWorksheet(workbook, sheetName); 

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

  const values = usedRange.getValues() as (string | number | boolean)[][]; 
  const rowCount = values.length; 
  const colCount = values[0]?.length ?? 0; 
  if (rowCount === 0 || colCount === 0) return; 

  const headerRowIndex = Math.max(0, headerRowNumber - 1); 
  if (headerRowIndex >= rowCount) { 
    throw new Error("headerRowNumber is outside the used range."); 
  } 

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

  const originHeaderIndex = headers.indexOf(originColumnHeader.trim()); 
  if (originHeaderIndex < 0) { 
    throw new Error(`Origin column not found in header row: "${originColumnHeader}"`); 
  } 

  const destinationHeaderIndexes = getDestinationColumnIndexesOrThrow( 
    headers, 
    destinationColumnHeadersCsv 
  ); 

  for (let rowIndex = headerRowIndex + 1; rowIndex < rowCount; rowIndex++) { 
    const originValueRaw = values[rowIndex][originHeaderIndex]; 
    const originValue = normalize(String(originValueRaw ?? "")); 

    if (!originValue) { 
      for (const destIdx of destinationHeaderIndexes) { 
        values[rowIndex][destIdx] = ""; 
      } 
      continue; 
    } 

    const parts = originValue 
      .split(delimiter) 
      .map(p => normalize(p)) 
      .filter(p => p.length > 0); 

    if (parts.length === 0) continue; 

    const destCount = destinationHeaderIndexes.length; 

    for (let i = 0; i < Math.min(parts.length, destCount); i++) { 
      values[rowIndex][destinationHeaderIndexes[i]] = parts[i]; 
    } 

    if (parts.length > destCount) { 
      const overflow = parts.slice(destCount).join(" "); 
      const lastDestIdx = destinationHeaderIndexes[destCount - 1]; 

      const existing = normalize(String(values[rowIndex][lastDestIdx] ?? "")); 
      values[rowIndex][lastDestIdx] = existing ? `${existing} ${overflow}` : overflow; 
    } 
  } 

  usedRange.setValues(values); 
  usedRange.getFormat().autofitColumns(); 
} 

function getWorksheet(workbook: ExcelScript.Workbook, sheetName?: string): ExcelScript.Worksheet { 
  if (sheetName && sheetName.trim()) { 
    const ws = workbook.getWorksheet(sheetName.trim()); 
    if (!ws) throw new Error(`Worksheet not found: "${sheetName}"`); 
    return ws; 
  } 

  const active = workbook.getActiveWorksheet(); 
  if (active) return active; 

  const all = workbook.getWorksheets(); 
  if (all.length === 0) throw new Error("No worksheets found in the workbook."); 
  return all[0]; 
} 

function getDestinationColumnIndexesOrThrow(headers: string[], destinationColumnHeadersCsv: string): number[] { 
  const destinationHeaderNames = destinationColumnHeadersCsv 
    .split(",") 
    .map(h => h.trim()); 

  if (destinationHeaderNames.length === 0) { 
    throw new Error("destinationColumnHeadersCsv must contain at least one destination column name."); 
  } 

  const missing: string[] = []; 
  const indexes = destinationHeaderNames.map(name => { 
    const idx = headers.indexOf(name); 
    if (idx < 0) missing.push(name); 
    return idx; 
  }); 

  if (missing.length > 0) { 
    throw new Error( 
      `Destination column(s) not found in header row: ${missing.map(m => `"${m}"`).join(", ")}` 
    ); 
  } 

  return indexes; 
}