Edit

Get user input for scripts

  • Article

Adding parameters to your script lets other users provide data for the script, without needing to edit code. When your script is run through the ribbon or a button, a prompt pops up that asks for input.

The dialog box shown to users when a script with parameters is run.

Important

Currently, only Excel on the web users will be prompted to enter data for parameterized scripts. Power Automate flows also support giving data to scripts through parameters.

Example - Highlight large values

The following example shows a script that takes a number and string from the user. To test it, open an empty workbook and enter some numbers into several cells.

/**
 * This script applies a background color to cells over a certain value.
 * @param highlightThreshold The value used for comparisons.
 * @param color A string representing the color to make the high value cells. 
 *   This must be a color code representing the color of the background, 
 *   in the form #RRGGBB (e.g., "FFA500") or a named HTML color (e.g., "orange").
 */
function main(
  workbook: ExcelScript.Workbook, 
  highlightThreshold: number, 
  color: string) {
    // Get the used cells in the current worksheet.
    const currentSheet = workbook.getActiveWorksheet();
    const usedRange = currentSheet.getUsedRange();
    
    const rangeValues = usedRange.getValues();
    for (let row = 0; row < rangeValues.length; row++) {
        for (let column = 0; column < rangeValues[row].length; column++) {
          if (rangeValues[row][column] >= highlightThreshold) {
              usedRange.getCell(row, column).getFormat().getFill().setColor(color);
          }
        }
    }
}

main parameters: Pass data to a script

All script input is specified as additional parameters for the main function. New parameters are added after the mandatory workbook: ExcelScript.Workbook parameter. For example, if you wanted a script to accept a string that represents a name as input, you would change the main signature to function main(workbook: ExcelScript.Workbook, name: string).

Optional parameters

Optional parameters don't need the user to provide a value. This implies your script either has default behavior or this parameter is only needed in a corner case. They're denoted in your script with the optional modifier ?. For example, in function main(workbook: ExcelScript.Workbook, Name?: string) the parameter Name is optional.

Default parameter values

Default parameter values automatically fill the action's field with a value. To set a default value, assign a value to the parameter in the main signature. For example, in function main(workbook: ExcelScript.Workbook, location: string = "Seattle") the parameter location has the value "Seattle" unless something else is provided.

Help others using your script in their flow by providing a list of acceptable parameter choices. If there's a small subset of values that your script uses, create a parameter that is those literal values. Do this by declaring the parameter type to be a union of literal values. For example, in function main(workbook: ExcelScript.Workbook, location: "Seattle" | "Redmond") the parameter location can only be "Seattle" or "Redmond". When the script is run, users get a dropdown list with those two options.

Document the script

Code comments that follow JSDoc standards will be shown to people when they run your script. The more details you put in the descriptions, the easier it'll be for others to the scripts. Describe the purpose of each input parameter and any restrictions or limits. The following sample JSDoc shows how to document a script with a number parameter called taxRate.

/**
 * A script to apply the current tax rate to sales figures.
 * @param taxRate The current sales tax rate in the region as a decimal number (enter 12% as .12).
 */
function main(workbook: ExcelScript.Workbook, taxRate: number)

Note

You don't need to document the ExcelScript.Workbook parameter in every script.

Type restrictions

When adding input parameters and return values, consider the following allowances and restrictions.

  1. The first parameter must be of type ExcelScript.Workbook. Its parameter name doesn't matter.

  2. The types string, number, boolean, unknown, and object.

  3. Arrays (both [] and Array<T> styles) of the previously listed types are supported. Nested arrays are also supported.

  4. Union types are allowed if they're a union of literals belonging to a single type (such as "Left" | "Right", not "Left" | 5).

  5. Object types are allowed if they contain properties of type string, number, boolean, supported arrays, or other supported objects. The following example shows nested objects that are supported as parameter types.

    // The Employee object is supported because Position is also composed of supported types.
interface Employee {
    name: string;
    job: Position;
}

interface Position {
    id: number;
    title: string;
}

  6. Objects must have their interface or class definition defined in the script. An object can also be defined anonymously inline, as in the following example.

    function main(workbook: ExcelScript.Workbook, contact: {name: string, email: string})

See also