A large request

[Anonymous]

Hey all, this is a pretty big request but I've been frantically working away for a while now and can't make any headway. Any help you can give would be appreciated.

I have a Flow which takes specific attachments from my email inbox and places them into a Sharepoint library successfully.

It's important to note that this spreadsheet is in xls format. I'll refer to this spreadsheet as Spreadsheet A.

What I am looking to do is manipulate this data using another spreadsheet, via Flow. My master spreadsheet (Spreadsheet B) has the aim of extracting data from Spreadsheet A using INDEX MATCH (or hopefully XLOOKUP soon!) and then creating a static copy of the transferred data into a second table.

For example: Spreadsheet A (produced daily and file is overwritten every day)

Officer             Sales

A. Fakename   100

T. Lerone         150

Spreadsheet B (contains formula to get key information from Spreadsheet A) *I have simplified the formula for demo

Officer           Sales

=A2              =B2

=A3              =B3

Which gives the result

Officer             Sales

A. Fakename   100

T. Lerone         150

However when a new day hits, Spreadsheet B formula picks up the new data from Spreadsheet A because Spreadsheet A has been overwritten with the new days data, therefore the previous days data is lost.

I'm looking for the Flow to pick up, in this example:

Officer             Sales

A. Fakename   100

T. Lerone         150

and paste into a new table in Spreadsheet B, and be intelligent enough to add onto the new table. This would happen after the first bit of the flow (transferring the file from email to sharepoint has occured, with presumably a delay in the middle)

My extensive testing has produced erroneous results and mostly blank rows in tables.

Can anyone help me move forward with this please?

[ManishJain]

Hi @Anonymous ,

From Spreadsheet A you are pulling data and doing calculation and putting in Spreadsheet B . Is there anything else that you are doing in Spreadsheet B ? 

Thanks

[Anonymous]

Hi @ManishJain and everyone

Here is a more pictorial representation of what I am looking for. Hope it makes sense. The build of Table 2 from Table 1 is where I'm looking for the Flow to help.

[Anonymous]

Just trying to bump this up.

Has anyone got any ideas as I am still struggling with this.

[ManishJain]

Hi @Anonymous ,

I hope you are doing calculation inside your flow with compose , select and filter actions as they are native and support concurrency in looping. This is something that I have done in past to create an array based on conditions like yours. Once this is done I used create csv table to create csv file , instead you can use Add a row into table to append rows on daily basis.

Thanks

[Anonymous]

Hi @ManishJain 

I'm looking for advice on actually how to construct the flow from scratch as I am completely stumped at the moment.

Thanks

[susilacherla12]

Even I need help here. I will receive a Spreadsheet on mail. I need to copy few contents from the attachment and add to my master spreadsheet and subsequently use it in the flow

[ManishJain]

Hi @susilacherla12 , @Anonymous ,

This has been long awaited feature (Run Script(preview) which allows us to execute a script inside excel itself , I would definitely want to try and replace my functionality with this considering I was about to process almost 10K records using flow. It will definitely save lot of time and errors . 

https://docs.microsoft.com/en-us/office/dev/scripts/tutorials/excel-read-tutorial

Thanks