ESP8266 y Google SpreadSheets

En artículos anteriores abordamos el tema de guardar datos de los sensores en Excel, sea por medio de un script en Python que recibe los datos por medio de MQTT o mediante el puerto serie; en esta ocasión quiero trabajar con las planillas de calculo de Google, puesto que ofrecen la posibilidad de escribir scripts para automatizar tareas.

Serán estos scripts los que nos ayudaran a guardar la información de los sensores conectados al microcontrolador (para el ejemplo utilizare un ESP8266, pero puede ser cualquiera) en una planilla.

Utilizaremos un sensor de gases MQ-5 para el ejemplo, pero es posible utilizar cualquier sensor disponible; el objetivo es registrar en la planilla la fecha y hora de la medición y el valor de la lectura.

Comenzaremos ingresando en Google Drive y creando una Hoja de Cálculo nueva, la llamaremos nivelGas y en la columna A indicaremos como titulo Fecha y Hora; en la columna B Nivel Gas a modo informativo.

Luego que se haya guardado la planilla, debemos crear el script que realizara los cambios sobre la misma; para ello vamos al menú Herramientas – Editor de secuencias de comandos:

Se abrirá una nueva ventana en nuestro navegado que contiene un editor para que podamos cargar el código que utilizaremos:

Aquí debemos indicar el nombre del proyecto, en mi caso utilizare modificarHoja y debemos copiar el siguiente código:

function doPost(e) {
   var ss1 = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss1.getSheets()[0];

   var fechaHora = Utilities.formatDate(new Date(), "GMT-3", "yyyy-MM-dd HH:mm:ss");
   valor = String(e.parameters.data);
   sheet.appendRow([fechaHora,valor]);
   
   return ContentService.createTextOutput("Completo");
}

Este sencillo programa toma la hoja de cálculos abierta y de la misma identifica la primer hoja y por ultimo agrega una fila con los datos que fueron enviados desde el NodeMCU.

Por ultimo debemos establecer los permisos y accesos sobre el script para poder ejecutar correctamente, debemos ir a Publicar – Implementar como aplicación web

Debemos elegir que se ejecute como nuestro usuario y que cualquier persona incluso los anónimos tengan acceso a la aplicación; luego confirmamos las opciones que se nos presenta.

Copiamos la url que nos presenta, la utilizaremos en el programa del ESP8266.

Construiremos un sencillo circuito para tomar datos del entorno y guardarlos en la hoja de calculo, utilizaremos un sensor de gas MQ-5 que va conectado:

NodeMCU	MQ-5
3v3	VCC
GND	GND
A0	A0

En el Arduino IDE utilizaremos las librerías y variables siguientes:

#include <ESP8266WiFi.h>
#include <WiFiClientSecure.h>

const char* ssid = "SSID"; //Replace with your own
const char* password = "PASSWORD";

const char* myDomain = "script.google.com";

String script = "/macros/s/XXXXXXXXXXXXXXXXXXXXX/exec";

long ultimoReporte=0;
long periodoReportes = 60 * 1000; 

Donde ssid y password reemplazamos por los valores correspondientes a nuestra red wifi y en el variable script reemplazamos XXXXXXXXXXXXXXXXXXXXX por la correspondiente que nos otorgo Google Script.

Luego el metodo setup() es sencillo solo establecemos la salida serie y la conexion a la red. En el metodo loop tendremos lo siguiente:

void loop() {
  
  if ((millis() - ultimoReporte) > periodoReportes) {
    
    if(WiFi.status() != WL_CONNECTED) {
      setup_wifi();
    }
    ultimoReporte = millis();
    int valor = analogRead(0);
    Serial.println(valor);
    enviarDatosCloud(valor);
  }

}

Respecto a la función enviarDatosCloud(), es la que se conecta al script de Google y pasa los parametros determinados:

void enviarDatosCloud(int valor) {
  WiFiClientSecure clientSecure;
  if (clientSecure.connect(myDomain, 443)) {
    Serial.println("Connection successful");  

    String Data = "data="+String(valor);
    clientSecure.println("POST " + script + " HTTP/1.1");
    clientSecure.println("Host: " + String(myDomain));
    clientSecure.println("Content-Length: " + String(Data.length()));
    clientSecure.println("Content-Type: application/x-www-form-urlencoded");
    clientSecure.println();
    
    clientSecure.print(Data);    
    Serial.println("Waiting for response.");
    long int StartTime=millis();
    while (!clientSecure.available()) {
      Serial.print(".");
      delay(100);
      if ((StartTime+60000) < millis()) {
        Serial.println();
        Serial.println("No response.");
        break;
      }
    }
    Serial.println(); 
    
    while (clientSecure.available()) {
      Serial.print(char(clientSecure.read()));
    }  
    
  } else {
    Serial.println("Connected to " + String(myDomain) + " failed.");
  }
  clientSecure.stop();
}

Si todo esta correcto deberíamos poder compilar y al cabo de 60 segundos se actualizara nuestra hoja de cálculo; podemos cambiar el tiempo de actualización cambiando el valor de la variable periodoReportes.

En breve publicare en github el repositorio con el código del ejemplo.