Can Power Automate Excel read without table?

Obviously I've no idea what I'm doing and have taken the above from the internet via search engine results.

I need to return the last row and return a value from it. I may need to get other values from other columns at some point too so it'd be handy to know how to extract anything and I can just apply the logic to get this last record. Any help would be appreciated.

Formerly known as Microsoft Flow, Power Automate unlocks a range of features and tools that help you automate any business process, from running tasks on a schedule to sending automatic reminders for past deadlines. If you’re subscribed to Microsoft 365, then you already have access to all Power Automate tools.

In this article, you’ll see how Power Automate works with Excel, learn how to create an Excel file, and get data from this file automatically. We’ll also provide an alternative solution to overcome Power Automate limitations when combined with Excel.

Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Recently Microsoft announced Graph API HTTP Request action step. Good news is its not a premium connector. There are lots of potential for this.

In this blog I will explain how to create a table and read an Excel sheet dynamically?

My scenario is drag and drop an Excel file (No table) in One Drive. Power Automate will trigger and read the Excel contents.

Lets start building the flow.

Here I am using my flow trigger as When a file (Excel) is created under OneDrive.

Next we need to capture the Excel file identifier which is needed for subsequent Graph API steps below. Graph API URL to get the identifier is:

https://graph.microsoft.com/v1.0/me/drive/root/children?$select=name,id&$filter=startswith(name, ‘{triggerOutputs()?[‘headers/x-ms-file-name-encoded’]}’)

Next we need to capture the body value (See below Content Value mapping : body(‘Send_an_HTTP_request’)?[‘value’]) of the response above and collect the identifier and name. For this I am going to add a Parse JSON step and pass the body value (body(‘Send_an_HTTP_request’)?[‘value’]) of response body of the previous step. See below.

Above Parse JSON Schema is as follows:

{
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“@@odata.etag”: {
“type”: “string”
},
“id”: {
“type”: “string”
},
“name”: {
“type”: “string”
}
},
“required”: [
“@@odata.etag”,
“id”,
“name”
]
}
}

To avoid the loop I have used the following expressions to find the Excel file name and Id.

body(‘Parse_JSON’)?[0][‘name’]

body(‘Parse_JSON’)?[0][‘id’]

Next we need to find the used range values of Excel worksheet. This is used for creating the table with specified range.

To find the used range using Graph API is as follows:

https://graph.microsoft.com/v1.0/me/drive/items/outputs(‘Excel_File_Id’)/workbook/worksheets(‘Sheet1’)/usedRange(valuesOnly=true)

Next capture the body response and look for the address property to get the value range (eg:Sheet1!A1:B7). We are interested in the cell values only. So we need to use the last() and split() expressions to extract the cell range. See below.

Above expression is as follows:

last(split(body(‘Send_an_HTTP_request_4’)?[‘address’],’!’))

Next we are ready to create table.

Graph API to create table is:

https://graph.microsoft.com/v1.0/me/drive/items/outputs(‘Excel_File_Id’)/workbook/worksheets(‘Sheet1’)/tables/add

Under the body we need to pass the range value earlier we found and set whether the excel sheet has header or not.

Above step will create a table under the worksheet. Above under the URI I have hardcoded Sheet1 which is the default sheet name for my worksheet.

Next I am using the Parse JSON step to get the table id by passing the body from previous step. Using the filename and table id we can read the Excel sheet contents.

In this example, we will get the rows and cells of an Excel Spreadsheet, look for a specific value, and then update it to a new value in Microsoft Power Automate. The same steps here will also work in Azure Logic Apps.

In this flow, first we are going to get the rows and cells of the spreadsheet. Now we will loop through each row, and then each cell in the result. We will setup a conditional to look for a specific value. When we find it, we will set the cell value with a new value, and then write the updated spreadsheet result back into OneDrive.

First, let's setup our flow:

Now, let's run it. And here we have the result:

Download Example Flow

Video Walkthrough

Can Power Automate pull data from Excel?

What can Power Automate do with Excel?

How do I read an Excel file in Power Automate desktop?

Can Power Automate write to Excel?