ESP32 und Google Tabellen (Sheets) – ESP32 Arduino Projekte

Beispiel für direkte Messwert Übertragung von ESP32 in Google Tabellen (Sheets)
ESP32 über HTTPS und Google App Script zum Google Tabellen (Sheets)

Bei dem ESP8266 gab es die Möglichkeit mit Hilfe externe Bibliothek HTTPSRedirect mit Google über SSL zu kommunizieren. ESP32 hat schon passende Mittel dabei. WiFiClientSecure.h ermöglicht eine HTTPS SSL Verbindung direkt zum Google Tabellen (Sheets) herzustellen. Ok nicht ganz so direkt. Dazwischen soll noch ein Google Apps Script verwendet werden. Google Script kann von überall gestartet werden und in der GET (oder auch POST) Anfrage werden die Daten von ESP32 übertragen und von Google Script in die Google Tabelle eingetragen. Vorteil von dieser Methode ist, dass keine zusätzlichen Service verwendet werden. Kein IFTTT oder PushingBox ist nötig. Vorgehensweise:

Google Tabelle erzeugen (Tabellen ID Merken)
Google Apps Script (JavaScript) anlegen und als Web-App einrichten
In ESP32 Beispiel Sketch das Web-App-URL eintragen.

Beschreibung des Google Scripts

der Google Script Empfängt Daten per Get-Anfrage und platziert die Werte in die nächste freie Zeile. Damit der Script auf Ihre Google Tabelle den Zugriff erhält, fügen sie in die Zeile 1 des Google Scripts die ID der Google Tabelle. ID der Tabelle entnehmen Sie dem URL der Google Tabelle.
In die erste Spalte (A) wird automatisch Timestamp eingetragen. Weitere Zellen werden mit den Daten gefüllt. Die Zahl mit der das Argument der GET-Anfrage anfängt, bestimmt die Spalte wo der Wert eingetragen wird. „2_Spalte=200“ Spalte 2 (C) wird mit dem Wert „200“ beschrieben. Anzahl der Argumente-Wert Paare ist nicht begrenzt. Wichtig ist das Argument mit einer Zahl anfängt, der Text dahinter kann beliebig sein. Bei erfolgreicher Ausführung des Google Scripts erscheint im Browser ein „Ok“ als Text.

var id = '11DaCrAGtFplNqW5VJOsvTELR2oHoyJ9bhiCbrfwertz';  // ID der Tabelle

function doGet(e) {                 // GET Parameter Empfangen

  if (e.parameter == undefined) {   // Wenn keine Parametr über GET Empfangen
    result = 'Parameter fehlen';
  }
  else {
    var sheet = SpreadsheetApp.openById(id).getActiveSheet(); // Zuweisung des Tabellendokuments und AktiverTabelle einer Variable
    var newRow = sheet.getLastRow() + 1;  // Letzte beschriebene Zeile + 1 
    var rowData = [];        // Array anlegen
    rowData[0] = new Date(); // Timestamp in Spalte A Erste Elemet des Arrays rowData
    
    for (var param in e.parameter) {    // Alle empfangenen Parametr Durchgenen
      var value = e.parameter[param];   // Werte einlesen
      rowData[parseInt(param)] = value; // Werte in Array Speichern. Position bestimmt die Zahl im Parametr
    } 
    
    // Lege neue Zeile an in der Länge des Arrays mit Werten
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);    // Zeile  mit Daten aus Array befüllen
    var result =  'Ok'// Text des Anworts
  }

  // Antwort als Text an Browser senden
  return ContentService.createTextOutput(result);
}

var id = '11DaCrAGtFplNqW5VJOsvTELR2oHoyJ9bhiCbrfwertz'; // ID der Tabelle

function doGet(e) { // GET Parameter Empfangen

if (e.parameter == undefined) { // Wenn keine Parametr über GET Empfangen

result = 'Parameter fehlen';

}

else {

var sheet = SpreadsheetApp.openById(id).getActiveSheet(); // Zuweisung des Tabellendokuments und AktiverTabelle einer Variable

var newRow = sheet.getLastRow() + 1; // Letzte beschriebene Zeile + 1

var rowData = []; // Array anlegen

rowData[0] = new Date(); // Timestamp in Spalte A Erste Elemet des Arrays rowData

for (var param in e.parameter) { // Alle empfangenen Parametr Durchgenen

var value = e.parameter[param]; // Werte einlesen

rowData[parseInt(param)] = value; // Werte in Array Speichern. Position bestimmt die Zahl im Parametr

}

// Lege neue Zeile an in der Länge des Arrays mit Werten

var newRange = sheet.getRange(newRow, 1, 1, rowData.length);

newRange.setValues([rowData]); // Zeile mit Daten aus Array befüllen

var result = 'Ok'// Text des Anworts

}

// Antwort als Text an Browser senden

return ContentService.createTextOutput(result);

}

Anleitung: Google Script als Web-App einrichten

Beschreibung ESP32 Sketches

Der Sketch erzeugt HTTPS Verbindung mit script.google.com und sendet eine GET Anfrage an Web-App (Google Script). Diese Anfrage sieht so aus:
„https://script.google.com/macros/s/AKrycby0T0QaLhhdlDRiBNyYSWYwq9bNM-Y17YrzFVeZ8V6im9FL42c/exec? 1_Spalte=100&2_Spalte=200&3_Spalte=300&4_Spalte=400&5_Spalte=500&6_Spalte=600“

Das URL bis zum Fragezeichen erhalten Sie beim Anlegen des Google Scripts als Web-App. Text Nach dem Fragezeichen sind Die Daten die zum Google Script übertragen werden. Google App Script antwortet erst mit einem Redirect. Und auf der umgeleiteten Webseite befindet sich der Antwort des Google App Scripts.

Das Beispielprogramm sendet die Werte 100,200,300,400,500,600 zum Google Sheet beim jeden Start. Wenn das Google Script Web-App mit einem „Ok“ antwortet Geht die Eingebaute LED an. Das Beispielprogramm sollte die Grundfunktion zeigen und als Vorlage für weitere Entwicklungen dienen.

#include <WiFiClientSecure.h>
#define LED_BUILTIN 22         // LED an ESP32 LOLIN32

const char* ssid     = "Bitte eintragen";     
const char* password = "Bitte eintragen";

const char*  server = "script.google.com";  // Server URL
// google script key
const char* key = "AKfycby0T0QaLhodlDRiBNyYSWYwq9bNM-Y17YrzFVeZ8V6im9FL42c"; 

WiFiClientSecure client;
#define debug true

boolean SendeZumGoogle(String URL)
{
  String movedURL;
  String line;
  client.setInsecure();
  if (debug)Serial.println("Verbinde zum script.google.com");
  if (!client.connect(server, 443))
  {
    if (debug) Serial.println("Verbindung fehlgeschlagen!");
    return false;
  }

  if (debug) Serial.println("Verbunden!");
  // ESP32 Erzeugt HTTPS Anfrage an Google sheets
  client.println("GET " + URL);
  client.println("Host: script.google.com" );
  client.println("Connection: close");
  client.println();

  // ESP32 empfängt antwort vom Google sheets
  while (client.connected())     // ESP32  empfängt Header
  {
    line = client.readStringUntil('\n');
    if (debug) Serial.println(line);
    if (line == "\r") break;      // Ende Des Headers empfangen
    if (line.indexOf ( "Location" ) >= 0)   // Weiterleitung im Header?
    { // Neue URL merken
      movedURL = line.substring ( line.indexOf ( ":" ) + 2 ) ;
    }
  }

  while (client.connected())    // Google Antwort HTML Zeilenweise Lesen
  {
    if (client.available())
    {
      line = client.readStringUntil('\r');
      if (debug) Serial.print(line);
    }
  }
  client.stop();

  movedURL.trim(); // leerzeichen, \n entfernen
  if (debug) Serial.println("Weiterleitungs URL: \"" + movedURL + "\"");

  if (movedURL.length() < 10) return false; // Weiterleitung nicht da

  if (debug) Serial.println("\n Starte Weiterleitung...");
  if (!client.connect(server, 443))
  {
    if (debug) Serial.println("Weiterleitung fehlgeschlagen!");
    return false;
  }

  Serial.println("Verbunden!");
  // // ESP32 Erzeugt HTTPS Anfrage an Google Tabellen
  client.println("GET " + movedURL);
  client.println("Host: script.google.com");
  client.println("Connection: close");
  client.println();

  while (client.connected()) // ESP32  empfängt Header
  {
    line = client.readStringUntil('\n');
    if (debug) Serial.println(line);
    if (line == "\r")break;
  }
  
  while (client.connected()) // Google Antwort HTML Zeilenweise Lesen
  {
    if (client.available())
    {
      line = client.readStringUntil('\r');
      if (debug) Serial.print(line);
    }
  }
  client.stop();
  if (line == "Ok") return true;
}


void setup()
{
  pinMode(LED_BUILTIN, OUTPUT);
  digitalWrite(LED_BUILTIN, 1);
  Serial.begin(115200);
  Serial.println("Warte auf Verbindung");
  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED)
  {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.print("IP Addresse: ");
  Serial.println(WiFi.localIP());
  String URL="https://script.google.com/macros/s/";
  URL += key;
  URL += "/exec?";
  URL += "1_Spalte=100&2_Spalte=200&3_Spalte=300&4_Spalte=400&5_Spalte=500&6_Spalte=600";
  Serial.println(URL);
  if (SendeZumGoogle(URL)) digitalWrite(LED_BUILTIN, 0);
}

void loop()
{
  delay(1);
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

#include <WiFiClientSecure.h>

#define LED_BUILTIN 22 // LED an ESP32 LOLIN32

const char* ssid = "Bitte eintragen";

const char* password = "Bitte eintragen";

const char* server = "script.google.com"; // Server URL

// google script key

const char* key = "AKfycby0T0QaLhodlDRiBNyYSWYwq9bNM-Y17YrzFVeZ8V6im9FL42c";

WiFiClientSecure client;

#define debug true

boolean SendeZumGoogle(String URL)

{

String movedURL;

String line;

client.setInsecure();

if (debug)Serial.println("Verbinde zum script.google.com");

if (!client.connect(server, 443))

{

if (debug) Serial.println("Verbindung fehlgeschlagen!");

return false;

}

if (debug) Serial.println("Verbunden!");

// ESP32 Erzeugt HTTPS Anfrage an Google sheets

client.println("GET " + URL);

client.println("Host: script.google.com" );

client.println("Connection: close");

client.println();

// ESP32 empfängt antwort vom Google sheets

while (client.connected()) // ESP32 empfängt Header

{

line = client.readStringUntil('\n');

if (debug) Serial.println(line);

if (line == "\r") break; // Ende Des Headers empfangen

if (line.indexOf ( "Location" ) >= 0) // Weiterleitung im Header?

{ // Neue URL merken

movedURL = line.substring ( line.indexOf ( ":" ) + 2 ) ;

}

while (client.connected()) // Google Antwort HTML Zeilenweise Lesen

{

if (client.available())

{

line = client.readStringUntil('\r');

if (debug) Serial.print(line);

}

client.stop();

movedURL.trim(); // leerzeichen, \n entfernen

if (debug) Serial.println("Weiterleitungs URL: \"" + movedURL + "\"");

if (movedURL.length() < 10) return false; // Weiterleitung nicht da

if (debug) Serial.println("\n Starte Weiterleitung...");

if (!client.connect(server, 443))

{

if (debug) Serial.println("Weiterleitung fehlgeschlagen!");

return false;

}

Serial.println("Verbunden!");

// // ESP32 Erzeugt HTTPS Anfrage an Google Tabellen

client.println("GET " + movedURL);

client.println("Host: script.google.com");

client.println("Connection: close");

client.println();

while (client.connected()) // ESP32 empfängt Header

{

line = client.readStringUntil('\n');

if (debug) Serial.println(line);

if (line == "\r")break;

}

while (client.connected()) // Google Antwort HTML Zeilenweise Lesen

{

if (client.available())

{

line = client.readStringUntil('\r');

if (debug) Serial.print(line);

}

client.stop();

if (line == "Ok") return true;

}

void setup()

{

pinMode(LED_BUILTIN, OUTPUT);

digitalWrite(LED_BUILTIN, 1);

Serial.begin(115200);

Serial.println("Warte auf Verbindung");

WiFi.begin(ssid, password);

while (WiFi.status() != WL_CONNECTED)

{

delay(500);

Serial.print(".");

}

Serial.println("");

Serial.print("IP Addresse: ");

Serial.println(WiFi.localIP());

String URL="https://script.google.com/macros/s/";

URL += key;

URL += "/exec?";

URL += "1_Spalte=100&2_Spalte=200&3_Spalte=300&4_Spalte=400&5_Spalte=500&6_Spalte=600";

Serial.println(URL);

if (SendeZumGoogle(URL)) digitalWrite(LED_BUILTIN, 0);

}

void loop()

{

delay(1);

}

Erzeugen und konfigurieren des Google Scripts

Legen Sie neue Tabelle an.
Geben Sie die Überschriften. Erste Spalte heißt „Timestamp“ oder „Datum/Zeit“.
Bitte formatieren Sie entsprechend die erste Spalte. (auf „A“ klicken => Format => Zahl => Datum/Uhrzeit). Sonst wird nur Datum angezeigt.
Direkt vom Google Tabelle können wir zum Google Scripteditor wechseln: Tools => Skripteditor. Vergeben Sie dem Script eine Name sonst heiß er „Unbenanntes Projekt“.
Ersetzen Sie function myFunction durch den Script von dieser Webseite.

Damit der Google Script auf die Tabelle Zugriff erhält, muss dem Script die ID des spreadsheets mitgeteilt werden. Wechseln Sie zu Google Tabelle und kopieren Sie aus dem URL die ID.

https://docs.google.com/spreadsheets/d/17k0fEFhPdL9QlmfLF9H8UD0u3v9eUQWsWoWqZe35CKc/edit#gid=0

Fügen Sie die ID in das Script (Zeile 1). Speichern Sie das Script „Strg+s“
Wechseln Sie zum Menü Veröffentlichen => Als Web-App einrichten
Google Script Speichert GET daten von ESP32 in die Tabelle
Jetzt kommt Meldung „Autorisierung erforderlich“ Melden Sie sich mit ihrem Google Konto.
Trotz dem, werden Sie nicht weiter zum Script zugelassen.
Klicken Sie auf „Erweitert“ => „Unsicher ausführen“

Kopieren Sie die Web-App-URL. ID aus dieser URL kommt in ESP32 Sketch in die Zeile 9 (google script key).

Testen des Google Scripts

Bevor man zu ESP32 greift, sollte erst die Funktionalität des Google Scripts über Browser geprüft werden. Geben Sie das URL „https://script.google.com/macros/s/_Script_ID_/exec? 1_Spalte=100&2_Spalte=200&3_Spalte=300&4_Spalte=400&5_Spalte=500&6_Spalte=600“ in die Adresszeile Ihres Browsers(„_Script_ID_“ gegen Ihr Google Script ID austauschen). Es sollte ein „Ok“ erscheinen und Ihre Tabelle bekommt eine neue Zeile.

Anzeige als Diagramm

Bei Google Tabellen gibt es Möglichkeit, genauso wie beim Excel, ein Diagramm zu erstellen. Dieses Diagramm lässt sich als Grafik veröffentlichen. Ein öffentliches Diagramm kann man in eigene Homepage einbinden.
Ich habe mir Gedanken gemacht wie zeige ich nur die aktuelle Daten und nicht alle in der Tabelle. Passende Anweisungen sind schnell gefunden:

if(sheet.getLastRow()>20) sheet.deleteRows(2);

Wenn die nächste freie Zeile 21 ist wird Zeile 2 gelöscht.
Die Tabelle wird bis Zeile 20 gefüllt, danach wird Zeile 2 gelöscht(Erste Zeile hat Überschriften) und die anderen Zeilen rutschen nach oben. So erhalten Wir immer die letzten 20 Eintragungen und die neuste Werte im Diagramm. Man könnte noch die Zeile 2 vor dem löschen in eine andere Tabelle eintragen fürs Protokoll.

Ideen

Personenwaage die das Gewicht an Google sheets sendet. Waage umbauen mit HX711 modul.
Gmail notifier. Wand Anzeigetafel die das Anzahl ungelesene Mails anzeigt.
var ureadMsgsCount = GmailApp.getInboxUnreadCount();
Wand Anzeigetafel die Text des Mails anzeigt, wenn das Betreff bestimmte Codewort enthält. Oder von bestimmten Versender.
Vordefinierte E-Mail über Gmail senden per Tastendruck, Reedkontakt oder Touch. Batteriebetrieben.