Office Scripts + Power Automate (Excel Automation in the Cloud)

💡 Key concepts

• Office Scripts: Excel on the web automation (Automate tab). The Action Recorder writes the code for you, and you edit it in the Code Editor. Saved to OneDrive/SharePoint, so it works with flows (VBA doesn't run in cloud flows).
• Script with parameters and a return value:

  function main(workbook: ExcelScript.Workbook, threshold: number): string[] {
    const table = workbook.getTable("EVMSnapshot");
    const values = table.getRangeBetweenHeaderAndTotal().getValues();
    const headers = table.getHeaderRowRange().getValues()[0] as string[];
    const iProg = headers.indexOf("ProgramID");
    const iCPI = headers.indexOf("CPI");
    const flagged: string[] = [];
    values.forEach(r => { if ((r[iCPI] as number) < threshold) flagged.push(r[iProg] as string); });
    return flagged;
  }
  

• Run script action in Power Automate passes threshold and receives flagged as dynamic content.
• Tool choice:

  Need	Best tool
  Reshape and clean incoming data	Power Query
  Format, rearrange, or stamp a workbook in the cloud on a schedule	Office Scripts + Power Automate
  Desktop-only legacy macros	VBA (not callable from cloud flows)
  Heavy transforms, Word/Excel document generation, complex logic	Python (Phase 4)

🛠 Hands-on exercise: Format-and-Stamp Script

Data: An Excel workbook in OneDrive with Table EVMSnapshot (ProgramID, ProgramName, CPI, SPI, CV, SV, EAC, VAC, PeriodEnd).

1. Record a script that applies number formats, freezes the header row, applies RAG fill to CPI/SPI, and stamps "Generated: " in a header cell.
2. Edit the code so thresholds are parameters.
3. Build a flow: scheduled monthly → Run script → email the workbook link to yourself.

📦 Mini-project: Flagged Programs Digest

Use the parameterized script above (returns an array of flagged programs) in a flow that builds an HTML table of flagged programs (Select + Create HTML table) and emails a digest to a distribution list only when at least one program is flagged (length(...) > 0).