Google Sheets Charts

There are a couple Google Sheet data logging smart apps (GoogleSheetsLogging and SimpleEventLogger) and I just wanted to share a way to get a chart from a Google Sheet that could be used in SmartTiles or potentially a DH in the SmartThings App. The trick is to publish the chart as an image; from that, you have a URL that can be used anywhere you can reference an image on the internet.

1-set up your preferred logging smart app
2-insert chart from button bar, set up a chart as you like. Note: make sure to include the last row of the sheet in data ranges and this will allow the chart to refresh the new data added by your smart app.
3-(optional)from chart pulldown, select ā€˜move to own sheetā€™ (now the chart will have its own tab)
4-click publish, under link, change interactive pulldown to ā€˜imageā€™
6-click publish, copy the URL
7-use the URL in SmartTiles camera

Here is how I formatted my HEM chart as shown in the screen shot (I am using GoogleSheetsLogging so there are separate columns for time energy and power) :

  • Select date/time column, energy kWh column, power W column, insert chart
  • Select Area Chart from the ā€˜Areaā€™ section (not stacked)
  • Under ā€˜advanced editā€™, ā€˜customizationā€™ā€¦
    -Legend none
    -Axis horizontal none
    -Axis left vertical title ā€œenergy blueā€¦ā€ min 0 max 1000 allow bounds to hide data checked
    -Axis right vertical title ā€œcurrent power redā€¦ā€ min 0 max 8000 gridlines 4
    -Series Area1 is my kWh color blue, line thickness 2, area opacity 30%, point size none, shape circle, axis left
    -Series Area2 is Power in W color red, line thickness 4, opacity 50%, point size none, shape circle, axis right
    -Select the pencil button for quick edit mode, click on different parts of the chart then drag to resize the chart area to make it bigger and less ā€œwhite spaceā€ around edges
  • In order to keep only one month week or day visible on the chart, add a second tab (I called it RecentData), in the first cell type: ā€œ=filter(FullData!A:R,FullData!A:A>minus(now(),7))ā€ Adjust the A:R to match your columns, adjust the 7 for how many days you want to chart. This new tab will automagically populate with the most recent X days of data. Now change your chart to reference the RecentData tab instead of FullData.

Notes:

  • For Aeon HEM I have found that report type set to time interval works better. With report on wattage change and the Google Sheets Logging, there could be cells with no data (if the wattage didnā€™t change enough to trigger during the timespan). When this happens, the Google Sheets Chart seems to get stuck and doesnā€™t add new data points to chart even though they do exist later.
12 Likes

I donā€™t know anything about Google sheets, I just set up the account logging app. Trying to wrap.my head around sheets.

Could you possibly run a macro or script to filter out your data to only the current week? Trigger script / macro by time or something? Place data on another sheet and graph off that sheet?

Just thinking outloud, hoping someone smarter than me knows how. Then I can learn something new off their experience!

On Sheets, you could just do a Filter by Condition for Date is in the past week

3 Likes

Can you give more detailed instructions on how you make the chart with overlaid energy and power?

I have my HEM logging to sheets but I canā€™t seem to make the same chart you have.

@greg I updated first post, added some formatting details for the chart in the screenshot.

1 Like

Thanks for your instructions here. I have it all working but have one issue ā€¦ when I set this filter, once the few rows the sheet is reduced to are filled I get no more updates in the sheet. If I remove the filter it starts again. I am sure I doing something wrong but canā€™t see it. Any ideas?

When I click insert chart google sheets locks up. I donā€™t think it is my sheet, but Google.

@joelw135 Hmmm, maybe try inserting a chart with a small data range, see if that works. Google Sheets was not working well for me earlier this morning, kept popping up an error saying not responding. Still seems to be responding slow, hopefully a temporary issue.

It locks up as soon as I click insert chart.

If you leave the filter off, it works okay? Is the last line in the sheet the most recent data (no empty rows at bottom)? Iā€™ve only been using the filter past week or so but it seems to be working for me. Also make sure the data ranges in the chart include the last line (for me the last line is most recent data, no empty rows). Once I saw the data ranges did not seem to remember what I had set, but corrected it to include last row like this and it has worked fine since:

FullData!A3:A13486, FullData!C3:C13486, FullData!F3:F13486

I tried inserting a couple small charts and it worked okay. Maybe try closing out browser (what browser? Iā€™m using chrome) and trying again? Sorry I honestly donā€™t have too much experience with Sheets.

Correct. Thatā€™s the way I have it now and itā€™s working fine (other than the kWh number not coming over occasionally, kW comes over always).

Yes, the last row has the most recent data but I do have empty rows after the last line of data. If I filter it removes the empty rows and that seems to be the problem.

I have the entire columns selected. The chart just creates data points for rows that have values.

How can you set the chart to only rows with data when the rows are always being added to?

Try highlighting then deleting the blank rows at bottom, then close the browser and go back to the sheet to make sure it sticks. I wonder if were seeing some issues with trying to edit the sheet as it is getting written to by the smartapp(???). Here is what last rows of my Sheet looks like, 13490 is last one, nothing after itā€¦

In a few minutes when the smartapp updates it, 13491 will be last row, never adds blanks (for me at least)

Iā€™ll give it a whirl in a few.

btw, the blanks row were put there by me, not the SmartApp.

Thx!

Okay, so I have put the filter back on and deleted the blank rows below the data. It looks like it has stopped populating again.

I just took the filter off again, did not add back any blanks rows and it has started populating again.

I do notice that updates are not always 5 minutes (as I have set). Sometimes 12 or even more so perhaps itā€™s not really stopping but just taking longer so I think itā€™s stopped. My last two were 16 and 18 minutes apart. Other times itā€™s better and closer to 5 minutes.

Are your updates consistently timed?

When energy is logged to google sheetsā€¦ What units are they in?

Energy is in kW. Power is kWh.

Is there a way it can push only the watts used for the given period of timeā€¦ I thought thats what reset the state values was but its still just a running total in the google sheets.

I think you might have it backwards, Energy is kWh (kilowatt hour), this accumulates through the month. I think ideally it should match your electric bill if you could reset HEM at the same time your meter reader takes a reading. My meter is still read by a human so it gets read on a different day every month (so far anyway), so I canā€™t make the HEM match it exactly.

Power is W (watt), this is how much power your house is using at the moment the HEM records it. Turn on a 100W bulb and you should see the HEM power increase by 100W. The 4kW red spikes in my chart above are when electric clothes dryer is running.

Yes, sorry, I had it flipped. Replied when tired.