Do you want to get data from Excel to Power Automate without using a table?

Tables are the best tool for organizing and managing your data in Excel. They are also well integrated with Power Automate as the built-in methods for getting data from Excel in your flow all require tables.

Even though tables are the recommended method for storing data in Excel, they might not always be suitable fo your use.

When your data is not inside a table, it is still possible to get the Excel data into your flow using Office Scripts.

This article will walk you through the process of getting data from an Excel file without a table in Power Automate.

How to Create an Office Script in Excel

Office Scripts enable the automation of tasks and extraction of data.

Office Scripts is a TypeScript based language that allows users to automate tasks and processes in Excel.

These scripts can also be run from Power Automate and be used to extract data from your Excel files without the need for a table.

Follow these steps to create an Office Script from scratch.

1. Open Excel and go to the Automate tab.
2. Click on the New Script command to open the script editor with a new blank script.

This will open the Code Editor pane on the right side of the Excel workbook.

function main(
    workbook: ExcelScript.Workbook,
    sheetName: string,
    cellAddress: string
) {
    let sheet = workbook.getWorksheet(sheetName);
    let range = sheet.getRange(cellAddress).getValues();
    return range;
}

3. Paste the above code into the Code Editor. This code allows you to return a range of cell values to Power Automate based on the sheetName and cellAddress as inputs.
4. Give your script a new name.

5. Click on the Ellipses icon in the top right of the Code Editor.
6. Choose the Save script as option from the menu.

6. Select the location in SharePoint where you want to save the script.
7. Click on the Save button.

Now your script is saved in SharePoint and you will be able to call and run it for any Excel file by using Power Automate.

How to Use Office Scripts in Power Automate

This Office Script will now allow you to get Excel data into your flow without a table. Here’s how to use the Office Script.

1. Add the Run script from SharePoint library action to your flow.
2. Select the SharePoint site where your Excel file is saved in the Workbook Location dropdown.
3. Select the SharePoint Library where your Excel file is saved in the Workbook Library dropdown.
4. Choose your Excel file with the Workbook field using the folder icon to open the file picker menu.
5. Select the SharePoint site where your Office Script file is saved in the Script Location dropdown.
6. Select the SharePoint Library where your Office Script file is saved in the Script Library dropdown.
7. Choose your Office Script file with the Script field using the folder icon to open the file picker menu.

Once you have selected the Office Script file, the sheetName and cellAddress fields will appear in the action. These are defined in the Office Script file and you can now use these fields to define the range of data to extract from Excel.

8. Add the sheet name which contains the data you want to extract from your Excel file in the sheetName input. In this example, data will be extracted from the My Data sheet in the Excel file.
9. Add the range address to the cellAddress input. This should be the row and column address of the data your want to extract such as B2:E12. In this example, data from cell B2 to E12 will be extracted.

You will now be access the Excel data in any subsequent step in your flow using the result dynamic content.

This will return an array of arrays. The first array contains an element for each row in your range and each inside array contains the data in the row where each element is a column.

You can now get any value from the array with its index values.

@{outputs('Run_script_from_SharePoint_library')?['body/result']?[1]?[2]}

For example, to get the value from the second row and third column of the data, you can use the above expression.

Here [1] is the zero based row index and [2] is the zero based column index.

Conclusions

Working with Excel data not formatted as a table can be challenging.

There are techniques you can employ to overcome this limitation by using Office Scripts to access data from any range of data. This allows you to interact with data even if it is not in a table format.

By utilizing these tools you can process data from Excel files without the constraints of table formatting.

Have you tried working without Excel tables in Power Automate yet? Let me know how it worked for you in the comments!