Introduction

At Pico Labs, we have a Wovyn CO₂ sensor device. It sends minute-by-minute heartbeat events to our "Wovyn CO2" pico. The pico is endowed with the following rulesets:

• wovyn_router which reacts to the heartbeat event by raising wovyn events for each of the sensors in the device
• wovyn_co2_levels which reacts to the wovyn:new_gas_reading event raised by the router, keeps an internal record of one reading every 10 minutes, and checks a threshold
• wovyn_notifications which selects on the wovyn:co2_level_high event raised by the levels ruleset, and reacts by sending a slack notification once per hour

These rulesets each depend on the one listed before it. The source code for these rulesets is maintained in Pico Labs' GitHub Wovyn repository.

Problem

Over time, the pico has accumulated a very large number of (date-stamped) readings in the levels ruleset. As of this writing, it is holding 68,712 such readings, in an array:

[
{ "timestamp": "2017-06-13T16:00:35.385Z", "concentration": 533 },
...
{ "timestamp": "2018-10-31T18:50:29.734Z", "concentration": 529 },
{ "timestamp": "2018-10-31T19:00:48.491Z", "concentration": 520 }
]

This is not counting 5,216 readings which had previously been recorded, and then archived. The archive was created, and the pico's array of readings was pruned, manually.

The problem is to have the pico periodically archive its readings and prune its collection so as to maintain only a certain number of recent readings. This would be more efficient use of pico memory and processing time. And we want this to be automatic, rather than manual.

Current operation

Selected parts of the rule which selects on wovyn:new_gas_reading, whose purpose is to record the reading, are shown here:

  rule record_new_gas_reading {
    select when wovyn new_gas_reading
    pre {
      reading = event:attr("readings").filter(function(r){r.name=="co2"}).head()
      ...
      timestamp = event:attr("timestamp")
      this_ten = timestamp.substr(0,15)
      already_recorded = ent:this_ten == this_ten
      new_entry = { "timestamp": timestamp, "concentration": reading{"concentration"}}
    }
    if not already_recorded then noop()
    fired {
      ent:this_ten := this_ten;
      ...
      ent:co2_levels := ent:co2_levels.append(new_entry);
      raise wovyn event "co2_level_recorded" attributes new_entry
    }
  }

Lines 7-8, 11, and 13 are the logic that keeps only the first reading of any given 10 minute period of time.

The new entry is established in line 9 and appended to the growing array of readings in line 15.

A key feature of this rule is that, whenever it has added an entry, it raises the internal (to this pico) event wovyn:co2_level_recorded. This event is used in the same ruleset to check the concentration against a threshold and, if exceeded, raise the wovyn:co2_level_high event.

Solution

We will create a new ruleset, wovyn_co2_recorder, which also reacts to the wovyn:co2_level_recorded event, by appending the reading to a spreadsheet. Automating the pruning will be a future task, once we have recoded our existing display mechanisms which currently depend on the pico having all readings from 2017-06-13T16:00:35.385Z on. Instead of depending on the pico, these display mechanisms will first consult the spreadsheet, and then ask the pico for readings since the last row of the sheet.

Appending to a Google sheet

Google offers a general purpose RESTful web API for accessing Google sheets. We've tried using it and found it disarmingly complex. Recently we discovered a different approach. Google will host any number of custom web APIs. They provide a toolkit which enables the owner of a sheet to specify a custom web API for that sheet. The owner programs the API using the Google scripting language.

Using Google scripting

A small amount of scripting code will be added to a Google sheet. This is done by opening the script editor from the "Tools" tab of a spreadsheet. The code (note that this is not KRL code, but rather Google scripting code) is shown here:

function doPost(e) {
  recordReading(e.parameter);
}

function recordReading(data) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  sheet.appendRow([data.timestamp,data.concentration]);
}

A function with the special name of doPost, once the script is deployed* as a web app (from the Publish tab), gives us a URL to which we can post a reading.  The function defined in line 1 fills this purpose. In line 2, it takes the query string from the URL and passes it, as JSON, to our recordReading function.

The recordReading function, lines 5-9, uses the active spreadsheet (line 6), obtains the sheet in the tab named "Sheet1" (line 7), and appends the reading as a row (line 8).

With this small program in place within the sheet, we have specified a web API which will accept a POST with query parameters timestamp and concentration. Upon receipt of the POST, this web API will append one line containing these two values to the sheet.

Posting to the Google script from KRL

Now, all we need to do is add a ruleset which uses our custom web API to post each new reading to the Google sheet:

ruleset wovyn_co2_recorder {
  rule record_co2_level_to_sheet {
    select when wovyn co2_level_recorded where ent:url
    pre {
      ts = time:strftime(event:attr("timestamp"), "%F %T");
      data = {"timestamp": ts, "concentration": event:attr("concentration")}
    }
    http:post(ent:url,qs=data) setting(response)
    fired {
      ent:lastData := data;
      ent:lastResponse := response;
      raise wovyn event "co2_level_recorded_to_sheet" attributes event:attrs()
    }
  }
}

First, we install this ruleset in our "Wovyn CO2" pico. Notice that the rule record_co2_level_to_sheet will not be selected until we have provided the URL given to us when we deployed the Google script. We do this by sending our pico an event furnishing the URL, as described in the Keeping keys "safe" in a different way page. As soon as the ruleset has the value for ent:url, it will begin posting a reading to the spreadsheet each time the wovyn_co2_levels ruleset records it internally.

Each time a CO₂ reading is recorded internally to the pico, the record_co2_level_to_sheet rule (lines 2-14) will select (as declared by line 3). It will throw away the millisecond information from the timestamp and format it in a way acceptable to the spreadsheet (line 5), package it up with the concentration number (line 6), and post it to the Google script (line 8). For debugging purposes, the rule saves the latest data and HTTP reponse (lines 10-11). Significantly, the rule raises a (new) wovyn:co2_level_recorded_to_sheet event (line 12), which we can use in a future rule or ruleset to do other things, such as, for example, prune data held within the pico.

Notes

*Guidance on deploying a Google script as a web app can be found in the Google Apps Script documentation

Conclusion

We now have a Google spreadsheet which holds our CO₂ sensor data, and we can prepare to automatically prune the data held within the pico.