Como publicar dados no Google Sheets usando ESP32

Não existe dúvida que o Arduino mudou a forma do mundo ver a eletrônica, tornando o assunto muito mais amigável aos não especialistas no assunto.

Com isto, uma série de ideias e movimentos estão cada vez mais fortes. Nós da Crescer estamos dando robustez a estas ideias, tornando-as usuais na indústria e como produto comercializável.

Hoje vamos aprender como utilizar uma ESP32 para salvar dados em uma planilha do Google.

Sumário

1. Hardware

2. Configurando o Google Sheets

3. Programando a ESP32

1. Hardware

Para esse projeto foi utilizada a CPB32, entre as diversas funcionalidades dessa placa ela é equipada com um ESP32, que é um dual core de 32 bits, com 10x mais velocidade de clock que o Arduino Uno, entradas e saídas analógicas de 12 bits e o principal, WiFi e Bluetooth onboard. Na Figura a seguir podemos ver a CPB32.

O objetivo deste artigo é ter a CPB32 como um monitorador de consumo elétrico de uma empresa, publicando este consumo periodicamente em uma planilha do Google, com isto poderemos ter tal controle e banco de dados na nuvem. Este conceito pode ser expandido a muitos tipos de aplicações, como apontamento de produção em um chão de fábrica, medições de nível em caixas d’água, entre muitas outras ideias que virão.

O Google Sheets é uma das ferramentas do Google disponíveis dentro do Drive, é gratuita bastando ter uma conta do Gmail / Google, funcionando muito semelhante ao Excel. Certamente é um diferencial deste post, usar esta ferramenta.

Lembrando que tudo o mostrado neste post, funciona no ESP32 e com algumas modificações, no ESP8266 também.

Hardware utilizado:

1 – Fonte 24V / 1A

2 – CPB32

3 – Shield de Medição AC Crescer

4 – Sensor de corrente AC SCT013

2. Configurando o Google Sheets

O Google Sheets é uma ferramenta gratuita muito similar ao Excel, portanto, é uma excelente opção gratuita para criação de planilhas. Além disso, ele funciona totalmente online, portanto, pode ser usado como banco de dados em nuvem. Sendo necessário somente ter uma conta Google / Gmail.

Para a acessá-lo basta ir na página inicial do google e entrar no seu drive, Veja as figuras a seguir:

Vá em novo, depois em planilha em branco:

Clique no nome da planilha e renomeie, nós colocamos "BlogCrescerCPB32":

Agora vamos copiar a parte da URL que nos interessa, e guardar num arquivo de apoio para usarmos no momento correto, no nosso exemplo o link é:

18uN94-3XER_Jjs1-lryEyWkpFa0ORZZK9BHz3blM7_U

Agora, vamos agora criar um script para rodar Macro, para isso utilizamos o App Script. Para acessá-lo vamos em extensões, depois Apps Script, com essa extensão podemos programar diversas automações nas nossas planilha, aqui ela será usada para gravar, no arquivo, os dados recebidos.

A tela do Apps Script deve ser algo parecido com a figura a seguir:

O próximo passo é colar o código abaixo no lugar do código destacado na figura anterior.

function doGet(e) {
  Logger.log( JSON.stringify(e) ); // view parameters
  var result = 'Ok'; // assume success
  if (e.parameter == 'undefined') {
    result = 'No Parameters';
  }
  else {
    var sheet_id = '176RxIsfqc6a5fuljrN6-rRM_uF3Xf0k2GCXgnThYXek';  // Spreadsheet ID
    var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet(); // get Active sheet
    var newRow = sheet.getLastRow() + 1;
    var rowData = [];
    d = new Date();
    rowData[0] = d; // Timestamp in column A
    rowData[1] = d.toLocaleTimeString(); // Timestamp in column A

    for (var param in e.parameter) {
      Logger.log('In for loop, param=' + param);
      var value = stripQuotes(e.parameter[param]);
      Logger.log(param + ':' + e.parameter[param]);
      switch (param) {
        case 'value1': //Parameter 1, It has to be updated in Column in Sheets in the code, orderwise
          rowData[2] = value; //Value in column A
          result = 'Written on column A';
          break;
        case 'value2': //Parameter 2, It has to be updated in Column in Sheets in the code, orderwise
          rowData[3] = value; //Value in column B
          result += ' Written on column B';
          break;
        case 'value3': //Parameter 3, It has to be updated in Column in Sheets in the code, orderwise
          rowData[4] = value; //Value in column C
          result += ' Written on column C';
          break;
        default:
          result = "unsupported parameter";
      }
    }
    Logger.log(JSON.stringify(rowData));
    // Write new row below
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);
  }
  // Return result of operation
  return ContentService.createTextOutput(result);
}
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}

Agora devemos colar a URL copiada anteriormente no local indicado abaixo, comentado como //Spreadsheet ID:

Nós iremos inserir 3 valores, por isto podemos verificar no código os “value1”, “value2” e “value3”.

Agora vamos salvar o script, depois vamos executá-lo, esse passo é necessário para que o Apps Script solicite as permissões necessárias. Ele irá dar erro, pois, não estamos passando o parâmetro para o script, esse passo é somente para conceder a permissão.

A seguir veja a janela que deve aparecer quando clicar em executar:

Clicando em revisar permissões o seguinte deverá ser exibido, basta seguir os passos e permitir:

Ao final deve aparecer esse erro:

Agora vamos publicar o Script, pois o ESP32 irá buscar o script, e o script irá inserir os dados na planilha. Para isso vamos em, implantar e depois em nova implantação.

Na janela que irá abrir vá em app da web, como mostrado na figura a seguir:

Troque a opção destacada de somente eu para qualquer pessoa, como mostrado na figura a seguir, depois basta clicar em implantar.

Agora copie a URL em destaque na figura a seguir, salve ela em um arquivo, pois ela será usada no futuro.

https://script.google.com/macros/s/AKfycbz1_ARoBNobmMUcbKbqsTf3YEunSsSQwAuEZIst6sV1HfFjVTD5OGsUwLm17R771gse4A/exec

Para testar se o script está funcionando basta colar a URL no navegador e dar um enter. O resultado deverá ser uma tela escrito OK e serão gravados dados na planilha, como visto na figura a seguir:

Essa URL, que nos possibilita testar a planilha, é o caminho para todos os acessos a ela. Imaginem que encontramos um post de um pessoal que fez o APP inventor postar dados no Google Sheets, isto me faz pensar que nenhuma empresa pode rodar sem algum tipo de controle, pois está muito acessível tudo isto.

Como vimos no primeiro teste, gravamos na planilha apenas a data e hora, pois, utilizamos a URL sem nenhum outro parâmetro. Agora vamos adicionar o código a seguir no final da URL.

?value1=113&value2=125&value3=55

Como pode ser visto na figura a seguir os dados, value1, value2 e value3 foram gravados nas colunas.

https://script.google.com/macros/s/AKfycbz1_ARoBNobmMUcbKbqsTf3YEunSsSQwAuEZIst6sV1HfFjVTD5OGsUwLm17R771gse4A/exec?value1=113&value2=125&value3=55

A cada vez que rodar a URL, será inserida uma nova linha, abaixo, com os valores informados.

Se for necessário alterar o script deve-se refazer o processo de implantação, porém, para que a URL não seja alterada devemos fazer o seguinte. Vamos em implantar e depois em gerenciar implantações.

Vamos editar, destacado na figura a seguir.

Selecionamos nova versão, depois vamos implantar. Assim o script continua funcionando com a mesma URL.

3. Programando a ESP32

Agora vamos ao código que pode ser baixado no link abaixo:

https://github.com/casaautomacao/ESP32_GoogleSheets_v2.git

O código tem 2 partes distintas, sendo 1 em cada Task. A Task (atividade) que roda no Core 0, é a atividade de envio para o Google Sheets e leva cerca de 4 segundos. Já a atividade do Core 1, é a medição da corrente e tensão da rede e calculando o valor RMS destes sinais.

Caso você não entenda o conceito de Core, verifique o nosso blog anterior: Usando o Dual Core.

Vou apontar de agora em diante, alguns pontos chaves para funcionar e gerar o gráfico abaixo:

Na última aba tem algumas funções para conexão wifi.

Na primeira Aba, tem a configuração dos Cores no setup e a chamada da função da conexão WiFi.

Atenção, algumas versões o ESP32 não conecta na primeira tentativa no WiFi, necessitando resetar a placa para na próxima tentativa conectar.

Em nossas aplicações, quando não há sucesso na conexão, o ESP reseta e tenta novamente.

Na primeira aba temos a inserção do endereço do script, ali que é feita a amarração entre ESP32 e Google Script.

https://script.google.com/macros/s/AKfycbz1_ARoBNobmMUcbKbqsTf3YEunSsSQwAuEZIst6sV1HfFjVTD5OGsUwLm17R771gse4A/exec

O ponto de atenção principal nesta primeira Aba está no loop(), onde estamos desabilitando a proteção de watchdog no Core 0, por esta proteção não deixar a aplicação rodar mais que 1 hora sem que ocorra um reset.

Na aba da Task1 que roda no Core 1, temos o cálculo do RMS, o detalhamento vai depender dos comentários neste blog, se tiver bastante pergunta, vamos detalhar como foi feito o cálculo e qual a performance. Caso vocês não tenham interesse...

Mas basicamente ele calcula a tensão e a corrente RMS e disponibiliza para envio na Task2 que roda no Core 0.

Então na Task2 deve ter o que mais é necessário para o projeto funcionar.

Sendo uma função para conectar ao host, que é o próprio Google, e a montagem da URL e envio.

String url = String("https://script.google.com") + "/macros/s/" + GScriptId + "/exec?" + "value1=" + tensaoFinal + "&value2=" + String(correnteFinal, 2) + "&value3=" + tempoEnvio;

A montagem da String Request que tem todo o texto acima, é o ponto de maior atenção, pois precisa resultar em algo semelhante ao exemplo abaixo:

https://script.google.com/macros/s/AKfycbx6um9WNDjCKcdIuJljEPX7MQsTjGZ3Da1-tyTL2LfckhHHoZsW6uf9snyD7b-iMb632/exec?value1=113&value2=125&value3=55

E tudo isto enviado para o cliente que é o script do Google.

O que vem depois, fica para vocês olharem no Monitor Serial da IDE do Arduino...

FUNCIONAMENTO PRÁTICO COM APLICAÇÃO REAL: CPB32 COMO MEDIDORA DE CONSUMO DE ENERGIA ELÉTRICA

Avalie-nos no Google para que possamos alcançar e auxiliar cada vez mais pessoas a fazerem seus projetos e produtos! Contamos com você!

E aí o que achou desta aplicação?