Office Scripts: Automate a Weekly Excel Report With Power Automate

Office Scripts let you automate an Excel workbook with TypeScript and run that automation from Power Automate without opening Excel. This tutorial builds one complete example: a script that reads a sales table, keeps the last seven days, totals them by region, rewrites a formatted Weekly Summary sheet and returns the figures, plus a scheduled flow that runs it every Monday at 8:00 and emails the result. Copy the script, change the table name, and the report runs itself.

Who this is for and what you will build

This is for analysts, team leads and Microsoft 365 administrators who keep sales, tickets or orders in an Excel table and build the same weekly summary by hand. You need no prior Office Scripts experience, but basic JavaScript or TypeScript helps.

At the end you will have:

• A script named Weekly Sales Summary saved in your OneDrive.
• A Weekly Summary worksheet that the script clears and rewrites on every run.
• A scheduled cloud flow with three steps after the trigger: Run script, Create HTML table and Send an email (V2).

If you only need to email raw rows from a table, the Excel connector alone is enough; see Power Automate Scheduled Flow: Send a Daily Report Email from Excel. An Office Script is the better tool when the report needs calculations, grouping or formatting inside the workbook.

Requirements and licensing

Microsoft's platform limits page lists these requirements for business and education accounts:

• Excel on the web, Excel for Windows (Version 2210 or higher), or Excel for Mac.
• OneDrive for Business, with "People in your organization" sharing links enabled.
• Internet connectivity with connected experiences enabled.
• One of these subscription licenses: Office 365 Business, Office 365 Business Premium, Office 365 ProPlus, Office 365 ProPlus for Devices, Office 365 A3, Office 365 A5, Office 365 Enterprise E1, E3 or E5, or Office 365 F3.

Two details matter for this tutorial. First, running Office Scripts from Power Automate needs a business Microsoft 365 license. Enterprise E1 and F3 can use scripts in Power Automate, but they don't get the Power Automate integration inside Excel. Second, Office Scripts is still in preview for personal and family subscriptions, so this flow is a work-account scenario.

Excel Online (Business) and Office 365 Outlook are standard connectors, so no premium Power Automate plan is needed. Run script isn't available in sovereign clouds such as GCC and GCC High.

Office Scripts basics before you start

The Automate tab: Action Recorder vs Code Editor

Everything starts on the Automate tab in Excel. It offers two tools:

• Action Recorder (Record Actions) captures what you do, such as editing cells, changing formats and creating tables, and saves it as a script. It also has a Copy as code button that turns recorded steps into code without saving a whole script.
• Code Editor (New Script, then Create in Code Editor) lets you write or edit scripts, including logic the Excel interface can't record, such as if/else conditions and loops.

The recorder is a good way to learn the API for a formatting step. A report that filters by date and groups by region needs the Code Editor.

Where scripts are stored

Scripts are saved as .osts files in the /Documents/Office Scripts/ folder of your OneDrive. You can move a script to a SharePoint site so your team owns it, or share it with a workbook so anyone with access to the file can run it. Save before closing Excel, because unsaved edits only live in the browser.

The main function and TypeScript rules

Every script has a main function whose first parameter is the workbook:

function main(workbook: ExcelScript.Workbook) {
  // Your code here.
}

Extra parameters after workbook become input fields, and a return type becomes output. Office Scripts uses TypeScript 4.0.3 with a few restrictions that catch people out:

• No any type, explicit or implicit. The editor reports errors such as Explicit Any is not allowed. Use a union such as string | number | boolean for cell values.
• Array callbacks (map, filter, forEach, sort) must be arrow functions.
• eval isn't supported, and Excel, ExcelScript and console can't be used as identifiers.
• Office Scripts APIs can't be called inside Array.sort callbacks or generator functions.

Step 1: Prepare the SalesData table

Store the workbook in OneDrive for Business or a SharePoint document library. Format the data as an Excel table (Insert, then Table) with a header row and name it SalesData. The script looks columns up by header name, so the four headers must be spelled exactly as shown, but their order doesn't matter.

Sample data used throughout this tutorial:

Date (as displayed)	Stored as	Region	Rep	Amount
10/23/2026	46318	North	R-101	1,200.00
10/25/2026	46320	East	R-104	640.00
10/26/2026	46321	North	R-101	1,450.50
10/26/2026 14:24	46321.6	South	R-102	980.00
10/27/2026	46322	East	R-104	2,100.00
10/28/2026	46323	West	R-103	760.25
10/28/2026	46323	North	R-105	530.00
10/29/2026	46324	South	R-102	1,325.75
10/30/2026	46325	East	R-106	415.00
10/31/2026	46326	West	R-103	1,890.00
11/1/2026	46327	North	R-101	300.00
11/2/2026	46328	South	R-102	2,500.00

The Stored as column shows why dates need care. Excel keeps a date as a serial number of days, and a time of day as the fraction after the decimal point. When a script reads the table, it gets these numbers, not the text you see in the cell.

Step 2: Create the script in the Code Editor

1. Open the workbook in Excel.
2. Go to Automate, select New Script, then Create in Code Editor.
3. Rename the script to Weekly Sales Summary.
4. Replace the default code with the full script below and save it.

The script does five things: reads the SalesData table, works out the seven-day window, filters and totals the rows by region and by rep, rewrites the Weekly Summary sheet, and returns a typed summary object.

/**
 * Weekly Sales Summary
 * Reads the SalesData table, totals the 7 days before the report date by region,
 * rewrites the "Weekly Summary" worksheet and returns the figures to Power Automate.
 * @param reportDate Optional. The run date as yyyy-MM-dd. The report covers the
 *   7 full days before this date. Leave it blank to use today's date in UTC.
 */
function main(workbook: ExcelScript.Workbook, reportDate?: string): WeeklySummary {
  const TABLE_NAME = "SalesData";
  const SHEET_NAME = "Weekly Summary";

  // 1. Read the table.
  const table = workbook.getTable(TABLE_NAME);
  if (!table) {
    throw new Error(`Table "${TABLE_NAME}" was not found in this workbook.`);
  }

  // Look up columns by header name, so the column order doesn't matter.
  const headers = table.getHeaderRowRange().getValues()[0].map((h) => h.toString().trim());
  const dateCol = findColumn(headers, "Date");
  const regionCol = findColumn(headers, "Region");
  const repCol = findColumn(headers, "Rep");
  const amountCol = findColumn(headers, "Amount");

  const rows: (string | number | boolean)[][] =
    table.getRowCount() > 0 ? table.getRangeBetweenHeaderAndTotal().getValues() : [];

  // 2. Work out the reporting window as Excel serial day numbers.
  const runDay = toSerialDay(reportDate);
  const startDay = runDay - 7; // first day included
  const endDay = runDay - 1;   // last day included (the day before the run)

  // 3. Filter the rows and total them by region and by rep.
  const regionTotals: { [region: string]: RegionTotal } = {};
  const repTotals: { [rep: string]: number } = {};
  let salesCount = 0;
  let grandTotal = 0;

  for (const row of rows) {
    const dateValue = row[dateCol];
    const amountValue = row[amountCol];

    // Real Excel dates arrive as numbers. Skip text dates and non-numeric amounts.
    if (typeof dateValue !== "number" || typeof amountValue !== "number") {
      continue;
    }
    const day = Math.floor(dateValue); // ignore any time of day
    if (day < startDay || day > endDay) {
      continue;
    }

    const region = row[regionCol].toString().trim() || "(blank)";
    const rep = row[repCol].toString().trim() || "(blank)";

    if (!regionTotals[region]) {
      regionTotals[region] = { region: region, sales: 0, amount: 0 };
    }
    regionTotals[region].sales += 1;
    regionTotals[region].amount += amountValue;
    repTotals[rep] = (repTotals[rep] || 0) + amountValue;
    salesCount += 1;
    grandTotal += amountValue;
  }

  const regions: RegionTotal[] = Object.keys(regionTotals).map((key) => ({
    region: key,
    sales: regionTotals[key].sales,
    amount: round2(regionTotals[key].amount)
  }));
  regions.sort((a, b) => b.amount - a.amount);

  let topRep = "";
  let topRepAmount = 0;
  Object.keys(repTotals).forEach((rep) => {
    if (repTotals[rep] > topRepAmount) {
      topRep = rep;
      topRepAmount = repTotals[rep];
    }
  });

  // 4. Clear (or create) the summary sheet and write the results.
  let sheet = workbook.getWorksheet(SHEET_NAME);
  if (sheet) {
    sheet.getRange().clear();
  } else {
    sheet = workbook.addWorksheet(SHEET_NAME);
  }

  const periodStart = serialToIso(startDay);
  const periodEnd = serialToIso(endDay);

  sheet.getRange("A1").setValue("Weekly sales summary");
  sheet.getRange("A1").getFormat().getFont().setBold(true);
  sheet.getRange("A2:B2").setValues([["Period", `${periodStart} to ${periodEnd}`]]);

  const output: (string | number)[][] = [["Region", "Sales", "Amount"]];
  regions.forEach((r) => {
    output.push([r.region, r.sales, r.amount]);
  });
  output.push(["Total", salesCount, round2(grandTotal)]);

  // The block starts at A4 (row index 3, column index 0).
  const block = sheet.getRangeByIndexes(3, 0, output.length, 3);
  block.setValues(output);
  sheet.getRange("A4:C4").getFormat().getFont().setBold(true);
  sheet.getRangeByIndexes(3 + output.length - 1, 0, 1, 3).getFormat().getFont().setBold(true);
  sheet.getRangeByIndexes(4, 2, output.length - 1, 1).setNumberFormat("#,##0.00");
  block.getFormat().autofitColumns();

  // 5. Return the summary to Power Automate.
  const regionText = regions.map((r) => `${r.region} ${formatMoney(r.amount)}`).join("; ");
  const summaryText = salesCount === 0
    ? `No sales recorded from ${periodStart} to ${periodEnd}.`
    : `${salesCount} sales from ${periodStart} to ${periodEnd}, total ${formatMoney(grandTotal)}. ` +
      `By region: ${regionText}. Top rep: ${topRep}.`;

  return {
    periodStart: periodStart,
    periodEnd: periodEnd,
    salesCount: salesCount,
    grandTotal: round2(grandTotal),
    topRep: topRep,
    regions: regions,
    summaryText: summaryText
  };
}

// Returns the position of a header, or stops the script with a clear message.
function findColumn(headers: string[], name: string): number {
  const index = headers.indexOf(name);
  if (index < 0) {
    throw new Error(`Column "${name}" was not found in the table header row.`);
  }
  return index;
}

// Turns yyyy-MM-dd (or today's UTC date when blank) into an Excel serial day number.
function toSerialDay(isoDate?: string): number {
  let utcMs: number;
  if (isoDate && isoDate.trim() !== "") {
    const parts = isoDate.trim().split("-").map((p) => Number(p));
    if (parts.length !== 3 || parts.some((p) => isNaN(p))) {
      throw new Error(`reportDate must look like 2026-11-02, not "${isoDate}".`);
    }
    utcMs = Date.UTC(parts[0], parts[1] - 1, parts[2]);
  } else {
    const now = new Date();
    utcMs = Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate());
  }
  // Excel serial 25569 is 1 January 1970, where JavaScript time starts.
  return Math.round(utcMs / 86400000) + 25569;
}

// Turns an Excel serial day number back into yyyy-MM-dd text.
function serialToIso(serial: number): string {
  return new Date((serial - 25569) * 86400000).toISOString().slice(0, 10);
}

function round2(value: number): number {
  return Math.round(value * 100) / 100;
}

function formatMoney(value: number): string {
  return value.toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",");
}

interface RegionTotal {
  region: string;
  sales: number;
  amount: number;
}

interface WeeklySummary {
  periodStart: string;
  periodEnd: string;
  salesCount: number;
  grandTotal: number;
  topRep: string;
  regions: RegionTotal[];
  summaryText: string;
}

How the script handles dates and return values

Excel dates are serial numbers

getValues() returns each cell as a string, number or boolean, and a date comes back as its serial number. Microsoft's own samples convert a serial to a JavaScript Date with (serial - 25569) * 86400 * 1000, because serial 25569 is January 1, 1970. This script goes the other way: it turns the report date into a serial day and compares whole days. Math.floor drops the time of day, which is why the 10/26/2026 14:24 sale still counts as October 26, and comparing whole days avoids time zone shifts between Date objects.

Time zones still matter for one value: today. Power Automate always runs scripts in UTC, while Excel uses the local time zone of whoever opens the workbook. At 8:00 local time, any time zone more than eight hours ahead of UTC, such as eastern Australia, is still on the previous day in UTC. That is why the script accepts an optional reportDate parameter, and the flow below passes the local date explicitly.

Returning data to Power Automate

Return types follow the same rules as parameters: string, number, boolean, unknown and object, arrays of those (including nested arrays), unions of literals of one type, and objects whose properties use supported types. Interfaces must be defined in the script itself. The WeeklySummary interface meets those rules, so each property (periodStart, grandTotal, regions, summaryText and so on) becomes dynamic content in the flow. A single string would also work, but an object lets the email use individual values and turn the regions array into a table.

Step 3: Run the script in Excel and check the output

Run the script from the Automate tab. When a script with parameters runs from the ribbon or a button, Excel prompts for the values. reportDate is optional: leave it blank for today's UTC date, or enter 2026-11-02 (a Monday) to reproduce the numbers in this tutorial.

With the sample data, the report window is October 26 to November 1. Three rows fall outside it: October 23 and October 25 are too early, and November 2 is the run day itself. The Weekly Summary sheet should look like this:

Cell row	A	B	C
1	Weekly sales summary
2	Period	2026-10-26 to 2026-11-01
4	Region	Sales	Amount
5	West	2	2,650.25
6	East	2	2,515.00
7	South	2	2,305.75
8	North	3	2,280.50
9	Total	9	9,751.50

The returned summaryText is:

9 sales from 2026-10-26 to 2026-11-01, total 9,751.50. By region: West 2,650.25; East 2,515.00; South 2,305.75; North 2,280.50. Top rep: R-103.

A second run clears and rewrites the sheet instead of duplicating it. A missing table, a misspelled header or a badly formatted reportDate stops the script with a clear error rather than a wrong report.

Step 4: Build the scheduled flow

The built-in Script scheduling option in the Code Editor is temporarily disabled (existing schedules keep running), and Microsoft recommends a Power Automate flow instead. A flow also gives you the email step.

Add the Recurrence trigger

1. Sign in to Power Automate, select Create, then Scheduled cloud flow.
2. Name the flow and set it to repeat every 1 Week, then select Create.
3. Open the Recurrence trigger and set Interval to 1, Frequency to Week, choose your Time zone, set On these days to Monday, At these hours to 8 and At these minutes to 0.

Add the Run script action

1. Add an action, search for "Excel run script" and choose Run script from Excel Online (Business).
2. Set Location to OneDrive for Business (or the SharePoint site that holds the workbook) and Document Library to OneDrive (or the site's library).
3. Pick the workbook in File with the file browser instead of typing the name.
4. Choose Weekly Sales Summary in Script. The reportDate field appears because it is a script parameter.
5. In reportDate, add this expression, using the Windows time zone name that matches your Recurrence trigger, such as Eastern Standard Time or India Standard Time:

convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time', 'yyyy-MM-dd')

Turn the regions array into a table

Add Create HTML table from Data Operation. Set From to the regions dynamic content from Run script and Columns to Automatic. The column headers come from the property names: region, sales and amount. Amounts in this table are raw numbers; the formatted versions are in summaryText and in the workbook.

Send the email

1. Add Send an email (V2) from Office 365 Outlook.
2. Set To to the recipients.
3. Set Subject to text such as "Weekly sales" followed by the periodStart and periodEnd dynamic content.
4. In Body, add a short line, the summaryText dynamic content, and then the Output of Create HTML table.
5. Select Save.

To skip the email on empty weeks, add a Condition that checks whether salesCount is greater than 0, as described in the daily report tutorial linked earlier.

Step 5: Test the flow

Select Test, choose Manually, then Run Test. Approve the Excel and Outlook connections if asked. In the run history, open the Run script step to see the returned JSON, then open the workbook to confirm the Weekly Summary sheet changed. If a step fails, the run history shows which action failed and why; Power Automate Flow Failed: Read Run History and Fix Common Errors walks through reading it.

Office Scripts vs VBA

Both tools let people who aren't professional developers record and edit automation. The difference is where they run.

Area	Office Scripts	VBA macros
Excel on the web	Yes	No
Excel for Windows and Mac	Yes	Yes
Excel for iOS	No	No
Power Automate	Yes, through Run script	No connector
Language	TypeScript	VBA
What the code can reach	Only the workbook	Same access as Excel, including the desktop
Excel events	Not supported; scripts run manually, from a button or from a flow	Supported
Feature coverage	Nearly all Excel on the web scenarios	More complete, especially desktop features
Licensing	A qualifying Microsoft 365 license	Built into desktop Excel
Admin control	On or off per tenant or for one group; sharing and Power Automate use controlled separately	Allow all, none or only signed macros

For a report that has to run on a schedule against a file in OneDrive or SharePoint, Office Scripts is the only one of the two that works without someone opening Excel.

Limits to know

• Each user can make 1,600 Run script calls per day, reset at 12:00 AM UTC, and the connector allows 3 Run script calls per 10 seconds.
• Synchronous Power Automate operations time out after 120 seconds. Split long jobs into several script calls.
• Excel on the web limits a script's requests and responses to 5 MB and a range to five million cells.
• Parameters passed to Run script can total up to 30,000,000 bytes (28.6 MB).
• The Excel Online (Business) connector supports files up to 25 MB, and a file may stay locked for up to 6 minutes after the connector last used it.
• In a flow, getActiveWorksheet returns the first worksheet and Range.select throws an error, so refer to sheets and tables by name, as this script does.

Common errors and fixes

The Automate tab is missing. Confirm the license is on the supported list, allow third-party cookies in the browser for Excel on the web, install WebView2 on Windows, and make sure you aren't signed in as a guest. Then ask an admin whether Office Scripts was turned off. Tenant controls moved from the Microsoft 365 admin center (Settings, Org settings, Services, Office Scripts) to the Microsoft 365 Cloud Policy service; the three policies are "Let users automate their tasks in Excel", "Let users with access to Office Scripts share their scripts with others in the organization" and "Let users with access to Office Scripts run their scripts with Power Automate". All three are enabled by default.

"You don't have access to Office Scripts. Your organization's admin may have turned off this feature, or you don't meet the requirements." In desktop Excel this message appears when a Group Policy setting has disabled Office Scripts. The Automate tab is still visible but its options are grayed out.

The script doesn't appear in the Run script dropdown. Run script only lists scripts in the default OneDrive location. If you moved the script to SharePoint, use Run script from SharePoint library. Under Conditional Access, an unmanaged device can also produce "We can't find this script. It may have been deleted by another user."

reportDate or the result fields don't show in the flow. The flow stores the script's signature when you add the action. After changing parameters or the return type, delete the Run script action and add it again. Also check that every type follows the parameter and return rules.

"The response payload size has exceeded the limit." or timeouts. Read or write smaller ranges and split the work across several Run script calls.

429 Too many requests. The flow called Run script more than 3 times in 10 seconds. Add a delay or run the calls one after another.

BadGateway or 403 Forbidden. BadGateway appears when the workbook is read-only or saved in the ISO strict Open XML format; save it as a standard Excel Workbook. 403 means the connection account lacks write access to the file.

"Runtime error: Line X: fetch is not defined". External calls don't work from flows; use a connector action for the web request instead.

The report covers the wrong week. The script ran without reportDate and UTC was already a different day. Pass the local date with convertTimeZone as shown in Step 4.

Closing checklist

• The license is on the supported list and the Automate tab is visible.
• The workbook is in OneDrive for Business or SharePoint, and the data is an Excel table named SalesData with Date, Region, Rep and Amount headers.
• The Weekly Sales Summary script is saved and produces the expected sheet when run in Excel.
• The script is in your OneDrive, or the flow uses Run script from SharePoint library.
• The Recurrence trigger runs weekly on Monday at 8:00 in the correct time zone.
• reportDate uses convertTimeZone with the same time zone as the trigger.
• Create HTML table reads the regions array, and the email uses summaryText.
• A manual test run succeeded and the workbook shows the new summary.
• You re-add the Run script action whenever the script's parameters or return type change.

More Microsoft 365 tutorials

References

• Office Scripts in Excel (overview)
• Platform limits, requirements, and error messages for Office Scripts
• Run Office Scripts with Power Automate
• Pass data to and from scripts in Power Automate
• Get user input for scripts (type restrictions)
• TypeScript restrictions in Office Scripts
• Office Scripts file storage and ownership
• Troubleshoot Office Scripts
• Troubleshoot Office Scripts running in Power Automate
• JavaScript Date samples for Office Scripts
• Tutorial: Send weekly email reminders based on spreadsheet data
• Differences between Office Scripts and VBA macros
• Manage Office Scripts settings (Microsoft 365 admin)
• ExcelScript.Workbook interface
• ExcelScript.Table interface
• ExcelScript.Worksheet interface
• ExcelScript.Range interface
• ExcelScript.RangeFormat interface
• Excel Online (Business) connector reference
• Run a cloud flow on a schedule in Power Automate
• Use data operations in Power Automate
• Workflow expression functions reference (convertTimeZone, utcNow)

---

This guide was researched and drafted with AI assistance and checked against the vendor documentation listed in its References. Read the maintained version and related guides on EpiFive.