Linked spreadsheet: Never overwrite existing data

[matthijskooijman]

I previously posted this as feedback on the PR before it was applied (#1758 (comment)), but since it was out of scope for the initial PR, I'm reposting this here.

Nextcloud:

• Nextcloud-Version: 28.0.1
• Forms-Version: 4.0.0 with PR Added possibility to link spreadsheet for automatic submission export in multiple formats #1758 applied

Is your feature request related to a problem? Please describe.

Usecase
The usecase I want to implement is a logbook for work on the electrical installation in a building. We currently have a spreadsheet in Nextcloud with a line for each change made, with some details about what, where, who, when, etc. To lower the bar for people to enter something in the logbook (and importantly, to allow unauthenticated users to add but not edit entries), I want to use a public form that adds entries to this logbook (and then put up QR-codes in the electrical cabinets so people can find the form).

Overwriting data
Currently every time a new answer is added, the field names and all previous answers are also written to the spreadsheet (again). This means that I cannot:

1. Manually add a row in the spreadsheet - it will be overwritten by the next form response
2. Edit any form data in the spreadsheet (fix typo's, add extra info) - it will be reverted on the next form response
3. Add extra rows above the table with some instructions for users of the spreadsheet
4. Insert extra columns in between the result columns or reorder columns (adding extra columns to the right of the results works already).

The first three are problematic for my usecase - the reason I want to export to a spreadsheet is so I have the logbook in an easy to manage and edit form, and also so people that are more familiar with the tech can just add their entries directly in the spreadsheet. Problem 4 I added for completeness, fixing it would be nice but also easy to work around by adding extra data to the right if needed.

Describe the solution you'd like

Just add only values for the new entry on the first empty row in the spreadsheet, never modify any existing content. This also means that the header row with column names should be written only when the sheet is completely empty. This should fix problem 1-3 above, but leave problem 4 unfixed.

As a side effect, this does mean that if the form is modified to e.g. add extra questions, the column headers become outdated (and also old and new answers might put the same answer in different columns). Also, it leaves problem 4 unfixed. This could maybe be fixed by doing a column lookup first: When writing a new answer, look in the first row for the corresponding column name and write the answer into that column. If the column name is not found in the first row, write the column name to the first empty cell in the first row, and then write the answer in the same column. This would fix problem 4, but unfix problem 3 (since the column headers must now remain in the first row), unless we can somehow tell the forms app in what row the column headers live (using a configurable row offset, or maybe some magic tag value in the spreadsheet, not sure if that can be done invisibly to the user)?

Personally, I suspect that the extra complexity of fixing problem 4 is not worth the trouble, so it should be fine to fix just 1-3 and leave 4 unfixed. The most important part is that then existing data is never overwritten, so any weirdness in column layout will be visible to the user and they can just fix up things manually or adjust their workflow.

Challenges

One potential challenge with my proposed solution is that atomicity of writing data to the spreadsheet becomes more relevant. If each for submission only writes one entry, and two submissions happen at the same time, there might be (depending on implementation - I have not checked) a risk of one submission overwriting the previous one, dropping one entry. With the current code, where all entries are written, this risk is minimal (even if the first submission overwrites the data of the second submission, then a later third submission will write the second response again). This should probably be solved with some kind of locking to make sure that no changes are made to the spreadsheet between loading it from storage and writing the updated file.

[estux]

Different usecase but similar requirements. This would be a total gamechanger for us too.
Maybe the point 4 could be resolved with a static filter (to setup the first time and editable in the future) to map the fields of the form to the columns headers. And the same or similar filter could be used to link Forms with Tables.

Podio already does something similar to connect webforms and tables and I find it really useful (but we inherited the tool and we are not using it anymore as we believe in open source and data privacy)

[jospoortvliet]

Yeah, we also bumped into this - wanted to use Forms for signing up to something, but then people still should be able to go into the spreadsheet and make manual changes. So only the last line should be added when a new entry is created - without overwriting data - and ideally, it also works while a live editing session is happening. Right now, when editing in Nextcloud Office, any changes are just lost and you're not even warned of what happened.

Maybe in the mean time, a warning could b e added in the user interface that this file will get overwritten and users should not make manual changes while the form might still be used.

[AIlkiv]

@Chartman123 I researched this issue. My results:

1. Manually add a row in the spreadsheet - it will be overwritten by the next form response

Here then it is necessary that when you manually add a record that it duplicates in the Results of the form, otherwise there will be different data in the Table and in the Form/Results.

Or, in my opinion, a better option is to allow admins (or special permission) to add multiple answers to the form, so that it is done through the form.

2. Edit any form data in the spreadsheet (fix typo's, add extra info) - it will be reverted on the next form response

This destroys the credibility of the data. Because it is impossible to be certain that the data was entered by the author or by someone who has access to the file.

Now it can be done very simply. Create two sheets Source and Work. Data is entered automatically in Source. In Work, the function copies all the necessary cell. What needs to be redefined, you write down your value instead of a function.

3. Add extra rows above the table with some instructions for users of the spreadsheet
4. Insert extra columns in between the result columns or reorder columns (adding extra columns to the right of the results works already).

Create two worksheets, Source and Work. In the Source worksheet, data is entered automatically. In Work, the function copies all the cells you need in the order and design you want.

[matthijskooijman]

@AIlkiv Thanks for your response. I can see your reservations about the credibility/authenticity of the data, but:

• For my particular usecase, this is not a consideration at all. For me, the spreadsheet is the primary storage and intended to be directly edited, and the form is just an extra way to let other people add data (without allowing them to modify data).
• If you want to authenticate the data, the current implementation does not do this. If someone changes the data in the spreadsheet, it will remain modified until the form is submitted again. The only way to really know what the original form submission was, is to look at the form answer database, and that way is still available with the changes proposed in this issue.

Now it can be done very simply. Create two sheets Source and Work. Data is entered automatically in Source. In Work, the function copies all the necessary cell. What needs to be redefined, you write down your value instead of a function.

Create two worksheets, Source and Work. In the Source worksheet, data is entered automatically. In Work, the function copies all the cells you need in the order and design you want.

This is a way that I had not really considered like that, and would indeed be a way to make this work. It is a bit clunky (and maybe fragile), in having to explain to people where and how to make modifications, and resulting confusion if someone switches to Source and the next user entering the document on the Source sheet (while the instructions are in the Work sheet). So it is usable (provided that #1971 is fixed), but it is not ideal.

[AIlkiv]

@matthijskooijman #1971 is fixed. Please check and close that issue.