Using A Flow To Change An Excel Cell From One Value To Another

[WmSWoodson1]

Hello:

I'm in the middle of trying to create a Flow that will send a recipient an email whenever a new item gets added to a table called "Adjustments."  However, the Flow is set to run every 15 minutes, since there are multiple locations/users that will use the file, and the data could be updated multiple times per day.  My issue is that one of the columns in the table is labeled, "Status."  This column is validated to only accept "New," "Pending," or "Processed" as the possible options.  In the flow, I'm comparing a "Date" column to today's date, which is automatically entered when an employee's name is entered in the "Employee" column.  So that the recipient of the email doesn't get the same email multiple times, I tried using the Update a row action.  However, I can't seem to get the parameters correct.  I'm trying to automatically change the "New" status to "Pending" and change the date to a date in the past or completely "wipe it out" by defaulting it to "0000-00-00," so that the Flow doesn't send another email with the same information.  The Flow worked correctly with the first entry, but has failed every time since then, telling me that "Action 'Update_a_row' failed."

The Flow is still sending the email as expected, but after the email gets sent, the row isn't being updated, so that, when the Flow runs 15 minutes later, it sends exactly the same information.  

 

Here's my Flow, so far:

[grantjenkins]

One important aspect of updating rows in an Excel Table is that each row needs something to uniquely identify that row. This where the Key Column and Key Value is used. The Key Column needs to be a column that uniquely identifies that column based on the value. Looking at your current Table there isn't anything that gives you that.

My suggestion would be to add a new Column to your Table and call it ID. Then set a formula in that column that returns the Row number which will always be unique. In my example below I've created a new Column called ID and set the Row formula to look at the column Status.

=ROW([@Status])

Then in your Update a row you would use ID for the Key Column and get the value from the ID value returned from List rows present in a table for the Key Value.

[WmSWoodson1]

I did as you instructed to add the ID row.  However, even though this file is saved in OneDrive, and my table has been re-sized to include the new column, Flow isn't picking up the new column in the Update a row dynamic content list.  Also, I must be missing something else because I'm not sure how I'm supposed to get the value from the ID Value as you instructed.  The "value" I'm pulling into the Apply to each action, to generate an email before I do the Update a row, is today's date...

[grantjenkins · Accepted solution]

For the flow to pick up the new ID column you'll need to save your flow, close it, refresh the browser, then come back into the flow and it should now appear. It caches the schema, so a refresh is required.