Hi all,
I am using ESP32-WROOM-32D to send and receive data to the spreadsheet. But I am facing a problem in getting the "response body" from the Google Apps Script.
My code can successfully post data on Google Spreadsheet but it cannot return the text as mentioned in Google Apps Script.
I was expecting to receive an output such as below on the serial monitor. But the serial monitor shows nothing about the "response body".
Successfully wrote:
value: 1037.55
Below is the full program code.
#include "WiFi.h"
#include <HTTPSRedirect.h>
#include <Adafruit_MLX90614.h>
// WiFi credentials
const char* ssid = "";
const char* password = "";
// Gscript ID and required credentials
const char* host = "script.google.com";
const char* GScriptId = "";
const int httpsPort = 443;
String url = String("/macros/s/") + GScriptId + "/exec?value=";
HTTPSRedirect* client = nullptr;
Adafruit_MLX90614 mlx = Adafruit_MLX90614();
float temp = 0.0;
// --------------------------------------------------------------------------------------------------------
void setup() {
Serial.begin(115200);
mlx.begin();
setupWiFi();
setupData();
}
void loop(){
Serial.println("Inside MAIN");
float temp = mlx.readAmbientTempC();
Serial.println("Temp: " + String(temp) + "°C");
postData(temp); // Post the data to google spreadsheet
delay(10000); // Time delay of 10 sec
}
// --------------------------------------------------------------------------------------------------------
// Function to setup the WiFi connection
void setupWiFi(){
WiFi.begin(ssid, password); // Connect to the network
// Wait for the WiFi to be connected
while (WiFi.status() != WL_CONNECTED)
{
delay(500);
Serial.println("Connecting to WiFi..");
}
Serial.println("Connected to the WiFi network");
Serial.print(" IP address: ");
Serial.println(WiFi.localIP());
}
void setupData(){
client = new HTTPSRedirect(httpsPort);
client->setInsecure();
client->setPrintResponseBody(true);
client->setContentTypeHeader("application/json");
// Connect to host - "script.google.com"
Serial.print("Connecting to ");
Serial.println(host);
// Try to connect for a maximum of 5 times
bool WiFiFlag = false;
for (int i=0; i<5; i++)
{
int retval = client->connect(host, httpsPort);
if (retval == 1)
{
WiFiFlag = true;
break;
}
else
Serial.println("Connection failed. Retrying...");
}
// Connection Status, 1 = Connected, 0 is not.
Serial.println("Connection Status: " + String(client->connected()));
Serial.flush();
// Exit if the connection is failed
if (!WiFiFlag){
Serial.print("Could not connect to server: ");
Serial.println(host);
Serial.println("Exiting...");
Serial.flush();
return;
}
}
void postData(float value){
if (!client->connected())
{
Serial.println("Connecting to client again...");
client->connect(host, httpsPort);
}
String urlFinal = url + String(value);
Serial.println("URL created");
if (client != nullptr)
{
if (!client->connected())
{
client->connect(host, httpsPort);
urlFinal = url + String(value);
client->POST(urlFinal, host);
client->GET(urlFinal, host);
Serial.println(">> Succeed to POST data");
}
}
else {
Serial.println(">> Failed to POST data");
}
client->GET(urlFinal, host);
String payload = client->getResponseBody();
Serial.println(payload);
client->stop();
}
And this is the coding in Google Apps Script.
function doGet(e){
Logger.log("--- doGet ---");
var value = "";
try {
value = e.parameters.value;
var range = sheet.getRange('A1');
range.setValue(val);
return ContentService.createTextOutput("Successfully wrote:\n value: " + value);
}
catch(error) {
Logger.log(error);
return ContentService.createTextOutput("oops...." + error.message + "\n" + new Date()
+ "\nvalue: " + value);
}
}
Thanks for any tips or direction given!