Anyone with an answer!
What is wrong with this so I can create a graph?
According to the error, it says it should only contain datesâŠand you have dates and times.
OK, how do I fix that?
@joelw135 I donât use this kind of graph (so i wasnât commenting) but I would think it can also do graphs of things that happened within one day. So I would think including time isnât a problem.
Joel, Is the first column indeed date-time data? Which is to say, can you change to a different format (like, dd/mm/yyyy) and it will change? If it doesnât change, then it might be strings, and not actually date objects. Know what I mean?
To be honest, it seems unlikely. But it is something easy to check. Just try to change display format in Sheets ( /Format /Number, then try different dates or custom dates. google for help if needed).
HTH
Thanks will try that.
My Google Sheet has stopped updating as of a few hours ago. Live Logging shows the events are being captured and sent:
11:45:21 AM: debug Google accepted event(s)
11:45:21 AM: debug 200
11:45:19 AM: debug https://script.google.com/macros/s/xxxxxxxxxxxxxxxxx/exec?Time=2%2F16%2F2017+11%3A40%3A19&Pantry+Door+temperature=71&Home+Energy+Meter+energy=15.032&Hot+Water+Tank+Leak+Sensor+temperature=71&Home+Energy+Meter+power=860.310
11:45:19 AM: debug url [https://script.google.com/macros/s/xxxxxxxxxxxxxxxxx/exec?]
11:45:19 AM: debug Processing Queue
Mine stopped updating around 9pm last night. No entries on my sheet, IDE device history doesnât show any issues.
I opened live logging now and it is showing events accepted by google. I didnât change anything but my sheet is suddenly updating. 
Might you have 2 million cells in your sheet?
No, Iâm up to 4,440 rows with 11 columns.
Ugh, nevermind, it did not stop. For some reason it started adding rows to the bottom of my sheet after several thousand blank rows I had in there so I didnât see the new rows. I have the blanks in there as Iâve had issues in the past with no updates when there are no blank rows (hope that makes sense).
After catching up in this thread, I checked the sheet I log temperatures in and found it hit the cell limit on Jan 27, so nothing had been logged since then. I manually rotated it (just like @RedKnight described above) and itâs recording again.
The problem is that while using Sheets like this is a quick way to log data, it really makes a terrible database.
Charles,
I was looking at how to create a single Google Doc with multiple sheets (tabs). I have this working (but I cludged it together and so it can probably be done better).
Anyway, wondered if you wanted to incorporate it into your base. If so, feel freeâŠ
The basic premise is the URL would have a key / value pair where the key is âworksheetâ (figured that was safe enough that it wouldnât be a device name, but maybe thereâs a better name). Instead of using the sheet âSheet1â in the script code, I added this at the top:
function doGet(request) {
if(request != null) {
// Assume a parameter is the sheetname (worksheet=[name])
var sheetname = "Sheet1";
for (var i in request.parameters) {
if(i.toString().toLowerCase()=="worksheet") {
sheetname = request.parameter[i];
delete request.parameters[i];
break;
}
}
Then a couple of lines down, I use
var sheet = ss.getSheetByName(sheetname);
Thatâs it for the script. On to the smartapp. I had to change two places: in the settings to add the sheet name and in baseUrl().
Settings (for âGoogle Sheetsâ section):
input "sheetName", "text", title: "Sheet (tab) Name", required: false, defaultValue: "Sheet1"
baseUrl (after the line with url += âs/${urlKey}/exec?â)
if (settings.sheetName != null) url += "worksheet=" + URLEncoder.encode(settings.sheetName) + "&"
And thatâs it! Now I have one Google Doc with several tabs and each named uniquely.
Paul
1 Like
Is the cell limit per sheet or per document? If the later, this would just make it happen sooner.
Itâs 2M per worksheet (document). This change is backward compatible, though so you can still have many docs or a mix.
@krlaframboise recent revised his Simple Event Logger (SEL) to delete extra unused columns, so they donât keep propagating with each new log row. SEL outputs one row for each event (it doesnât summarize them, like CSchwerâs logger) and only uses 4 or 5 columns, it doesnât need the 26 (A-Z) created with all new sheets. SEL gives a little readout of how much space is left if you bring up the app.
The 2M count is very simple: rows times columns. It doesnât matter if the columns or rows are completely empty. Additional tabs add to the count. Each new one immediately uses 26,000 cells (1.3% of 2M) unless you pare it back.
It would be great if these logging apps sent you an email or notification when about to get full. Repeated as you get closer. The code to detect how many rows are left is pretty easy, but I donât know how to send an email or notification from Google Apps Script.
@phlepper, can I ask what youâre outputting to the different tabs? I like having it in one place.
@RedKnight, I just started using the logger. I have different tabs for different devices (one for lights, one for door, one for presence, one for temperatures, one for battery status, and so forth). I tried originally with one sheet for everything and it gets messy fast.
The 26,000 cells for each tab is just based on the fact that it starts with 1,000 rows and 26 columns (A-Z). You could delete the extra columns and eventually youâll use up the rows anyway. Ultimately, one single tab versus multiple tabs shouldnât make (much) difference. Each tab has an extra column (Time) since itâs duplicated in each tab. But presumably the rest of the columns are the same.
As for the total size, the script could be enhanced to check the total rows and columns and there is a tutorial here that uses MailApp.sendEmail to send emails. Maybe that could be incorporated to send an email when the sheet is getting full? I havenât really played around with the scripting, but I can take a look at that nextâŠ
First off, first post here. This is such a great addition to my ST hub. And it was pretty easy!
I have a door sensor as I am sure a lot of you do. I am struggling with a count graph/table. I created a graph to count open and closed, (each equal to the other as it should be). I cannot though, route the data into either Days/Weeks/Months and count them based on either set up. I want to create a graph that counts Open per Week for instance. I cannot seem to group each day into their months as Sheets does not allow grouping! Such a silly omission.
Is there a way to group every day for individual months 1-28 for instance for Feb. and then allow me to count each Open that occurred that month?
Also with my energy meter, I would love to be able to average out Watts and then kWh into individual weeks or months as necessary. Thank you.
Hi @awest1981,
I canât directly address the counting - sorry - but can I mention a couple of things?
The logger outputs the latest value for endpoints you are monitoring, if any occurred in the the queue time. This has a couple of implications: 1) If more than one value happened, it only shows you the last one. (You will miss counts.) 2) If it is something like Open/Close or motion Active/Inactive, and the action usually takes much less time than the reporting period, then you will mainly see one type of value (like Open if you mainly leave it open). It will look like something was endlessly only Opening without Closing if the reporting period is much larger than the time that actions usually take. You will also see counts off when things happened across reporting intervals. Thereâs also a very small time period when info can simply be lost (when the script runs. Also of course, if your hub or internet is down, etc.).
So you canât get an accurate count of the number of times, but you can get a boundary (minimum) of how many times it happened. If you really want to count every single instance of something, use @krlaframboiseâs Simple Event Logger. You can have them both running, no problem.
Sorry but I canât help on the grouping per se, I know what you mean but not how to do that in Sheets. I myself would probably import it into a dataset and do it there. Or if it was in Excel, a pivot table. But I donât know Sheets well.
For the energy meter, you canât really use your watts except as examples of what it has been at specific points in time (which can still give you a feel for how it varies)⊠Watts are an instantaneous measure (one point in time). But the kWh measure is cumulative and can be used to get an average for the time that passed. If it went up 1 kWh in an hour, then you used an average of 1000 watts over that hour. To get this average, subtract the kWh value from the previous kWh reading, multiply that difference by 1000, and then divide it by the number of hours elapsed since previous reading. If you subtract one Time value from another, itâs a fraction of days. Multiply by 24 to get it in hours.
Note that your Time column is the time when your queue started. It is not the time of each datapoint. If your energy meter is flowing fast, the real time of your kWh values will probably be more toward the end of the queue time (it may have reported 10 times in the interval). Over long periods of time (many rows), this is not a big deal. But be careful of using rows real close to each other, especially if the kWh reading is changing slowly.
Again, Simple Event Logger handles this much better because each datapoint is logged individually together with its actual time stamp.
CSchwerâs logger is much better for summarizing or simplifying a lot of data, across time. The Simple Logger is much better for focussing precisely on some parameter ⊠but this can also make it hard to grasp across time, especially relative to other data values in your house. A virtue of CSchwerâs log is that each row is about the same time interval, regardless of how often the devices on the row report. Both of these loggers are great in their respective ways.
I hope this has helped some.
1 Like
This is great help. It makes sense. I do use the Simple Even Logger too, they both work great. I also realize after getting some of this set up a lot of the issue is missing features in Sheets. I will reach out to people in Sheet forums (if there is one) and find out what to do. Either way I think I have most of what I need now. Thanks again.