Power Automate: Auto Create Excel Table Based On Dynamic Content (With Excel Automation Script)

Learn how to efficiently retrieve the final index of utilized rows and columns within an Excel file, even in the absence of a table. This guide demonstrates the utilization of Power Automate in conjunction with Excel Automation Script to create a table based on the indexes being used in multiple sheets.

By employing this flow, you can automatically prepare Excel files with table generation and enhancing data retrieval dynamically in alignment with other Power Automate Excel actions that comes after.

In this demo, we will also deploy and store the Excel Automation Script within a SharePoint site. This placement ensures widespread accessibility across SharePoint teams. The script is automatically ran for every new Excel file generated in a designated SharePoint folder, promptly extracting the ultimate index values for both rows and columns from all existing sheets then creating tables based on the populated indexes.

Prerequisites:

• Access to create an Excel Automation Script in a SharePoint site folder or a OneDrive storage account folder.

Enabling the Excel Automation script tab

First we will need to enable the Excel Automation script tab, if you haven’t done so in Excel application.

Select “File“

Select “Options“

Select “Customize Ribbon” and add the “Automate” Tab to the right side, check the mark and click on “OK“

Creating the Excel Automation script

Next up we will create the Typescript file through the Excel Automation Script editor

Click on “Automate” and then on “New Script“

Now copy and paste the following code. I’ve added descriptions in between each line so that it’s easy to follow along with what the script does:

function main(workbook: ExcelScript.Workbook) {
  // Get all worksheets in the workbook
  const worksheets = workbook.getWorksheets();

  // Loop through each worksheet
  for (let i = 0; i < worksheets.length; i++) {
    const worksheet = worksheets[i];

    // Get active range of the worksheet
    let range = worksheet.getUsedRange();

    // Get last used row and column of the worksheet
    let lastRow = range.getRowCount();
    let lastColumn = range.getColumnCount();

    // Define the range for the table
    let tableRange = worksheet.getRangeByIndexes(0, 0, lastRow, lastColumn);

    // Create a table based on the range
    let table = worksheet.addTable(tableRange, true);
  }

  // Return a success message
  return "Tables created in all worksheets.";
}

Creating the Power Automate flow

When creating the automation flow in Power Automate, I’ve considered to publish the script to a public SharePoint library where it can be invoked.

Summary

We have successfully established a Power Automate workflow that automatically generates a new table from populated data rows and columns. This process is triggered whenever a fresh Excel file is detected, accompanied by an Excel Automation Script. This functionality proves particularly beneficial when working with other “Excel Actions” that necessitate dynamic data retrieval within a sheet.