To create your own server
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'); } }
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: /* #include <ESP8266WiFi.h> // Replace with your network credentials const char* host = "script.google.com"; const int httpsPort = 443; HTTPSRedirect client(httpsPort); //WiFi.begin(ssid, password); //HTTPSRedirect client(httpsPort); bool flag = false; int counter = 0; String url = String("/macros/s/") + GScriptId + "/exec?task=log&val1=" + counter; // In my testing on a ESP-01, a delay of less than 1500 resulted 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/ |
Robotics >