Importing Google Sheet cell data

Stu_Belshe · April 6, 2018, 3:34pm

I have a device handler that I would like to contain a tile with data from a Google sheet cell.

Essentially;

tileAttribute (“device.cost”, key: “SECONDARY_CONTROL”) {
attributeState(“cost”, label:‘TEST’)
}

Where the label is populated with the dynamic data from a Google Sheet. Thoughts?

nellering · August 27, 2018, 5:01pm

Did you ever get this working? I want to do the same thing.

Stu_Belshe · August 27, 2018, 5:13pm

I did not, abandoned it after not getting any feedback. Still love to do it myself but am lost on direction. Any ideas?

Stu_Belshe · August 27, 2018, 5:47pm

Might be an issue with where it’s published or categorized, I wasn’t sure where to put it.

nellering · August 27, 2018, 6:46pm

I posted in the integration category today. I’ll let you know if I get anything.

Have you tried using Webcore? I’m able to populate a virtual temp sensor with a hardcoded string value. I’m looking to see how to import from Google Sheets with a “GET” command.

Stu_Belshe · August 27, 2018, 7:00pm

I have not, no. The code I have I simple poked around with until finding something until it populated static text.

nellering · August 27, 2018, 9:54pm

I figured out how to do this with Webcore and the Google Sheets API.

Can you describe your use scenario so I can point you in the right direction?
-What data are you trying to obtain (string, integer, etc)?
-How many cells do you want to gather data from?

Stu_Belshe · August 28, 2018, 2:00pm

I have a spreadsheet that does some math and has a cell that calculates a SUM that I’d live to have listed as part of the tile label.

So single cell, and I suppose integer would be correct.

nellering · August 28, 2018, 2:55pm

That’s exactly what I’m doing.

You need to get an API key from Google associated with your Google account. This is a private number that I don’t think you want others to have access to. Go here to see how to get the key:

https://support.google.com/googleapi/answer/6158862?hl=en

Turn on sharing for you sheet by clicking on the “Share” button with your spreadsheet open. Copy the link and click “Done” to enable sharing.

Find your spreadsheet ID by copying the portion of the link after /d/

https://docs.google.com/spreadsheets/d/[This_Is_Your_Spreadsheet_ID]/edit?usp=sharing

Create and test your HTTP_GET function. This page explains how to create the http website:

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get

Essentially, you need to modify the portions in the brackets of the link below:

https://sheets.googleapis.com/v4/spreadsheets/[enter your spreadsheet ID here]/values/[enter your worksheet name here]![enter your cell that you want to read here]?key=[enter your API key here]

Paste this into the URL box of your browser and check what it returns on your screen. If everything worked correctly, you should see your value show up in a matrix. It will also tell you if you have an error. You can replace the cell portion with a range of cells by following the developers guide on Google.

This is what I get when I type in my URL:

“Report” is my worksheet.
“B23” is the cell I am reading.
“1.263” is the value of “B23” that I am interested in.

Let me know if this works for you, and I will explain the next steps of how to process the information in Smartthings.

Stu_Belshe · August 28, 2018, 3:08pm

Awesome, thanks so much. I’ll give it a shot in the next few days when i get some time and let you know how it goes.

Stu_Belshe · August 28, 2018, 5:35pm

That worked perfect @nellering , I have the data I want visible in the URL.
12%20PM

nellering · August 28, 2018, 6:40pm

Great. Follow the installation from GitHub method to install Webcore here:

I am working on documenting and uploading the code you will need.

nellering · August 28, 2018, 6:44pm

Are you interested in just displaying the value, or do you want to be able to change the background color based upon the value?

Stu_Belshe · August 28, 2018, 6:57pm

Just displaying the value at present, let’s start simple, haha. Let me see what I can do with what you posted.

Stu_Belshe · August 28, 2018, 7:05pm

Looks like we’re up and running.

nellering · August 28, 2018, 7:24pm

First thing you do is make a new device handler in the IDE. You can use this code to start with:

github.com

nellering/SmartThings_me/blob/master/devicetypes/nellering/device-to-display-google-sheets-value.src/device-to-display-google-sheets-value.groovy

/**
 *  Copyright 2018 nellering
 *
 *  Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except
 *  in compliance with the License. You may obtain a copy of the License at:
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software distributed under the License is distributed
 *  on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License
 *  for the specific language governing permissions and limitations under the License.
 *
 */
metadata {
    // Automatically generated. Make future change here.
    definition (name: "Device to Display Google Sheets Value", namespace: "nellering", author: "nellering") {
        capability "Temperature Measurement"
        capability "Sensor"
        capability "Health Check"

This file has been truncated. show original

Then create a new device in the IDE using the device handler you just created. You should now see the device in your SmartThings app.
Go into the webCoRE settings in the app and add the new device you created as an available device. Make sure it is loaded as a sensor.
Register a browser on your computer from the webCoRE app.

I will post a code example for webcore so you can have an example in my next post to pull the data and load it into the device handler.

Stu_Belshe · August 28, 2018, 7:46pm

For some reason when trying to add the device in webCorE’s setting, it only comes up under “refreshable devices”, it’s not listed under sensors.

nellering · August 28, 2018, 7:51pm

Towards the top, select the option “Which sensors” in the “Select devices by type” heading.

Check the box for the device in that category.

(I programmed the device handler as a really basic virtual temperature sensor).

Stu_Belshe · August 28, 2018, 7:58pm

Got it, wasn’t that, I was in the right place but had chosen the wrong device handler. All good, thanks.

nellering · August 28, 2018, 8:03pm

Good. Now you should be able to login to the webCoRE dashboard from your computer and import this piston that I created with the import code 0mzz. You will need to modify some of the variable definitions with your API key, etc. You can also change how often it updates, but I suggest you let it stay at 30 seconds until you know it works.

Select your device you created in the second “with” command. In the photos it is “unknown device 3”.

There is a log you can watch after you save the piston. The log will output certain variables when the piston runs to verify that you are pulling the data.

Let me know if it works!

Importing Google Sheet cell data

Customers

Developers

Download the SmartThings App