Excel Update a Row Doesn't Work When Sheet Has a Filter Enabled

[smilstea]

Hi,

I have an excel file in sharepoint I am updating through the "update a row" action. My rows are metadata for our ticketing system with each ticket having one or more rows (rmas and bugs etc each get their own row for the ticket). I have a control column I use which is the timestamp when the case was created plus a random integer. When the status of the ticket, among other metadata, changes I trigger update a row to change that value.

My problem is that if a user has filtered the table on the web view and the ticket is filtered out then my flow won't update that row. I tried to use a script in excel in my flow to first clear the filter and wait 60s but even if I do that the next action which is to list rows does not find any tickets. My current workaround is to protect the sheet and allow edits but not filtering. That is upsetting many people. I have also tried having users use different views to filter and make their edits then merge them but they are complaining of that too and I'm concerned if they merge the edits they might overwrite changes from power automate on the default view.

What options are there to allow editing and filtering and for power automate to work on filtered rows. 

[DarmonGill]

Hi,

Here are some suggestions to work around this problem:

1. Using Excel Table:
Convert your data range into an Excel Table. Tables in Excel are more versatile and often work better with Power Automate.

Convert your data to a table: Select your range, go to the "Insert" tab, and click "Table."
Use Table references in Power Automate: Instead of referencing specific rows, use table references such as Table1 or the table name.
2. Filter in Power Automate Instead:
If users rely heavily on filtering but it's causing issues, consider applying the filtering logic within Power Automate itself rather than relying on Excel filters.

Use the "Get rows" action with a Filter Query parameter to retrieve only the necessary rows based on specific criteria.
3. Clear Filters Before Updating:
As you've tried, clearing filters before updating the rows might work. However, the timing and process might need adjustment.

Try using the "Clear filters" action in Power Automate before updating the row.
Consider adding a delay after clearing filters before the next actions.
4. Different Approach for Filtering:
Explore if there's a different method users can use to filter the data that won't interfere with Power Automate updates.

Custom views or filters within SharePoint itself might be worth exploring as an alternative to Excel filtering.
Educate users on the impact of Excel filters on the automated processes and suggest alternate methods.
5. Error Handling:
Implement robust error handling within your Power Automate flow to catch situations where the row isn’t updated due to filters. Send alerts or notifications for such occurrences so they can be manually handled.

6. Testing and Monitoring:
Regularly test the flow under various conditions to ensure it works as expected. Also, monitor the flow's execution history to identify failures caused by filtered data.

7. Collaboration and Communication:
Continuously communicate with the users to understand their needs and any issues they're facing. Collaboration and feedback might lead to a better solution that suits everyone's requirements.

br,

Gill

[smilstea]

1. I am using a table in my excel for power automate.

2. I use OFilters for finding the right row(s) but other users have filters on the sheet enabled.

3. Even with a 60s timer this didn't work. I cannot add a lot of time as I'm having hundreds of updates per day. Could I wait longer? Maybe? But it's not scalable and not a real solution. 

4. Custom views is the current workaround, if my default view has a change from a power automate flow will it automatically be reflected in all other views or is a view like a snapshot? My concern is if the user saves a non default view as the default but the default has a flow that updated it but not the non default view. The problem is data consistency. If the non default is updated by flows updating the default view then I'll ask the users to use this. If it's going to cause data inconsistencies then I need another solution.

5. I have this setup now to detect an empty body of update a row, but I want to avoid manual work to correct data. 

Thanks, 

Sam

[DarmonGill]

1. You mentioned that you are using a table in Excel for Power Automate. If you are having trouble finding the right row(s) using OFilters, you can try using Filter Query instead. This will allow you to filter rows based on specific criteria. Here is a link to an example of how to enter a filter query for Excel list row in a table.

2. If other users have filters on the sheet enabled, you can use AutoFilter to filter your data. Here is a link to Microsoft Support’s guide on how to use AutoFilter.

3. You mentioned that even with a 60s timer, your current solution didn’t work. Waiting longer may not be a scalable or real solution. Have you considered using parallel flows? This would allow you to run multiple flows at the same time, which could help you process hundreds of updates per day more efficiently.

4. Custom views is a good workaround for your current situation. If your default view has a change from a Power Automate flow, it will automatically be reflected in all other views. However, if a non-default view is saved as the default but the default has a flow that updated it but not the non-default view, it could cause data inconsistencies. To ensure data consistency, you can ask users to use the default view that is updated by flows.

5. You mentioned that you have set up a flow to detect an empty body of update a row, but you want to avoid manual work to correct data. Have you considered using conditional statements in your flow? This would allow you to automate the process of correcting data based on specific criteria. Here is a link to a guide on how to filter Excel table rows in Power Automate using text, numbers, and dates.

Gill

[smilstea]

1. Sorry I am using filter query but when the web version of the excel has a filter applied any rows filtered out will not match. This means I cannot allow users to use filters on the sheets default view as I don't know when automation will try to update values.

2. This does not help. 

3. Parallel would make this worse as I need to wait for the clearing of filters to apply before taking any other action. Again not scalable and inconvenient to users. 

4. Can you explain further? If I have a flow or manual interaction to update the default view then is there a delay in updating other views? This seems to be my only valid workaround when filtering is needed. 

5. I guess I could add a timer to the conditional statement to clear the filter but this is a hack and not a good solution. 

How do I allow filtering on a worksheet, allow automation to update any row filtered or not, allow manual edits, and avoid race conditions from things like clearing filters.

I should note I was using a protected sheet but I kept running into graph api errors where I can't add rows or edit rows, I have tried all the workarounds from disabling filters before protecting, to selecting the entire table to protect, etc and automation fails. So now I have to trust the users not to filter and use views to update which means more work on me when things break and possible data corruption issues.