{"id":12561,"date":"2016-11-23T11:48:18","date_gmt":"2016-11-23T16:48:18","guid":{"rendered":"http:\/\/embedded-lab.com\/blog\/?p=12561"},"modified":"2017-12-14T11:21:16","modified_gmt":"2017-12-14T16:21:16","slug":"post-data-google-sheets-using-esp8266","status":"publish","type":"post","link":"https:\/\/embedded-lab.com\/blog\/post-data-google-sheets-using-esp8266\/","title":{"rendered":"How to post data to Google sheets using ESP8266"},"content":{"rendered":"

In the past couple years, the ESP8266 platform has flourished dramatically and emerged as one of the most popular hardware tools among electronics hobbyists and IoT enthusiasts. Packed with a 32-bit RISC CPU running at 80 MHz, a fully integrated WiFi radio with TCP\/IP protocol stack, serial peripherals (I2C, SPI, and UART), an ADC channel, and general purpose I\/O pins, the ESP8266 is the most integrated and affordable WiFi solution available in the current IoT market space. An ESP8266 hardware, like NodeMCU and ESP-01, can directly interface with sensors using its peripherals and upload the sensor measurements to a local or a remote web server via internet. Right now, there are already quite a bit of cloud IoT platforms (ThingSpeak, thinger.io, TESPA.io, Xively, \u2026 the list is getting bigger everyday) that provides APIs and tools to allow the ESP8266 users to directly upload their sensor readings online for real-time visualization and global access. If you are a regular user of Google Drive, like me, you would find a Google sheet more approachable than all those IoT cloud platforms. In this tutorial, I will describe a method of connecting the ESP8266 device directly to a Google sheet for storing\u00a0the sensor data without using any third party plugin. For illustration, I am\u00a0using a NodeMCU<\/a><\/strong> board that reads the analog output from a soil moisture sensor inserted into one my flower pots and directly connects to\u00a0a spreadsheet on my Google Drive for storing the data.<\/p>\n

\"Soil<\/a>

ESP8266 data logging to Google spreadsheets<\/p><\/div>\n\n\n\n\n
\n

Related Posts<\/h3>\n<\/td>\n<\/tr>\n

\nCheck out How to send email and text messages using ESP8266<\/a><\/strong>.
\nCheck out our weather web server project<\/a><\/strong> for local web hosting of the sensor readings using ESP8266.
\nCheck out our ThingSpeak temperature and humidity logger project<\/a><\/strong> for remote server data logging example using ESP8266<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

Basically there are two parts to\u00a0this project. The first part is setting\u00a0up the ESP8266 hardware to read the soil moisture sensor output. The second part involves creating a Google sheet and configuring it\u00a0to accept the sensor data sent by the ESP8266 module over the\u00a0internet using a Google App Script\u00a0that is provided with this tutorial.<\/p>\n

Hardware setup<\/h3>\n

This project uses a very simple and inexpensive\u00a0soil moisture sensor (one you can buy on eBay<\/strong> or Aliexpress<\/strong> for\u00a0$1) kit consisting\u00a0of two exposed metal pads and an instrumentation circuit board. The exposed pads act\u00a0as probes for sensing the moisture level within the soil. The more water in the soil means higher conductivity between the two pads, and vice-versa. The accompanying instrumentation board provides an analog output voltage that varies with the moisture level of the soil. The instrumentation circuit is powered with a 3.3V supply and its\u00a0output is fed to the\u00a0analog input channel (A0) of the NodeMCU.<\/p>\n

\"Soil<\/a>

Soil moisture sensor consisting of exposed metal pads and an instrumentation circuit<\/p><\/div>\n

Note:<\/strong><\/span> The ESP8266 analog input can handle the maximum input voltage\u00a0of 1V. However, the NodeMCU implements a voltage divider circuit on board to expand the range of input voltage to 3.3V.<\/p>\n

Software<\/h3>\n

Let’s first try to understand what really happens when we try to send data to Google’s end. We will obviously be sending data using a GET request over an URL which has a pattern of https:\/\/script.google.com\/<\/strong>\u2026.. When you enter this URL in a web browser, the Google’s server responds back asking the browser to redirect to another URL with domain script.googleusercontent.com<\/strong>\u00a0with a\u00a0new GET request. For a web browser, the URL redirection\u00a0is a very common thing and it works without any problem. However, for ESP8266, this is not straight forward. The ESP8266 needs to correctly decode the header information received from the first server to\u00a0extract the redirect URL and make a second GET request to the new server.\u00a0To make this redirection simpler, Sujay Phadke<\/a>, aka\u00a0electronicsguy<\/em>, has shared on GitHub a beautiful piece of code in the form of Arduino Library named HTTPSRedirect<\/a><\/strong>. There are other examples<\/a> of Arduino posting data to Google spreadsheets using a third party API service, like pushingbox<\/a><\/strong>,\u00a0for handling Google’s\u00a0https requirement and URL redirection. The use of\u00a0HTTPSRedirect<\/a>\u00a0<\/strong>library makes the task much simpler by avoiding the need of any third party service. So, the first thing you need to do is to copy the HTTPSRedirect library files from GitHub and install into your Arduino libraries folder. I have also posted\u00a0the zipped library files at the following link for convenience.<\/p>\n

\nDownload HTTPSRedirect Library<\/a><\/p>\n
Update: The above library is outdated.<\/div>\n

Please go to GitHub Sujay Phadke<\/a> and download the updated library. Thanks Sujay.<\/strong><\/div>\n

In order to install it on your machine, simply download the above zipped file, unzip it, and move the folder named\u00a0HTTPSRedirect<\/strong> into your Arduino’s libraries location. On Windows PC, it typically goes to C:\\Users\\\\Documents\\Arduino\\libraries\\\u000b<\/strong>.<\/p>\n

<\/h5>\n
\"Arduino<\/a>

Arduino IDE Library Path on a Windows PC<\/p><\/div>\n

Make sure both HTTPSRedirect.cpp and HTTPSRedirect.h files exist inside the copied library folder as shown above.<\/p>\n

Program ESP8266 to send data to Google Sheets<\/h5>\n

Posted below is the ESP8266 code written using Arduino IDE for reading the soil moisture sensor output and posting it to a\u00a0Google Sheet on your Google Drive. In order to make it work for your case, you will need to update the ssid<\/strong><\/em> and password<\/strong><\/em> matching to your WiFi network. Also, you will also need to get the\u00a0*GScriptId<\/strong>,<\/em>\u00a0which can only be obtained after publishing the required Google App Scripts. The process of obtaining the\u00a0*GScriptId<\/em><\/strong> is explained towards the bottom of this tutorial. The sensor data are\u00a0posted to the Google sheet every 15 minutes.<\/p>\n

\/\/ BY: Akshaya Niraula<\/span>
\n\/\/ ON: Nov 11, 2016<\/span>
\n\/\/ AT: <\/span>http:\/\/www.embedded-lab.com\/<\/span><\/u><\/span> <\/p>\n

#include<\/span> <<\/span>ESP8266WiFi<\/span>.<\/span>h<\/span>><\/span>
\n#include<\/span> “HTTPSRedirect.h”<\/span> <\/p>\n

const<\/span> char<\/span>*<\/span> ssid<\/span> =<\/span> “–Your-SSID–“<\/span>;<\/span>
\nconst<\/span> char<\/span>*<\/span> password<\/span> =<\/span> “–Your-Password–“<\/span>;<\/span> <\/p>\n

\/\/ The ID below comes from Google Sheets.<\/span>
\n\/\/ Towards the bottom of this page, it will explain how this can be obtained<\/span>
\nconst<\/span> char<\/span> *<\/span>GScriptId<\/span> =<\/span> “—Your-Google-Script-ID–=”<\/span>;<\/span> <\/p>\n

\/\/ Push data on this interval<\/span>
\nconst<\/span> int<\/span> dataPostDelay<\/span> =<\/span> 900000<\/span>;<\/span>     \/\/ 15 minutes = 15 * 60 * 1000<\/span> <\/p>\n

const<\/span> char<\/span>*<\/span> host<\/span> =<\/span> “script.google.com”<\/span>;<\/span>
\nconst<\/span> char<\/span>*<\/span> googleRedirHost<\/span> =<\/span> “script.googleusercontent.com”<\/span>;<\/span> <\/p>\n

const<\/span> int<\/span> httpsPort<\/span> =<\/span>     443<\/span>;<\/span>
\nHTTPSRedirect<\/span> client<\/span>(<\/span>httpsPort<\/span>)<\/span>;<\/span> <\/p>\n

\/\/ Prepare the url (without the varying data)<\/span>
\nString<\/span> url<\/span> =<\/span> String<\/span>(<\/span>“\/macros\/s\/”<\/span>)<\/span> +<\/span> GScriptId<\/span> +<\/span> “\/exec?”<\/span>;<\/span> <\/p>\n

const<\/span> char<\/span>*<\/span> fingerprint<\/span> =<\/span> “F0 5C 74 77 3F 6B 25 D7 3B 66 4D 43 2F 7E BC 5B E9 28 86 AD”<\/span>;<\/span> <\/p>\n

\/\/ We will take analog input from A0 pin <\/span>
\nconst<\/span> int<\/span> AnalogIn<\/span>                 =<\/span> A0<\/span>;<\/span> <\/p>\n

void<\/span> setup<\/span>(<\/span>)<\/span> {<\/span>
\n     Serial<\/span><\/b>.<\/span>begin<\/span>(<\/span>115200<\/span>)<\/span>;<\/span>
\n     Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>“Connecting to wifi: “<\/span>)<\/span>;<\/span>
\n     Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>ssid<\/span>)<\/span>;<\/span>
\n     Serial<\/span><\/b>.<\/span>flush<\/span>(<\/span>)<\/span>;<\/span> <\/p>\n

    WiFi<\/span><\/b>.<\/span>begin<\/span>(<\/span>ssid<\/span>,<\/span> password<\/span>)<\/span>;<\/span>
\n     while<\/span> (<\/span>WiFi<\/span><\/b>.<\/span>status<\/span>(<\/span>)<\/span> !=<\/span> WL_CONNECTED<\/span>)<\/span> {<\/span>
\n             delay<\/span>(<\/span>500<\/span>)<\/span>;<\/span>
\n             Serial<\/span><\/b>.<\/span>print<\/span>(<\/span>“.”<\/span>)<\/span>;<\/span>
\n     }<\/span>
\n     Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>” IP address: “<\/span>)<\/span>;<\/span>
\n     Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>WiFi<\/span><\/b>.<\/span>localIP<\/span>(<\/span>)<\/span>)<\/span>;<\/span> <\/p>\n

    
\n     Serial<\/span><\/b>.<\/span>print<\/span>(<\/span>String<\/span>(<\/span>“Connecting to “<\/span>)<\/span>)<\/span>;<\/span>
\n     Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>host<\/span>)<\/span>;<\/span> <\/p>\n

    bool<\/span> flag<\/span> =<\/span> false<\/span>;<\/span>
\n     for<\/span> (<\/span>int<\/span> i<\/span>=<\/span>0<\/span>;<\/span> i<\/span><<\/span>5<\/span>;<\/span> i<\/span>++<\/span>)<\/span>{<\/span>
\n             int<\/span> retval<\/span> =<\/span> client<\/span>.<\/span>connect<\/span>(<\/span>host<\/span>,<\/span> httpsPort<\/span>)<\/span>;<\/span>
\n             if<\/span> (<\/span>retval<\/span> ==<\/span> 1<\/span>)<\/span> {<\/span>
\n                         flag<\/span> =<\/span> true<\/span>;<\/span>
\n                         break<\/span>;<\/span>
\n             }<\/span>
\n             else<\/span>
\n                     Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>“Connection failed. Retrying…”<\/span>)<\/span>;<\/span>
\n     }<\/span> <\/p>\n

    \/\/ Connection Status, 1 = Connected, 0 is not.<\/span>
\n     Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>“Connection Status: “<\/span> +<\/span> String<\/span>(<\/span>client<\/span>.<\/span>connected<\/span>(<\/span>)<\/span>)<\/span>)<\/span>;<\/span>
\n     Serial<\/span><\/b>.<\/span>flush<\/span>(<\/span>)<\/span>;<\/span>
\n     
\n     if<\/span> (<\/span>!<\/span>flag<\/span>)<\/span>{<\/span>
\n             Serial<\/span><\/b>.<\/span>print<\/span>(<\/span>“Could not connect to server: “<\/span>)<\/span>;<\/span>
\n             Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>host<\/span>)<\/span>;<\/span>
\n             Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>“Exiting…”<\/span>)<\/span>;<\/span>
\n             Serial<\/span><\/b>.<\/span>flush<\/span>(<\/span>)<\/span>;<\/span>
\n             return<\/span>;<\/span>
\n     }<\/span> <\/p>\n

    \/\/ Data will still be pushed even certification don’t match.<\/span>
\n     if<\/span> (<\/span>client<\/span>.<\/span>verify<\/span>(<\/span>fingerprint<\/span>,<\/span> host<\/span>)<\/span>)<\/span> {<\/span>
\n             Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>“Certificate match.”<\/span>)<\/span>;<\/span>
\n     }<\/span> else<\/span> {<\/span>
\n             Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>“Certificate mis-match”<\/span>)<\/span>;<\/span>
\n     }<\/span>
\n}<\/span> <\/p>\n

\/\/ This is the main method where data gets pushed to the Google sheet<\/span>
\nvoid<\/span> postData<\/span>(<\/span>String<\/span> tag<\/span>,<\/span> float<\/span> value<\/span>)<\/span>{<\/span>
\n     if<\/span> (<\/span>!<\/span>client<\/span>.<\/span>connected<\/span>(<\/span>)<\/span>)<\/span>{<\/span>
\n             Serial<\/span><\/b>.<\/span>println<\/span>(<\/span>“Connecting to client again…”<\/span>)<\/span>;<\/span>
\n             client<\/span>.<\/span>connect<\/span>(<\/span>host<\/span>,<\/span> httpsPort<\/span>)<\/span>;<\/span>
\n     }<\/span>
\n     String<\/span> urlFinal<\/span> =<\/span> url<\/span> +<\/span> “tag=”<\/span> +<\/span> tag<\/span> +<\/span> “&value=”<\/span> +<\/span> String<\/span>(<\/span>value<\/span>)<\/span>;<\/span>
\n     client<\/span>.<\/span>printRedir<\/span>(<\/span>urlFinal<\/span>,<\/span> host<\/span>,<\/span> googleRedirHost<\/span>)<\/span>;<\/span>
\n}<\/span> <\/p>\n

\/\/ Continue pushing data at a given interval<\/span>
\nvoid<\/span> loop<\/span>(<\/span>)<\/span> {<\/span>
\n     
\n     \/\/ Read analog value, in this case a soil moisture<\/span>
\n     int<\/span> data<\/span> =<\/span> 1023<\/span> –<\/span> analogRead<\/span>(<\/span>AnalogIn<\/span>)<\/span>;<\/span> <\/p>\n

    \/\/ Post these information<\/span>
\n     postData<\/span>(<\/span>“SoilMoisture”<\/span>,<\/span> data<\/span>)<\/span>;<\/span>
\n     
\n     delay<\/span> (<\/span>dataPostDelay<\/span>)<\/span>;<\/span>
\n}<\/span> <\/p>\n

You can also download the NodeMCU code from the following link.<\/p>\n

Download NodeMCU code<\/a><\/h5>\n
Create Google Sheets<\/h5>\n

Create a Google spreadsheet in your\u00a0Google Drive and name it something like DataCollector<\/strong>. Rename the current\/active sheet to Summary <\/strong>and add a second sheet, call it DataLogger<\/strong>. From the URL address bar of the sheet, copy the string between d\/ and \/edit and save it somewhere. This is your spreadsheet’s unique sharing key, which will be needed in the Google Apps scripts later.<\/p>\n

\"Spreadsheet<\/a>

Unique sharing key for your Google spreadsheet can be found in the URL<\/p><\/div>\n

Note: <\/strong><\/span> The name of the Google spreadsheet does not matter much because\u00a0we will be using the sharing key\u00a0of\u00a0the sheet, which is unique, in the Google Apps Scripts to access it. However, the sheets name (Summary <\/strong>and DataLogger<\/strong>) have to match with what you put in your\u00a0Google Apps Scripts (described later).<\/p>\n

In the Summary sheet, type Last Modified On<\/strong><\/em>\u00a0on cell\u00a0A1<\/strong>, DataLogger Count<\/strong><\/em> on A2<\/strong>, and Next Read Time<\/strong><\/em> on A3<\/strong>. In cell\u00a0B2<\/strong>, type a formula that will give us the data count ‘=counta(DataLogger!D:D) -1<\/em>‘ In B3<\/strong>, type this equation, =B1 + TimeValue(“00:15”)<\/em>, which will simply add 15 minute to last modified date time. To support a gauge chart in Google Sheets, some information from A6 to B7 has been added, as showing in the image below<\/p>\n

\"Goolge<\/a>

Goolge Sheets – Summary tag<\/p><\/div>\n

In the DataLogger <\/strong>sheet, type\u00a0ID, DateTime, Tag, and Value<\/strong> in A1, B1, C1 and D1<\/strong>, respectively.<\/p>\n

\"Google<\/a>

Google Sheets Data Logger Tab<\/p><\/div>\n

Google Apps Scripts<\/h5>\n

To create a Google App Scripts, go to Tools > Script Editor<\/strong> from the Google Sheets. In the code section, paste the code below. The code or script can be saved under any name.<\/p>\n\n

\/\/ BY: Akshaya Niraula<\/span>\n\/\/ ON: 2016 November 12th.<\/span>\n\/\/ AT: http:\/\/www.embedded-lab.com\/.....<\/span>\n \n \n\/\/ Steps are valid as of 2016 November 12th.<\/span>\n\/\/ 0) From Google spreadsheet, Tools &gt; Scriipt Editor...<\/span>\n\/\/ 1) Write your code<\/span>\n\/\/ 2) Save and give a meaningful name<\/span>\n\/\/ 3) Run and make sure "doGet" is selected<\/span>\n\/\/    You can set a method from Run menu<\/span>\n\/\/ 4) When you run for the first time, it will ask <\/span>\n\/\/    for the permission. You must allow it.<\/span>\n\/\/    Make sure everything is working as it should.<\/span>\n\/\/ 5) From Publish menu &gt; Deploy as Web App...<\/span>\n\/\/    Select a new version everytime it's published<\/span>\n\/\/    Type comments next to the version<\/span>\n\/\/    Execute as: "Me (your email address)"<\/span>\n\/\/    MUST: Select "Anyone, even anonymous" on "Who has access to this script"<\/span>\n\/\/    For the first time it will give you some prompt(s), accept it.<\/span>\n\/\/    You will need the given information (url) later. This doesn't change, ever!<\/span>\n \n\/\/ Saving the published URL helps for later.<\/span>\n\/\/ https:\/\/script.google.com\/macros\/s\/---Your-Script-ID--Goes-Here---\/exec<\/span>\n\/\/ https:\/\/script.google.com\/macros\/s\/---Your-Script-ID--Goes-Here---\/exec?tag=test&amp;value=-1<\/span>\n \n\/\/ This method will be called first or hits first  <\/span>\nfunction<\/span> doGet(<\/span>e)<\/span>{<\/span>\n  Logger.log<\/span>(<\/span>"--- doGet ---"<\/span>)<\/span>;<\/span>\n \n var<\/span> tag =<\/span> ""<\/span>,<\/span>\n     value =<\/span> ""<\/span>;<\/span>\n \n  try<\/span> {<\/span>\n \n    \/\/ this helps during debuggin<\/span>\n    if<\/span> (<\/span>e ==<\/span> null<\/span>)<\/span>{<\/span>e=<\/span>{<\/span>}<\/span>;<\/span> e.parameters<\/span> =<\/span> {<\/span>tag:<\/span>"test"<\/span>,<\/span>value:<\/span>"-1"<\/span>}<\/span>;<\/span>}<\/span>\n \n    tag =<\/span> e.parameters<\/span>.tag<\/span>;<\/span>\n    value =<\/span> e.parameters<\/span>.value<\/span>;<\/span>\n \n    \/\/ save the data to spreadsheet<\/span>\n    save_data(<\/span>tag,<\/span> value)<\/span>;<\/span>\n \n \n    return<\/span> ContentService.createTextOutput<\/span>(<\/span>"Wrote:\\n<\/span>  tag: "<\/span> +<\/span> tag +<\/span> "\\n<\/span>  value: "<\/span> +<\/span> value)<\/span>;<\/span>\n \n  }<\/span> catch<\/span>(<\/span>error)<\/span> {<\/span> \n    Logger.log<\/span>(<\/span>error)<\/span>;<\/span>    \n    return<\/span> ContentService.createTextOutput<\/span>(<\/span>"oops...."<\/span> +<\/span> error.message<\/span> \n                                            +<\/span> "\\n<\/span>"<\/span> +<\/span> new<\/span> Date<\/span>(<\/span>)<\/span> \n                                            +<\/span> "\\n<\/span>tag: "<\/span> +<\/span> tag +<\/span>\n                                            +<\/span> "\\n<\/span>value: "<\/span> +<\/span> value)<\/span>;<\/span>\n  }<\/span>  \n}<\/span>\n \n\/\/ Method to save given data to a sheet<\/span>\nfunction<\/span> save_data(<\/span>tag,<\/span> value)<\/span>{<\/span>\n  Logger.log<\/span>(<\/span>"--- save_data ---"<\/span>)<\/span>;<\/span> \n \n \n  try<\/span> {<\/span>\n    var<\/span> dateTime =<\/span> new<\/span> Date<\/span>(<\/span>)<\/span>;<\/span>\n \n    \/\/ Paste the URL of the Google Sheets starting from https thru \/edit<\/span>\n    \/\/ For e.g.: https:\/\/docs.google.com\/....\/edit <\/span>\n    var<\/span> ss =<\/span> SpreadsheetApp.openByUrl<\/span>(<\/span>"https:\/\/docs.google.com\/spreadsheets\/d\/---Your-Google-Sheet-ID--Goes-Here---\/edit"<\/span>)<\/span>;<\/span>\n    var<\/span> summarySheet =<\/span> ss.getSheetByName<\/span>(<\/span>"Summary"<\/span>)<\/span>;<\/span>\n    var<\/span> dataLoggerSheet =<\/span> ss.getSheetByName<\/span>(<\/span>"DataLogger"<\/span>)<\/span>;<\/span>\n \n \n    \/\/ Get last edited row from DataLogger sheet<\/span>\n    var<\/span> row =<\/span> dataLoggerSheet.getLastRow<\/span>(<\/span>)<\/span> +<\/span> 1<\/span>;<\/span>\n \n \n    \/\/ Start Populating the data<\/span>\n    dataLoggerSheet.getRange<\/span>(<\/span>"A"<\/span> +<\/span> row)<\/span>.setValue<\/span>(<\/span>row -<\/span>1<\/span>)<\/span>;<\/span> \/\/ ID<\/span>\n    dataLoggerSheet.getRange<\/span>(<\/span>"B"<\/span> +<\/span> row)<\/span>.setValue<\/span>(<\/span>dateTime)<\/span>;<\/span> \/\/ dateTime<\/span>\n    dataLoggerSheet.getRange<\/span>(<\/span>"C"<\/span> +<\/span> row)<\/span>.setValue<\/span>(<\/span>tag)<\/span>;<\/span> \/\/ tag<\/span>\n    dataLoggerSheet.getRange<\/span>(<\/span>"D"<\/span> +<\/span> row)<\/span>.setValue<\/span>(<\/span>value)<\/span>;<\/span> \/\/ value<\/span>\n \n \n    \/\/ Update summary sheet<\/span>\n    summarySheet.getRange<\/span>(<\/span>"B1"<\/span>)<\/span>.setValue<\/span>(<\/span>dateTime)<\/span>;<\/span> \/\/ Last modified date<\/span>\n    \/\/ summarySheet.getRange("B2").setValue(row - 1); \/\/ Count <\/span>\n  }<\/span>\n \n  catch<\/span>(<\/span>error)<\/span> {<\/span>\n    Logger.log<\/span>(<\/span>JSON.stringify<\/span>(<\/span>error)<\/span>)<\/span>;<\/span>\n  }<\/span>\n \n  Logger.log<\/span>(<\/span>"--- save_data end---"<\/span>)<\/span>;<\/span> \n}<\/span><\/pre><\/td><\/tr><\/table><\/div>\n\n
Important Note:<\/span><\/strong> In the above script, the sheet names should\u00a0match to those we want to be populated.<\/p>\n\n
    var<\/span> summarySheet =<\/span> ss.getSheetByName<\/span>(<\/span>"Summary"<\/span>)<\/span>;<\/span>\n    var<\/span> dataLoggerSheet =<\/span> ss.getSheetByName<\/span>(<\/span>"DataLogger"<\/span>)<\/span>;<\/span><\/pre><\/td><\/tr><\/table><\/div>\n\n
Similarly, you would also need to edit the spreadsheet sharing key in the script\u00a0to match with yours (one you copied from the spreadsheet URL earlier).<\/p>\n\n
var<\/span> ss =<\/span> SpreadsheetApp.openByUrl<\/span>(<\/span>"https:\/\/docs.google.com\/spreadsheets\/d\/---Your-Google-Sheet-ID--Goes-Here---\/edit"<\/span>)<\/span>;<\/span><\/pre><\/td><\/tr><\/table><\/div>\n\n
Download\u00a0Goolge App Scripts<\/a><\/strong><\/p>\n
Deploy the contents as Web app<\/h5>\n
Next step is to\u00a0publish the script so that it is accessible through\u00a0an URL. In order to do that, Publish > Deploy as Web App<\/strong>.<\/p>\n
Note: <\/strong> Whenever you modify your code, you have to create a “New” Project version and publish it otherwise you will still be hitting the same old code.<\/p>\n
Copy the Current web app URL<\/strong><\/em> and save\u00a0it somewhere as we will need it for extracting the GScriptID<\/strong>. I usually save it in my\u00a0code as a comment. The web app URL would look like this:<\/p>\n
https:\/\/script.google.com\/macros\/s\/–Your Google Script ID–\/exec?tag=test&value=-1<\/p>\n
The string between s\/ and \/exec? is your\u00a0GScriptID<\/strong>.<\/p>\n
\"Deploy<\/a>
Deploy As Web App<\/p><\/div>\n
During publishing, Google will ask to grant some of the permissions and you need\u00a0to allow those.<\/p>\n
Test the Progress<\/h5>\n
If things been followed properly, the following test should go successful. Copy the web app\u00a0URL that you received\u00a0during publishing, format it like shown above and paste this string in your web browser URL field. You should see the following information populated in the DataLogger<\/strong> sheet as shown below.<\/p>\n
\"Test<\/a>
Test data posted to Google sheet<\/p><\/div>\n
So far so good? If not, double check the steps. If the URL entered in your browser populated the values in your sheet then update your GScriptID<\/strong> in the ESP8266 code posted earlier.<\/p>\n
Output<\/h3>\n
I added a line chart to visualize the time series of sensor data posted on the DataLogger<\/strong> sheet. The source range for the chart is big enough to cover any new data sent by ESP8266. You can also\u00a0add a gauge to display the most recent soil moisture data.<\/p>\n
\"Charts<\/a>
Charts based on data posted on DataLogger sheet by NodeMCU<\/p><\/div>\n
Here’s a quick view of the DataLogger<\/strong> sheet. The moisture data are recorded with time stamps.<\/p>\n
\"Google<\/a>
Sensor data posted by ESP8266 with a minute interval<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
In the past couple years, the ESP8266 platform has flourished dramatically and emerged as one of the most popular hardware tools among electronics hobbyists and IoT enthusiasts. Packed with a 32-bit RISC CPU running at 80 MHz, a fully integrated WiFi radio with TCP\/IP protocol stack, serial peripherals (I2C, SPI, and UART), an ADC channel, and general purpose I\/O pins, the ESP8266 is the most integrated and affordable WiFi solution available in the current IoT market space. An ESP8266 hardware, like NodeMCU and ESP-01, can directly interface with sensors using its peripherals and upload the sensor measurements to a local<\/p>\n","protected":false},"author":4,"featured_media":12562,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1044,1464,958],"tags":[1321,1569,1570],"class_list":["post-12561","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-esp8266","category-featured","category-internet-of-things","tag-esp8266","tag-google-sheet","tag-moisture-sensor"],"_links":{"self":[{"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/posts\/12561","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/comments?post=12561"}],"version-history":[{"count":41,"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/posts\/12561\/revisions"}],"predecessor-version":[{"id":13949,"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/posts\/12561\/revisions\/13949"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/media\/12562"}],"wp:attachment":[{"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/media?parent=12561"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/categories?post=12561"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/embedded-lab.com\/blog\/wp-json\/wp\/v2\/tags?post=12561"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}