Monitoring capacity in climbing gyms

2020-04-06 update: As of tomorrow, Singapore will enact a soft lockdown. All climbing gyms in Singapore will close for at least a month. I have therefore disabled automatic updates for the dashboard.

Climbing gyms in Singapore have started to limit the maximum number of climbers in the facility, as part of Ministry of Health (MOH) guidelines. I put together a simple monitoring dashboard for the gyms I frequently visit.

• View in Google Sheets
• Screenshot

Implementation

The two gyms included in the dashboard at the time of this post both conveniently expose their live counters via a Google spreadsheet. To import their data, I created a spreadsheet and used the =IMPORTRANGE function to pull the values from their sheet.

In order to periodically log the current capacity, I created a Google Apps Script project with a timed-based trigger of 5 minutes. This will execute a function every 5 minutes. The function looks like this:

const BPLUS_CELL = "C2"
const ONSIGHT_CELL = "C3"

function logGymCapacities() {
  now = new Date();
  
  // don't log between 0000hrs and 0700hrs SGT
  if (now.getUTCHours() > 16 && now.getUTCHours() < 23) {
    return; 
  }
  
  var spreadsheet = SpreadsheetApp.openById("abc123");
  var sheet = spreadsheet.getSheetByName("counters");
  var bplusCount = sheet.getRange(BPLUS_CELL).getValue();
  var onsightCount = sheet.getRange(ONSIGHT_CELL).getValue();
  
  var logSheet = spreadsheet.getSheetByName("log");
  logSheet.appendRow([now, "boulder+", bplusCount]);
  logSheet.appendRow([now, "onsight", onsightCount]);
}

What this function does is:

1. Create a reference to my spreadsheet.
2. Read the value from the cells with the =IMPORTRANGE function. Effectively I’m reading the value exposed by each gym’s sheet.
3. Create a reference to a sheet called “log”.
4. Write a record for each gym. Each record contains the timestamp, gym name, and the current climber count.

Records in the “log” sheet look something like the following:

3/29/2020 12:14:26  boulder+  36
3/29/2020 12:14:26  onsight   60
3/29/2020 12:29:26  boulder+  40
3/29/2020 12:29:27  onsight   55
3/29/2020 12:44:26  boulder+  38
3/29/2020 12:44:26  onsight   51
3/29/2020 12:59:26  boulder+  39
3/29/2020 12:59:26  onsight   49
3/29/2020 12:59:55  boulder+  40
3/29/2020 12:59:55  onsight   50
3/29/2020 13:14:28  boulder+  37
3/29/2020 13:14:28  onsight   53

From this point I can just slice and dice this data to create the graphs I want.