Help on reading data from google sheet

Projects Programming
1

Hi, anyone has encountered this while reading data from google sheet. My code compiles and runs OK but does not return any value from google sheet. I want to read a cell in google sheet and get the value (message) to ESP8266 to be used in my LED board project. But the respond returns nothing. My code will read the value in cell A2 every 5 seconds and returns the value as message.

This is my code:

#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>

const char * ssid = "xxxxxxx";   // Wifi Username
const char * password = "yyyyyyyy";  // Wifi Password
String GOOGLE_SCRIPT_ID = "AKfycbxw0eRx6qgPUBqW6VcD4BTiYbVC-2GEev_43kiiQHQrL9PYFUOhQClTfq7KXTf3Afsitg";
String message;

WiFiClientSecure client;

void setup()
{
  Serial.begin(19200);

  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid, password);

  Serial.println("Connecting to Wi-Fi");
  while (WiFi.status() != WL_CONNECTED)
  {
    delay(500);
    Serial.print(".");
  }
  Serial.print("Wifi Connected: ");
  Serial.println("OK");
}

void loop()
{
  read_google_sheet();
  delay(5000);
}

void read_google_sheet(void)
{
  //-----------------------------------------------------------------------------------
  std::unique_ptr<BearSSL::WiFiClientSecure>client(new BearSSL::WiFiClientSecure);
  client->setInsecure();
  HTTPClient https;
  String url = "https://script.google.com/macros/s/" + GOOGLE_SCRIPT_ID + "/exec?read=A2";
  Serial.println("Reading Data From Google Sheet.....");
  Serial.print("URL: ");Serial.println(url);
  https.begin(*client, url.c_str());
  //-----------------------------------------------------------------------------------
  //Removes the error "302 Moved Temporarily Error"
  https.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS);
  //-----------------------------------------------------------------------------------
  //Get the returning HTTP status code
  int httpCode = https.GET();
  Serial.print("HTTP Status Code: ");
  Serial.println(httpCode);
  //-----------------------------------------------------------------------------------
  if (httpCode <= 0) {
    Serial.println("Error on HTTP request");
    https.end();
    return;
  }
  //-----------------------------------------------------------------------------------
  //reading data comming from Google Sheet
  String payload = https.getString();
  Serial.println("Payload: " + payload);
  //-----------------------------------------------------------------------------------
  if (httpCode == 200)
    message = payload;
  Serial.println("Message: "+ message);
  Serial.println("--------------------");
  //-------------------------------------------------------------------------------------
  https.end();
}

The serial monitor output this:

.......Wifi Connected: OK
Reading Data From Google Sheet.....
URL: https://script.google.com/macros/s/AKfycbxw0eRx6qgPUBqW6VcD4BTiYbVC-2GEev_43kiiQHQrL9PYFUOhQClTfq7KXTf3Afsitg/exec?read=A2
HTTP Status Code: 200

Message: 
--------------------
Reading Data From Google Sheet.....
URL: https://script.google.com/macros/s/AKfycbxw0eRx6qgPUBqW6VcD4BTiYbVC-2GEev_43kiiQHQrL9PYFUOhQClTfq7KXTf3Afsitg/exec?read=A2
HTTP Status Code: 200

Message: 
--------------------

If I copy the URL and paste it in a browser it shows the value in cell A2. I don't know what is wrong with my code. Your help is much appreciated. Thanks.

2

what's weird is that your console does not show this

nor

  Serial.println("--------------------");

are you really showing us the real output of the code you posted?

note that your client variable in the function obfuscate the global one.

PS: you might want to edit out your credentials from your post...

3

I did notice that payload wasn't shown, that is why there is an empty line before Message:

I edited this line after I compiled and uploaded the code.

I copied this code from somewhere, and trying to understand what it does. I'm trying to figure out why my code does not work. Or maybe it has something to do with Google Script.

var SS = SpreadsheetApp.openById('Google Sheet ID');
var sheet = SS.getSheetByName('Sheet1');

function doGet(e){
  
  var read = e.parameter.read;
  
  if (read !== undefined){
    return ContentService.createTextOutput(sheet.getRange(read).getValue());
  }
  
  return ContentService.createTextOutput("No value passed as argument to script Url.");
}
4

even if payload was empty you should see "Payload: "

what else did you change?...

5

Yes it should but for some reason it did not.

Just that ------ line to separate the request cycle.

p/s: I updated the output but the ----- does not appear and Message becomes bold :sweat_smile:

6

use code tags to post the output of the console (I fixed it)

7

okay thanks.. will update :innocent:

8

It turns out that I made a mistake while deploying the web app script. I chose "Anyone with Google account" instead of just "Anyone". Now my code is working fine (there was nothing wrong with it in the first place :sweat_smile:) and I managed to add some additional code to read and write to Google sheet. Thanks to @J-M-L for spending time helping me out here.

9

Good to know !

Season’s greetings :christmas_tree:

10

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