Store the workbook in SharePoint or OneDrive

Put the controlled workbook in a document library that Power Automate can access.

Example:

• SharePoint site → Finance Reporting
• Library → Shared Documents
• Folder → Management Reports
• Workbook → MonthlyManagementReport.xlsx

Prepare a dedicated Report sheet

Create a worksheet that represents the final output management should receive.

A simple layout might contain:

• Report title
• Reporting date
• Headline KPIs
• Monthly trend
• Variance analysis
• Comments or exceptions

This allows the automation to format one known reporting surface rather than manipulating arbitrary worksheets.

Create an Office Script

Open the workbook in Excel for the web and create an Office Script that prepares the report.

function main(workbook: ExcelScript.Workbook) {

    const reportSheet = workbook.getWorksheet("Report");

    const usedRange = reportSheet.getUsedRange();

    if (usedRange) {
        usedRange.getFormat().autofitColumns();
    }

    reportSheet.getRange("B2").setValue(
        new Date().toISOString()
    );

    reportSheet.getRange("B2").setNumberFormat(
        "dd mmmm yyyy"
    );

    reportSheet.getRange("A1:H1")
        .getFormat()
        .getFont()
        .setBold(true);

    return {
        status: "Prepared",
        preparedAt: new Date().toISOString()
    };
}

This example:

• Finds the Report worksheet.
• Autofits the used columns.
• Writes the preparation date to B2.
• Applies simple formatting.
• Returns a status to Power Automate.

Create a scheduled cloud flow

In Power Automate create a Scheduled cloud flow.

Example monthly schedule:

• Frequency → Month
• Interval → 1
• Day → 1
• Time → 07:00

Add the Excel Online Run script action

Add the Excel Online (Business) action Run script.

Configure:

• Location → SharePoint Site or OneDrive for Business
• Document Library → your reporting library
• File → MonthlyManagementReport.xlsx
• Script → Prepare Monthly Report

Select the workbook using the file picker rather than manually typing the filename.

Understand the Excel refresh limitation

This is an important architectural point.

Office Scripts called through Power Automate currently do not refresh most Excel data connections. Microsoft documents that refreshAllDataConnections() only refreshes Power BI sources when the script is executed by Power Automate.

Safer options include:

• Update the workbook data upstream before the reporting flow.
• Populate an Excel Table directly using a supported connector.
• Move recurring transformation into SQL, Power BI or Microsoft Fabric.
• Use an architecture where the workbook consumes already-prepared data.

Build a dynamic report filename

Add a Compose action and use:

concat(
    'Management-Report-',
    formatDateTime(
        utcNow(),
        'yyyy-MM-dd'
    ),
    '.xlsx'
)

An example output is:

Management-Report-2026-08-25.xlsx

Dynamic filenames make the reporting archive easier to navigate and audit.

Retrieve the source workbook content

Add the SharePoint or OneDrive action Get file content.

Select the controlled source workbook.

This returns the workbook binary content so Power Automate can create the dated report copy.

Create the archived report copy

Add Create file.

Example configuration:

• Folder → /Management Reports/Archive
• File Name → output from your filename Compose action
• File Content → output from Get file content

This leaves the controlled source workbook in place and creates a dated archive version.

Retrieve the newly created report

Add another Get file content action that points to the archived report created by the previous step.

This content becomes the email attachment.

Send the report by email

Add Send an email (V2).

Example subject:

Monthly Management Report - @{formatDateTime(utcNow(),'MMMM yyyy')}

Attach:

• Attachment Name → dynamic report filename
• Attachment Content → archived report file content

Add failure notification

Add a separate notification action configured with Run after so it executes if a key preparation or file action:

• has failed
• has timed out

The notification should tell the report owner:

• which flow failed
• when it failed
• which reporting period was affected
• where to review the flow run

Test the complete process

Run the flow manually before relying on the schedule.

Confirm:

• The correct workbook is selected.
• The Office Script finishes successfully.
• The report date is updated.
• The archived filename is correct.
• The archive copy opens successfully.
• The email contains the correct file or link.
• The recipient list is correct.