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

(0) Share

Report

Posted on by Kosenurm

802

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

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (1)

Verified answer

eliotcole 4,363 Moderator on at

Like (0)

Report

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-using-power-BI/m-p/1599045/highlight/true#M178374

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

This may also help.

Was this reply helpful? Yes No
Kosenurm 802 on at

Like (1)

Report

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

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report

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=v1.0&GraphUrl=https://graph.microsoft.com

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.

Was this reply helpful? Yes No
Kosenurm 802 on at

Like (0)

Report
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" } } } } } } }

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
Kosenurm 802 on at

Like (0)

Report

Thank you.

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

Error:

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

Was this reply helpful? Yes No
ryleybauer 327 on at

Like (0)

Report

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

Was this reply helpful? Yes No
ryleybauer 327 on at

Like (0)

Report

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

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report
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.

Was this reply helpful? Yes No