Sensordata automatisch opslaan in Google Sheets

Een blogpost over een oplossing waarvan ik zelf inmiddels al geconstateerd heb dat hij tóch niet zo handig is? Moet kunnen. Want ik wil in ieder geval even documenteren hoe ik e.e.a. voor elkaar gekregen heb. Wie weet heeft iemand anders er toch nog wat aan.

Naast de fijnstofsensor voor het RIVM experiment heb ik er ook eentje gemaakt die ik in de woonkamer opgesteld heb. Gewoon om te zien hoe in huis eventueel het niveau fijnstof zou stijgen als we in de keuken aan het koken waren, of een paar uur wafels stonden te bakken.

Ik wilde de data snel kunnen verwerken zonder teveel gedoe met databases of zo, zou het niet handig en mogelijk zijn om de data op te slaan in een Google Sheet?

Ik weet inmiddels dat als ik het kan verzinnen, iemand anders dat ongetwijfeld ook al gedaan heeft. Zo ook nu. En lang geleden al.In 2011 schreef Martin Hawksey een script waarmee  je via een URL data door kunt geven aan  een Google Sheet. Je moet een Sheet aanmaken en dan in de Script editor het script inplakken. Eenmalig moet je dan de Setup() procedure uitvoeren en via Publish > Deploy as web app het script publiceren. Daarbij moet je er dan voor kiezen om het script ook voor “Anonymous” beschikbaar maken. Google zal dan moord en brand schreeuwen omdat het script niet door hen getest is etc.

De werking is dan eenvoudig. Op de eerste rij van de sheet zet je de namen van de waarden die je wilt doorgeven. Tip: noem de eerste kolom “Timestamp”, dan voegt het script automatisch datum en tijd in waarop de nieuwer data is ingevoerd.

Daarna kun je via GET of POST de data doorsturen naar de Google Sheet waarbij elke waarde gelijk moet zijn aan de titel van een kolom (hoofdlettergevoelig).

Ik gebruikte een NodeMCU ESP8266. Omdat Google een beveiligde verbinding gebruikt moet je gebruik maken van een andere bibliotheek dan normaal:

#include <WiFiClientSecure.h>
en
WiFiClientSecure client2;

Daarna kun je een url samenstellen en de data versturen:

void sendtoGoogle() {
const char* host2 = "script.google.com";

Serial.println("");
  Serial.print("Connecting to ");
  Serial.println(host2);
  if (!client2.connect(host2, httpsPort)) {
    Serial.println("connection failed");
  }

  String url = "/macros/s/AKfycbwOO059w88vUOZxFcxxg_RpcWLqHNj4TmFU/exec";
  url += "?id=" + String( chipid ) ;
  url += "&Temp=";
  url += x0;
  url += "&Hum=";
  url += x2;
  url += "&PM25=";
  url += x3;
  url += "&PM10=";
  url += x4;
  url += "&RSSI=";
  url += x5;
  url += "&AQI=";
  url += x6;
  url += "&AQIString=";
  url += x7;
  url += "&SMPLS=";
  url += x8;

  Serial.print("Sending data to Google: ");
  Serial.print(host2);
  Serial.println(url);

  // This will send the request to the server
  client2.print(String("GET ") + url + " HTTP/1.1\r\n" +
  "Host: " + host2 + "\r\n" +
  "Connection: close\r\n\r\n");
  unsigned long timeout = millis();
  while (client2.available() == 0) {
    if (millis() - timeout > 5000) {
      Serial.println(">>> Client Timeout !");
      client2.stop();
      return;
    }
  }
}

Het eerste stukje van de url-variabele is specifiek voor je eigen Sheet, dat is de url die je door krijgt van Google (en die ik hier uiteraard een stukje ingekort heb zodat jullie niet allemaal data naar mijn Sheet kunnen sturen). Daarna voeg ik er waarden aan toe vanuit de variabelen x1 t/m x8 die ik elders in het script van een waarde voorzie op basis van de fijnstofsensor en de aangesloten DHT11 temperatuur/vochtigheid sensor.

Het resultaat in getallen zie je hiernaast/hierboven. Ik heb een grafiek laten genereren op de data (en het bereik ingesteld op 10.000 rijen zodat de grafiek automatisch bijgewerkt wordt als er nieuwe data binnen komt), die zie je hierboven bij het bericht.

Waarom niet handig?
Je kunt aan de screenshot van de data wellicht al zien dat de sensor redelijk vaak data doorstuurt: 1x per 2 minuten, 30x per uur,  720x per dag, 5.040x per week. Dat zijn ruim 20.000 regels per maand. Ik merk nu, na nog geen twee dagen al dat het opbouwen van de grafiek al trager gaat. En dat is dus met minder dan 2.000 dataregels.

Voor dit soort tijdreeksen kun je beter toch een database gebruiken. En dan eentje die daar speciaal voor ontwikkeld is, zoals InfluxDB. Dat is niet toevallig ook de database die het RIVM gebruikt voor het opslaan de data die de verschillende sensoren verspreid over heel Nederland aanleveren. Dat is dus iets voor morgen.

Maar het concept was interessant om uit te zoeken (en het was vakantie).