Optimize updates to Google Sheets with fiddler fingerprints

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

  const getss = ({id}) => id ? SpreadsheetApp.openById(id) : SpreadsheetApp.getActiveSpreadsheet()
    
  // open a sheet
  const getSheet = ({ id, sheetName}) => getss({id}).getSheetByName(sheetName)
  
  // open a fiddler and assign a sheet
  const getFiddler = ({ id, sheetName, noCache = false}) => {
    return new bmFiddler.Fiddler(getSheet({id, sheetName}))
  }

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

    // some are only needed here
    const reqSheets = ['timetables', 'routes' , 'schedules', 'journeys', 'users']
    
    // need to force no cache for journeys
    const fiddlers = reqSheets
      .reduce ((p,sheetName)=> {
          p[sheetName] =  getFiddler({id, sheetName, noCache: true})
          return p
      }, {})

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

    // if it's a new cycle, we'll need a new timetable
    setTimetables  ({...fiddlers,simReg, botBehavior})

    // maybe there's been some timetable disruption
    disruptTimeTables ({...fiddlers, simReg,  botBehavior}) 

    // update the timetable for current time
    updateTimetables({...fiddlers, simReg, botBehavior})

    // create any bot journeys for any dangling users
    createJourneys ({...fiddlers, simReg,  botBehavior}) 

    // update any journey completed from timetables
    updateJourneys  ({...fiddlers, simReg, botBehavior})

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

    // dump out any fiddler changes
    Object.keys(fiddlers).forEach(f=>{
      if (fiddlers[f].isDirty()){
        fiddlers[f].dumpValues()
      }
    })

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.

    // these following bots will fiddle about with flight system updates 
    // if it's a new cycle, we'll need a new timetable
    setTimetables  ({...fiddlers,simReg, botBehavior})

    // maybe there's been some timetable disruption
    disruptTimeTables ({...fiddlers, simReg,  botBehavior}) 

    // update the timetable for current time
    if (fiddlers.timetables.isDirty()) {
      updateTimetables({...fiddlers, simReg, botBehavior})

      // create any bot journeys for any dangling users
      createJourneys ({...fiddlers, simReg,  botBehavior}) 

      // update any journey completed from timetables
      updateJourneys  ({...fiddlers, simReg, botBehavior})
    }

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.

    // watch out for status or time changes in the timetable
    fiddlers.timetables.setDirtyWatch('need-journey-update', ['status', 'departsSim', 'arrivesSim'])

    // these following bots will fiddle about with flight system updates 
    // if it's a new cycle, we'll need a new timetable
    setTimetables  ({...fiddlers,simReg, botBehavior})

    // maybe there's been some timetable disruption
    disruptTimeTables ({...fiddlers, simReg,  botBehavior}) 

    // update the timetable for current time
    if (fiddlers.timetables.isDirty('need-journey-update')) {
      updateTimetables({...fiddlers, simReg, botBehavior})

      // create any bot journeys for any dangling users
      createJourneys ({...fiddlers, simReg,  botBehavior}) 

      // update any journey completed from timetables
      updateJourneys  ({...fiddlers, simReg, botBehavior})
    }

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.

1. 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.
2. 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
3. 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.

    const reqSheets = sheetNames.concat(['timetables', 'routes' , 'schedules', 'journeys', 'users'].filter(f=>sheetNames.indexOf(f)==-1))
    

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

  const getPack  = ({fiddler}) => {
    const sheet = fiddler.getSheet()
    const pack = {
      name: sheet.getName(),
      id: sheet.getParent().getId(),
      sheetId: sheet.getSheetId()
    }
    
    pack.fingerprint = fiddler.getFingerprint()
    return pack
  }

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.

    const packs = sheetNames.reduce((p,sheetName) => {
      p.everything[sheetName] = getPack({fiddler: fiddlers[sheetName]})
      return p;
    }, {
      simReg,
      sheetNames,
      id,
      pollerStarted,
      cached: false,
      everything: {}
    })

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.

    // add the overall fingerprint 
    const fingerprints = Object.keys(packs.everything || []).map(f=>packs.everything[f].fingerprint)
    // we can use any fiddler to access its digester
    packs.fingerprint = fiddlers.users.fingerprinter(fingerprints)
    packs.changed = !current || packs.fingerprint !== current.fingerprint

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.

    if (packs.changed) {
      const pe = packs.everything
      
      Object.keys(pe).forEach (f=> {
        const e = current && current.everything && current.everything[f]
        pe[f].changed = !e || e.fingerprint !== pe[f].fingerprint
        if(pe[f].changed) {
          pe[f].data = fiddlers[f].getData()
        }
      })
    }

Where to get fiddler

v8 version