(An attempt at) Solving the multi-user, bidirectional data flow problem

In this blog, I will explain an interesting problem I encountered while trying to build an automation script with Google Sheets and Apps Script. I believe the final solution I propose is the best way to solve this problem, but if there are any other workarounds, always open to suggestions!
Reply to this blog or write a mail to namyalg@gmail.com :)

The context: High-Level analysis

Imagine a hypothetical situation where XYZ Company has a Central Inventory Management Unit A (CIMUA). There are many retail stores under XYZ to which groceries are supplied from this Inventory Unit A. If the need arises groceries can be supplied from one retail store to another. It could also occur that some retailers can source groceries from other inventory units CIMUB, CIMUC, CIMUD, etc.

Considering the ease of use, it is decided that Google Sheets is the database to manage the inventory moving in and out of CIMUA and the retail stores.

Spreadsheet requirements:

1. A sheet for each retail store and the groceries it has
2. A single separate sheet for the CIMUA, containing consolidated information about all the various groceries each retail store has

Functionalities:

1. The groceries sent from the CIMUA to retail stores must be updated, i.e for each transfer of items from the inventory unit to the item should be added to the retail store spreadsheet
2. If groceries are sent from retail store D to E, then in the spreadsheet of D, the item should be removed, and added to E. In the inventory spreadsheet, the change needs to be updated
3. If groceries are sourced from other inventories like CIMUB or CIMUC to a retail store, then it needs to be added to the spreadsheet of the retail store and added to the inventory spreadsheet
4. It is also possible that Retail Store D can source the items of Retail store E from other inventories like CIMUB or CIMUC, in such a case, the inventory is initially added to the sheet of Retail Store D, it needs to be removed from retail store D’s sheet, added to Retail Store E’s sheet and added to the inventory Sheet

ASSUMPTION: Data is entered manually into these sheets. The removal and addition of items are done manually by the employee responsible for that grocery item

The code: Low-level analysis

As stated earlier, Google Sheets in the database, and this process is supported by Google Apps Script.

David Weiss has amazing content on YouTube to learn about Google Apps Script : https://www.youtube.com/c/DavidWeissProgramming

At a lower level :

• Data can move from the Inventory Sheet to the Retail Store Sheets (A grocery item is moved from the Inventory Unit to a Retail Store), Retail Store to Retail Store sheet (An item is moved from one retail store to another), Retail Store to Inventory Sheet (If a retail store sources items from somewhere else, it needs to be updated in the Inventory Sheet)
• As there are many employees there can be concurrent writes to the same sheet

It is required that the ACID — Atomicity, Consistency, Isolation and Durability properties are not violated. In our case, it is very important to maintain data consistency

Approach to implementation

Google Apps Script supports JavaScript. Files with a .gs extension are present, in which vanilla JS is to be written.

All data transfers

The first step was to come up with a mechanism of how the data transfers are going to happen. I chose a Master/Slave architecture, where the Inventory Sheet was the Master and the Retail Store Sheets were the Slaves.

• If there is an update in the Master Sheet, the changes propogate to all the Slave Sheets
• If there is an addition to the Slave Sheet, the changes to are propogated to the Master Sheet, then from the Master Sheet, the changes are propogated to the other sheets

When there are changes in the Master that need to reflect in the slave sheets, there is a direct downward movement of data

Master to Slave update

When there is an update in the any of the Slave sheets, a 2-step process is followed, the Slave updates the Master, and these changes are propogated from the Master to all the Sheets. This is done to facilitate the transfer of items from one Retail store to another and to handle the case when Retail Store D can source the items of Retail store E from other inventories like CIMUB or CIMUC

Step 1 : Update Master from Slave

Step 2 : Propogate changes from the Master to all Slaves

Implemenation

The functionality is fairly easy to achieve, but an important issue to address here is that of concurrent writes and data consistency across all operations.

If carefully observed, there is a chance that may lead to a circular writes, i.e when the Master Sheet Updates and the Slave Sheet updates, this could lead to a problem. I tried using the Script lock as described in the documentation for Google Apps Script. I observed that it did not work as expected.

Circular concurrent writes in both directions from the Master to Slave and Slave to Master

Possible updates

I faced an issue of overwrites when there were updates from 2 Slave Sheets into the Master Sheet. When the Master Sheet updated and Slave Sheet updated simultaneously, the data was not consistent

What is finally working : The idea that I am finally sticking to is, to allow the update of only one Sheet by a single person. This means, at any given point of time, either of the Master or one Slave Sheet trigger an update and this can be done by only one person. As soon as an update is triggered in one of the sheets, all the other sheets become read-only for the time taken to propogate changes. Once it is completed, the updates and edits can be made.

My solution is to allow a SINGLE UPDATE (only one user can update a sheet) to a SINGLE SHEET at any given point of time

The efficiency of this approach might be questionable, but maintaining the consistency of data is of more importance here. I will share more on the implementation soon!