Extract specific data from one Excel spreadsheet into another determined by name

[julia_rott]

Hi Guys,

first of all apologies if the topic has been already raised, but honestly I am probably lacking professional vocabulary to find what I need - so, if this has already been discussed and resolved, can you please provide links? 

Anyway, the issue I am struggling with and not able to find a solution is as follows:

1. I have a standard flow that tracks MS Forms responses in Excel (general file that gathers every response) and also sends an e-mail. That is fine.

2. Now I want to extract data from one Excel spreadsheet into another one, but it would be determined by "name" I would put manually in Column 1, so let's say:

Column 1 - manually added by myself (name)

Column 2 - tracked from MS Forms

Column 3 - tracked from MS Forms

Column 4 etc. - tracked from MS Forms and so on

What I want to get is when I put in Column1 for example "ABC" then it will automatically create a row only in a spreadsheet (Excel file) that is called "ABC", if it goes "DEF" then it would be populated in a spreadsheet that is called "DEF", so I just want to exactly define which row goes into which spreadsheet. Is this even possible?

I am not sure if this is clear enough, I am not a native English speaker, so I am a bit struggling with correct wording to describe the issue, but hopefully you will get the point. If I am to explain something better, let me know. 

[Rhiassuring]

Hi there, 

I think what you're asking is possible, if I am clear. Let me ensure I understand:

1) Column 1 in the first spreadsheet tracks responses, by the user's name.

2) When you save and close that spreadsheet, you want a flow to check for any new rows.

3) If there is a new row, you want the flow to check Column 1 (for example, "Rhia"), and then find "Rhia.xlsx", and add that row's data to that spreadsheet. 

Are all of these named spreadsheets already created? 
Do all of these named spreadsheets have the same tables and tabs?
What is the frequency of data being added to the main sheet? (It might be better to schedule this a couple times a day, so that it doesn't just trigger on every modification.)

Cheers,

Rhia

[julia_rott]

Thanks Rhia!

 1) Column 1 does not track response by the user's name who sent the response, the name would be put manually, it's not connected to the flow. It would be just a name of the person who has been assigned the request to do (see below the further explanation). 

2) Yes

3) Exactly!

The named spreadsheet are created. Indeed, they have exactly the same tables and tabs. The frequency might be different, it depends on how often the stakeholders would send forms, not able to specify 😞 In general, to give you a better background, the main spreadsheet would gather requests sent by our stakeholders (via MS Forms). By putting the user's name (=my worker) in Column1 manually it would mean the response (=task, request) is assigned to that person. It would then populate into that user's named spreadsheet, so they also do not have to copy paste it manually. I tried to do it via "condition" -> "add a new row to the table" but I believe that would be too easy. 

Let me know if that's clear enough!

Julia

[Rhiassuring]

Ah, gotcha! Okay. 

❓ Question: In order to track this, we're going to need a way to indicate whether something has been tracked in the secondary sheet or not. Could we add a column that's just "Copied" or "In Named Spreadsheet" or something similar? The value can just be "x" or empty. We will need something like that to track this, unless we use another list or set of values held elsewhere to do so.  I am going to go ahead assuming adding a column called "Copied" will suffice 🙂 

So let's say we want to run this twice a day (just to put a timeframe in there, which you can change at any point). Your flow might then look something like this: 

⭐Step 1 - Create a recurring flow for twice a day.

⭐Step 2 - Add "List rows present in a table", and point it at your main spreadsheet's table. (Mine is named Approvals.xlsx) Filter query: Copied ne 'x'

⭐Step 3 - Now, I'm guessing, we might have some tasks where Column 1 isn't yet populated because stakeholders haven't yet decided who to assign to, or a new task has come in. So, we'll use a Filter Array to yeet the nulls into space.  We just plop in our value output from the previous action, then Column1 is not equal to (leave blank).

⭐Step 4 - We will now Parse JSON to get the values in a nice format. If you're not familiar with this: save your flow here, and run it - then copy the entire Output of  Filter Array. Add your Parse JSON action, point the "Content" at the Filter Array, and click the "Generate from sample" button - paste in your copied text, click done - boom! You did it.

⭐Step 5 - Now the fun part, getting to those other spreadsheets. I should've asked where they're kept -- right now I'm going to assume they're all  in the same place (even if it's apart from the main spreadsheet.) When you get to the "File" dropdown, click in the field, and then click on "Add Dynamic Content" at the bottom right. Then, click on "Column 1" from your "Parse JSON". Suddenly, we have an "Apply to each" - which is exactly what we want!

⭐Step 6 - Click open your "Add row into a table" action again, and where your Column 1 variable is, ensure there's a / in front of it, and a .xlsx after it. Click on the "Table" field and click on "Enter Custom Value". Mine is called "Approvals", so I type that in.

⭐Step 7 - Row data! After completing the previous steps, you should now have a field called "Row". To populate this, we will use an Array. The Array will contain the column name, and the value you want to input. All it really means for us is that in our Row field, we type in { } and then inbetween it's "ColumnName":"ValueToPut" with commas between. 

⭐Step 8 - We have ONE LAST STEP ... we need to mark the row in the main spreadsheet with an x so we don't pick it up again. We'll use the "Update a row" - you'll need a unique column here to match on. For you I'm guessing it might be "Task Description" or something similar:

Annnnnnnnnd that'll do it! 

All in all it looks like this:

What do you think?

Cheers,

Rhia

[julia_rott]

Thanks, will definitely try this!

Best,

Julia