Pushing all of your data into “The Cloud” sounds great, until you remember that what you’re really talking about is somebody else’s computer. That means all your hard-crunched data could potentially become inaccessible should the company running the service go under or change the rules on you; a situation we’ve unfortunately already seen play out.
Which makes this project from [Zoltan Doczi] and [Róbert Szalóki] so appealing. Not only does it show how easy it can be to shuffle your data through the tubes and off to that big data center in the sky, but they send it to one of the few companies that seem incapable of losing market share: Google. But fear not, this isn’t some experimental sensor API that the Big G will decide it’s shutting down next Tuesday in favor of a nearly identical service with a different name. All your precious bits and bytes will be stored in one of Google’s flagship products: Sheets.
It turns out that Sheets has a “Deploy as Web App” function that will spit out a custom URL that clients can use to access the spreadsheet data. This project shows how that feature can be exploited with the help of a little Python code to push data directly into Google’s servers from the Raspberry Pi or other suitably diminutive computer.
Here they’re using a temperature and humidity sensor, but the only limitation is your imagination. As an added bonus, the chart and graph functions in Sheets can be used to make high-quality visualizations of your recorded data at no extra charge.
You might be wondering what would happen if a bunch of hackers all over the world started pushing data into Sheets every few seconds. Honestly, we don’t know. The last time we showed how you could interact with one of their services in unexpected ways, Google announced they were retiring it on the very same day. It was probably just a coincidence, but to be on the safe side, we’d recommend keeping the update frequency fairly low.
Back in 2012, before the service was even known as Google Sheets, we covered how you could do something very similar by manually assembling HTTP packets containing your data. We’d say this validates the concept for long-term data storage, but clearly the methodology has changed considerably in the intervening years. Somebody else’s computer, indeed.
Google gives every account 15GB or so of free storage. These sheets should using that pool of storage. There’s probably a more efficient way to upload and store the data using the Google Drive API. https://developers.google.com/drive
Free data storage from Google! Surely they can’t be mining this, right? ;)
It’s easy to find out, just a matter of reading the service agreement.
Upload a file with a list of key words and if you hear a helicopter over head and you need a new door, you know they were watching you.
No, the black helicopters are silent.
B^)
I had a google account, it got shut down. All I had was a single google sheet with the columns:
Google_Employee_FirstName, Google_Employee_LastName, P3nisSize, PhotoAvailable
and they shut me down…
LOL!
If you are only pushing data to the sheet there is an easy way using google forms, just prepare an url with the post variables of your form and you are done! Google forms can write its data into a google sheet :)
With this approach either you have to implement own security (authn/authz) or be left vulnerable to some hacker/bot to submit junk data to your sheet.
A better approach is to use google sheet’s rest api and directly write to sheet (secured by their jwt token). I have been collecting my solar panel data for last 4 years that way. Code runs on heroku one off instance which grabs data from local utility’s website and my solar provider and then pushes to google sheets, google sheet crunches the numbers and generates a nice graph from raw data. Takes little bit extra code to send token but you will sleep easy.
Do you have an example/tutorial case of this?
Yes, in particular, do you need to use oauth2? It has always been a blocker on ESP32/ESP8266.
I’ve just prototyped this approach with an ESP32: you only need the googleusercontent SSL certificate, the URL, and then you can push data.
check out https://medium.com/@bretcameron/how-to-use-the-google-drive-api-with-javascript-57a6cc9e5262 . and use ‘service account’ auth (vs oauth2 etc). this article is nodejs code but i am sure there are libraries for several other languages too (python/java etc).
Thank you, that’s exactly what I was after. The REST API is fully described and thus can be used on an ESP32.
“Raspberry Pi or other suitably diminutive computer.” – no, that’s a big fat computer… A diminutive one would be a 8266..
The lack of cryptographic hardware in the ESP8266 makes the SSL connection hard to manage.
I could easily implement the RPI bit on an ESP32 though.
A 8266 is a diminutive computer? Didn’t Contiki start as an OS with TCP stack for the C64?
I was wondering about the <$0.10 microcontrollers HaD has mentioned previously…
Come on. Any “hacker” falling for the big data cloud easiness is not worth the title hacker. It is not easy, it is not productive and creates unbelievable amounts of waste. Trust the do no evil company? Not anymore on my account. To much nice, but obsolete, google driven hardware laying around.
Sheets have scripts that run like cron jobs, one could do this the other way round and have Google servers connect to their http device and collect the data periodically. The script could even automatically throw the data from the sheet to a free Google hosted database.
i use google app script heavily to automate much of tracking online. some examples to give you some ideas ..
1) i have a sheet with my mortgage parameters (value, credit score, LTV, county code etc). a script runs every day and uses those parameter to check that day’s mortgage rate from my favourite lender. if rates are below my current rate, it sends me an alert.
2) track wait time at local motor vehicle department (check every 5 minutes). if wait time fell below 15 minutes send me an alert or log in a google sheet. Based on the log of 1 week, i was able to find exact day/time in week when wait time was minimal.
3) check my email with specific subject lines and based on content perform specific actions. (e.g. i use my bank balance emails to automatic budgeting/accounting, apply emailed coupons automatically to my account at some retailers). Reduces lot of time and effort for me in manually parsing/processing these repeated tasks.
Google app script is one of the most underappreciated and underused free cloud computing resource (even with it’s shortcomings) we have available.
Hope you all will find useful our project! :)
Thank Tom for sharing.
Have fun and take care,
Zoltan
“Pushing all of your data into “The Cloud” sounds great, until you remember that what you’re really talking about is somebody else’s computer.”
Very well put.