Heavy Form entry traffic + Complex column formulas = Inability to SAVE manual sheet changes

Jeff Reisman

Story time. This is a long one, so buckle up!

This post is for anyone who might be having issues with saving changes on sheets with complex formulas while many form entries or new rows are being created. (TL;DR summary and the real Discussion Topic at the bottom!)

Last week, I put together a small Smartsheet solution utilizing our warehouse scan guns. This was built off the cuff to capture bin locations, model numbers, and serial numbers from many bin locations in the Warehouse for serial data reconciliation. There were two main sheets. "Serial Collection" was where the staff recorded each Serial #, its Product Code, and Bin Location. "Model Count" was where the staff recorded Bin Location, Product Code, and their physical count of each of those products in the bin. So while one person created a row for each Serial # in the Serial Collection sheet, another person counted each of the boxes of a particular product in a given bin location and recorded those in one row in the Model Count sheet. This Model Count sheet would then count all the rows in "Serial Collection" where the bin location and product code matched, in order to determine if the number of serials collected for each product in each bin matched the physical inventory. Due to differences in labels and barcode data on boxes for different types of products from different factories, the Serial Collection sheet had some columns running data transformations to attempt to automatically extract certain data. I also had columns comparing serial number length with the average serial number length for the same product code in order to error check, and also running counts for each product code. So there was some complex stuff going on in the sheet.

The request for this build came Wednesday and I built it Thursday, before the Memorial Day weekend, so there was not time to put this to any significant load test before the staff was scheduled to use it on Saturday.

Serial Collection:

Model Count:

Saturday mid-morning I received a text message from a warehouse supervisor indicating that he was unable to save changes directly in the Serial Collection sheet. (I had my toes in the sand watching a ski/wakeboard tournament, but fortunately I had my laptop in the car, with a cell phone hotspot and a power inverter.) 

 It took a bit to figure out what was causing the issue. The warehouse staff was still able to scan their barcodes into the online Smartsheet form and instantly save each record. I suspected that the number of entries coming in at one time was taxing a few of the formulas built into the sheet to transform and/or error check some of the data. However, with all the records pouring in, I couldn't get enough resources to save changes to formulas or save removals of columns/formulas in order to reduce the processing load!

I asked the supervisor to have the staff stop scanning for a few minutes, and after about 10 minutes he confirmed they had stopped. But the strangest thing was going on: new record entries were still appearing, sporadically, in the sheet! How was that possible? He confirmed for me again that data collection had ceased. After a little while longer, it dawned on me that this was actually related to an issue I helped Smartsheet resolve back in late 2019.

 In late 2019, I built a BOL (Bills of Lading) Management System for the Warehouse staff in Smartsheet. Each morning, using a USB barcode scanner and a Smartsheet form, the Warehouse admins would scan in several hundred BOL entries for tracking purposes. The problem arose when the sheet they were scanning into hit about 1000 rows. It started taking longer and longer for the form to save each new entry; what had been taking 1-2 seconds was now taking 10, 20, 30 seconds or more, making it take far too long to scan the entries. Smartsheet support was unable to figure out what the issue was. To solve the issue from my end, I created a BOL Entry Sheet 1 for collecting the form entries, and used an automation rule to move new rows to BOL Entry Sheet 2, and then a rule on that sheet to move the rows into the main BOL tracking sheet. By leapfrogging the entries, each new entry was saved into a sheet with at the most only a couple of rows in it, and then were quickly able to move from there to another nearly empty sheet, where they could move more slowly into the larger BOL tracking sheet. Now the warehouse admins were back to 1-second submit times on the form. Once I figured this out, I spoke with some higher tier support at Smartsheet and suggested that Smartsheet forms should incorporate a shadow table or some sort of buffer for saving form entries instantly even when the underlying sheet is getting heavily taxed. Smartsheet apparently had rolled a fix out at some point, as evidenced by the users on Saturday being able to keep saving form entries instantly, and then those form entries flowing into the heavily taxed sheet every 15-120 seconds until they were caught up. 

 So now I knew why we couldn't make any changes to the underlying sheet.

Once the Serial collection form entries were caught up, I was able to remove the formula columns from the collection sheet. I created two additional sheets, "Serial Collection Landing" and "Serial Collection Landing Final", recreated the formulas on the Landing Final sheet, and put the same type of leapfrog entry automation in place. By using the automation, it adds a minute or two between form entries and the new rows appearing in the final sheet, giving some time to save manual changes to the final sheet. I moved the rows that had been created thus far into the Landing Final sheet. I also pointed the formulas on the Model Count sheet at the Landing Final sheet for count comparisons.

TL;DR - When needing to make rapid, multiple form entries to a sheet with complex formulas and/or a lot of rows, consider leap-frogging the row entries with Move Row automation from an initial entry sheet, to an intermediary sheet, and then to the final sheet containing the complex formulas / many rows.

Now for the true Discussion Topic - If a sheet owner does not leapfrog their entries, how can Smartsheet offer a "safety switch" on an overtaxed sheet to allow data or formula changes while new entries are pouring in? What if the sheet is so taxed that the owner/admin cannot even open it?

I'm thinking of an option in the context menu (for sheet owners/admins only) when right-clicking on a sheet name in Browse, which would allow them to effectively HALT ALL formula execution within the sheet. All formula cells would display as blank, the underlying formulas would effectively be "commented out" (in development parlance) temporarily. I have no idea how this might be done programmatically, but in a situation like the one I had, this would allow an owner or admin to gain enough resources to wrestle back control of the sheet and change or remove the resource-hogging formulas, in turn allowing sheet editors to make corrections to collected row data while not interfering with new data collection. (Maybe this just applies to column formulas? Seems like column formulas maybe have a single point of reference in the code, rather than a reference for every cell in a column - I don't really know, just spit-balling.)

Other ideas / Questions / Comments / Opinions?