Unanswered

Update Excel Spreadsheet from email attachment

(1) Share

Report

Posted on by Community Power Pla...

Is it possible to append the rows of an excel spreadsheet in an email attachement to an excel spreadsheet stored in sharepoint?

Every day we are sent a report for the last 24hrs as an excel spreadsheet attached to an email. Each row in this daily report needs to be copied into a yearly spreadsheet which is stored in a sharepoint library.

Categories:

Building flows

I have the same question (0)

All responses (15)

Answers (0)

v-yuazh-msft on at

Like (2)

Report
Hi @Anonymous,

I have made a test on my side, please refer to screenshot below to create the flow:

Note:

1.Please makre sure the Excel table would always have the same Table name, My excel table in the flow with Table name "Table1".

2.The expression of column Carrier as below:

items('Apply_to_each_2')['Carrier']

The expression of column E-mails as below:

items('Apply_to_each_2')['E-mails']

The expression of column Path as below:

items('Apply_to_each_2')['Path']

The expression of column DATE as below:

items('Apply_to_each_2')['Date']

The flow would run successfully as below:

Best regards,

Alice

Was this reply helpful? Yes No
farhandsome 175 on at

Like (0)

Report

@v-yuazh-msft ,

Thank you for the great answer. Is it possible to ask a follow-up?

Is there a workaround if the Excel data is not presented within a table?

Farhan

Was this reply helpful? Yes No
David54 12 on at

Like (0)

Report

this is brilliant! Thank you so much! I was looking the whole day for a solution to this!!

Thank you!!!

Was this reply helpful? Yes No
Akashb 10 on at

Like (0)

Report

Hello, I have some issues while trying this flow, could you please help me

Was this reply helpful? Yes No
Akashb 10 on at

Like (0)

Report

@Anonymous

Was this reply helpful? Yes No
Jia2 7 on at

Like (0)

Report

You can use Run Script function to format table.

Was this reply helpful? Yes No
tahneelperry 2 on at

Like (0)

Report

First and foremost, thank you so much for the detail on this! I was not able to complete the last step of this and got the following error:
The dynamic operation request to API 'excelonlinebusiness' operation 'GetTable' failed with status code 'BadRequest'. This may indicate invalid input parameters. Error response: { "status": 400, "message": "Invalid request clientRequestId: 0032100b-dde7-4890-a54c-1ce4f147c001 serviceRequestId: 0e7c8a09-709e-4043-a982-4016e7411dcf", "error": { "message": "Invalid request" }, "source": "excelonline-ncus.azconn-ncus.p.azurewebsites.net" }

Any ideas on what I can try here?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

I was wondering if you could help, i am trying to replicate this flow but instead of creating file in onedrive i am creating it in Sharepoint.

the problem i have is that when i come to the "get a row" action i try and use the current item as the source but it doesn't find the Table name or the key column/key value:
is "get a row" the correct action as that would mean i need to specify a location and document library or is it a different action?

Was this reply helpful? Yes No
edu 11 on at

Like (0)

Report

Same situation here: cannot go forward from Get Rows.
Was somebody able to apply v-yuazh-msft solution?
Thanks in advance

Was this reply helpful? Yes No

eliotcole

4,363 Moderator on at

Like (1)

Report

Just realised that @Jia2 was replying to a rather old issue 😅 ... not to worry, this still stands.

Yo, y'all (@Anonymous, @Anonymous, @edu, @Jia2, and @tahneelperry ), you need to run a Graph HTTPS request to get the Excel data, as I demonstrated here.

That will provide you with the used cells on a given sheet, which you can then turn into a table, then you can extract the data from the table.

However, it's actually easier than all that. Because if you use the HTTPS request that I provided to the user there (they were quite steadfast in not wanting to use it 😅) it actually returns the data that would be returned from a table, just possibly without defined keys/headers/field names.

Here is an image from that untaken ( 😓 ) solution:

If you know for sure that:

One excel file is sent with each email.
Only the first worksheet is used in that file.

... then you can:

Use a Create file action to make the file on your OneDrive in the root folder.
(you will delete it at the end!)
Run a Get worksheets action on the file you just made.
Place a Send an HTTPS request to Graph directly after the Get worksheets action, not in a loop.
(no Apply to each)

Then the following in the URI is much more simple:

https://graph.microsoft.com/v1.0/me/drive/items/@{last(split(outputs('Create_file')?['body/Id'], '.'))}/workbook/worksheets/@{encodeUriComponent(outputs('Get_worksheets')?['body/value'][0]?['id'])}/usedRange

This will actually provide all the data that is usually inside a call for data from a table, I just don't remember if it formats those column headers.

(the Id from the Create file action is DRIVE_ID.FILE_ID, so you need to split it on '.' to take the second/last() value)

But if you really want headers, etc, then make a table from the returned range value. 😉

You can then delete the file at the end of the flow!