Google Sheets Charts

I’m having the issue of blank cells also, but I have a Gen5 HEM, and the DH for the gen5 doesn’t have an option for time interval or watt interval. Is there a solution for Gen5 owners?

Question on google sheets for you. Do I need to add enough rows for the data or will google sheets logging add the rows for me? If it adds the rows for me how do I now what range to use in my chart?

It should add the rows for you.

The chart range should expand to follow the added rows.

I did have some issues with this at first but it seems okay now.

1 Like

Is there a special formula for the chart range to expand automatically?

Not that I know of. You might post your question in the Google Sheets Logging thread … Log events to Google Sheets [see post 154 for current Github repo and v1.1]

I think it describes a test as well where you just add to your sheet directly with a specicific URL separately from SmartThings just to make sure the Sheets part is working. Then you can go to the next step.

@diehllane , Is your sports calendar “chart” still working? I was trying to do something similar for my regular calendar events but when I try to publish the “table chart” I don’t see the options to make it an image. Sheets just publishes it as a mini-spreadsheet (not full sheets UI)… not an image for ActionTiles though.

Yea. Mine is still working.

I just made a new one for testing.
When I am on the chart sheet I select Publish Chart.
I have it on the Link tab. The first box is empty (I have no name for my chart) and the second one I change from Interactive to Image. I’m not Publishing the sheet that the Chart is on (I put it on its own sheet and it’s called Chart1), but the chart itself.

https://docs.google.com/spreadsheets/d/1z12ce6QZHcZniSn77nVs4url9Yu0447QgzhWiyDk_Ns/pubchart?oid=1203545699&format=image

Thank you, now I see it. I swear it seems to hide from me sometimes. :confused:

Another question, does this Calender Event Importer (I found the same one earlier) automatically refresh/sync the events? I don’t see mention of that, seems like a one time import. I would like it to refresh/sync continually without interaction. Thanks again!

It is a 1 time importer.

It works fine for my needs (as I can import an entire sports schedule prior to the season).

I do not know of a way to have it constantly refresh itself, but that would likely required a paid extension.

Anyone have a way to display non-numerical data? I’m trying to visualize on a timeline when my HVAC is running. The data is coming in from my Ecobee into a google sheet, but I haven’t found a chart that will allow it. I just want to plot something like this:

No idea if it’s possible with google sheets, but I’d like to try!

Can we presume that the data is currently in a format like “active” / :“inactive”? If so, have you considered converting that to text?

I did something similar with my motion sensors… Sensor 1 on = 1, off = 0… Sensor 2 on = 2.5, off = 1.5… etc

Then, I created a chart with three columns (or more if you have more sensors). Time, Sensor 1, Sensor 2. Each row has the associated data… Works well for me, I published it to a chart and have it automatically update… Using it in actiontiles without issue… so far…

@moblsu screenshot please :). which chart type?

Ecobee reports it as heating, inactive, and cooling. I guess I can make a formula that translates that to 1,2,3 and graph that. Not sure why I didn’t think of that but thanks for the tip!

I’d go 1, 0 , -1 personally… formulas should be pretty easy.

Worked like a charm. Thanks!

@moblsu @Brian_Busch - which chart type are you using? I wish we could do a Bar Chart in the Sparkline format, but that doesn’t work. Using Line Chart with the numbering scheme you suggested seems to be the only way.

Thank you!

@kevin - Sorry for the delay in response. I was not aware of sparklines before you mentioned them here, though they look cool, I don’t know how you can use them.

The type of chart that I’m using is simply a line chart, though I was having quite a few difficulties getting the exact binary look that I desired… it seemed to work… I’ve since really abandoned that on my side, because it hasn’t been very useful. Also, I learned that Google Sheets can only have 400,000 cells at any given time in a given Sheet. Thus, I had to make some modifications to my methodology.

I’ve since created multile instances of Simple Event Logger to specifically focus on given data, like temp or motion… I’m also trying to utilize query, filter, arrayconstrain, and other formulas to narrow down the data within one cell rather than needing multiple sheets to narrow down the the same amount of data…

No problem. I have the line chart working pretty well using your technique of changing the text to numbers for each device…

I agree, I don’t know that its’ all that useful, I just like lots of charts I guess :slight_smile:

Kevin, do you mind explaining how you get the even history on AT?

@idioffo89 This topic got me started…

It is a bit convoluted and not working very well…
-I have WebCoRE post the desired events to a top secret blog via IFTTT (weebly or blogger are two options)
-Find the RSS feed for your top secret blog
-Create an ActionTile MTG RSS media image
-Use that image in ActionTiles

Problems…
-my IFTTT applet keeps turning itself off, haven’t been able to figure out why
-the MTG RSS media image seems to have a bug where the order isn’t quite as expected

1 Like

© 2019 SmartThings, Inc. All Rights Reserved. Terms of Use | Privacy Policy

SmartThings; SmartApps®; Physical Graph; Hello, Home; and Hello, Smart Home are all trademarks of the SmartThings, Inc.