Sending to google sheets does not work

Projects General Guidance
1

I am trying to send data from an LDR to google sheets using arduino Mega and sim900 GPRS module, but apparently I am having a problem with the AT+HTTPREAD function and have not been able to fix it so far.
Any guidance?

#include <SoftwareSerial.h>

// Configuração do módulo SIM900
#define SIM900_RX_PIN 7
#define SIM900_TX_PIN 8

SoftwareSerial sim900Serial(SIM900_RX_PIN, SIM900_TX_PIN);

// Configuração do sensor
#define SENSOR_PIN A0

// Configuração do Google Sheets
#define GOOGLE_SHEETS_API_KEY "YOUR_API_KEY"
#define GOOGLE_SHEETS_ID "YOUR_SHEET_ID"

void setup() {
  Serial.begin(9600);
  sim900Serial.begin(9600);
  delay(2000);
  
  // Inicialização do módulo SIM900
  sendATCommand("AT");
  sendATCommand("AT+CMGF=1");
  sendATCommand("AT+CNMI=2,2,0,0,0");
  sendATCommand("AT+CGATT=1");
  sendATCommand("AT+SAPBR=3,1,\"Contype\",\"GPRS\"");
  sendATCommand("AT+SAPBR=3,1,\"APN\",\"claro.com.br\"");
  sendATCommand("AT+SAPBR=1,1");
}

void loop() {
  // Leitura do valor do sensor
  int sensorValue = analogRead(SENSOR_PIN);
  
  // Montagem da URL do Google Sheets
  String url = "/macros/s/AKfycbxsbJl8s4WjWqHjANlVW7OLiVCmo5E2ky1CijnBAcJhMwVn7nx_Qj9vYxi3VU43/exec?value=" + String(sensorValue);
  
  // Estabelecimento da conexão com o Google Sheets
  if (!sendATCommand("AT+HTTPINIT")) {
    Serial.println("Erro ao enviar comando AT+HTTPINIT.");
    while (1); // Parar a execução em caso de erro
  }
  
  if (!sendATCommand("AT+HTTPPARA=\"CID\",1")) {
    Serial.println("Erro ao enviar comando AT+HTTPPARA (CID).");
    while (1); // Parar a execução em caso de erro
  }
  
  if (!sendATCommand("AT+HTTPPARA=\"URL\",\"https://script.google.com" + url + "\"")) {
    Serial.println("Erro ao enviar comando AT+HTTPPARA (URL).");
    while (1); // Parar a execução em caso de erro
  }
  
  if (!sendATCommand("AT+HTTPACTION=0")) {
    Serial.println("Erro ao enviar comando AT+HTTPACTION.");
    while (1); // Parar a execução em caso de erro
  }

  // Leitura da resposta
  String response = sendATCommand("AT+HTTPREAD=?");
  if (response.indexOf("+HTTPREAD: 0") != -1) {
    Serial.println("Dados enviados com sucesso para o Google Sheets!");
  } else {
    Serial.println("Falha ao enviar dados para o Google Sheets.");
  }

  // Encerramento da conexão
  if (!sendATCommand("AT+HTTPTERM")) {
    Serial.println("Erro ao enviar comando AT+HTTPTERM.");
    while (1); // Parar a execução em caso de erro
  }

  delay(5000);  // Espera 5 segundos antes de enviar os dados novamente
}

And this is the response received on the serial monitor:

19:16:16.409 -> Comando: AT+HTTPTERM
19:16:16.409 -> Resposta: 
19:16:21.557 -> Comando: AT+HTTPINIT
19:16:21.557 -> Resposta: 
19:16:21.652 -> Comando: AT+HTTPPARA="CID",1
19:16:21.652 -> Resposta: 
19:16:21.930 -> Comando: AT+HTTPPARA="URL","https://script.google.com/macros/s/AKfycbxsbJl8s4WjWqHjANlVW7OLiVCmo5E2ky1CijnBAcJhMwVn7nx_Qj9vYxi3VU43/exec?value=210"
19:16:22.059 -> Resposta: 
19:16:22.151 -> Comando: AT+HTTPACTION=0
19:16:22.151 -> Resposta: 
19:16:22.244 -> Comando: AT+HTTPREAD=?
19:16:22.244 -> Resposta: 
19:16:22.291 -> Falha ao enviar dados para o Google Sheets.
2

So do we. Using Your native language makes all not code text useless.
Why not post i a forum section of Your native language?

3

That's a little harsh. This looks like Portuguese, but the comments so closely mirror the code (and are thus kind of useless) that if you just look for latin similarities, it pretty much pops out. You probably don't need translate.google.com (which is awesome) to guess that
// Estabelecimento da conexão com o Google Sheets
is
Establish a connection to Google Sheet. or that "successo" in that context is probably "success" and "falha" looks pretty much like "failure"? If you can order lunch from an Italian or Mexican restaurant, this just isn't that bad. Your overall point, however IS valid. If this code had actually been hard or if it were a language that's not so close to English, it's totally fair to place the support load back to the people that don't have to additionally add a translation load.

Pablo, you seem to be laying the problem at the feet of Google Sheets (have you actually replaced your API keys like it says in the code?) but if you copy and paste that generated URL into a plain ole browser does it generate a post that lands in your spreadsheet? If not, you can focus on the Sheets API side of the fence.

More worrisome to me, having less experience with 3g modems than with Portuguese, is that your program keeps barking AT commands at the modem, but the "Reposta" (response) is always nothing. If it were 1985 and this was a Hayes compatible modem, I'd expect to see 'OK' after each of the AT+whatever

Pablo, are you sure that connections with your modem are working as expected? Maybe it's on on the pins you think it is or the TX and RX lines are crossed and shouldn't be (or shouldn't be, but are) or the modem really isn't at 9600 baud or there's some kind of additional setup that you have to do beyond the Serial.begin (parity, word size, etc.) or that you need to express to sim900Serial.begin() the pins that your modem is connected on.

Your Arduino is the boss or the endpoint of two conversations: one over a serial port with the modem and one over the internet (which travels via your modem) with scripts.google.com. I'd thus divide the problem into two parts: the "talking to the modem" part and the "talking to Sheets" part. Until the first works, the second is a bit moot.

4

There is an add in for Excel if you have it , which lets you collect data over a serial port . Might be an alternative ??

5

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.