Automating Tasks

Building a scheduled flow in Power Automate:

1. Go to make.powerautomate.com
2. Create → Scheduled cloud flow
3. Set the schedule: start date, repeat interval (hourly / daily / weekly / monthly)
4. Add a step: + New step → search "Excel Online (Business)"
5. Choose action: "Run script"
6. Configure the action:
   Location:          SharePoint (or OneDrive for Business)
   Document Library:  Documents (or the library containing the file)
   File:              Browse to your workbook (.xlsx)
   Script:            Select from your script library

That is the complete flow for a simple scheduled run.
No parameters needed if the script reads data from the workbook directly.

Example use cases:
  ✓ Format and timestamp a weekly data dump every Monday 6am
  ✓ Calculate a monthly summary and write it to a Summary sheet
  ✓ Clear a staging table and flag it as ready for new data
  ✓ Archive the current week's rows to a History sheet and clear the active table

Declaring parameters in the script:

// Parameters are declared as additional arguments in the main function
// after the workbook parameter. Power Automate detects them automatically.

function main(
  workbook:   ExcelScript.Workbook,
  reportDate: string,    // passed in from the flow
  region:     string,    // passed in from the flow
  targetRows: number     // passed in from the flow
) {
  const sheet = workbook.getActiveSheet();

  // Use the parameters directly
  sheet.getRange("A1").setValue("Report Date: " + reportDate);
  sheet.getRange("B1").setValue("Region: " + region);

  console.log("Processing " + targetRows + " rows for " + region);
}

In Power Automate:
  After selecting the script, Power Automate detects the parameters
  and shows input fields for each one automatically.
  You can type a static value or use dynamic content from earlier
  in the flow (e.g. the form submission date, the email sender name).

Supported parameter types:
  string  → Text input in the flow
  number  → Number input in the flow
  boolean → Yes/No toggle in the flow
  string[] → Array of strings (passed as JSON)

Returning a value from a script:

// Change the function return type from void to the type you want to return

function main(workbook: ExcelScript.Workbook): string {
  const sheet     = workbook.getActiveSheet();
  const usedRange = sheet.getUsedRange();
  const rowCount  = usedRange.getRowCount() - 1; // subtract header row

  // Process the data...
  let urgentCount = 0;
  const values = usedRange.getValues();
  for (let i = 1; i <= rowCount; i++) {
    if (values[i][4] === "Overdue") urgentCount++;
  }

  // Return a summary string
  return urgentCount + " overdue rows found out of " + rowCount + " total.";
}

Returning an object (use JSON.stringify):

function main(workbook: ExcelScript.Workbook): string {
  const sheet  = workbook.getActiveSheet();
  const result = {
    rowCount:     100,
    urgentCount:  12,
    reportDate:   "2025-03-10",
    status:       "complete"
  };
  return JSON.stringify(result);
  // Power Automate receives this as a JSON string
  // Use the "Parse JSON" action in the flow to extract individual fields
}

In Power Automate, the return value appears as:
  result → body → result (in the "Run script" action output)
  You can use this value in Send an email, Post a Teams message, etc.

Pattern 1 — Daily report formatter (Scheduled):
  Trigger:  Recurrence — every weekday at 06:30
  Action:   Run script "Format Daily Report" on Sales.xlsx
  No parameters, no return value needed
  The script formats the sheet, adds the date stamp, auto-fits columns

Pattern 2 — Form to spreadsheet (Event-triggered):
  Trigger:  Microsoft Forms — "When a new response is submitted"
  Action 1: Get response details (Forms connector)
  Action 2: Run script "Add Form Response" on Tracker.xlsx
            Parameters: name (string), email (string), score (number)
  Script appends a new row to the Responses table with the form data

  Script that receives and uses the parameters:
  function main(
    workbook: ExcelScript.Workbook,
    name:  string,
    email: string,
    score: number
  ) {
    const sheet = workbook.getActiveSheet();
    const table = sheet.getTable("Responses");
    const today = new Date().toISOString().split("T")[0]; // "2025-03-10"
    table.addRow(-1, [today, name, email, score]);
  }

Pattern 3 — Exception alert (Scheduled + conditional email):
  Trigger:  Recurrence — every Monday at 08:00
  Action 1: Run script "Count Overdue" on Orders.xlsx (returns a string)
  Action 2: Condition — if script result contains "0 overdue"
            Yes: do nothing (terminate)
            No: Send email to manager with the script result in the body