5. Power Automate: Running a Script on a Schedule

A flow in Power Automate is a trigger followed by a chain of actions. To make a script run on its own, say every Monday at 9:00 AM:

1. Save the script (it's stored in OneDrive as an .osts file) and keep the workbook in OneDrive or SharePoint;
2. in Power Automate, create a scheduled cloud flow with the Recurrence trigger: frequency, day of the week, time and time zone;
3. add the Excel Online (Business) › Run script action: choose the location, document library, file and script, and fill in the parameter fields;
4. use the script's result (whatever return sent back) in later steps, for example in an Outlook send-email action.

Other triggers include "when a file is created in a folder," "when an email arrives," "when a Microsoft Forms response is submitted," and a manual button. You can also run a script by hand, from a button on the sheet or from the Automate tab.

function main(workbook: ExcelScript.Workbook): string {
  const table = workbook.getTable("Orders");
  const values = table.getColumnByName("Status").getRangeBetweenHeaderAndTotal().getValues();
  const open = values.filter(r => r[0] === "Open").length;
  return "Open orders: " + open;
}

The flow: "Recurrence (Mon, 9:00 AM)" → "Run script" → "Send an email" with the text from result.

Limits to keep in mind: a single run has an execution time limit, so split heavy processing into parts; the flow runs as its creator and with that person's access to the file; the workbook must be in the cloud, not on a computer's disk; and if an administrator has turned off Office Scripts in your organization, the run won't work.

The simulator doesn't run scripts. Run the code from the lesson in Excel on the web (Automate tab › Code Editor). Here you learn to read code and check yourself with formulas and a quiz.