Writing to and reading from Google Sheets

#1

Some time back, I created a couple of pistons for writing to and reading from google sheets. As a simple use case, below is an example I created using these two scripts for monitoring rainfall in my area. If you want to implement this, first create your google sheet and scripts as described art the links above. The pistons below assume that is complete and the URLs for the two scripts are stores in global variables @URLWriteScript and @URLReadScript. Both of these are declared as strings. These pistons also take advantage of another global variable I have @@myLocationName. This is a string with the name of my city. You can chose to hard code this or use your own google sheet tab name. Just be sure the tab exists in your google sheet document.

Write piston

Rain%20Log
Rain Log.png1178Ă—591 108 KB

Log

|**6/1/2020, 11:52:59 PM +244ms**||
| --- | --- |
|+269ms|â•‘There was 0.00 inches of rain on Jun 01, 2020 as of 11:52 PM.|
|**5/31/2020, 11:52:59 PM +205ms**||
|+238ms|â•‘There was 0.00 inches of rain on May 31, 2020 as of 11:52 PM.|
|**5/31/2020, 12:52:07 AM +588ms**||
|+258ms|â•‘There was 0.00 inches of rain on May 31, 2020 as of 12:52 AM.|
|**5/29/2020, 11:52:59 PM +211ms**||
|+273ms|â•‘There was 0.00 inches of rain on May 29, 2020 as of 11:52 PM.|
|**5/28/2020, 11:52:59 PM +229ms**||
|+242ms|â•‘There was 0.00 inches of rain on May 28, 2020 as of 11:52 PM.|
|**5/27/2020, 11:52:59 PM +154ms**||
|+365ms|â•‘There was 0.01 inches of rain on May 27, 2020 as of 11:52 PM.|
|**5/26/2020, 11:52:59 PM +209ms**||
|+415ms|â•‘There was 0.00 inches of rain on May 26, 2020 as of 11:52 PM.|

Google Sheet snapshot
Rain%20Log%20Sheet

Read piston

Rain%20Check
Rain Check.png1178Ă—807 160 KB

Log

|**6/2/2020, 11:47:04 PM +898ms**||
| --- | --- |
|+2197ms|â•‘In the last 7 days the rain in Las Vegas has been [[0], [0.01], [0], [0], [0], [0], [0]] for a total of 0.01 inches. (Average of 0 in/day)|

Now I can look into my log and see/plot the history of rainfall in my area and the second piston will tell me if I should turn off the sprinkler system.

WebCoRE logging to Google Sheets with multiple tabs
Export Full Log to Cloud
#2

Beautiful rendition, @guxdude!

It still amazes me, once we bring a new piece of tech into our SmartHomes, how many variations we can do with that…

#3

Hi y’all! I am just starting on this, as an alternative to Webcore fuel streams that no longer seem to work.
I am running into a basic issue:
@URLscript “?XtabX=test1”
should write to a tab called test1, I assumed. I do have that tab. However, it writes to my default tab.
When I created the script in Google Docs, I specified a tab - so wondering if i have to have a different script for each tab? or is my Webcore syntax above missing something?

#4

Sorry, I was offline for the last few days. Yes, that should write to the test1 tab. Is it writing the correct data to the default tab? I am wondering if there is some other error that is stopping it from picking up the correct tab. You got the script from this post, correct (not from GitHub)?

#5

Thanks. Eventually it started working. I don’t think i did anything differently, started a new sheet, wrote a new piston, and voila. So all good.

I also tried the automatic logging described here


but that doesn’t do anything. I didn’t troubleshoot too much, since I am doing my own logging with pistons.