Logging and alerting

In this chapter, we are going to set up logging to Google Sheets. Using the ESP-link`s REST API we can send messages to any REST capable service on the internet straight from the Arduino Sketch. As Google Sheets only accepts HTTPS data and the ESP-link currently does not support HTTPS connections we are going to need to relay messages between the Arduino and Google Sheets using a free 3rd party messaging API called PushingBox.

The following tabs are available within the GrowBoxLog sheet:

- Status tab: Displaying the latest report received from the Gbox420 Main module
  - GrowBox latest status: Overview of all values extracted from the latest report with some formatting applied to it based on the data type.
  - Last report from ESP-link: Shows details of the raw data received by the Google App Script and the result of extracting and processing the received JSON report.
- Charts tab: Shows how the readings changed over time using line, area, and bar charts. All charts are re-generated automatically when a new report is received.
- Log tab: Collection of historical report data. New columns detected in a report are automatically added after the existing columns. You can re-arrange the columns, the App Script will automatically find the matching column based on the header when adding a new row.
- Columns tab: When a new column is detected in the received report a Columns entry is automatically added. Here you can specify the data type (Temperature, Weight, Percentage..etc), Email alert ranges (Min-Max acceptable values), and which chart to show the value on.
- Settings tab: Email address to send the alerts to, switching between Metric/Imperial units, Default date format and time zone, and activating debug logging to Stackdriver.

By default the Main module from GitHub will report to the sample sheet embedded here (Until my account reaches the 1000 report / day limit), so you can verify your Main module's logging without setting up your own Google Sheets. Please change this as soon as possible as described below :)

Gbox420 - GrowBoxLog

Logging implementation

Google Sheets - Apps Script

1. Grab a copy of the Gbox420 - GrowBoxLog spreadsheet. Open the link, select File - Make a copy... (Make sure to log in to your Google account for the option to become available) and save it to your Google Drive.

2. Go to the Log tab and delete all sample data rows starting from row 3. The fastest way to do this: Select the entire Row 3 by clicking the row number, then hold CTRL + Shift + Down arrow. Once you are at the bottom press delete. The charts in the second row will display #N/A until at least 2 data rows are inserted, ignore this for now.

3. Click on Tools - Script Editor to open the script editor in a new tab, and navigate to Publish - Deploy as web app.
PS: Google started rolling out the new App Scripts editor in 2020 December. Switch to the legacy editor by clicking the "Use legacy editor" button at the top right corner to switch to the editor shown below. (I'll make new screenshots once the new editor stops hanging during a Web App deployment... )

4. On the Deploy as a web app page select the “Anyone, even anonymous” option and click the Update button, you will be asked to grant permission for the macro to access Google Sheets and show you a warning about the app verification. You need to accept this to be able to relay data from the Arduino to Google Sheets.

5. If all goes well you should end up with an address for the freshly published web app under the Current web app URL. Save this URL, it will be needed when setting up PushingBox.

6.(Optional) If you make changes to the script you will need to re-publish the web app with a new version number using Publish - Deploy as web app. Without setting the Project version to New the changes would not apply! The web app URL will not change after publishing a new version.

Google Sheets - Publish to the web

To be able to access the GrowBoxLog spreadsheet over the internet and inside the ESP-link web page, you need to publish the spreadsheet to the web.

1. Open your copy of the Gbox420 - GrowBoxLog spreadsheet and Navigate to File - Publish to the web and select Entire Document - Web page and click Publish. You will get the link to the web version of the spreadsheet. Save the URL to your favorites, with it you can access the current logs over the internet from any device. Later on, we will need the link when preparing the ESP-link's Logs tab too.

2. (Optional) While remembering a URL with 50 random characters is a great memory exercise it is not practical. You can use a free URL shortening service to make a custom short link for your published spreadsheet. In this example, I`m going to be using bitly. Just navigate to the main screen, paste in your Spreadsheet published URL, and hit the SHORTEN button to get a short link pointing to your spreadsheet. If you take the extra 2 minutes and register an account with Bitly you can even customize the link.

PushingBox setup

Next we are going to setup PushingBox to relay messages between Arduino and Google Sheets Web App. The Pushingbox notification API is free to use up to 1000 messages per day (once every 90 seconds).

1. Go to https://www.pushingbox.com and use the Login with Google option at the top right corner.

2. On PushingBox main page navigate to My Services and select Add a service button.

3. Choose the CustomURL service type.

4. Enter a Name for the service, set the Root URL to the Web app link created in Google Sheets ( Google Sheets - Apps Script - Step 5), and use the POST method. Click Submit to create the GrowBoxLog_Relay service.

5. Next head over to My Scenarios and create a scenario for the GrowBoxLog_Relay service.

6. Once the scenario is ready select Add an Action

7. Select the Add an action with this service button next to the GrowBoxLog_Relay service

8. Inside the action you just insert: BoxData=$BoxData$

9. Save the DeviceID displayed directly under the scenario name.

10. In the Gbox420 - Main module, under Settings.h update the PushingBoxLogRelayID to match your own DeviceID.

11. In the Gbox420 - Main module, under Settings.h update the Version number. This step is important if you already had the Main module uploaded to the Arduino with a different PushingBoxLogRelayID. Without changing the version number the previous value will get loaded from the Arduino's EEPROM storage! When the version number is different it will force an EEPROM update with the new values. (This is true for all other values stored in Settings.h too! )

Verification test - Browser

The fastest way to test the PushingBox relay service is from a browser, by updating the InsertDeviceIDHere to your PushingBox Device ID in the below URL, and pasting the entire line in a browser window:

http://api.pushingbox.com/pushingbox?devid=InsertDeviceIDHere&BoxData={"Log":{"IFan":{"S":"1"},"EFan":{"S":"2"},"Lt1":{"S":"1","B":"42","T":"1","On":"04:20","Of":"16:20"}}}

For example to relay data to the Sample sheet embedded at the top of this page, click this link: http://api.pushingbox.com/pushingbox?devid=v755877CF53383E1&BoxData={"Log":{"IFan":{"S":"1"},"EFan":{"S":"2"},"Lt1":{"S":"1","B":"42","T":"1","On":"04:20","Of":"16:20"}}} It will not give back any feedback, but you should notice the Received at and the Extracted BoxData JSON fileds getting updated and a new entry appearing under the Log tab.

Verification test - Arduino

To relay the same data from the Arduino Mega as the browser-based test, use the below test sketch. Do not forget to update the PushingBoxLogRelayID variable to your own DeviceID before uploading the sketch. Note: This step assumes you already have the ESP-Link set up and connected to the WiFi network.

Test-ReportToGooleSheets sketch

Expected output: Report getting sent out from the Main module and processed by the Apps Script in the Google Sheets - GrowBoxLog sheet.

Final note: As all parameters are passed part of the URL you have to make sure to URL encode special characters. This is only a problem if you attempt to pass text that contains special characters, with sensor data this is usually not an issue. To URL encode/decode text you can use: https://meyerweb.com/eric/tools/dencoder/.

Here is an example:

- Decoded text: Encode this text !@#$%^&*() - this cannot be passed as part of a URL
- Encoded text: Encode%20this%20text%20!%40%23%24%25%5E%26*() - this can be passed as part of a URL

Gbox420 web interface - Logs tab

In the very last step of the ESP8266 setup, we uploaded the three web-pages to be hosted by the ESP-link firmware. Now it is time to update the Logs.html page with the URL generated above at the Google Sheets - Publish to the web step. Paste the URL inside the iframe section's src property:

Save the file and re-upload all three HTML files to the ESP-link's web server, just like we did in the ESP8266 - Gbox420 web interface section.

Gbox420 web interface - Debug tab

When the ESP-link firmware receives a REST API request from the Arduino Mega it prints it to the Debug log. This is the best way to check if the Google Sheets report was successfully sent out to PushingBox:

Let's look at the steps:

Data is getting sent from the Arduino to the ESP-link firmware using the SLIP protocol. Maximum 1024kB can be relayed at once!! In this case, the total length was 850kB.
ESP-link checking the received data (If the data was too long you would get a CRC error here)
ESP-link starting to assemble the REST request
The assembled REST GET request is displayed here
ESP-link starting connection to the PushingBox relay service
ESP-link resolved the DNS name (api.pushingbox.com) to an IP address
to 10. are the sending process logs that should end with a REST:Sent message. Notice that the REST API request is larger than the Arduino data (971kB vs 850kB). This is normal, the ESP-link adds a few extra parameters like the PushingBox URL and the DeviceID to the data:

Example data sent by the Arduino via SLIP protocol:
{"Log":{"IFan":{"S":"1"},"EFan":{"S":"0"},"APump1":{"S":"1"},"Lt1":{"S":"1","B":"50","T":"1","On":"08:20","Of":"02:20"},"Lt2":{"S":"0","B":"0","T":"0","On":"04:20","Of":"16:20"},"LtSen1":{"R":"960","D":"0"},"DHT1":{"T":"22.90","H":"54.00"},"Pow1":{"P":"269.40","E":"135.82","V":"223.80","C":"1.28","F":"50.00","PF":"0.94"},"Hemp1":{"S":"1","PB1":"1","PB1S":"100","WB1":"18.32","WR1":"3.56","SWB1":"16.00","StWB1":"19.00","WLB1":"13.00","PB2":"1","PB2S":"100","WB2":"16.74","WR2":"1.04","SWB2":"16.50","StWB2":"19.00","WLB2":"13.00"},"Aero1":{"S":"1","P":"6.14","W":"18.09","Mi":"5.00","Ma":"7.00","LS":"6.30","St":"1","PS":"100","SE":"1","D":"1.00","DI":"6","NI":"10"},"Res1":{"S":"1","P":"2.40","T":"-158.13","W":"57.05","WT":"19.31","AT":"24.10","H":"45.70"},"Main1":{"M":"1","D":"1"}}}

Actual REST request sent out by ESP-link:
api.pushingbox.com/pushingbox?devid=v755877CF53383E1&BoxData={"Log":{"IFan":{"S":"1"},"EFan":{"S":"0"},"APump1":{"S":"1"},"Lt1":{"S":"1","B":"50","T":"1","On":"08:20","Of":"02:20"},"Lt2":{"S":"0","B":"0","T":"0","On":"04:20","Of":"16:20"},"LtSen1":{"R":"963","D":"0"},"DHT1":{"T":"22.90","H":"54.20"},"Pow1":{"P":"269.50","E":"135.74","V":"223.80","C":"1.28","F":"49.90","PF":"0.94"},"Hemp1":{"S":"1","PB1":"1","PB1S":"100","WB1":"18.33","WR1":"3.53","SWB1":"16.00","StWB1":"19.00","WLB1":"13.00","PB2":"1","PB2S":"100","WB2":"16.76","WR2":"1.01","SWB2":"16.50","StWB2":"19.00","WLB2":"13.00"},"Aero1":{"S":"0","P":"6.97","W":"17.66","Mi":"5.00","Ma":"7.00","LS":"6.99","St":"4","PS":"100","SE":"1","D":"1.00","DI":"6","NI":"10"},"Res1":{"S":"1","P":"2.48","T":"-163.17","W":"57.00","WT":"19.38","AT":"24.10","H":"46.00"},"Main1":{"M":"1","D":"1"}}}

Stackdriver logging

On the GrowBoxLog sheet - Settings tab you can enable additional debug messages while the Google Apps Script processes the received report:

To view these logs go to Tools - Script editor, and inside the script editor select View - Stackdriver Logging:

These logs can come in handy when making changes to the Apps Script and you need to do some debugging.

To develop and manage Apps Script projects from your terminal rather than the Apps Script editor, you can use an open-source tool called clasp https://developers.google.com/apps-script/guides/clasp