Robotics‎ > ‎

Internet of Things

To create your own server
  • Create a new Google Spreadsheet
  • Click on Tools
  • Click Script Editor...
  • Copy and paste the following code:
var SpreadSheetID = '1soD-QzPojcVmjHAXLR70KA-EWajrQNGszXvRtW17TaY';
var ss = SpreadsheetApp.openById(SpreadSheetID);

function doGet(e) {
  if(e.parameter.task === 'get') {
    var sheet = ss.getSheetByName('Control Panel');
    var response = sheet.getRange(e.parameter.row,e.parameter.column).getValue();
    return ContentService.createTextOutput(response)
  } else if (e.parameter.task === 'controlpanel') {
    return HtmlService.createHtmlOutputFromFile('index');
  }
}
  • Click Publish
  • Click Deploy as Web App
    • Execute the app as yourself
    • Allow "Anyone, even anonymous" to access the app.
  • Copy the QR code into a new tab in your browser.
  • Test it by trying to get a value from the Control Panel sheet in your spreadsheet. For example, try:
https://script.google.com/macros/s/AKfycbyEVxRON5dLacSqIFNyQbZUBhFsobjG2DLIyjnufrEsnbm-zFWG/exec?task=get&row=1&column=2


Once you've tested your doGet function and are sure you are able to get data from your spreadsheet using get requests, copy and paste the following code into your Arduino IDE. You will also need to install my modified version o fthe HTTPSRedirect library. These will allow you to make get requests to Google services (which, prior to this library, was difficult because Google requires a secure connection and redirects to a different web page when sending a response). Pay particular attention to the lines with a yellow background. They indicate code that is specific to the HTTPSRedirect library.

/*
 *  HTTPS with follow-redirect example
 *  Created by Sujay S. Phadke, 2016
 *  Modified by Michael Backus on Jan 29th, 2017
 *  
 *  Based on the WifiClientSecure example by
 *  Ivan Grokhotkov
 *  *
 *  This example is in public domain.
 */

#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"

// Replace with your network credentials
const char* ssid = "DeviceNet";
const char* password = "T333l@nd";

const char* host = "script.google.com";
const char* googleRedirHost = "script.googleusercontent.com";
const char *GScriptId = "AKfycbyEVxRON5dLacSqIFNyQbZUBhFsobjG2DLIyjnufrEsnbm-zFWG";

const int httpsPort = 443;

HTTPSRedirect client(httpsPort);
  
void setup() {
  Serial.begin(115200);
  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());

  Serial.print("Connecting to ");
  Serial.println(host);

  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...");
  }
  
  Serial.flush();
  if (!flag){
    Serial.print("Could not connect to server: ");
    Serial.println(host);
    Serial.println("Exiting...");
    return;
  }
}

void loop() {
  if (!client.connected())
    client.connect(host, httpsPort);

  String url = String("/macros/s/") + GScriptId + "/exec?task=get&row=1&column=2";
  String red = client.printRedir(url, host, googleRedirHost);
  Serial.print("Red: ");
  Serial.println(red);

  // 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(1500);
}

Once you are able to get a single value back from your webserver, modify it so that it returns several values separated by commas. Below is a demo program that shows how you can store each value into a variable.
String msg = "123,321,312";
String red = "";
String green = "";
String blue = "";

void setup() {
  // put your setup code here, to run once:
  Serial.begin(115200);
  red = msg.substring(0,msg.indexOf(","));
  msg.remove(0,msg.indexOf(",")+1);
  green = msg.substring(0,msg.indexOf(","));
  msg.remove(0,msg.indexOf(",")+1);
  blue = msg;
}

Logging to a Google Spreadsheet
Modify the Google Web App to inlclude a log task. Make sure it returns a \n at the very end.
function doGet(e) {
  if(e.parameter.task === 'get') {
    var sheet = ss.getSheetByName('Control Panel');
    var response = sheet.getRange(e.parameter.row,e.parameter.column).getValue();
    return ContentService.createTextOutput(response)
  } else if (e.parameter.task === 'controlpanel') {
    return HtmlService.createHtmlOutputFromFile('index');
  } else if (e.parameter.task === "log") {
   var sheet = ss.getSheetByName("log");
   sheet.appendRow([e.parameter.val1,2,3]);
   return ContentService.createTextOutput('Data logged\n');  
  }
}

Modify the Arduino code to send the value of a counter:
/*
 *  HTTPS with follow-redirect example
 *  Created by Sujay S. Phadke, 2016
 *  Modified by Michael Backus on Jan 29th, 2017
 * 
 *  Based on the WifiClientSecure example by
 *  Ivan Grokhotkov
 *  *
 *  This example is in public domain.
 */

#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"

// Replace with your network credentials
const char* ssid = "GUEST_MSBSD";
const char* password = "";

const char* host = "script.google.com";
const char* googleRedirHost = "script.googleusercontent.com";
const char *GScriptId = "AKfycbyEVxRON5dLacSqIFNyQbZUBhFsobjG2DLIyjnufrEsnbm-zFWG";

const int httpsPort = 443;

HTTPSRedirect client(httpsPort);
 
void setup() {
  Serial.begin(115200);
  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);
  WiFi.begin(ssid);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());

  //HTTPSRedirect client(httpsPort);
  Serial.print("Connecting to ");
  Serial.println(host);

  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...");
  }
 
  Serial.flush();
  if (!flag){
    Serial.print("Could not connect to server: ");
    Serial.println(host);
    Serial.println("Exiting...");
    return;
  }
}

int counter = 0;
void loop() {

 
  if (!client.connected())
    client.connect(host, httpsPort);

  String url = String("/macros/s/") + GScriptId + "/exec?task=log&val1=" + counter;
  Serial.println(url);
  client.printRedir(url, host, googleRedirHost);
  counter++;

  // 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(2000);
}

Here is an example that shows how to log data to a Google spreadsheet: http://embedded-lab.com/blog/post-data-google-sheets-using-esp8266/
ċ
HTTPSRedirect.zip
(42k)
Michael Backus,
Jan 29, 2017, 10:29 PM
v.1
Comments