Esp8266 Google Sheet Pager

An Epaper screen displaying message from google sheet powered by an esp8266

• esp8266 (nodemcu 1.0)
• Epaper screen (WeAct 2.9") from aliexpress

Here is where I found the "inspiration" for my code

For dealing with https request

https://gitlab.com/makeitforless/spotify_controller

For google sheet connection

https://www.hackster.io/thatiotguy/sensor-data-upload-to-google-sheets-through-nodemcu-632358

You should start by creating a new spreadsheet into your google drive

In the URL you can find the sheet ID that you can save, we will be needing it later on

https://docs.google.com/spreadsheets/d/1mhS...8T982UDOOjZNf_RYkU/edit?gid=0#gid=0

Then we need to link this sheet to a custom API, google script will allow you to do that,

https://script.google.com/home/start

You can create a new project, and paste the following code,

don't forget to add your spreadsheet ID.

This API will be accessible from anyone, as a lvl 0 security I added a password as a key you can put anything you want.

The password will be clearly exposed in the request.

function doGet(e) { //e is input
  var result = 'Ok'; // assume success
  var sheet_id = 'your_spreadsheet_id';     // Spreadsheet ID
  var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet();   // get Active sheet
  var newRow = sheet.getLastRow() + 1;            
  var rowData = [];
  var my_str;
  rowData[0] = new Date();                      // Timestamp in column A
  if (e.parameter["pass"]=="any_pass_you_like"){//handshake
  var range = sheet.getDataRange();
  var values = range.getValues();
  return ContentService.createTextOutput(values[0]);//return the first cell value
  }
  return ContentService.createTextOutput("NOK");
}

This script will take the password as only parameter, if it's valid it will return the first cell of your spreadsheet.

You can then hit Deploy, new deployment (keep default, as web app)

It should be setup as "executed as you", with access to anyone as you device won't be able to authenticate.

It will give you the "Deployment ID", that you can save for next step.

Using Arduino IDE, with :

• esp8266 board installed (version 3.1.2 as I write this)
• Adafruit GFX library for display

Epaper screen connection:

• BUSY->D0
• RES->D1
• D/C->D2
• CS->D4
• SCL->D5
• SDA->D7
• GND->GND
• VCC->3.3V

Even though pins say SDA and SCL it is actually SPI and not I2C.

Then you can use the following arduino code, modify various "your_" fields, and upload it to your device.

#include <Arduino.h>
//Epaper import
#define ENABLE_GxEPD2_GFX 0
#include <GxEPD2_BW.h>
#include <GxEPD2_3C.h>
#include <Fonts/FreeMonoBold18pt7b.h>

//request import
#include <ESP8266HTTPClient.h>
#include <WiFiClientSecureBearSSL.h>
String GAS_ID = "your_Deployment_ID";
String sript_password = "your_password";
const char* host = "https://script.google.com";

//Wifi client
#include <ESP8266WiFi.h>
const char *ssid     = "your_wifi_SSID";
const char *password = "your_wifi_password";


//screen setup
GxEPD2_BW<GxEPD2_290_BS, GxEPD2_290_BS::HEIGHT> display(GxEPD2_290_BS(/*CS=5*/ 2, /*DC=*/ 4, /*RES=*/ 5, /*BUSY=*/ 16)); // DEPG0290BS 128x296, SSD1680


//Display function
void helloFullScreenPartialMode(String input_message)
{
  const char fpm[] = "fast partial mode";
  const char spm[] = "slow partial mode";
  const char npm[] = "no partial mode";
  display.setPartialWindow(0, 0, display.width(), display.height());
  display.setRotation(1);
  display.setFont(&FreeMonoBold18pt7b);
  if (display.epd2.WIDTH < 104) display.setFont(0);
  display.setTextColor(GxEPD_BLACK);
  const char* updatemode;
  if (display.epd2.hasFastPartialUpdate)
  {
    updatemode = fpm;
  }
  else if (display.epd2.hasPartialUpdate)
  {
    updatemode = spm;
  }
  else
  {
    updatemode = npm;
  }
  // do this outside of the loop
  display.setTextSize(1);
  int16_t tbx, tby; uint16_t tbw, tbh;
  display.getTextBounds(input_message, 0, 0, &tbx, &tby, &tbw, &tbh);
  uint16_t hwx = ((display.width() - tbw) / 2) - tbx;
  uint16_t hwy = ((display.height() - tbh) / 2) - tby;
  display.firstPage();
  do
  {
    display.fillScreen(GxEPD_WHITE);
    display.setCursor(hwx, hwy);
    display.print(input_message);
  }
  while (display.nextPage());
  //Serial.println("helloFullScreenPartialMode done");
}

//Https request class
class Gcon {
private:
    std::unique_ptr<BearSSL::WiFiClientSecure> client;
    HTTPClient https;
public:
  Gcon(){
        client = std::make_unique<BearSSL::WiFiClientSecure>();
        client->setInsecure();
    }

  String get_cell_value(){
    String var_load = "/macros/s/" + GAS_ID + "/exec?pass=" + sript_password ;
    String full_url = host+var_load;//Whole url, host + script id


    //IDK how much HTTP10 is mandatory, as well as various headers, but here they are 
    https.useHTTP10(true);
    https.setFollowRedirects(HTTPC_FORCE_FOLLOW_REDIRECTS);//Google url will actually redirect to another url to access the data
    https.begin(*client,full_url);
    https.addHeader("User-Agent","BuildFailureDetectorESP8266");
    https.addHeader("Accept-Encoding","gzip");
    https.addHeader("Connection","close");


    String response = "none";
    int httpResponseCode = https.GET();
    if (httpResponseCode == HTTP_CODE_OK) {
      response = https.getString();
    }
    https.end();
    return response;
  }
};


Gcon google_connector; //Create Gcon object


void setup()
{
  Serial.begin(9600);
  WiFi.begin(ssid, password); //Connect to local wifi
  while ( WiFi.status() != WL_CONNECTED ) {
    delay ( 500 );
    Serial.print ( "." );
  }
  display.init(115200,true,50,false); //initiate Epaper module
}


void loop() {
String cell_val = google_connector.get_cell_value();
helloFullScreenPartialMode(cell_val);//send to display
Serial.println(cell_val);//send to terminal
delay(1000*11);//Check every 15sec
}

The code might not be optimized nor safe but here it is, at your own risk!

You can change the value of the spreadsheet cell, and see it appears few seconds later on screen.

You can find attached the printed housing that fit the Epaper screen, but there is no back panel yet.