De Tilt, Google Sheets a ThingSpeak

vinnymoy

hace 4 años

El día de hoy llegamos con un proyecto técnico, intentando conectar un hidrómetro Tilt al sistema ThingSpeak.

Antecedentes y equipo necesario

Este proyecto nace porque he comprado dos Tilt y necesito gestionar sus datos, no solamente visualizarlos.

El problema se da porque el Tilt comunica sus datos a través de Bluetooth, lo que directamente te obliga a tenerlo conectado a algún tipo de puente hacia internet. Tilt soluciona esto mediante dos medios: 1) una App de móvil, 2) utilizando una Raspberry Pi. Es decir que si quieres disfrutar de todas las ventajas del sistema, sí o si estás obligado a dejar conectado un teléfono o una RaspberryPi.

Lo siguiente que tienes que saber es que Tilt envía todos sus datos hasta una hoja de cálculo de Google Sheets. Lo cual, es una ventaja porque al final Google tiene muchas librerías para poder utilizar sus sistemas, pero también un problema porque salvo que seas un manitas, no es tan fácil ni tan evidente hacerlo.

Ahora que tengo los datos del Tilt en Google Sheets, ¿qué hago con eso? Seamos sinceros, tenerlos en una hoja de cálculo se ve bien pero no hay cosa que sirva menos. ¿Quién está entrando a Google Sheets para ver cómo va la fermentación? Uf. Por esa razón, he decidido enviar los datos a ThingSpeak, que es una cosa difícil y fácil de entender.

En un resumen, ThingSpeak, es un sistema de análisis de datos online basado en Matlab. ¿Y para qué me puede servir esto? Primero, porque desde ThingSpeak puedes almacenar, enviar y procesar los datos no de 1 ni de 10 sensores, sino de centenares, puedes crear tu propia App, y así integrar sensores Tilt, Plaato, Arduinos, Pt100, y casi cualquier cosa que se pueda conectar a internet. De hecho, esta plataforma es un sistema para IoT. ¿Suena interesante? Lo es.

Este proyecto tiene por objetivo conectar 1 Tilt, enviar sus datos a través de un RaspberryPi hasta Google Sheets, y desde ahí hacia ThingSpeak. Para esto necesitarías:

1 Raspberry Pi 3B, 3B+, 3A, 4, o Zero W.
Cuenta propia en Google (correo @gmail.com).
Cuenta en ThingSpeak (gratuita con limitaciones).
Evidentemente, 1 Tilt.

De Tilt a la nube

Yo tengo una Raspberry Pi 3 B+. El problema es que Tilt desarrolló el TILT™ PI V2, que no es más que descargar una imagen del sistema operativo Raspbian que ya tiene precargadas todas las librerías necesarias para que el Tilt funcione. Si vas por esa vía tienes que seguir las instrucciones oficiales que pueden encontrar en este link: TILT™ PI V2 BUSTER FEB20, y listo.

Pero bien, ¿qué pasa si es que tienes ya tu Raspberry y no la quieres mandar a cero instalando todo de nuevo? ¿Cómo cargo el Tilt Pi V2 sin usar la imagen oficial? Bien, deberás ingresar en la terminal del Raspberry Pi, puedes hacerlo directamente o mediante el uso de un comando SSH, y seguir los siguientes pasos:

1) sudo apt-get update --allow-releaseinfo-change
2) sudo apt-get install python3-distutils
3) wget https://github.com/baronbrew/aioblescan/archive/master.zip
4) unzip master.zip
5) cd aioblescan-master/
6) sudo -H python3 setup.py install
7) bash <(curl -sL https://raw.githubusercontent.com/baronbrew/raspbian-deb-package/master/resources/update-nodejs-and-nodered)
8) sudo -H npm install node-red-dashboard@2.9.7 -g
9) sudo -H npm install node-red-dashboard@2.15.5 -g
10) sudo systemctl enable nodered.service
11) sudo reboot
12) wget -O /home/pi/flow.json https://raw.githubusercontent.com/baronbrew/TILTpi/Aioblescan/flow.json
13) curl -X POST http://localhost:1880/flows -H "Content-Type: application/json" -H "Node-RED-Deployment-Type: nodes" --data "@/home/pi/flow.json"
14) curl -L wifi.brewpiremix.com | sudo bash

OJO: Este procedimiento está probado en Rasbian Buster. La descripción del porqué de cada uno de estos pasos puedes encontrarla dando clic aquí.

Finalmente podrás ingresar a cualquier navegador y escribir en la barra de direcciones: http://raspberrypi.local:1880/ui, con esto verás una imagen como la siguiente:

De la nube a Google Sheets

Una vez que sabemos que los datos están siendo enviados a la nube, llega el momento de visualizarlos en Google Sheets. Para esto, Tilt nos proporciona los pasos que debemos seguir.

1. Lo primero será dar clic aquí: Tilt Cloud App para iOS/Android/Tilt Pi. Se abrirá una hoja de Google Sheets, con las instrucciones que debes seguir.

2. Luego deberás crear una nueva copia y darle un nuevo nombre al archivo, puede ser cualquiera.

3. Posteriormente en el nuevo archivo deberás ir a Herramientas y dar clic a Editor de secuencias de comandos.

Archivo App Tilt. Editar Secuencia de comandos

4. Se abrirá un nuevo archivo en el cual no debes hacer más que dar clic en IGNORAR en el mensaje que aparece en la parte superior. Esto es importante porque el script no está actualizado por el momento.

5. Dar clic en PUBLICAR, y luego en IMPLEMENTAR COMO APLICACIÓN WEB.

Archivo App Tilt. Publicar como aplicación web

6. Es importante que en la sección: Who has access to the app (Quién tendrá acceso a la aplicación?), te asegures que diga: Anyone, even anonymous (Cualquiera). Finalmente aceptar y dar todos los permisos necesarios.

Archivo App Tilt. Dar los permisos necesarios

7. Ahora deberás ir al archivo que has creado y das clic en Actualizar en el navegador. Te fijarás que se ha aumentado un menú en el sistema: TILT. Ahí podrás dar clic y se despliegan dos menús más, del cual nos interesa el primero: VIEW CLOUD URL.

8. Se abrirá un menú en el que se muestra una dirección web que deberás guardar y será la que nos sirva de nexo entre el Tilt y Google Sheets.

Nuevamente en la Raspberry Pi

Puedes ingresar desde cualquier navegador en la misma red usando http://raspberrypi.local:1880/ui. Una vez allí deberás ir al menú Logging.

Aquí, deberás escoger cuál Tilt estás usando, en mi caso el RED. También deberás dar un nombre cualquiera a la cerveza que elaborarás.

Luego, en Cloud URL, debe ingresar la dirección que obtuviste en el punto anterior en el paso 8. Das clic en Star Logging to the Cloud, y eliges un Tiempo de intervalo aceptable. Me parece que para hacer pruebas 20 minutos está bien.

Finalmente, ingresarás tu mail en Quick Start y das ENTER. Recuerda que este correo debe ser de gmail y el mismo que usaste en todo el paso anterior.

Después de estos pasos tus datos están en Google Sheets.

Nuevamente en Google Sheets

Ahora, al ingresar al archivo te fijarás que aparece la cerveza que has registrado, un ID del documento y un link en la tercera casilla. En ese link encontrarás el registro en sí mismo de tu cerveza.

Se abrirá un nuevo archivo, que es en donde podemos hallar todos los datos de nuestra cerveza. En la primera pestaña tenemos la vista del reporte general, pero a nosotros lo que nos interesa es la tercera pestaña: Data.

Aquí, convenientemente cabe darle formato a cada uno de los campos que tenemos, para no provocar conflictos con el ThingSpeak. Particularmente que la fecha no cuente con segundos (ya que al hacer muestreos cada 20 minutos, los segundos nos son irrelevantes), el Timepoint no cuente con comas de millares, y en SG el punto represente un decimal.

Con estos pasos podemos dar por terminado nuestro trabajo en el lado de Google Sheets, Raspberry y el Tilt, y nos adentramos en operar sobre ThingSpeak.

ThingSpeak

Evidentemente lo primero será crearnos una cuenta gratuita, para lo cual conviene utilizar o crear una de MathWorks, ya que ThingSpeak es un servicio de esta empresa y el tener una cuenta de ella nos da acceso a más herramientas.

Luego, lo primero será crear un canal. En este sistema, un canal representa un sensor, que puede tener 8 datos distintos (Fields) y uno extra que representa el Timestamp (Marca de tiempo, es decir fecha y hora a la que se accedió al dato).

Después de crear el canal procedemos a crear los campos. Para nuestro caso dejamos en blanco el primer campo y activamos los demás. Aunque solamente nos interesará conocer la Densidad Específica y la Temperatura. Guardamos el canal y pasamos al siguiente paso.

Nos vamos a la pestaña APPS, y entramos en Matlab Analysis, dando clic en Custom (no starter code). Así, pasamos a darle un nombre cualquiera al script y pasaremos a copiar el siguiente código en la sección Matlab Code.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function result = GetGoogleSpreadsheet(DOCID)
% result = GetGoogleSpreadsheet(DOCID)
% Download a google spreadsheet as csv and import into a Matlab cell array.
%
DOCID = '1YRY4nclTUDOCIDc38WPh-DkUBdM';
GID = '0';
%
loginURL = 'https://www.google.com'; 
csvURL = ['https://docs.google.com/spreadsheet/ccc?key=' DOCID '&gid=' GID '&output=csv&pref=2'];
%
%Step 1: go to google.com to collect some cookies
cookieManager = java.net.CookieManager([], java.net.CookiePolicy.ACCEPT_ALL);
java.net.CookieHandler.setDefault(cookieManager);
handler = sun.net.www.protocol.https.Handler;
connection = java.net.URL([],loginURL,handler).openConnection();
connection.getInputStream();

%Step 2: go to the spreadsheet export url and download the csv
connection2 = java.net.URL([],csvURL,handler).openConnection();
result = connection2.getInputStream();
result = char(readstream(result));

%Step 3: convert the csv to a cell array
result = parseCsv(result);

%Step 4: convert los datos de cell array a al tipo necesario
result = convertir(result);

%Step 5: Envio de datos al canal ThingSpeak
result = enviar(result);
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function data = parseCsv(data)
% splits data into individual lines
data = textscan(data,'%s','whitespace','\n');
data = data{1};
for ii=1:length(data)
   %for each line, split the string into its comma-delimited units
   %the '%q' format deals with the "quoting" convention appropriately.
   tmp = textscan(data{ii},'%q','delimiter',',');
   data(ii,1:length(tmp{1})) = tmp{1};
end

%Separo la última cadena, y la comparo con la penúltima, por ver si son iguales.
temp = length(data);
UltimoDato(1,:) = data(temp,:);
PenultimoDato(1,:) = data(temp-1,:);
tf = strcmp(UltimoDato(1,1),PenultimoDato(1,1));
if tf == 0
    data = UltimoDato;
else data = PenultimoDato;
end
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function out = readstream(inStream)
%READSTREAM Read all bytes from stream to uint8
%From: http://stackoverflow.com/a/1323535
import com.mathworks.mlwidgets.io.InterruptibleStreamCopier;
byteStream = java.io.ByteArrayOutputStream();
isc = InterruptibleStreamCopier.getInterruptibleStreamCopier();
isc.copyStream(inStream, byteStream);
inStream.close();
byteStream.close();
out = typecast(byteStream.toByteArray', 'uint8');
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function datos = convertir(datos)
%Arreglos
datos{:,2} = str2num(datos{:,2});    %De Timepoint de texto a numeros
datos{:,3} = str2num(datos{:,3});    %De SG de texto a numeros
datos{:,4} = str2num(datos{:,4});    %De Farenheit a Celcius
datos{:,4} = round((5/9)*(datos{:,4}-32),2);
datos(:,1) = replace(datos(:,1),'-','/'); %TimeStamp
d = datetime(datos{:,1}, 'InputFormat', 'MM/dd/yyyy HH:mm', 'TimeZone', 'America/Los_Angeles', 'Format', 'dd MMM yyyy HH:mm ZZZZ');
d.TimeZone = 'Europe/Madrid'; %AQUI VA TU ZONA HORARIA
datos{:,1} = d;
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function datos = enviar(datos)
channelID = 10TUCHANNELID3; %AQUI VA TU  Channel ID
writeKey = 'PVTUWRITEAPIKEYNO'; %AQUI VA TU Write API Key
tStamp = datos{:,1};
TP = datos{:,2};
SG = datos{:,3};
Temp = datos{:,4};
Color = datos{:,5};
Cerv = datos{:,6};
Comenta = datos{:,7};
thingSpeakWrite(channelID,{TP,SG,Temp,Color,Cerv,Comenta},'Fields',[2,3,4,5,6,7],'WriteKey',writeKey,'TimeStamp',tStamp);
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

NOTA 01/07/2020: El código mostrado no se encuentra aún optimizado, por lo cual, posteriormente iré subiendo correcciones. Sin embargo funciona correctamente. Si encuentras alguna falla, o tienes una sugerencia no dudes en contactarnos a través de los comentarios.

De este código es importante que cambiemos 5 datos por cada cerveza que estemos monitoreando:

DOCID
GID
channelID
writeKey
OPCIONAL: d.TimeZone: Por defecto está ‘Europe/Madrid’, pero depende del lugar geográfico en donde te encuentras, por lo cual, podrías cambiarlo a otros como ‘America/Guayaquil’ si es que estás en Ecuador, o si deseas conocer otras ciudades nos lo puedes preguntar o en Matlab ingresar el siguiente comando timezones(‘America’), el que te devolverá la lista de ciudades y zonas horarias soportadas.

DOCID y GID

Para poder obtener estos dos valores debes ir nuevamente a la hoja de Google Sheets en donde se encuentra los registros de tu cerveza. Te irás a la pestaña DATA, y una vez allí capturarás la dirección URL de esa página.

En mi caso la página se ve algo así: https://docs.google.com/spreadsheets/d/1YRY4nclMk0TUDOCIDuu3puSHc38WPh-DkUBdM/edit#gid=0

De donde DOCID es el valor que hemos dejado en negritas y hemos borrado una parte por seguridad, pero deberás copiarlo así: ‘1YRY4nclMk0TUDOCIDuu3puSHc38WPh-DkUBdM’

GID, es el número que se encuentra al final, anecdóticamente, en mi caso es un simple 0, pero no necesariamente es así, pudiendo ser un número de unos 5-7 cifras. Como en el caso anterior deberás copiarlo entre comillas simples: ‘0’

Estos dos datos los debes introducir al inicio del archivo.

Channel ID y WRITE API KEY

En este caso, la información se la encuentra en un lado del MATLAB ANALYSIS. Ahí se encuentran los datos que requerimos, y que deberemos copiarlos en nuestro código anteriormente presentado en la function datos = enviar(datos), casi al final.

TimeControl

Para terminar, iremos hasta el final del MATLAB ANALYSIS y añadiremos una App llamada TimeControl.

Ahí, seleccionaremos una frecuencia recurrente de 20 minutos, y en acción debe quedar seleccionado en Matlab Analysis, y el Code to Execute el nombre del que creamos, en mi caso TILT1. Con esta acción pretendemos que ThingSpeak de forma automática ejecute el código que anteriormente programamos de forma recurrente.

Guardamos y finalmente damos clic en Save and Run.

De Tilt a ThingSpeak: visualización

Hecho todo esto podemos ir a la pestaña Channels, en el que se visualizarán los datos que el sensor automáticamente envía.

En mi caso, se puede observar tantos los datos de Densidad Específica y Temperatura, pero internamente se están guardando todos los demás que el Tilt entrega a ThingSpeak.

Cualquier cervecero se dará cuenta que los datos mostrados no tienen sentido, pero no es más que para las pruebas de este trabajo introduje el Tilt en un vaso con azúcar y no representa a una cerveza normal.

Con todo esto hemos conseguido el objetivo inicial de conectar y visualizar los datos obtenidos por un Tilt en ThingSpeak.

Nuestro siguiente proyecto será conectar un Airlock Plaato, también con ThingSpeak, con el fin de ver todas las funcionalidades que se puede tener al poseer todos los datos de diferentes sensores y sistemas en una misma plataforma.

¿Te ha gustado este proyecto? ¿Crees que debamos corregir algo? ¿Te parece útil saber cómo usar de forma alternativa los datos de sensores como el Tilt? Cuéntanos más en los comentarios.