If you use some of my libraries, you may have come across the Fiddler class, which abstracts sheet data and provides a more functional way to manage data from multiple sheets in a Google Spreadsheet. A common problem when manipulating sheet data is knowing whether you’ve updated anything, especially if the processing is in discrete functions. Fiddler has a built in way to track not only changes to an entire sheet content, but also to collection of columns. Knowing if you’ve changed anything can not only minimize writing unecessarily, but also avoid processing paths that should only happen following certain changes.
A skeleton version of this is incuded in the template described in How to use Vue.js, Vuex and Vuetify to create Google Apps Script Add-ons
Getting started with fiddler
There are plenty of tutorials on Fiddler generally, and also a video introduction, so I’ll assume you’re already using it or will get up to speed with the fundamentals. In this example, I have a number of discrete processing steps over a collection of sheets, and at the end of it all I want to write back any sheets whose data has changed.
Let’s start with these 3 useful functions. I’ll deal with what the ‘noCache’ parameter is for in a later post in this series.
Getting the fiddler
Populating the fiddlers
Fiddler just needs a sheet object – it’ll take care of reading and objectifying it. Fiddler works entirely in memory, so it’s super fast for inserting lines, reorganizing the spreadsheet structure etc. In this App, I need the data from 5 separate sheets
Processing the fiddlers
For the moment it doesn’t matter what these functions do, except to know that they may or may not update the spreadsheet data in some or all of the sheets they reference
Writing only sheets that have changed
fiddler.isDirty() will tell us whether any data has changed since the fiddler was first populated, so it’s easy to decide whether or not to dump the updated values back to the sheet
Digging a little deeper
This app is a collection of bots that emulate movements in a flight system. They are triggered by a tick of a simulated clock which is running at some factor of real time. Sim clock values (it’s actually an instance of TimeSimmer : An adjustable timer for apps that need to speed up or slow down time) is included in simReg. They each run at every tick. Here’s what they each do.
- setTimetables – the timetables are based on rotating weekly schedule. Each instance of a flight is scheduled to occur at the same times each week. When a new simulated week is detected, it does a one off timetable setting for the week, randomly making small adjustments to the regular weekly schedule.
- disruptTimetables – makes random adjustments to the timetable such as cancellations and flight delays.
- updateTimetables – adjusts the status of each flight according to the current simulated time. Some flights take off, others land others close the gate to further passengers and so on
- createJourneys – a journey is created for a passenger who is ready to take a flight. He is automatically assigned to some flight scheduled in the timetable to leave from the airport he is currently at.
- updateJourneys – adjusts the status of each journey and passenger based on any changed in the status of any flights whose timetable status has changed
It’s clear from this that not all steps are always necessary. For example, there’s no need to create or update journeys if there’s been no timetime changes. we can use the dirty flag to control execution of some of these functions.
And a little more
It turns out that we only need to update journeys if certain columns of the timetable changes, so we can avoid a little more processing by setting a dirty watch on specific columns. Here’s the updated code to minimize unnecessary processing.
Limiting data transfer between client and server side
In fact this entire collection of bots are provoked by a client side htmlservice app. The simulated clock runs there and sends an instruction to process and send back the current contents of some of these sheets. Of course we don’t want to send back data if nothing has changed, so we can use a similar technique here.
- The client sends a tick request to run a cycle of the above processing, and also a list of sheets it needs the content of. The request includes the fingerprint of every sheet it already knows.
- The server notes what the client already knows, does its tick processing, then sends back the content of the sheets where the fingerprint differs from what the client already knows – including the new finger print
- The client receives the package from the server, and where nothing has changed uses its own copy of the data from the previous tick, replaces the updated data, and stores each of the current fingerprints ready for the next tick.
Here’s the decorated process including dealing with client server communucation.
Which sheets are involved
Some sheets are required for tick processing, some are required to send back to the client and there’s overlap so make a definitive list of all the sheets in scope.
Definitively identify each sheet that will be packaged and sent back
The client might need to know there’s been a change of name or store the id of the sheet for future useage, so we can send back some identifying detail about each of the sheets requested
Consolidate the packs
The idea here is that all the sheet data is sent back in a consolidated object, which each individual sheet in the ‘everything’ property. Because we also send back the simReg object (which contains details of the simulated time this update refers to), the client can also check that the results it received are for the request it made and to what time they refer.
Create an overall fingerprint
In addition to a fingerprint for each sheet, there will be an overall fingerprint – which is essentially a fingerprint of each of the fingerprints. This is a quick way to tell if there’s been any changes at all in any sheet. Fiddlers expose a handy function, fiddler.fingerprinter() to allow you to make a fingerprint of anything – just pass whatever arguments you like to it.
Finalize pack
If there’s been any changes we need to populate the data with the new sheet content , but just for those sheets that have changed. Now this pack is ready to send back for reconstitution at the client end.
Where to get fiddler
v8 version
13EWG4-lPrEf34itxQhAQ7b9JEbmCBfO8uE4Mhr99CHi3Pw65oxXtq-rU