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:
- 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);
}