Run a script on all Excel files in a folder

This project performs a set of automation tasks on all files situated in a folder on OneDrive for Business. It could also be used on a SharePoint folder. It performs calculations on the Excel files, adds formatting, and inserts a comment that @mentions a colleague.

Sample Excel files

Download highlight-alert-excel-files.zip for all the workbooks you'll need for this sample. Extract those files to a folder titled Sales. Add the following script to your script collection to try the sample yourself!

Sample code: Add formatting and insert comment

This is the script that runs on each individual workbook. In Excel, use Automate > New Script to paste the code and save the script. Save it as Review script and try the sample yourself!

function main(workbook: ExcelScript.Workbook) {
  // Get the table named "Table1" in the workbook.
  const table1 = workbook.getTable("Table1");

  // If the table is empty, end the script.
  const rowCount = table1.getRowCount();
  if (rowCount === 0) {
    return;
  }

  // Force the workbook to be completely recalculated.
  workbook.getApplication().calculate(ExcelScript.CalculationType.full);

  // Get the "Amount Due" column from the table.
  const amountDueColumn = table1.getColumnByName('Amount Due');
  const amountDueValues = amountDueColumn.getRangeBetweenHeaderAndTotal().getValues();

  // Find the highest amount that's due.
  let highestValue = amountDueValues[0][0];
  let row = 0;
  for (let i = 1; i < amountDueValues.length; i++) {
    if (amountDueValues[i][0] > highestValue) {
      highestValue = amountDueValues[i][0];
      row = i;
    }
  }

  let highestAmountDue = table1.getColumn("Amount due").getRangeBetweenHeaderAndTotal().getRow(row);

  // Set the fill color to yellow for the cell with the highest value in the "Amount Due" column.
  highestAmountDue.getFormat().getFill().setColor("FFFF00");

  // Insert an @mention comment in the cell.
  workbook.addComment(highestAmountDue, {
    mentions: [{
      email: "AdeleV@M365x904181.OnMicrosoft.com",
      id: 0,
      name: "Adele Vance"
    }],
    richContent: "<at id=\"0\">Adele Vance</at> Please review this amount"
  }, ExcelScript.ContentType.mention);
}

Power Automate flow: Run the script on every workbook in the folder

This flow runs the script on every workbook in the "Sales" folder.

1. Create a new Instant cloud flow.

2. Choose Manually trigger a flow and select Create.

3. In the flow builder, select the + button and Add an action. Use the OneDrive for Business connector's List files in folder action. Use the following values for the action.

   • Folder: /Sales (selected by the file picker)

   

4. Ensure only workbooks are selected. Add a new Condition control action. Use the following values for the condition.

   • Choose a value: Name (dynamic content from List files in folder)
   • ends with: (from the dropdown list)
   • Choose a value: .xlsx

   

5. Under the True branch, add a new action. Select the Excel Online (Business) connector's Run script action. Use the following values for the action.

   • Location: OneDrive for Business
   • Document Library: OneDrive
   • File: Id (dynamic content from List files in folder)
   • Script: Review script

   

6. Save the flow. The flow designer should look like the following image.

   

7. Try it out! Use the Test button on the flow editor page or run the flow through your My flows tab. Be sure to allow access when prompted.

Training video: Run a script on all Excel files in a folder

Watch Sudhi Ramamurthy walk through this sample on YouTube.