Hi, I’m pretty new to Blynk but have now run several projects on the NodeMCU chip. I now want to pass data to a google sheet using the Webhooks widget and IFTTT. I’ve followed some of the old links which never seemed to reach a conclusion, and I have tried for days trying to do this with info from the blynk and github communities but no luck.
Does anyone have a concise, complete, idiots guide to doing this?
Thanks in advance!
Have you tried searching this forum?
Hi Pavel, thank you for your time to reply, I’m sure you are pretty busy and may I thank you for Blynk, why didn’t I find it years ago before I went down the very expensive fibaro route!
My application is pretty similar to others in the forum, I have a bore hole which feeds a storage tank from which I have a pump, 4 solenoid valves and a thirsty garden to water. I measure the depth of water in the storage tank with an ultrasonic transducer every 5 seconds and if its too low I turn on the bore hole pump. At various times I run the second pump, select which area to water etc etc. I am about to integrate our swimming pool management into the same system. So I need plenty of timers and switches.
Tracking water usage and function in googlesheets would be a great help to me. I do this with Fibaro although I cant pass the actuation time which is a real pain.
I have looked through the Blynk and Github forums, I found one article dated Oct 2016 that almost worked, but the guy could not pass data, I could replicate his issue but when I tried to change the data in the Blynk app it all stopped and I could do nothing. I deleted everything in IFTTT and webhooks, started again but still no luck. This guy was not using your webhooks widget which should make life a lot easier. As with most things I’m running out of time to solve this issue but I’m sure someone must have done it?
I understand your issues with IFTTT but a direct integration would help a lot.
If you have any suggestions I’m more than happy to try them.
Many thanks
Personally, it sounds to me like you’re approaching this from the wrong direction.
I’d be making an API call direct to google sheets from my NodeMCU code rather than introducing two other steps into the process.
In addition, I’d probably use the Blynk Superchart functionality (linked with the reporting widget if needed) to visualise/extract the data.
The only drawback to Superchart is that the maximum granularity of data stored in the database is one minute. If multiple data points are received within a 1 minute period then they will be averaged.
Given your description of the setup, I doubt that this would be an issue, and if you have your pump turning on and off more than once per minute then you probably ought to re-visit your logic, as you’re likely to run into mechanical reliability issues if the on/off frequency is too high.
Pete.
Good morning Peter,
I have seen your helpful comments on many of the forums, so again thank you for taking the time to reply.
Indeed the bore hole pump comes on once per day for about 20 minutes and the others twice a day for about 20 minutes each.
Oddly enough this morning I looked at the super chart and exporting options. The chart doesn’t seem to show any graph at all, so I need to look at that but the export worked well, although a zipped format is a pain on the ipad. The direct API sounds interesting although I don’t have a clue how to implement that, if you could point me in the right direct that would be useful.
Many thanks
John
Just a quick update, Ive got the chart working well, by changing the “style”, don’t know why it didn’t work the first time. The exported data has a time stamp, can this be converted to a real time / date or is it just a server time stamp? There is nothing in the widget help that defines this.
Thanks
The timestamp is UNIX time (number of seconds since 01/01/1970). If you search the forum, or the internet, then there are plenty of example of how to convert this into Excel date format.
Are you actually using the Reports widget to extract this data?
As far as the google sheets API is concerned, it’s not a Blynk thing, so there should be plenty of examples of how to do it on the internet. I don’t use google sheets myself, so it’s not something I’ve ever tried.
TBH, for this sort of application, I see little pint in having the data in a spreadsheet when you can get an excellent long-term visualisation in Superchart.
Don’t forget that you can plot several data feeds on one Superchart, so data on solid moisture levels, air temperature, rainfall etc can be overlaid with pump run time data.
Pete.
Hello again Pete,
Thank you for your reply, API is something I will look into as calculating water usage and system uptime is something that is not very convenient to do in the MCU. Yes the super chart is very useful and I will be tracking several sensors and storage tank fill level so if I can push data to a spread sheet all will be well.
This may take a while as the sun is shining and the garden needs some hands!
Best regards
John
There are potential other ways to do this.
I think at the moment you’ve probably focussed on one solution, and aren’t thinking outside of the box. I was guilty of doing the same things when I stared using Blynk, and it took me a while to move away from the spreadsheet approach for my analytics.
Writing values to the Blynk server, then retrieving them again if the MCU reboots for any reason, is one solution for monitoring things like uptime. Clever use of widgets like the Table widget can also provide a solution to time related data, and arrays can also be written to virtual pins to maintain data over time.
I’m using similar methods when monitoring rainfall over time and windspeed trends, and these don’t sound too different to what you’re doing.
Pete.
Hi Pete,
Thanks again, I made my career by thinking out side of the box, I must be loosing my touch! I will look in more detail on what I can do with Blynk, many thanks for your guidance. I’ve only been using it for a week and already have 3 of my neighbours interested. I have an Oregon scientific weather station, so frustrating that all the sensors are wireless and yet the main unit is so dumb. What sensors are you using for your weather data?
Is there any way of getting around the Blynk app “phone” layout? I have an IPad Pro and would love to do a full landscape layout for my Blynk projects.
How I wish I had seen this before I started with Z-Uno!
Thanks again,
John.
I use a “homemade” collection of sensors for my weather monitoring - off the shelf sensors that are hard-wired to a NodeMCU.
If you have one of the 433MHz wireless weather stations then you should be able to intercept the data using a 433 receiver and use it in whatever way you want.
The current version of Blynk is portrait only, although the Superchart crash’s can be displayed in landscape format. The new version of Blynk is supposed to work in either format, but don’t hold your breath for that.
Pete.
I’ve been thinking about this a bit more, and re-read your earlier posts.
It sounds like your irrigation system, swimming pool system and weather station could be the tip of the iceberg as far as your ‘home automation’ projects are concerned. Are you using solar power at all, be cause if you are then that could be another area where Blynk could be useful.
I actually use Blynk in a very different way to most people, and it’s a way that works very well for Home Automation systems where you have a collection of physically remote sensors and MCUs that need to be controlled together, and potentially interact with each other (don’t water the garden of its just rained for example).
My system uses lots of ESP8266/NodeMCU devices that all talk to a central Raspberry Pi, using a messaging system called MQTT. The Pi runs software called Node-Red, that’s a graphical programming/workflow/rules engine system. Node-Red talks to Blynk, and vice versa, via a Blynk plug-in.
Coincidentally, Node-Red also has plug-ins that allow data to be pushed/pulled from Google Sheets, if you still feel the need to do that, and also has a plug-in for Oregon Scientific weather stations.
I use my system for home automation at home in London, and at our holiday home in Spain - where I’ve just installed a solar panel, charge controller and MQTT interface to allow me to run our garden lighting from solar power and control it, and monitor the state of the batteries, via Blynk.
Before you go too much further with your existing project, you should think about this other type of architecture, as it can simplify things considerably when you start working with multiple devices.
More info on my way of doing things here:
Pete.