Update Excel Spreadsheet from email attachment

[Anonymous]

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.

[v-yuazh-msft]

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

[farhandsome]

@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

[David54]

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

Thank you!!!

[Akashb]

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

[Akashb]

@Anonymous 

[Jia2]

You can use Run Script function to format table.

[tahneelperry]

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?

[Anonymous]

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? 

[edu]

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

[eliotcole]

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:

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

... then you can:

1. Use a Create file action to make the file on your OneDrive in the root folder.
   (you will delete it at the end!)
2. Run a Get worksheets action on the file you just made.
3. 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!

[edu]

@eliotcole Thanks so much! Will give a try 👍

[m_vishal_c]

HI, what should i pick if i want to update Ondrive (business) excel file or sharepoint excel file for Get rows section. thanks

[eliotcole]

It's not a simple '*bosh* there ya go' kind of thing, @m_vishal_c ... however, speaking purely broadly, yes, it might be easier to do it on your drive, as you can simply get the root files easily enough with a 'me/drive/root/children' call to graph.

If you follow the link that I put on that, it will take you to the Graph Explorer with that command in the bar, ready to roll. So see if you can figure this out purely by using that. 😉

[PrudviThoran]

d

[PrudviThoran]

Yes it is,
Create a table For that file with Range OFFSET(Users!A1,0,0,SUBTOTAL(103,Users!$A:$A),15) , this will create a table for existing Data and then follow the steps as is make sure to use the Correct Column names when using Update a row from excel. Works like a charm for you. Glad i found this post I was on it for a week