Adding Thingspeak?

Topics: 3. Feature Requests
Jan 10 at 12:34 PM
Hi
has someone checked how difficult it can be to send data to ThingSpeak in addition to PVOutput? the advantage would be to rely on a powerful engine to trigger actions (I already use ThingSpeak to alert me when my domotic system is not sending temperature reading for more than 1hr for instance)

Thanks!
Riccardo
Coordinator
Jan 10 at 9:34 PM
Well, looks interresting...
You just need another upload daemon/service to do the job, or writing your own script that reads the db and uploads the data with curl
How do you create a trigger to warn you about an event?
I already created a channel and uploaded some data with csv file (manually)
Jan 11 at 7:32 AM
on the bottom right of the channel settings you have a link to ThingSpeak apps - with "React", I am sending a POST to IFTTT.com when no data is received, but you can do many more things (i.e. activate something in the domotic system if the PV production is above a certain threshold).

I have googled for daemons able to upload to thingspeak (i.e. https://github.com/iobridge/thingspeak ) but I am not sure I am able to do this integration :-(

Thanks!
Riccardo
Coordinator
Jan 11 at 1:59 PM
Edited Jan 15 at 9:48 AM
You don't have to make it too complex... I made a small example for Linux (Rpi) with SQLite and Curl installed (which are already dependencies for SBFspot)
Change the serial nr and the API key in the script below and run it right after SBFspot.
Temperature goes to field3 and EToday to field4 - You can edit this to suit your needs
#!/bin/bash
#
# thingspeak.sh by SBF V1.0.1 (14-JAN-2017)
#
# Revision History
# V1.0.0 (11-JAN-2017) Initial Version
# V1.0.1 (14-JAN-2017) Changed query
#
# Example how to upload SBFspot data to ThingSpeak.com
# This script gets the inverter temperature and EToday from SQLite db
#

# Serial nr of inverter
serial="2100xxxxx"

# Write API Key of your channel to update
ts_apikey="ABCDEFG123456"

# Full path of SBFspot.db
db="/home/pi/smadata/SBFspot.db"

qry="SELECT Temperature,EToday*1000 FROM vwInverters WHERE Serial=$serial AND gridrelay='Closed'";

# Execute the query
result=$(sqlite3 "$db" "$qry")

# For each row (there should be only 1 row)
for ROW in $result; do
    # Parsing data (sqlite3 returns a pipe separated string)
    f3=`echo $ROW | awk '{split($0,a,"|"); print a[1]}'`
    f4=`echo $ROW | awk '{split($0,a,"|"); print a[2]}'`
    curl -v -H "X-THINGSPEAKAPIKEY: $ts_apikey" -X POST -d "field3=$f3;field4=$f4" "http://api.thingspeak.com/update"
done
Jan 12 at 7:23 AM
wow, this is great I will try it and let you know! thanks!!
Jan 28 at 10:04 PM
thanks a lot, I was able to make it work but I had to modify the query in the script like this
qry="SELECT Temperature,EToday*1000 FROM inverters WHERE Serial=$serial";

so inverters instead of vwInverters and remove gridrelay='Closed'

Could it be just because today production was zero?

Thanks a lot!
Riccardo
Coordinator
Jan 29 at 10:57 AM
Edited Jan 29 at 10:57 AM
Using the table or the view won't make any difference here.
I think you configured another language? In that case you need the translation for "Closed"
Jan 29 at 4:51 PM
thanks I have compared the output below with .fullschema (need to refresh my SQL memories...), the field GridRelay has value N/A in my case

sqlite> SELECT * FROM inverters;
1930003042|SN: 1930003042|SB2.5-1VL-40|02.05.01.R|1485708302|0|1.617|1789.01|3481.9|3382.93|OK|N/A|0.0

Temperature is also 0, but this could be because it's dark now :-)

Thanks!!
R