4. Script Parameters and Return Values

A script becomes reusable when values are not hard-coded but arrive as parameters. After workbook in main you can declare your own arguments of type string, number or boolean. A parameter with a ? is optional. When you run the script from Power Automate, the Run script action shows a separate field for each parameter.

A script can also return a result with return: a number, text, an array or an object. The flow receives it as an output value and can, for example, put it into an email.

function main(workbook: ExcelScript.Workbook, threshold: number, label: string): number {
  const sheet = workbook.getWorksheet("Clients");
  const values = sheet.getRange("B2:B7").getValues();
  let count = 0;
  const marks = values.map(r => {
    const hit = (r[0] as number) >= threshold;
    if (hit) count++;
    return [hit ? label : "-"];
  });
  sheet.getRange("C2:C7").setValues(marks);
  return count;               // goes back to the Power Automate flow
}

The flow runs the script with threshold = 50000 and label = VIP. Repeat this on the sheet: put the parameters in cells and have the formula refer to them. It is the same principle as "don't hard-code numbers in a formula."

When a script runs from a flow, the file has no "active" user, so refer to sheets by name (getWorksheet) rather than by selection or the active sheet.

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.