How to set input parameters for a script in Excel?

Question

I have a script that formats the data from a table to JSON. My Excel document has many sheets all with one table in each sheet. I am trying to pass in an input parameter (of type string) for the name of the sheet so that when I run the script I am able to specify the sheet that will convert the data. I currently have a default parameter "Sheet1." I don't know where I can specify the parameter when I run the script.

Format Table Data Code Snippet

I know I can Just copy the script for as many sheets I have in my Excel document and hard-code it so that each script formats the data in one sheet, but for obvious reasons, I don't want to do that.

Answer 1

At the time of writing, there are only two ways to pass extra arguments to an office script:

1. User Power Automate.

Your scripts can be written to expand the "Run script" Power Automate action to get additional input or give output.

2. User input via a pop-up in excel.

When your script is run through the ribbon or a button, a prompt pops up that asks for input.

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

Use Power Automate (Option 1)

To pass an argument via Power Automate (option 1), which will be assigned to sheetName in your example office script, follow these steps:

1. Create a new flow in Power Automate, or edit an existing one.

2. Add a 'run script' action

3. Enter the path to your saved Excel file into the File field.

4. Choose the relevant script from the Script drop down menu.

5. After you choose your script, new fields that correspond to the script's parameters will appear in the action menu.

6. Enter your desired value for each field. You can type in static values, or use the features of Power Automate to supply dynamic values.