Expected (desired) behavior when storing data in a spreadsheet

[DmitrySharabin]

For now, when we store data to a spreadsheet, we update the overlapped data but not remove the existing one. What do I mean by that? Suppose I have a todo list that I saved to a spreadsheet, and it became the data source for my app. The next day I remove all the items from the list and save the list again. I expect that all the data were erased from the spreadsheet, but they are not since we have no data to store after deleting everything from the app. We envoke Google Sheets API without providing any data, and the spreadsheet remains untouched.

Another case when I deleted a couple of todos, updated some of the remaining, and saved them. After refreshing the page, I get the mix of updated data and the ones that were "deleted" since, in reality, we delete nothing but only update the existing data that are overlapped with the new one.

Another thing to consider is that there might be other data on a sheet (e.g., for/from another app). So we can't simply, for example, delete the sheet with the old data and create another for the new one.

The described situation is not a problem for apps where data is not deleted but only updates and adds.

@leaverou, @karger Will you please share your thoughts on the expected (desired) behavior for the GSheets plugin.

[LeaVerou]

Firstly, data should definitely be deleted from the sheet when it's deleted in the app. However, if the app behaves essentially like pasting cells, it sounds like if you add rows it would overwrite anything that's underneath? E.g. if I have a table of 200 rows, and I specify mv-storage-range="A1:C100", what happens when I add rows?

Is it possible to delete/add cells and have the other cells be shifted up/down? You can do it in the UI, I'm not sure if the API allows it.

[karger]

Verifying: if the storage is specified as a range of columns, to let me manage a collection of arbitrarily many items, then when I write, I should effectively write all the rows.  ie, if the number of items decreases, I should write blanks into the now-empty rows.

followup: can I specify rectangular ranges, e.g. a1:C100?  and what are the consequences?  Is this asserting that the collection is not allowed to have more than 100 items?  This doesn't seem useful on its own, but perhaps it is important if the mavo is only one part of a larger spreadsheet context.

On 12/11/2020 3:35 PM, Lea Verou wrote:

Firstly, data should definitely be deleted from the sheet when it's deleted in the app. However, if the app behaves essentially like pasting cells, it sounds like if you /add/ rows it would overwrite anything that's underneath? E.g. if I have a table of 200 rows, and I specify |mv-storage-range="A1:C100"|, what happens when I add rows?

Is it possible to delete/add cells and have the other cells be shifted up/down? You can do it in the UI, I'm not sure if the API allows it.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/DmitrySharabin/mavo-gsheets/issues/1#issuecomment-743413677, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAIWSXTQKJR55BUVMSZQO3DSUJ7BFANCNFSM4UW6OVZA.

[LeaVerou]

Yes, you are allowed to specify such ranges.

[DmitrySharabin]

Thank you very much for your thoughts and questions.

I implemented data deletion. So now, the data-saving works as it should.

E.g. if I have a table of 200 rows, and I specify mv-storage-range="A1:C100", what happens when I add rows?

can I specify rectangular ranges, e.g. a1:C100? and what are the consequences? Is this asserting that the collection is not allowed to have more than 100 items?

When we work with rectangular ranges like A1:C100, we are limited by this exact range. That means if we try to save data that has 101 rows and/or 4 columns, we won't be able to do that—the Sheets API will block this operation.

Unfortunately, since the write request comes after the clear request, the data in the A1:C100 range will be cleared. Luckily, we have the versions in the Google Sheets so we can recover the needed data. And yes, I know that's not the way it should work. 😔

I'll try to find a way to solve this issue and perform the clear and write requests in one "transaction," so data is written only if both operations succeed. However, I am not sure whether it is possible (I searched for it but couldn't find it yet).

Is it possible to delete/add cells and have the other cells be shifted up/down?

Unfortunately, the API doesn't allow that. We can append data to a spreadsheet by inserting new rows. But the algorithm used in this operation can break data we already have in the spreadsheet and are not used by either mavo app. And not only because the append operation will insert the whole rows.

In apps, we can use adjacent ranges separately, and data in them won't interfere if we specify ranges. But the algorithm used by the append operation tries to find a place where to insert data analyzing the whole sheet, not only the specified range, so the adjacent ranges will be interpreted as they belong to one table. And the insertion point will be found incorrectly. And that may cause data loss.

[DmitrySharabin]

And one more thing concerning ranges. If we use ranges other than rectangular, we are not limited by sheet bounds: if there are not enough rows/columns to store data, they will be added automatically. So we can start with a sheet with only one cell. 😎

[DmitrySharabin]

Unfortunately, since the write request comes after the clear request, the data in the A1:C100 range will be cleared. Luckily, we have the versions in the Google Sheets so we can recover the needed data. And yes, I know that's not the way it should work. 😔 I'll try to find a way to solve this issue and perform the clear and write requests in one "transaction," so data is written only if both operations succeed. However, I am not sure whether it is possible (I searched for it but couldn't find it yet).

Not an issue anymore. I believe I could found the solution.

Unfortunately, the API doesn't allow that.

Actually, I'm mistaken. The Google Sheets API allows us to perform almost any action we need. Of course, it might not be very straightforward, but still. I'm going to leave this here as a reference for future use: https://developers.google.com/sheets/api/reference/rest.