Create Table in Excel WorkSheet

If you are receiving daily/weekly excel workbooks containing data that isn’t saved as a table, how are you going to query it in PowerAutomate? One method is to use Office Scripts. With a relatively simple piece of TypeScript, you can insert a Table into your Excel Worksheet. Not only can the script detect the used range of rows and columns but it can also run on a specific sheet and specifically name your table. If Table1 isn’t what you are looking for, call it as you please, all automatically, via Power Automate.

The Script

If you are unfamiliar with Office Scripts, you need to take a read-up here on the list of requirements and how to get started. If you are ready to give this a try, the sample office script is below, ready for you to copy and paste into a new script, in my case called CreateTableInSheet.

function main(workbook: ExcelScript.Workbook, 
TableName: string = "Table1",
SheetName: string = "Sheet1"
) {
  // Get the worksheet by name
  const selectedSheet = workbook.getWorksheet(SheetName);
  // Alternatively, get the first worksheet (uncomment below and comment above)
  // const selectedSheet = workbook.getFirstWorksheet();
  // Create a table using the data range.
  let newTable = workbook.addTable(selectedSheet.getUsedRange(), true);
  newTable.setName(TableName);
}

You will see that I have specified two input parameters, which will default to Table1 and Sheet1. You may change these as appropriate to suit your use case. If you don’t know the name of your worksheet and you want this to run on your first sheet of the Excel Workbook, you can uncomment the const selectedSheet = workbook.getFirstWorksheet(); expression that I have included in the above sample script.

Calling the Script

There is a bespoke action for Office Scripts, named Run Script. This will allow you to browse for a script that you own or have shared access to. The script must run on a specified file name albeit this could be dynamic. What if the file was being received via email for example?

Receiving a file via Email

If your file is being received via email, you can use the when a new email arrives trigger. You should ensure that as a minimum Include Attachments is enabled under advanced settings. Then I would recommend that you filter the dynamic value attachment name using the Filter Array Action for the exact file name, or the filename extension ending with i.e. .xlsx

Then by creating a copy of the file, using Create File, and saving it to your SharePoint or OneDrive library, you can run your script on the newly created file and insert the table, don’t forget to specify your sheet name and table name in the script parameters if the defaults don’t suit your requirements.

Overwriting the original file

If you have a flow that relies on that excel file, for instance, a system-generated excel file that you wish to overwrite on a regular basis, you might find that flow fails when the file is overwritten. You might encounter the following:

The dynamic operation request to API ‘excelonlinebusiness’ operation ‘GetTable’ failed with status code ‘NotFound’. This may indicate invalid input parameters.

In order to prevent this from happening you should consider getting the file by path using Get File Metadata using Path action. You can then supply the Id from the action to the list rows or other excel actions and your flow will continue to run, no matter how many occasions you overwrite the file.