Help needed with updating an Excel spreadsheet row based on values in a filter array - how do you do it?

[ianwuk]

I have a flow that looks like this:

I read an Excel spreadsheet (40 rows).

I perform a filter (reduces to 5 rows).

I then want to update those 5 rows in the actual Excel spreadsheet itself with data I specify. 

I know that I use the Update a row option and probably the Appy to Each control, but I don't know how to link it all together.  

Can anyone kindly help me?

Regards.

[Rhiassuring]

After the "Filter Array", get the Output from that (from a run), and plop it into a Parse JSON. 

Then you'll have some names of things in your Dynamic Content now.

Now when you go to use your Update a Row, and you point the Key Value at the "Key Column" output from the Parse JSON (for example, if you had "TITLE OF THING" as the key, you'd select that for Key Value in update row), it will turn into an appropriate Apply To Each automatically. 

[ianwuk]

Thank you for replying @Rhiassuring 

Now I have this:

I'm not sure if it is correct (probably not!).  For example, is the output from the filter correct that I have used?  I only have Body or Item.

Is there any way you could create a basic flow to show me the steps you just described?

Thank you!

[v-chengfen-msft]

Hi @ianwuk ,

Please try to use the formula to get the return value of Filter array

items('Apply_to_each')?['Position']

By the selected key column
Then use the formula to dynamically obtain the key value from the return value of the Filter array
Then go to update the content you want to update

Best Regards

Cheng Feng

[ianwuk]

Hello @v-chengfen-msft.  Thank you for replying.

I can't get your steps to work.  So I am trying again what @Rhiassuring said.

I have this:

1 - Filter spreadsheet and get the filter results in JSON (body contents not shown, but they are correct - just 3 matching filtered records).

But, when I then try to update Excel row, I have no dynamic content to work with from the JSON - just Body which doesn't work.

What do I put here to update just the records in the spreadsheet that matched the filtered JSON?

Thanks.

[ianwuk]

@v-chengfen-msft  @Rhiassuring 

I'm getting there - slowly!

I followed the advice of @Rhiassuring and I figured out how to parse the JSON and use that.

The problem is that the original filter array has three records, which is correct.

But the flow, using Apply to each doesn't seem to work properly. 

Instead of updating the spreadsheet on three separate rows (based on the array filter results), it only updates one of the cells in the spreadsheet from the array filter results and it does this three times.

The Apply to each looks like this:

The Body comes from the filter array results JSON.

The Key Column is the column name of the spreadsheet.

The Key Value is the value name from the JSON which I want to update in the spreadsheet.

For each of those records I want to add the Get Month Name variable, so 'January' to the Night Shift start column.  So 'January' would be put into the cell.

Any help is much appreciated - thanks!

[v-chengfen-msft]

Hi @ianwuk ,

If you still want to use the Parse JSON method, please first obtain the Body returned by the Filter array and copy it

Then follow the steps below to Parse JSON, and then you can get the parsed content directly.

Here is result:

I think the essence of doing this is to parse Object through ParseJSON. The result is the same as the dynamic acquisition through the formula

Best Regards

Cheng Feng

[ianwuk]

@v-chengfen-msft Thank you for replying.

Something doesn't work for me. It just updates the same one row multiple times.

Here are my steps:

I parse the JSON, based on your kind help.

You can see Night shift start and Night shift completed in the schema.  These are the values I want to update in the Excel spreadsheet.

It then uses the body from Parse JSON to update each row.

I want to update the Night shift completion column in the spreadsheet.

I use the key value formula - items('Apply_to_each')?['Night shift completion']

Based on that, I want to update the following spreadsheet columns (Night Shift Start and Night shift completion) :

With these values:

Get month name = the current month name (e.g., 'January')
FutureTime = the month name six months from now (e.g. 'June').

In the spreadsheet, as a test, you can see six rows have 'January' in them. 

These are what I expected to be updated in the spreadsheet by the flow and the flow should update the Night Shift Start column accordingly.

The flow has six records, which matches the number of results from the array results and the spreadsheet.

But it's only the first spreadsheet row updated six times, which means it's the same one record six times.

What am I doing wrong here?  Thanks!

[v-chengfen-msft]

Hi @ianwuk ,

I think key column and key value should be unique. (Please use a unique column as the key column)

What I have used in the case is just to show how to get the value.

Best Regards

Cheng Feng

[ianwuk]

@v-chengfen-msft I'm not sure what you mean?

I do not understand why it just updates the same one row multiple times instead of iterating through the JSON results from the array.

This seems really hard for me to do. Sorry.

[v-chengfen-msft · Accepted solution]

Hi @ianwuk ,

The key is [update a row] is to match the row that needs to be updated through the key-value pair.

Now there are five such key-value pairs that can be matched. But it will only update the row on the first match.
I mean, I want you to change the key column. This column has unique properties and will not be duplicated with other rows.

Best Regards

Cheng Feng

[ianwuk]

Thank you @v-chengfen-msft @Rhiassuring I got it working.  It was indeed the key column and key value fields that I did not quite understand.

Update - The issue below is fixed.

I have another issue, This flow outputs an Excel table to an HTML table and then sends an email.

The problem is that the Excel column displays the date in the email as a timestamp.

How can I format column date to just be DD/MM/YYYY?

In the HTML table, to get the value from the spreadsheet from inside an array I use item()?['Start date of Night Shift'].  Can that formula somehow be updated to also format the date and time format?

Thanks so much!