cancel
Showing results for 
Search instead for 
Did you mean: 
abm
Most Valuable Professional

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

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’]}’)

 

image.png

 

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.

 

image.png

 

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)

 

image.png

 

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.

 

image.png

 

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.

 

image.png

 

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.

 

image.png

 

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.

 

image.png

 

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.

 

Comments

Hi!

It is very interesting and helpful!

Thank You very much! 😀

Best Regards

Maciek

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/