Row vs cell updates

[fdmsaraiva]

I just ran into a use case that left me with missing data and I’d like to confirm if what happened really is the expected behaviour or if something is amiss.

Context and sequence of events:

• I have a package delivery app for delivery drivers, whose database is a Google Sheet
• My company delivers between 100 and 200 packages every Tuesday
• I have an email workflow to let customers know their package is en route, triggered by manually pressing an action at the start of the delivery day
• This creates between 100 and 200 updates that take several minutes to process
• Each update consists of changing the value of a single boolean column
• This week, the person who clicked the action did so from a mobile device, probably ran into network issues, and the sync stopped with 88 updates left at the beginning of the delivery day
• At the end of the day, when most of the deliveries had been completed, the sync resumed and the 88 updates finally made it to the google sheet

What happened then is that when those 88 updates synced, they not only wrote the expected value in the one boolean column related to the workflow, but they also reverted all other columns to the values that were present in the sheet in the morning, erasing all traces of the deliveries that had happened throughout the day.

When the end-user of an app updates the value of a column, does the update write just that one column, or the whole row as it was before the update + the updated value in that column?

[tony1 · Accepted solution]

Hi @Filipe, I’m sorry about the data loss. I can explain what happened and give a suggestion about how to refactor your app to prevent that.

As you suspected, changes to your data happen per row, not per cell. So the change that is sent from your device is the entire row of data from when the action runs. That means that other changes to the row are going to be overwritten, in accordance with the “last writer wins” policy https://help.appsheet.com/users/concurrent-usage-with-multiple-users

One workaround you might consider is splitting your table into two separate but related tables. So for example, you might have a master table of packages, then a related table of delivery info. Your action could set a flag on the master table, and then when the package is delivered, a related row could be created on the deliver info table. I know that this isn’t ideal, but it’s a consequence of how AppSheet does updates.

I’ll forward this thread along to my colleagues to see if they have any ideas or suggestions.

[fdmsaraiva]

No worries @tony, I could rollback the sheet to a point before the data loss without much hassle. I just always assumed Appsheet updated individual cells and was hoping it was something on my end I could fix.

Thanks for the suggestion, I may give it a try.

Would Appsheet always update whole rows even using a MySQL database, or is it strictly a constraint of the Google Sheets API?

Cheers

[tony1]

@Filipe The row-level updates happen independent of the data provider. So the same thing would happen with a MySQL database.

[fdmsaraiva]

Ok, all clear @tony, thanks.