Nov 04 2020 04:23 PM
Hi there!
I have a web-based OneDrive form which records responses in an Excel sheet automatically. I would like to have results be sorted such that the newest results populate at the top. In other words, I need the table to be automatically sorting by the sequential values that are automatically generated in Column A when someone submits a response to the form.
I've gotten this to work with the "=SORT" formula, but the problem is that I need the sorted data to be editable, and the Sort formula just produces a reflection.
I have a script set up to sort the column manually when triggered. Maybe there's a way to trigger the script to run automatically when new data is entered in that column?
Any ideas?
Nov 05 2020 12:26 PM
Alternatively I'd do not touch updated by Forms table but use for the reporting another range/table taking data from source. As variant
=SORTBY(Table1[#Data],Table1[Date],-1)
Nov 06 2020 09:12 AM
@Sergei Baklan thanks for your response! Sortby results in the same issue, that I need to sort the original table since the contents on the sorted data need to be editable.
Nov 06 2020 01:46 PM
I see. To change behavior of the agent which adds rows to table is not possible, it always adds new rows to the end of the table and do nothing more.
And I'm not sure if Office Script could be triggered by adding new row to the table, but who knows. Another issue here is conflict resolution if agent, script and person who edits work simultaneously.
In general it depends what and how often to edit. My usual scenario is to Power Query such forms table, combine with our data sources, transform and clean data and return back to user in another file. Max for manual editing is to add column returned by query, for example for comments, it is syncing with known technique by querying return by query table with itself and merging inside to return result.
Such approach has limitations, but could be used in some scenarios.