Power Automate – Excel – How to create Table and Read the Contents Dynamically?

[abm]

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.

Above Parse JSON Schema is as follows:

{
“type”: “object”,
“properties”: {
“@@odata.context”: {
“type”: “string”
},
“@@odata.type”: {
“type”: “string”
},
“@@odata.id”: {
“type”: “string”
},
“style”: {
“type”: “string”
},
“name”: {
“type”: “string”
},
“showFilterButton”: {
“type”: “boolean”
},
“id”: {
“type”: “string”
},
“highlightLastColumn”: {
“type”: “boolean”
},
“highlightFirstColumn”: {
“type”: “boolean”
},
“legacyId”: {
“type”: “string”
},
“showBandedColumns”: {
“type”: “boolean”
},
“showBandedRows”: {
“type”: “boolean”
},
“showHeaders”: {
“type”: “boolean”
},
“showTotals”: {
“type”: “boolean”
}
}
}

Now we have built our flow and below is the overall flow looks like.

References:

https://docs.microsoft.com/en-us/graph/use-the-api

https://docs.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0

Hope this is helpful and thanks for reading my post.