Lendo dados do Google planilhas com ESP - Banco de dados

Lendo dados do Google planilhas com ESP - Banco de dados

No tutorial Banco de dados com Google planilhas com ESP, aprendemos a enviar dados para o Google Planilhas. Entretanto, para fazer justo ao nome "banco de dados", também precisamos recuperar dados da planilha para manipula-los ou até criar comandos de controle pela planilha, que permitirá, por exemplo, ativar atuadores ou ler dados que colocamos na planilha. Dessa forma, neste tutorial iremos aprender como ler os dados do Google Planilhas através de um ESP.

[toc]

Configurações iniciais - Requisitando dados do Google planilhas

O processo necessário para adquirir dados da planilha é quase o mesmo em relação ao método de envio (GET), entretanto para fazer isso, precisamos obter uma chave secreta. Esta chave faz o papel de "senha", para que ninguém consiga ler seus dados sem ela.

Atenção: Há maneiras mais seguras, como restringir o acesso da chave ou usar autenticador. Será mostrado o método mais simples.

Passo 1-) Entre na plataforma de gerenciamento de API's da Google: https://console.developers.google.com/apis , crie um novo projeto e de um nome qualquer.

Este projeto será apenas para "guardar" sua chave com a respectiva API, neste caso, Google planilhas.

Figura 1 - Criando um projeto de API.

 

Passo 2-) Vá em "Credenciais" e crie uma "Chave de API"

Figura 2 - Criando a Chave de API.

 

Figura 3 - Chave de API.

 

Feche a janela e a partir de agora, já temos nossa chave necessária para requisição de dados (e futuramente, altera-los).

 

Passo 3-) Volte para a página inicial do Google APIs, clique em "Biblioteca", selecione "Google Sheets API" e "Ativar"

Figura 4 - Ativando a API.

 

 

 

Terminamos de criar nossa chave e ativa-la, agora podemos manipular qualquer planilha que tenhamos salvo no Google Drive!


Mãos a obra - Lendo o Google planilhas

Componentes necessários

Código do projeto

Atenção: é necessário que você use os dados da sua planilha e faça uma alteração na mesma, explicaremos logo abaixo.

#include <ESP8266WiFi.h>

WiFiClientSecure cl;//Cria um cliente seguro (para ter acesso ao HTTPS)
String textFix = "GET /v4/spreadsheets/12IYuWdV0aJa8mQPhsR5C6AVEwZufyC05wufTrTJsSg/values/";
String key = "?key=IzaSyDmot3XwHfsNhqeuKdINMYxpyFK4cY";//Chave de API
//Essas Strings serao auxiliares, para nao precisar ficar re-escrevendo toda hora


void setup()
{
    Serial.begin(115200);//Inicia a comunicacao serial
    WiFi.mode(WIFI_STA);//Habilita o modo estaçao
    WiFi.begin("SUA REDE", "SUA SENHA");//Conecta na sua rede
    delay(3000);//Espera um tempo para se conectar no WiFi

}

void loop()
{

    if (cl.connect("sheets.googleapis.com", 443) == true)//Tenta se conectar ao servidor do Google APIs na porta 443 (HTTPS)
    {
        String toSend = textFix;//Atribuimos a String auxiliar na nova String que sera enviada
        
        toSend += "C2:C4";//Os valores que queremos ler da planilha. Para uma única célula, use algo como "A2"; para ler varios, use algo como "A1:C4".
        toSend += key;//Adicionamos a chave na String
        toSend += " HTTP/1.1";//Completamos o metodo GET para nosso formulario.

        cl.println(toSend);//Enviamos o GET ao servidor-
        cl.println("Host: sheets.googleapis.com");//-
        cl.println();//-
			

        Serial.println("Dado recebido:\n");//Mostra no Serial Monitor todo o pacote recebido.-
        Serial.print(cl.readString());//-
        cl.stop();//Encerramos a conexao com o servidor.
    }
    else
    {
        Serial.println("Erro ao se conectar");
    }
	

    delay(5000);

}

Ajustando o código para sua planilha

Antes de testar, precisamos alterar os dados do código para os seus dados e também compartilhar o link da sua planilha. Sem isso não irá funcionar!

Lembrando: altere apenas os dados destacados, o restante é necessário para funcionamento correto.

Passo 1-) Vá no começo do código e altere a chave (destacada) por sua chave.

Figura 5 - Alterando a Chave de API.

 

Passo 2-) Ainda nesta parte, altere o ID com o da planilha que você deseja obter os dados. Para isso vá em seu Google Drive, entre na planilha e pegue o ID:

Neste caso, vamos pegar a própria planilha criada na primeira parte do material.

configurando o google planilhas - banco de dados

Figura 6 - Alterando o ID da planilha.

 

Se você testar o código, verá que não temos permissão para acessar o banco de dados no Google planilhas. Isso acontece pois nossa "Chave de API" precisa estar com o link público (Qualquer pessoa com o link pode visualizar). Você pode contornar isso usando os Autenticadores ou adicionando Emails confiáveis ao sistema. Usaremos o mais simples para didática e fácil entendimento. Veja a saída do Serial monitor:

Figura 7 - Serial Monitor: Erro de permissão ao acessar o banco de dados no Google Planilhas

 

Passo 3-) Para deixar o link público, vá na sua planilha e siga os passos:

  • 1-) Clique em "Compartilhar" no canto superior direito.
  • 2-) Clique em "Avançado".

 

  • 3-) Clique em "Alterar...".

 

  • 4-) Clique em "Ativado: qualquer pessoa com link" e salve.

Colocando para funcionar

Agora podemos testar e já estará funcionando!

lendo as células do google planilhas - banco de dados com ESP
Figura 8 - Serial Monitor: Resposta de um grupo de células do nosso banco de dados no Google Planilhas

 

Veja que o pedido foi de um grupo de células, neste caso, C2:C4. Isso significa que iremos receber os dados das células C2,C3 e C4 em ordem da respectiva chamada.


Entendendo a fundo

Software

- String auxiliar (textFix)

String textFix = "GET /v4/spreadsheets/12IYuWdV0aJa8mQPhsR5C6AVEwZufyC05wufTrTJsSg/values/";

Basicamente é onde colocamos o ID da nossa planilha, você deve alterar para o ID de sua planilha!

- String auxiliar (key)

String key = "?key=IzaSyDmot3XwHfsNhqeuKdINMYxpyFK4cY";//Chave de API

Esta outra String, é onde atribuímos nossa Chave de API, você deve alterar para sua Chave de API.

- Obtenção dos dados

toSend += "C2:C4";

Aqui fica a grande jogada, onde conseguimos pegar células individuais, ou grupos de células. Vamos a um breve resumo.

Nossa planilha:

Banco de dados no Google Planilhas
Nosso banco de dados no Google Planilhas

 

Célula individual: Imagine que você precisa do valor da célula C2("Vida").

toSend += "C2";

 

Grupo de células: Imagine que você precisa do valor de um grupo de células, neste caso C2, C3 e C4 ("Vida", "De", "Silicio").

toSend += "C2:C4";

 

Podemos ver a resposta do Google ao pedido C2:C4 no Serial Monitor:

Lendo o banco de dados no Google planilhas
Lendo o banco de dados no Google planilhas

 

Você pode estar se perguntando: "Como vou usar essa String cheia de 'lixo' ?"

Este será o desafio de vocês! Futuramente criaremos um tutorial sobre manipulação de Strings. Mas aqui vai uma dica, procure sobre estes comandos em programação:

Com elas, você é capaz de pegar partes e caracteres específicos da String e ainda criar protocolos de mensagens para uma comunicação efetiva.


Desafio

Tente pegar apenas o valor que você queria da planilha. Você irá usar os dois comandos citados acima para "limpar" a String recebida pelo Google e assim tornando-a útil para o uso no microcontrolador.

Fechamento

Agora que você consegue obter valores da planilha, pode usar dados da planilha para algum processamento interno ou criar comandos de controle através da planilha! As possibilidades são muitas e depende de você. Boa sorte.

Referências

  • https://developers.google.com/sheets/guides/concepts
  • https://developers.google.com/sheets/guides/values

Banco de dados com Google planilhas com ESP32 / ESP8266

Banco de dados com Google planilhas - ESP

O Google planilhas é um "Excel online", que te permite criar tabelas e coisas do tipo, online, sem a necessidade de instalar no seu computador. Podemos compartilhar essa tabela para outras pessoas visualizarem ou edita-las. Nesse tutorial aprenderemos como usar o Google Planilhas para criar um banco de dados online, gratuito e simples, visto que não precisamos ter um servidor dedicado à hospedar o serviço. Esta primeira parte será ensinado apenas a enviar os dados usando um ESP.

[toc]

Banco de dados

Banco de dados é um conjunto de dados, normalmente relacionados entre si, como por exemplo: dados de clientes, LOG de temperaturas e coisas similares. Normalmente são dados organizados por colunas e linhas (matriz/tabela) para fácil entendimento por nós.

Poderíamos usar MySQL ou Firebase, que nos permite criar banco de dados de forma eficiente e confiável, entretanto,  é preciso ter um servidor apenas para hospedar seu banco de dados sempre disponível.

Você pode ler mais sobre Banco de dados e o uso do MySQL no tutorial: Arduino e MySQL – Registrando temperatura em um banco de dados usando o Shield Ethernet W5100

O Google planilhas oferece uma solução simples e gratuita para o armazenamento dos dados coletados. Podemos criar essas tabelas em conjunto do Google Forms, para enviar os dados dos nossos sensores, clientes e etc., sem a necessidade de um computador ou servidor pago hospedando o banco de dados, já que a Google se encarrega totalmente do serviço.

Obtendo os recursos necessários

Para usar esse incrível recurso precisaremos de uma conta no Google, e com ela criaremos dois arquivos, um de Planilha e outro Form.

Iremos através do formulário, enviar dados para a planilha (os dados do formulário irão para a planilha).

Passo 1-) Vá ao seu Google Drive e crie uma Planilha.

Passo 2-) Altere o nome da sua planilha para ser de fácil identificação nos próximos passos.

Figura 3 - Alterando o nome da planilha.
Figura 3 - Alterando o nome da planilha.

Passo 3-) Volte no Drive e crie um formulário.

Passo 4-) Altere o titulo do seu formulário para o desejado, e configure as perguntas.

Na planilha, os títulos das perguntas serão as colunas, e as respostas serão as linhas. Usaremos a opção "Resposta curta", que permite adicionar números e textos pequenos.

Passo 5-) Vá em "Respostas", vamos atribuir o formulário à planilha criada anteriormente. Clique nas "três bolinhas", e "Selecionar destino da resposta".

Passo 6-) Selecione a planilha desejada e estará pronto para receber nossos dados.

Agora, nosso formulário esta pronto para receber os dados e automaticamente transmitir para a planilha.

Para testar o formulário, clique no "Olho" ao canto superior direito, para abrir o link do formulário.

Figura 7 - Abrindo o link do formulário.
Figura 7 - Abrindo o link do formulário.

Após digitar o numero e clicar em "Enviar", será mostrado o aviso que a resposta foi registrada.

Figura 8 - Resposta enviada.
Figura 8 - Resposta enviada.

Agora, volte à sua Planilha, e veja que os resultados já estão aparecendo!

A coluna "Carimbo de data/hora" é preenchida automaticamente pelo sistema do Google, poupando imenso trabalho e requisitos do nosso sistema, dispensando até mesmo RTCs. A outra coluna "Numero" é o titulo da nossa pergunta, e a linha é a resposta que inseri.

Figura 9 - Resposta do formulário na planilha.
Figura 9 - Resposta do formulário na planilha.

Agora que aprendemos a utilizar o formulário em conjunto com a planilha, precisamos apenas integrar no Microcontrolador.


Mãos à obra

Componentes necessários

Conheça um pouco mais sobre ESP:

Código do projeto

Atenção: Não copie e cole o código inteiro, precisamos fazer algumas alterações no link utilizado. Será explicado logo abaixo em "Entendendo a fundo".

Caso esteja usando o ESP32, altere as bibliotecas removendo a ESP8266WiFi.h e incluindo as bibliotecas WiFi.h e WiFiClientSecure.h

Alguns usuário relatam erro com as versões mais novas do core esp8266 (quando você instala pelo menu (ferramentas > placa > gerenciar placas). A solução é instalar e usar a versão 2.4.0

// Código Banco de dados com Google planilhas com ESP - Vida de Silício

#include <ESP8266WiFi.h> 
// Alterar a linha anterior por #include <WiFi.h> se estiver usando ESP32
// #include <WiFiClientSecure.h> // Incluir esta biblioteca se estiver usando ESP32

WiFiClientSecure client;//Cria um cliente seguro (para ter acesso ao HTTPS)
String textFix = "GET /forms/d/e/1FAIpQLSdm6M_0mTVx_LKHLB1J3u_hjaag_hBtMfDHQlTIKe0EoatfsQ/formResponse?ifq&entry.717212213=";
//Essa String sera uma auxiliar contendo o link utilizado pelo GET, para nao precisar ficar re-escrevendo toda hora


void setup()
{
    Serial.begin(115200);//Inicia a comunicacao serial
    WiFi.mode(WIFI_STA);//Habilita o modo estaçao
    WiFi.begin("SUA REDE", "SUA SENHA");//Conecta na rede
    
    delay(2000);//Espera um tempo para se conectar no WiFi

}


void loop()
{
    if (client.connect("docs.google.com", 443) == 1)//Tenta se conectar ao servidor do Google docs na porta 443 (HTTPS)
    {
        String toSend = textFix;//Atribuimos a String auxiliar na nova String que sera enviada
        toSend += random(0, 501);//Adicionamos um valor aleatorio
        toSend += "&submit=Submit HTTP/1.1";//Completamos o metodo GET para nosso formulario.

        client.println(toSend);//Enviamos o GET ao servidor-
        client.println("Host: docs.google.com");//-
        client.println();//-
        client.stop();//Encerramos a conexao com o servidor
        Serial.println("Dados enviados.");//Mostra no monitor que foi enviado
    }
    else
    {
        Serial.println("Erro ao se conectar");//Se nao for possivel conectar no servidor, ira avisar no monitor.
    }

    delay(5000);
}

Colocando para funcionar

Após a editar o código com as suas informações, tudo irá funcionar corretamente. Veja como ficou o nosso, enviando valores aleatórios para nossa planilha a cada ~5 Segundos.


Entendendo a fundo

Precisamos fazer algumas alterações para o funcionamento do sistema com sua planilha. Sem isso, você irá enviar dados para nossa planilha de teste! (hehe)

Passo 1-)  Abra seu formulário (Figura 7).

Passo 2-) Copie todo o link entre "docs.google.com" e "/viewform". Salve isso em algum lugar, já iremos utilizar.

O nosso ficou "/forms/d/e/1FAIpQLSdm6M_0mTVx_LKHLB1J3u_hjaag_hBtMfDHQlTIKe0EoatfsQ".

Passo 3-)  Clique com o direito no "Input Text Box" e "inspecionar elemento".

Passo 4-) Agora, iremos pegar o nome desse Input Text Box.

O nosso é "entry.717212213".

Agora, devemos alterar esses dois valores no código, pois são os valores da sua planilha, e no código demonstrado de exemplo, foi utilizado nossos dados.

Obs: Altere apenas as partes destacadas, o restante é necessário para o correto funcionamento.

Atualização por contribuição do leitor Anselmo:

Caso tenha dificuldade em encontrar os entry, bastar seguir os seguintes passos:

    1. Clique no formulário com o botão direito do mouse e selecione "Exibir código fonte da pagina" (Ctrl+U);
    2. Agora pesquise pelo nome do campo inserido no formulário usando a ferramenta de pesquisa do navegador (Ctrl+F);
    3. No final do código vai encontra algo parecido com:  FB_PUBLIC_LOAD_DATA_ = [null,[null,[[490158642,"nome do campo",null,0,[[12345678,null,0,null,[[1,9,[""];
    4. O entry vai ser o numero após o colchete “12345678”,
    5. Inclua "entry." antes do número, fincando “entry.12345678”;
    6. Se você tiver outros campos, os outros entry do formulário vão estar na sequencia.

Exemplo: Campo do formulário: Teste var 1

    • Procure por “Teste var” (Ctrl+F);
    • No final do código vai encontra algo parecido com: "teste var 1",null,0,[[278629525,null,0,null,[[1,9,[""]
    • Copie o número após o colchete “278629525”
    • basta agora incluir "entry." antes do número , ficando “entry.278629525”

 

Passo 5-) Vá no começo do código e altere esta parte destacada, pelo que você copiou no item 2.

Esta parte é o "ID/KEY" do seu formulário, cada formulário tem o seu.

Passo 6-) Nessa mesma parte do código, altere essa outra parte destacada, pelo valor copiado no item 4.

Se você tiver mais de uma entrada para dados, a String ficaria por exemplo:

"GET /forms/d/e/1FAIpQLSdm6M_0mTVx_LKHLB1J3u_hjaag_hBtMfDHQlTIKe0EoatfsQ/formResponse?ifq&entry.717212213=123&entry.312212717=1234"

Os dados em negrito são os dados que você adicionaria, sendo de sensores, temperatura e etc.

Se você quiser testar com nossa planilha, sinta-se livre. Entretanto, é permitido apenas a visualização. Link da nossa planilha.

Software

- String auxiliar

String textFix = "GET /forms/d/e/1FAIpQLSdm6M_0mTVx_LKHLB1J3u_hjaag_hBtMfDHQlTIKe0EoatfsQ/formResponse?ifq&entry.717212213=";

Essa String textFix é uma auxiliar para nao precisar ficar reescrevendo toda hora, desde que essa parte é FIXA. O único valor que iremos alterar é após o  igual "=", que será o valor enviado à planilha.

- Função WiFiClient::connect()

if (client.connect("docs.google.com", 443) == 1)

Precisamos antes de enviar o método GET, se conectar no servidor. Essa função se conecta à URL (IP), na porta desejada.

- Corpo restante do GET

String toSend = textFix;//Atribuimos a String auxiliar na nova String que sera enviada
toSend += random(0, 501);//Adicionamos um valor aleatorio
toSend += "&submit=Submit HTTP/1.1";//Completamos o metodo GET para nosso formulario.

client.println(toSend);//Enviamos o GET ao servidor-
client.println("Host: docs.google.com");//-
client.println();//-
client.stop();//Encerramos a conexao com o servidor
Serial.println("Dados enviados.");//Mostra no monitor que foi enviado

Após a conexão com o servidor, adicionamos um valor aleatório na String final à ser enviada, e também terminamos o restante do necessário a ser enviado.


Desafio

Foi demonstrado apenas o envio de uma variável para a planilha, entretanto em qualquer projeto pode ser necessário o envio de diversas. Faça as alterações necessárias, adicionando os novos "Input Text Box" e tente enviar varias variáveis para sua planilha!

Fechamento

Em diversos projetos precisamos enviar dados ou guarda-los para analise, e um banco de dados como SQL pode ser desnecessário, uma vez que precisamos de um servidor para hospedar o banco de dados; com este método é possível facilmente criar seu banco de dados online, compartilhar com amigos e etc.

Na parte 1 foi mostrado apenas o procedimento para envio dos dados, entretanto, podemos precisar pegar valores da tabela para uso. Já a parte 2, você aprenderá como ler os valores da planilha para manipulação de dados ou até criar comandos de controle para o MCU: Lendo dados do Google planilhas com ESP – Banco de dados

Referências

  • https://developers.google.com/sheets/api/guides/values