General Question: Is it possible to read Google Sheets data into WebCoRE?

[Brent_NC]

1) Give a description of the problem
I’m having some challenges getting a large “Phase of Day Setting” Piston to the finish line (something about my choice of design or character sets is causing some sort of corruption…I expect it has something to do with the large arrays I’m loading and then processing through nested switches).

I’m completely re-thinking the design and was lamenting not being able to make database calls in place of the arrays. Even if I could just pull keyed records from a spreadsheet, that would be amazing! I started looking at some of the work being done around integrating the Google API’s and specifically writing information to Google Sheets. I assume that if you can write data to a spreadsheet, you can surely read it. However, my excitement is tempered by the realization that all of the genius developers (of which I am neither) on this forum must have already deemed this a dead end. So there’s the question: Has anyone successfully integrated their WebCoRE Pistons with Google Sheets READ API calls?

The development work I’ve seen that allows writing to Google Sheets seems to involve SmartApps that perhaps interact with ST directly, not via WebCoRE. I’m not up for coding my own SmartApp. The WebCoRE + Google API’s I’m seeing appear to focus on maps/traffic. If I have understood correctly (unlikely), the Maps API is a different beast since you’re engaging a public service vs accessing a Sheet on an individual’s Google MyDrive. I think maybe an important difference here is that while the Maps API can be engaged with only an API key, the Google Sheets API would require OAuth??? And if that’s correct, then the next question would be… can you initiate an OAuth authenticated call directly from WebCoRE?

Phew… that’s a long question! If you’re still reading, my apologies!

I did Take a swag at this with just an API Key and got the stiff arm, which loosely supports my current assumption (uneducated guess) that I would need OAuth authentication:

“Error executing external web request: groovyx.net.http.HttpResponseException: Forbidden”

[eibyer]

I have no clue what Phase of Day Setting is but just curious as to what the data looks like that is making webCoRE choke?

[Brent_NC]

Hi @eibyer.

Thanks for your note. Cross-linking below to the original thread regarding my errant Phase of Day Setting Piston. I’ve posted more details there if you are interested.

Side note: “Phase of Day Setting” is just what I’ve taken to calling my Piston that determines when “Wakeup, Morning, Daytime, Evening, etc” occurs in my household, taking into account several factors such as day of week and ‘holiday’ exceptions.

“Backup Bin Updated” msg while trying to save Piston - Does not save to cloud

[Jonathan_Rider]

There is a way to do this using a JSON web request and setting the response as a variable which can be used in your pistons. I’ve been using this for a while to great effect!

What you need to do is share your Google sheet by publishing it to the web (n the File menu). Then in WebCoRE make a web request to:

https://spreadsheets.google.com/feeds/list/xxxxxxxxxxxxxxxxxxxxxxxx/default/public/values?alt=json

But put the spreadsheet ID where the xxxx is. When you open the spreadsheet the link should look like this:

https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxI/edit

Where the xxxx is is the spreadsheet ID.

Then set the variable on WebCoRE with:

{$response.feed.entry.gsx$_cn6ca[0].$t}

This needs to be amended to find the cell data you want, and this is the tricky part! Put the JSON link into jsonlint.com and click Validate and it will bring a lot of data back. Find the data in the cell you want to set as a variable then you need to work backwards to find the unique value you want. Basically, in the above you can break it down as follows (read from the bottom up):

• {$response. Needs to be on every request.

• feed. This is the value another step up from the below, even less indented than the below one.

• entry. This is the value another step up from the below, even less indented than the below one.

• gsx$_cn6ca[0]. This is the value in the ‘step up’ from the below, it will be less indented than the one below. There are usually lots of these that are the same, and the [0] indicates the first instance. If you put a [1] it will be the second instance. Just play around with this until you get the right one, I’ve never been able to get it right first time.

• $t} Wherever you find the data you want on jsonlint, this is the value immediately to the left (don’t include the speech marks.

It took me many hours to work all this out from scratch, but once it works, as long as the data stays in the same cells you can do the web request to update the values whenever you want. I found it easier to put just the data I want to use with WebCoRe into a shared spreadsheet and do all the calculations in a separate spreadsheet.

If you can’t get the variable to set properly, let me know and I’ll take a look. I’ll need access to the Google sheet, though, so make sure you’re happy to share this!

Good luck!

[Brent_NC]

Wow! Can’t wait to try this over the weekend. Will let you know how it goes!

[Brent_NC]

@Jonathan_Rider. Thanks a ton for sharing your approach to integrating Google Sheets data. I did get it to work. As you said a LOT of trial & error! Never would have gotten it without your instructions. I will definitely be incorporating this new (to me) tool into other Pistons.

I was surprised how much data is returned for one simple sheet. Have you tried to get the Google Sheets API v4 working with WebCoRE? It seems the refined capabilities that come with that API could significantly reduce the amount of JSON data that you have to process in the Piston. I’m guessing the authentication elements may be blockers for using that API directly with WebCoRE (may require an intermediate SmartApp?). For me, the ideal functionality would essentially allow a vlookup (Excel terminology) directly from the Piston. Wishful thinking, perhaps.

[Jonathan_Rider]

No worries.

I haven’t tried the Sheets API, but I’ve just had a look and it seems much easier to find the data you need. As you say, I’m not sure how the OAuth would work through WebCoRE, I will have a look at some point in the week and see if I can get it to work.

[MR-JR]

https://spreadsheets.google.com/feeds/list/xxxxxxxxxxxxxxxxxxxxxxxx/default/public/values?alt=json

Thanks for this, very easy and very helpful!

[MR-JR]

_cn6ca

Just in case it wasn’t mention (apologize if I’m redundant with another) … but it seems that you can replace the “gsx$_cn6ca” with gsx$columntext … where columntext is the value of cell $A$1 in all lower case with spaces and non alpha numerics removed … this makes it very easy to locate your cell data:

so in general, assuming your table has headers and starts in $A$1, it’s:

$response.feed.entry.gsx$column-a1-text[row].$t

or specifically, if $A$1 contains “Title Two”, and you want contents of cell $A$3, it becomes:

$response.feed.entry.gsx$titletwo[2].$t

HTH.

[guxdude]

This is such a great shortcut for accessing the data in a google sheet. Only downside of this method is the spreadsheet and data has to be made “public” by publishing to the web.

[MR-JR]

be made “public” by publishing to the web

Agreed, but … in reality unless they have the link, its not really visible. Having said that, I wouldnt keep my passwords there