cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kosenurm
Post Partisan
Post Partisan

Format an excel document into table with unknown amount of rows each day

Hi,

 

I have an Excel document which has three worksheets where I need to, each day, when the report arrives in my Inbox, extract the data from the first three worksheets, and ingest these into a Dataverse table. 

 

Firstly, I need to get the Excel data into the right format (table) so I can use Power Automate to undertake the actions. 


The data always starts on row 4 on each worksheet, and goes across 11 columns on each worksheet.

 

However, as the data changes, the amount of rows on each worksheet will change. 

 

Therefore, when I am using the Create table (Excel Online) action, what do I put for the Table range? It will start A4 (and go along to column K), but I don't know how many rows will be on these worksheets each day? 

 

Any help appreciated!!!

 

Thanks

K.

1 ACCEPTED SOLUTION

Accepted Solutions
eliotcole
Super User
Super User

I note that you've been around for a while, @Kosenurm, so hopefully I'm OK in letting you know that you should take a look at using the Send an HTTP request action inside the Office 365 Groups connector listings.

 

This will then allow you to send a Graph API request regarding the Excel sheet to 'getUsedRange', whereby you can then logic yourself into knowing the last used field, and can create a table (or just take the data straight from the result that it gives you.

 

I've discussed the connector a few times, and I've found this post to have a rather apt usage for you:

https://powerusers.microsoft.com/t5/Building-Flows/Copy-and-past-data-from-one-excel-to-another-usin...

 

Scroll down to 8 - HTTP GET usedRange and that should help you out a bit.

 

This may also help.

View solution in original post

9 REPLIES 9
eliotcole
Super User
Super User

I note that you've been around for a while, @Kosenurm, so hopefully I'm OK in letting you know that you should take a look at using the Send an HTTP request action inside the Office 365 Groups connector listings.

 

This will then allow you to send a Graph API request regarding the Excel sheet to 'getUsedRange', whereby you can then logic yourself into knowing the last used field, and can create a table (or just take the data straight from the result that it gives you.

 

I've discussed the connector a few times, and I've found this post to have a rather apt usage for you:

https://powerusers.microsoft.com/t5/Building-Flows/Copy-and-past-data-from-one-excel-to-another-usin...

 

Scroll down to 8 - HTTP GET usedRange and that should help you out a bit.

 

This may also help.

Thank you Eliot, this is superb. 

 

One thing, I am having trouble getting the Sharepoint siteID from Graph Explorer, as I don't have access to Graph Explorer directly.

 

Is there a way I can get the required ID from Power Automate, or from the Sharepoint site directly?

 

Thanks

K

There's a number of ways to get this ID, @Kosenurm, but it's never obvious 😅 ... some of the site settings screens will populate it into the address bar, even.

 

The good thing here is that you only need to do it once, and then you can store it in a variable or something.

 

Be sure to be aware that if it starts failing, then you probably need to uriEncode one or all of the IDs in use.

 

Here, use this Graph Explorer link, that should list all the sites on your domain:

https://developer.microsoft.com/en-us/graph/graph-explorer?request=sites?search=&method=GET&version=...

Just find the site you need, and there are 3 ID references separated by commas, you'll want the middle one, I believe.

 

You can also run that in a flow action, of course.

Thank you for your help. 

 

Last question, when I run the Send an HTTP (office 365 groups) action, the JSON schema from this is below. How can I drill down to just get each item within the 'values' element?    'Values' is an array of arrays.

 

Thanks

K.

 

 

{
    "type": "object",
    "properties": {
        "statusCode": {
            "type": "integer"
        },
        "headers": {
            "type": "object",
            "properties": {
                "Transfer-Encoding": {
                    "type": "string"
                },
                "Vary": {
                    "type": "string"
                },
                "Strict-Transport-Security": {
                    "type": "string"
                },
                "request-id": {
                    "type": "string"
                },
                "client-request-id": {
                    "type": "string"
                },
                "x-ms-ags-diagnostic": {
                    "type": "string"
                },
                "OData-Version": {
                    "type": "string"
                },
                "Timing-Allow-Origin": {
                    "type": "string"
                },
                "x-ms-apihub-cached-response": {
                    "type": "string"
                },
                "x-ms-apihub-obo": {
                    "type": "string"
                },
                "Cache-Control": {
                    "type": "string"
                },
                "Date": {
                    "type": "string"
                },
                "Content-Type": {
                    "type": "string"
                },
                "Content-Length": {
                    "type": "string"
                }
            }
        },
        "body": {
            "type": "object",
            "properties": {
                "@@odata.context": {
                    "type": "string"
                },
                "@@odata.type": {
                    "type": "string"
                },
                "@@odata.id": {
                    "type": "string"
                },
                "address": {
                    "type": "string"
                },
                "addressLocal": {
                    "type": "string"
                },
                "columnCount": {
                    "type": "integer"
                },
                "cellCount": {
                    "type": "integer"
                },
                "columnHidden": {
                    "type": "boolean"
                },
                "rowHidden": {
                    "type": "boolean"
                },
                "numberFormat": {
                    "type": "array",
                    "items": {
                        "type": "array",
                        "items": {
                            "type": "string"
                        }
                    }
                },
                "columnIndex": {
                    "type": "integer"
                },
                "text": {
                    "type": "array",
                    "items": {
                        "type": "array",
                        "items": {
                            "type": "string"
                        }
                    }
                },
                "formulas": {
                    "type": "array",
                    "items": {
                        "type": "array",
                        "items": {
                            "type": "string"
                        }
                    }
                },
                "formulasLocal": {
                    "type": "array",
                    "items": {
                        "type": "array",
                        "items": {
                            "type": "string"
                        }
                    }
                },
                "formulasR1C1": {
                    "type": "array",
                    "items": {
                        "type": "array",
                        "items": {
                            "type": "string"
                        }
                    }
                },
                "hidden": {
                    "type": "boolean"
                },
                "rowCount": {
                    "type": "integer"
                },
                "rowIndex": {
                    "type": "integer"
                },
                "valueTypes": {
                    "type": "array",
                    "items": {
                        "type": "array",
                        "items": {
                            "type": "string"
                        }
                    }
                },
                "values": {
                    "type": "array",
                    "items": {
                        "type": "array",
                        "items": {
                            "type": "string"
                        }
                    }
                }
            }
        }
    }
}

 

 

Values is your rows, 🙂 ... so process them how you would usually. 🙂

Thank you.

 

However, using values (@body('Parse_JSON')?['body']?['values']) returns as

 

Error:

 

@body('Parse_JSON')?['body']?['values']' is of type 'Null'

ryleybauer
Resolver II
Resolver II

There are a bunch of properties available when you browse to site\_api\site

 

I think the group ID is the same as the site ID as there isn't a separate property on this page: https://contoso.sharepoint.com/sites/SITENAME/_api/site?$select=GroupId

ryleybauer
Resolver II
Resolver II

Oh, and if it wasn't clear, you can do an HTTP GET request from SharePoint in Power Automate and then parse the return in JSON to find the "GroupId" value from a specified site at runtime by using a variable for SITENAME

 

https://contoso.sharepoint.com/sites/SITENAME/_api/site?$select=GroupId

Hmm.

 

I would hesitate to say it ... but that usually means that the sheet is empty.

I try not to use the parse_JSON where possible as it makes problems that you need to solve with the schema.

 

Here's what I just tested on an old flow with a sheet that I know had data in it, and I called the graph action "HTTP_GET_firstSheetUsedRange" ... 

body('HTTP_GET_firstSheetUsedRange')?['values']

It's possible that one of the IDs is wrong, or that the sheet that is specified is empty.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,279)