Office Scripts + Power Automate quick tour

1. Requirements

• A Microsoft 365 business or education account (Office Scripts aren't available on every plan — check that you see the Automate tab).
• The workbook saved in OneDrive for Business or SharePoint.
• Works in Excel on the web, and in recent Excel desktop versions for business accounts.

If you only have a personal/home account or files on your own disk, use VBA (Module 2) or Python + Task Scheduler (Lesson 1) instead.

2. Office Scripts vs VBA

3. Record your first script

Automate tab → Record Actions → format the header, set widths → Stop. Excel writes the script for you — the same idea as the macro recorder. Click Edit to see the code.

4. Anatomy of a script

Every script has one main function. Excel passes in the workbook:

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Data");
  sheet.getRange("A1").setValue("Updated by Office Script");
}

Pattern: get something (getWorksheet, getRange, getValues) and set something (setValue, setValues, setColor). Very similar to Apps Script.

5. A useful script: format the sheet and return the total

function main(workbook: ExcelScript.Workbook): number {
  const sheet = workbook.getWorksheet("Data");
  if (!sheet) throw new Error("Sheet 'Data' not found");
  const used = sheet.getUsedRange();
  if (!used) return 0;                       // empty sheet
  const values = used.getValues();

  // Total of column E (index 4), skipping the header row
  let total = 0;
  for (let i = 1; i < values.length; i++) {
    total += Number(values[i][4]) || 0;
  }

  // Header style
  const header = used.getRow(0);
  header.getFormat().getFont().setBold(true);
  header.getFormat().getFont().setColor("#FFFFFF");
  header.getFormat().getFill().setColor("#1F4E78");

  used.getFormat().autofitColumns();
  sheet.getFreezePanes().freezeRows(1);

  return total;
}

• getValues() returns a 2D array, exactly like Apps Script.
• : number after main(...) means the script returns a number. Power Automate can use that value in the next step.
• The two if checks handle a missing sheet or an empty sheet, so the flow gets a clear message instead of a crash.
• Click Run in the Code Editor to test it.

6. Schedule it with Power Automate

At make.powerautomate.com → Create → Scheduled cloud flow:

1. Recurrence — Repeat every 1 Week, on Monday, at hour 9, time zone (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi.
2. Excel Online (Business) → Run script — choose Location (OneDrive for Business), Document Library, the File, and the Script.
3. Office 365 Outlook → Send an email (V2) — To: owner; Subject: Weekly sales total; Body: "Total sales: " and pick result from the dynamic content of the Run script step.
4. Save → Test → Manually to try it now.

The workbook doesn't need to be open and no computer needs to be on.

7. Passing values into a script

Add parameters after workbook, and Power Automate shows a box for each:

function main(workbook: ExcelScript.Workbook, minAmount: number): string {
  const used = workbook.getWorksheet("Data")?.getUsedRange();
  if (!used) return "No data";
  const values = used.getValues();
  const big = values.slice(1).filter(r => Number(r[4]) >= minAmount);
  return `${big.length} sales of ${minAmount} or more`;
}

8. Other useful triggers

Instead of Recurrence, a flow can start when a file is added to a SharePoint folder, when an email with an attachment arrives, when a Microsoft Form is submitted, or from a button in the Power Automate mobile app. The Run script step stays the same.

9. Limits to know

Scripts launched from Power Automate have a short time limit (a couple of minutes) and a daily limit on runs, so keep them focused — format, summarise, return a value. Heavy data work belongs in Power Query or Python.

Common mistakes

Workbook saved on the local disk (Run script can't see it). Wrong time zone in Recurrence. Writing very long loops with getValue per cell instead of one getValues (slow, same as Apps Script). Expecting the script to send email itself — Power Automate does that.