Google Sheets Charts

I will post this here in case anyone else is interested.

I use my Google Calendar for my sports teams that I follow. I use the GCal smart app on here for turning on a switch when a game is on (so they show up on my ActionTiles dashboard). What I really wanted was a way to view the next week’s worth of games/events.

I have finally got it working using this thread as a basis.

What it will do:
Take any existing calendar events and import them to sheets to use in tables, charts, or for sorting.

What it won’t do:
Automatically update when you add new events to your calendar. You must re-import to get new data.

How I did it:

  1. I have added all the calendars to my Google Account (this actually works best if it’s all added to the SAME calendar, but it can be done with multiple calendars, although you’ll probably need an extra sheet for aggregating the data, which is what I had to do)
  2. Add the Calendar Event Importer add-on to Sheets (https://chrome.google.com/webstore/detail/calendar-event-importer/ffkehpeilnhbpobkeojanjohnocolbhi?utm_source=permalink)
  3. Open a new Sheet and go to Add-ons, Calendar Event Importer, Import events from calendars. This will open up a side-pane on the right where you can select which calendar(s) to pull data from. If you have multiple calendars selected, each will generate its own sheet. Fill out the date range and Import.
  4. Hide your excess columns for easy viewing (I only needed title, date, time, duration).
  5. Combine your data, if necessary (typically only required for multiple calendar import setups). To do this, I copied all data from each sheet and pasted into the main sheet.
  6. Create a new sheet for your Filtered Data. I called my original sheet Data and this new sheet Filtered Data.
  7. Use a filter formula in your Filtered Data to show only the information you are wanting (this is what you will use to make your Chart). =filter(Data!A2:G400,Data!B2:B400>=TODAY()-1,Data!B2:B400<=TODAY()+7) is the Filter formula I used as it allows me to check back 1 day (to see the games from yesterday) and check the next 7 days, including today. I only have about 250 events currently imported with a range of 1/1/2017-12/31/2017, but I left room for next year’s schedules for teams on the wraparound seasons for when I import later and add to it. I can always increase the number of rows by changing that Filter formula. I can also change the import date range to include a larger selection (end date further from today) or a smaller selection (start date closer to today).
  8. Select your Filtered Data and go to Insert Chart. The chart type I used is Table and it’s at the very bottom of the list on the Chart Type tab.
  9. Follow steps 3-7 from OP for publishing chart and getting a URL for ActionTiles.
1 Like

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:

1 Like

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