Importing Google Sheet cell data


(Stu Belshe) #1

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?


#2

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


(Stu Belshe) #3

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) #4

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


#5

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) #6

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


#7

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) #8

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.


#9

That’s exactly what I’m doing.

  1. 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

  1. 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.

image

  1. 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

  1. 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:
image
“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) #10

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) #11

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


#12

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

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


#13

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) #14

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


(Stu Belshe) #15

Looks like we’re up and running.


#16
  1. First thing you do is make a new device handler in the IDE. You can use this code to start with:
  1. 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.

  2. 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.

  3. 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) #17

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.


#18

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) #19

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


#20

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!