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’…
-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.
- 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.