Passing data from Google Sheets to nodeMCU OLED

Hello all,

I am a beginner and have cobbled together this code to pass the information in cell A1 on a google spreadsheet to my NodeMCU. Everything is working fine, the spreadsheet and NodeMCU are communicating and the display is showing almost everything but I can't seem to figure out how to print the contents of cell A1 to the screen, instead I'm getting "/macros/s...c?read". I've tried:

  1. display.print(url3);
    2.display.print(payload);
    3.display.print(String payload);

It looks like the code is printing the string only, and not running the GET request that reads cell A1. My serial monitor is showing "GET Data from cell 'A1':Tiprin" and I'd like 'Tiprin' to display on the screen. I hope this isn't an obvious answer, and I'm sure it's because I don't fully understand what is happening in my code.

The original GitHub Code is here: GitHub: ESP8266/HTTPSRedirect at master · electronicsguy/ESP8266 · GitHub

/*  HTTPS on ESP8266 with follow redirects, chunked encoding support
 *  Version 2.1
 *  Author: Sujay Phadke
 *  Github: @electronicsguy
 *  Copyright (C) 2017 Sujay Phadke <electronicsguy123@gmail.com>
 *  All rights reserved.
 *
 *  Example Arduino program
 */

#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"
#include "DebugMacros.h"
#include <SPI.h>
#include <Wire.h>
#include <Adafruit_GFX.h>
#include <Adafruit_SSD1306.h>

// for stack analytics
extern "C" {
#include <cont.h>
  extern cont_t g_cont;
}

// Fill ssid and password with your network credentials
const char* ssid = "FuzzyDuck";
const char* password = "QuackQuack";

const char* host = "script.google.com";
// Replace with your own script id to make server side changes
const char *GScriptId = "AKfycbyTQ1eI3beQh1xH-ZYfl9CTMrr_mRC5aoaeodzCp2kmC5EjtNw";

const int httpsPort = 443;

// echo | openssl s_client -connect script.google.com:443 |& openssl x509 -fingerprint -noout
const char* fingerprint = "";

// Write to Google Spreadsheet
String url = String("/macros/s/") + GScriptId + "/exec?value=Tiprin";
// Fetch Google Calendar events for 1 week ahead
String url2 = String("/macros/s/") + GScriptId + "/exec?cal";
// Read from Google Spreadsheet
String url3 = String("/macros/s/") + GScriptId + "/exec?read";

String payload_base =  "{\"command\": \"appendRow\", \
                    \"sheet_name\": \"Sheet1\", \
                    \"values\": ";
String payload = "";

HTTPSRedirect* client = nullptr;
// used to store the values of free stack and heap
// before the HTTPSRedirect object is instantiated
// so that they can be written to Google sheets
// upon instantiation

//Set up OLED screen
// If using SPI:
// Connect NodeMCU GND to display GND
// Connect NodeMCU 3V3 to display VCC

#define OLED_SDIN D7 //MOSI (Connect to display D1 or SDA)
#define OLED_SCLK D5 //SCLK (Connect to display D0 or SCL)
#define OLED_DC   D4 //Connect to display DC
#define OLED_RST  D3 //Connect to display RES
#define OLED_CS   D0 //Not connected to display, use a spare pin (e.g. D0 is NodeMCU LED)

// this is the constructor to use for hardware SPI pins as specfied above
Adafruit_SSD1306 display(OLED_DC, OLED_RST, OLED_CS); 

// this is the constructor to use for software SPI with different pin allocations
//Adafruit_SSD1306 display(OLED_SDIN,OLED_SCLK, OLED_DC, OLED_RST, OLED_CS); 
#define LOGO16_GLCD_HEIGHT 16 
#define LOGO16_GLCD_WIDTH  16 
static const unsigned char PROGMEM logo16_glcd_bmp[] =
{ B00000000, B11000000,
  B00000001, B11000000,
  B00000001, B11000000,
  B00000011, B11100000,
  B11110011, B11100000,
  B11111110, B11111000,
  B01111110, B11111111,
  B00110011, B10011111,
  B00011111, B11111100,
  B00001101, B01110000,
  B00011011, B10100000,
  B00111111, B11100000,
  B00111111, B11110000,
  B01111100, B11110000,
  B01110000, B01110000,
  B00000000, B00110000 };

#if (SSD1306_LCDHEIGHT != 64)
#error("Height incorrect, please fix Adafruit_SSD1306.h!");
#endif

void setup() {
  Serial.begin(115200);
  Serial.flush();

// by default, we'll generate the high voltage from the 3.3v line internally! (neat!)
  display.begin(SSD1306_SWITCHCAPVCC);  // initialize with the I2C addr 0x3C (for the 128x32)
  // init done
  
  // Show image buffer on the display hardware.
  // Since the buffer is intialized with an Adafruit splashscreen
  // internally, this will display the splashscreen.
  display.display();
  delay(500);

  // Clear the buffer.
  display.clearDisplay();
  
//Establish Wifi Connection
  Serial.println();
  Serial.print("Connecting to wifi: ");
  Serial.println(ssid);
  // flush() is needed to print the above (connecting...) message reliably, 
  // in case the wireless connection doesn't go through
  Serial.flush();

  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
  
//Display connection status on OLED
  display.setTextSize(1);
  display.setTextColor(WHITE);
  display.setCursor(0,0);
  display.print("Connected to: ");
  display.println(ssid);
  display.setCursor(0,16);
  display.print("IP address: ");
  display.println(WiFi.localIP());
  display.display();
  delay(500);
  display.clearDisplay();

  // Use HTTPSRedirect class to create a new TLS connection
  client = new HTTPSRedirect(httpsPort);
  client->setPrintResponseBody(true);
  client->setContentTypeHeader("application/json");
  
  Serial.print("Connected to ");
  Serial.println(host);


  // Try to connect for a maximum of 5 times
  bool flag = false;
  for (int i=0; i<5; i++){
    int retval = client->connect(host, httpsPort);
    if (retval == 1) {
       flag = true;
       break;
    }
    else
      Serial.println("Connection failed. Retrying...");
  }

  if (!flag){
    Serial.print("Could not connect to server: ");
    Serial.println(host);
    Serial.println("Exiting...");
    return;
  }
  
  if (client->verify(fingerprint, host)) {
    Serial.println("Certificate match.");
  } else {
    Serial.println("Certificate mis-match");
  }

  // Note: setup() must finish within approx. 1s, or the the watchdog timer
  // will reset the chip. Hence don't put too many requests in setup()
  // ref: https://github.com/esp8266/Arduino/issues/34
  
  Serial.println("\nGET: Write into cell 'A1'");
  Serial.println("=========================");

  // fetch spreadsheet data
  client->GET(url, host);

  // fetch spreadsheet data
  client->GET(url2, host);
  
   // fetch spreadsheet data
  client->GET(url3, host);
}

void loop() {
  static int error_count = 0;
  static int connect_count = 0;
  const unsigned int MAX_CONNECT = 20;
  static bool flag = false;
  
  
//Read Data from cell A1 and display on screen
display.setTextSize(1);
  display.setTextColor(WHITE);
  display.setCursor(0,0);
  Serial.print("GET Data from cell 'A1':");
  display.setTextSize(1);
  display.setTextColor(WHITE);
  display.setCursor(0,0);
  display.print("Bed belongs to:");
  display.display();
  delay(2000);
  display.clearDisplay();
 
  if (client->GET(url3, host)){
   ++connect_count;
     display.print(url3);
     display.display();
   delay(2000);
   display.clearDisplay();
   
  }
  else{
    ++error_count;
    DPRINT("Error-count while connecting: ");
    DPRINTLN(error_count);
  }

   // In my testing on a ESP-01, a delay of less than 1500 resulted 
  // in a crash and reboot after about 50 loop runs.
  delay(4000);
                          
}

Update: It looks like there is a method for this! I've added in the following towards the end and everything is working great!

  if (client->GET(url3, host)){
   ++connect_count;
   String str = client->getResponseBody();
     display.print(str);
     display.display();
   delay(2000);
   display.clearDisplay();

A great contribution from a 'beginner'

It not only works a treat and solved my problem but you also streamlined the messy example and cleaned out all the unnecessary memory information.

Thanks a lot! (:wink:
I can make good use of it.