At first, my question is from my little project IoT. Purpose, using ESP32 send data to Google Sheet through Google App Script.
The ESP32 task is to take two random numbers and pass them to Google Sheet through Google App Script (Web App) by http.
My problem is, I can insert data to Google Sheet only 3-4 records, after that connection between ESP32 and Google App Script has failed everytime. I am not sure about the problem.
My codes has 2 parts, first is for ESP32 (Arduino IDE), and second is for Google App Script
First part code is below, it is very simple.
#include <WiFi.h>
#include <WiFiClientSecure.h>
String t;
const char* host = "script.google.com";
const int httpsPort = 443;
const char* ssid = "MY_SSID";
const char* pass = "MY_SSID_PW";
String GAS_ID = "GAS_ID";
String GAS_Sheet = "dht_data";
WiFiClientSecure client;
long now = millis();
long lastMeasure = 0;
void setup() {
Serial.begin(115200); delay(500);
WiFi.begin(ssid, pass);
Serial.print("\nConnecting");
while (WiFi.status() != WL_CONNECTED) {
Serial.print(".");
}
Serial.print("\nSuccessfully connected to : "); Serial.println(ssid);
Serial.print("\nIP address: "); Serial.println(WiFi.localIP());
Serial.println();
client.setInsecure();
}
void loop() {
now = millis();
if (now - lastMeasure > 30000) {
lastMeasure = now;
float ValueSensor1 = random(2000, 4000) / 100.0;
float ValueSensor2 = random(6000, 8000) / 100.0;
Serial.println();
Serial.print("\ntemperature = "); Serial.print(ValueSensor1, 1);
Serial.print("\thumidity = "); Serial.print(ValueSensor2, 1);
Serial.println();
sendData(ValueSensor1, ValueSensor2);
}
}
void sendData(float SValue1, float SValue2) {
Serial.println("==========");
Serial.print("connecting to "); Serial.println(host);
//---- Connect to Google host
if (!client.connect(host, httpsPort)) {
Serial.println("connection failed");
return;
}
//---- Post Data
String url;
url += "/macros/s/" + GAS_ID + "/exec?";
url += "id=" + String(GAS_Sheet);
url += "&temperature=" + String(SValue1, 2);
url += "&humidity=" + String(SValue2, 2);
Serial.print("requesting URL: "); Serial.println(url);
client.print(String("GET ") + url + " HTTP/1.1\r\n" +
"Host: " + host + "\r\n" +
"User-Agent: BuildFailureDetectorESP8266\r\n" +
"Connection: close\r\n\r\n");
Serial.println("request sent");
//---- Wait Echo
while (client.connected()) {
String line = client.readStringUntil('\n');
if (line == "\r") {
Serial.println("headers received");
break;
}
}
String line = client.readStringUntil('\n');
if (line.startsWith("{\"state\":\"success\"")) {
Serial.println("ESP-32/Arduino CI successfull!");
} else {
Serial.println("ESP-32/Arduino CI has failed");
}
Serial.print("reply was : ");
Serial.println(line);
Serial.println("closing connection");
Serial.println("==========");
Serial.println();
}
Second part code is below,
function doGet(e){
// open the spreadsheet
var ss = SpreadsheetApp.getActive();
// use the 'id' parameter to differentiate between sheets
var sheet = ss.getSheetByName(e.parameter["id"]);
// extract headers
// getRange accepts row, col, number_of_rows and num_of_cols as argument
// getLastColumn returns the position of the last column that has content
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
// store the position of the last row
var lastRow = sheet.getLastRow();
var cell = sheet.getRange('a1');
var col = 0;
var d = new Date();
for (i in headers){
// loop through the headers and if a parameter name matches the header name insert the value
if (headers[i] == "Timestamp") {
val = d.toDateString() + ", " + d.toLocaleTimeString();
} else {
val = e.parameter[headers[i]];
}
// append data to the last row
cell.offset(lastRow, col).setValue(val);
col++;
}
return ContentService.createTextOutput('success');
}
What is the cause of problem? and How can I solve it?
Thank you very much in advance.
