Can sync conflicts happen with row edits from API invocation

[billyfw]

Hey guys - 

I've been trying to take to heart the idea that my gsheet backend shouldn't update any data directly that is being used for an app, and thinking through a design for how to have my gSheet run a bunch of code to calculate a handful of very complicated field updates (more complicated than I want to implement in AppSheet - maybe impossible, i'm not sure), and then send the edits to the app API.

So here's my question:  

If I use the API to call edit for a handful of rows and change some field values, those edits are made on the server (not my app client), correct? 

If so, what happens if, prior to a sync happening, the app user changes a value which was also changed on the server by the API call? And, is there a way to prevent this? How should I think about edits via the API (and ensuing syncs and data consistency)? 

Thanks much,

Billy

PS-An example chain of events to illustrate my question: 

1. App API is called to edit the Middle Name field on Billy W row from Smith to Johnson. 

2. Prior to the next sync (because the app is offline or just timing or whatever), an app user manually changes the field from Smith to Jones. 

3. A sync runs, and the server row has middle name Johnson, but the app has middle name Jones. Sync conflict? What happens and is this preventable? 

Thanks everyone!

[WillowMobileSys · Accepted solution]

What happens if two edits are sent to the server at nearly the same time?   There is NO Sync conflict....Last one in wins.   

How can you prevent this? 

Technically speaking you cannot prevent it in a systematic way.   AppSheet is a distributed system meaning that each "device" gets its very own copy of the data.  Changes are made by a user oblivious to what any other users are doing.  Edits are then simply submitted by the client and applied to the data - no inspection of any other edits being made is performed.

The good news is that most apps are segregated such that two users making changes to the same row at nearly the same time is very rare.

But if your app is one where changes by users frequently collide, there would need to be either some restructuring of data to make it less likely for a collision OR change in user procedures that make it unlikely two users are updating the same row.

For example, maybe a table has a large number of columns and different users are typically changing certain areas of the rows creating some logical division of the row.  You could divy the table by the logical areas into 2 or more tables then users would be editing different rows in different tables preventing any collision.

Another example might be assigning areas of data responsibility.  Employee 1 changes orders for clients A-M while Employee 2 changes orders for clients N-Z.  

[billyfw]

Many thanks and makes sense. I was a bit afraid there would be some sort of sync error that could get the app into a bad state. Last in wins makes sense as a resolution, as do the strategies to prevent such collisions. Much appreciated!

[dbaum]

While there may be benefits to routing your data updates via AppSheet API rather than making them directly in the sheet data source, keep in mind that you're still revising data in a manner essentially external to the app itself--as opposed to, for example, triggering the data change from within the app even if that data change is still implemented via the AppSheet API. So, there's still increased risk of both a user and your external trigger editing data simultaneously--that is, "increased" relative to having an intra-app user action that initiates the data updates, in which case at least for that user subsequent updates to the same data would be committed only after completion of the automation processing the updates via API.

[billyfw]

Thanks dbaum - I thought about this a bit, and I couldn't quite figure out how to do it. I might be missing something simple though since I'm still getting used to everything. 

It seemed like the only way I could fire a webhook was through a bot - not from an action in the app. So, my plan was that:

1. A user creates a record in the app

2. I have a bot watching for that record ADD which and that triggers a call a webhook task to my external system to figure out a bunch of changes that need to be made

3. My external system sends those changes back via an AppSheet API Edit. 

Is there a better way to do that, via an action in some sort of synchronous process after the user action, vs. a webhook on the server? 

Thanks for all the help from the community. 

[dbaum]

Indeed the only way to invoke the AppSheet API or an Apps Script script from an app is via a task within an automation, which, as you note, runs on the server. In case it's helpful: An automation can be triggered by lots of types of actions beyond adding a new row.

It's not necessarily helpful, but at least theoretically your automation could have a Call a script task that returns the data you need for the automation to then log via a Webhook task that invokes the AppSheet API.

The one benefit might be if you can semi-occupy the app/device of the user most likely to submit an out-of-date record by tieing it up with syncs associated with its triggering of an automation.