Introduction: Receive a Notification Email When a Channel on ThingSpeak Was Not Updated for a While

By Hamilton
About: Passionate about computers and stuff :-)

Background story

I have six automated greenhouses that are spread across Dublin, Ireland. By using a custom made mobile phone app, I can remotely monitor and interact with the automated features in each greenhouse. I can manually open / close the windows when temperature is too high / low; I can start / stop the irrigation when soil moisture is too low / high; and I can start / stop the ventilation fan when air humidity is too high / low. Or I can simply switch the system to Auto mode, and the vegetables will be looked after by the Arduino brain. More details about this project can be found here - http://eyeduinoproject.online/.

The remote connection to the Arduino boards at the six greenhouses is made possible with the help of USB GPRS dongles, one at each location (I bought mine from herehttps://www.aliexpress.com/item/Unlocked-New-Huawei-E353-E353s-2-With-Antenna-3G-USB-Modem-21-6-Mbps-HSPA-Mobile/32979630201.html?spm=a2g0s.9042311.0.0.44cb4c4dzVUThU). As it is the case with the mobile data connection (at least in Dublin), it randomly drops, sometimes for a few minutes, sometimes might be for a couple of hours. Should the connection drop, The Arduino is programmed to reset the USB dongle every 10 minutes, for it to try initiating a fresh connection. Sometimes however, due to (yet) unknown reasons, even if the GPRS data connection is back on, the Arduino (and the attached Ethernet shield) fails to take notice of the event. This is the moment I need to go to that specific location and manually reset the whole system.

When the data connection drops at some location, I wanted to be notified by email as soon as possible, so that I would keep an eye on that specific location. As the communication between the phone app and the Arduino takes place through an online service provided by https://thingspeak.com, until recently (and up to 31 March 2019), this was possible by using another service provided by https://ifttt.com/discover, and setting a ThingHTTP and a React on each channel, monitoring the fact of whether that particular channel has not been updated for some time. However, according to an email I received from Google, starting with 31 March 2019, due to non-compliance with their updated data privacy requirements (https://cloud.google.com/blog/products/g-suite/elevating-user-trust-in-our-api-ecosystems), access to some data in my Google account will not be available anymore to IFTTT, and as in my case the email was the only resource IFTTT had access to, my understanding was that the notification service described above would stop working.

So therefore, here we are, implementing an alternative solution in order for the email notifications to keep arriving when data connection at my locations drops. This still uses the ThingHTTP and a React features on my channels, only the link to IFTTT was re-pointed to Google Drive. So apart from the hardware (Arduino in my case) you might have communicating with your ThingSpeak account, you will need to create a Google account, that in case you don't already have one... and let's start!

Step 1:

In Google Drive

First, in Google Drive (https://drive.google.com) we need to create a spreadsheet and a simple form. Open your Google Drive, and click on New – Google Sheets – Blank spreadsheet.

Step 2:

I renamed mine "Location down spreadsheet". Then go to Tools – Create a form.

Step 3:

I renamed the form as "Location down form ", and changed "Untitled question" to "Status", and the type from "Multiple choice" to "Short answer".

Step 4:

I then removed the option for collecting email addresses – click on "Change settings", and unchecked all the options in the window that showed up. Click on "Save".

Step 5:

Close the current browser tab that holds your form, and you should be back to your main Google Drive tab, where you should have both the form and the spreadsheet you just created. Open the spreadsheet, and go to "File – Share...". In the new window click on "Advanced"

Step 6:

Then click on "Change..." beside the "Private – Only you can access" label

Step 7:

and change to "On – Anyone with the link", and also to "Can edit"

Step 8:

Click on "Save" and "Done" to get back to your spreadsheet. While there, click on "File – Publish to the web...", and then click on "Publish", and "OK" for the dialog window. Close the "Publish to the web" window.

While still in the spreadsheet, click on "Form – Go to live form". Right-click with your mouse (I am using Google Chrome browser) and choose "View page source".

Step 9:

In the new page that opens search for "form action" and then locate the link that looks similar to https://docs.google.com/forms/d/e/XXXXXXXXXXXXXXXXXXXXXXXXX/formResponse. Select that link, and copy and paste it to some text document. You will be using it to form the final link that needs to be entered in the ThingHTTP for ThingSpeak.

Step 10:

Go back to the source view of your form, and now search for "entry.". Locate and select the whole text, something like "entry.XXXXX". Copy and paste it in the same text document as above. You can now close the source view of your Google form.

Step 11:

In the new text document (where you now have the link and the entry we pasted before), create the final link, that should look like

https://docs.google.com/forms/d/e/XXXXXXXXXXXXXXXXXXXXXXXXX/formResponse?entry.XXXXX =LOCATION+NAME&submit=Submit

In my case, the "LOCATION+NAME" would be replaced by the actual name of each particular location that I need to monitor. The email notification email that I will receive when the connection drops will contain this text, so that I know exactly what location has problems. This text will in fact be submitted as the short-text content for the "status" field in the Google form. The "&submit=Submit " will silently submit the form, without the need of any further actions, when invoked by the ThingHTTP and React.

Finally, we need to add a script that will automatically send an email notification every time a new entry was added in the spreadsheet. Open the spreadsheet, and then click on "Tools – Script editor". In the new window that opens add the following code (with the required changes to reflect your needs):

function newEntryNotification (e)

{

try

{

var timestamp = e.values[0];

var location = e.values[1];

var message = location + ' location is DOWN\n' + timestamp;

MailApp.sendEmail("YOUR EMAIL ADDRESS", "Attention, location DOWN!", message);

}

catch(e)

{

MailApp.sendEmail("YOUR EMAIL ADDRESS", "Error - Attention, location DOWN!", e.message);

}

}

Replace the text "YOUR EMAIL ADDRESS" with the email address where the notification should be sent to, and the actual notification message, if you so wish.

Step 12:

This script needs to be triggered when a new entry was added in the spreadsheet. While in the same window (with the script code above), click on the "stopwatch" icon in the tool bar – "Current project's triggers". You will be asked to name your project (I named mine "locationDown"), and another browser tab will open, reporting that no results (no triggers) were found. Click on "create a new trigger".

Step 13:

In the new window, choose "From spreadsheet" for "Select event source"; "On form submit" for "Select event type"; "Notify me immediately" for "Failure notification settings". Click on "Save". You will be asked to sign in to your Google account, and "Allow" for this trigger to access your account when required.

Step 14:

You should now have a trigger in the triggers list, which will be linked to the script we created previously. Therefore, upon insertion of new data into the spreadsheet (using the automated form link and the silent method described above), the trigger will immediately call the script, which will send a notification to the email address provided, containing your chosen message.

We are done in the Google Drive side, and we are now moving to the ThingSpeak.

Step 15:

In ThingSpeak Sign in to you account (https://thingspeak.com/login), go to "Apps - ThingHTTP", and then click on "New ThingHTTP". Give it a name (I've chosen the actual name of each location; "MyLocationName" for the purpose of this tutorial), and in the "URL" field, paste the link from your text file, the one that looks like

https://docs.google.com/forms/d/e/XXXXXXXXXXXXXXXXXXXXXXXXX/formResponse?entry.XXXXX =LOCATION+NAME&submit=Submit

Leave all other fields as they are, and click on "Save ThingHTTP".

Step 16:

Then go to "Apps - React", and click on "New React". Give it a name (again, I've chosen the location name followed by the word "react", but you can choose any name you want); "No Data Check" for "Condition Type"; "Every 10 minutes" for "Test Frequency"; the channel name that you want to monitor for updates, for "If Channel"; the time that the channel has not been updated (I've chosen 15 minutes), for "has not been updated for"; "ThingHTTP" for "Action"; " MyLocationName " for "then perform ThingHTTP"; "Run action only the first time the condition is met", as I only want to get a notification once. This will reset itself when the channel gets updated again with new incoming data. Click on "Save React" and you are done.

From now on when your channel has not been updated for 15 minutes (or other time, depending on what you've chosen), the React will catch that exception which will trigger the ThingHTTP, which in turn will silently create a new entry in your spreadsheet. The Trigger and the script on Google Spreadsheet will take it from there, as already explained above.