I recently stumbled across some details on GitHub that convinced me that updating private Google Sheets should be possible with Blynk and specifically with the WebHook widget. It takes a bit of doing but doesn’t have the cons associated with Zapier.
The secret is Google Scripting which allows you to create a bit of code and then publish this as a web app. Google scripting is accessed in your Google Sheet from Tools and then select Script editor.
When accessing Google Sheets (and other Google online tools) the authentication involves a HTTPS redirect from one site to another which is not normally available with ESP’s. However a “HTTPSRedirect” library was published last year on GitHub for ESP’s, so it’s now possible. The library is at https://github.com/electronicsguy/ESP8266/tree/master/HTTPSRedirect and Sujay (@electronicsguy) has provided a sample Google script and ESP sketch to update private Google Sheets.
After some modification of the script and the sketch I now have the following:
The Webhook sends just one string with the data from three sensors and this appears in column A of the sheet. The final part of the webhook url is:
/pin[0]/%3C/pin[1]/%3E/pin[2] (where %3C is url encoding for < and %3E is >)
Column B is simply the time of the update (from the Google script) and Column C a sequential log of each update. Columns D, E and F manipulates the data in column A to split it out into each of the 3 sensors. Google script takes care of copying down the formula as each new row is added to the sheet.
My project has updates set on a 60s timer and a button tied to the Webhook widget for manual adhoc updates.
@Dmitriy what I have noticed is that the Webhook activation from a button fails from time to time. Stopping the project, clicking the Webhook widget, exiting the widget and restarting the project gets Webhook back up and running. Google allow 1 read and 1 write per user (for up to 5 users) per second as standard so it’s not a rejection by Google. ESP is connected throughout when the Webhook stops running.