It’s possible that others have run into that problem and just not reported it. I think most users that are primarily interested in reporting and graphing end up using the Google Sheets Logging SmartApp.
It doesn’t log every event, but that allows it to use a layout that’s easier to use for reporting.
After a bit of messing around I managed to solve my own problem. I changed the Locale from United Kingdom to United States (File > Spreadsheet settings), and then re-formatted the “Date/Time” column A to “m/d/yyyy h:mm:ss”. That seems to have worked.
I prefer Simple Event Logger to the Google Sheets Logging SmartApp as it lets me control the number of events recorded. For example, I could limit it to one event per hour. I couldn’t see a way to do that on Google Sheets Logging.
I’m pretty sure Simple Event Logger can’t do that either. Technically you can set it to log events every hour and you can set the max events per device setting to 1, but that won’t work as expected if the has more than one attribute.
For example, if you’re logging battery and motion events and within that hour a device created the events battery 100%, motion active, and motion inactive it’s only going to log one of them.
Yes, that’s how I’m doing it (with one attribute).
Hi Kevin,
First of thanks for such a great app. I am logging data from 15 Aeon energy meters (v1). I am having some issues though.
My Archiving apparently is deleting most recent records rather than the oldest.
also even if I ask the app not to log description, it still does. thanks.
The archive feature saves a copy of the workbook with a different name and then completely clears the active workbook so what you’re describing shouldn’t be possible.
I recommend starting over by creating a new workbook, adding the script, and updating the url in the SmartApp.
Before creating the new workbook make sure that the “Log Event Description” setting is disabled and “Delete Extra Columns” is enabled.
If it still logs the description then enable the “use event value and unit as description” setting. You shouldn’t have to do that, but I recently messed with that part of the code so it’s possible that there’s a bug with disabling description logging.
I am just setting this up for the first time and I just wanted to pass along that I, too, had to remove the “/u/1” in the URL to make it work.
And, I, too, am a multi-Google account user.
Just starting playing around with this today. Works great, got all my sensors logging and plotting using Pivot Table.
I see some posts about how it will eventually get errors when the google doc overloads. Just wondering if there’s a way in the App or in Google Docs to have it erase or overwrite old data at some time threshold. For instance if I only want to keep a weeks worth of data on the google doc.
Or if I have it publish the latest data at the top of the sheet can it say delete anything past row 10,000 or something.
Thanks.
I believe he created limits in the app to prevent that, it will archive your current data then clear your sheet and start writing the new data. Something about it a few post up.
Hi, GSuite user here. I had an issue with the url for the published sheet. Yes, I used the one on the publish dialog box, and not the redirect. The url I get was a bit different than your examples.I did set the correct permissions, but it included my domain name in the url like so:
*has been edited from the actual url
https://script.google.com/a/mydomain.com/macros/s/nr08hg[0rnv098ehgern9o[gnear0gneq0[ohng0ea]hngoiugbne/exec
I logged out of google and pasted the url and got the version# and it did redirect ok. When I pasted it into the app on my phone, I didn’t get any errors. It was unable to get the version of the script from google sheets to display in the app. I also wasn’t getting any google sheet info on the Live Logs in the IDE and no data was getting to the spreadsheet. I also didn’t see any logs in the app on my phone.
What I had to do was remove “/a/mydomain.com/” from the url and then everything started working as expected.
Here’s what the url looked like after removing the “/a/mydomainname.com/”
*has been edited from the actual url
https://script.google.com/macros/s/nr08hg[0rnv098ehgern9o[gnear0gneq0[ohng0ea]hngoiugbne/exec
I didn’t see this discussed above, but I also don’t know if I did something wrong. Maybe this will help someone else.
FYI - Also, in the APP there is a spelling error on the toggle for Log Event Descripion…(missing the t in description).
@tn_oldman is correct, see the archive settings in the “Other Options” section of the SmartApp.
You didn’t do anything wrong.
I just created a new sheet, installed the script, and deployed it and Google has made some changes.
The first time you go to publish the script it now displays a warning and you have to click the advanced button in order to get to the screen with the Allow button.
It also adds some stuff to the url which has to be removed in order to get it to work. New users need to change the url so that it looks like: “https://script.google.com/macros/s/[id]/exec”.
Having to manually figure out the url is a pain and I would modify the application so that it does it automatically, but that could prevent new users from being able to use the SmartApp if Google decides to change that part of the url.
When I have a moment I’ll update the documentation.
Is there any way to change to 12hr time? Tried formatting the entire column but when new info is populated, it goes back to the default 24 hr time.
Sorry, figured it out. Using a pivot table works for me.
Is there any reason why I couldn’t run this and the other google sheets logger in parallel in order to see which one works best for my needs?
Seems like it should work, they are independent smartapps. This one will show you every event that happens and details about the event / device. The other will give status at 5 minute intervals with one column for each device
I’m looking forward to using this, but having trouble getting it to work. I’ve double- and triple-checked that the URL for the script is formatted correctly -
https://script.google.com/macros/s/[ long string of characters ]/exec
I can paste the string into a browser while not logged in to my google account and get the version number - Version 01.03.00
I’ve installed the Smart App on my iPhone and chosen the devices to monitor, filled in the URL in the settings, hit Done - but when I tap on Simple Event Logger, it shows Google Script: ? (expected version is 01.03.00)
Any ideas on what I should check to see why it’s not working? I’m new to SmartThings, but I do IT for a living and am very familiar with Google Sheets, so it seems like this should be working, but … just not sure what I might be doing wrong!
The google sheet can log a lot of data but apparently the Pivot table that I’m using to plot each sensor over time has a much lower limit. After about 6 days the data was still logging but the pivot table sheet would error until I manually deleted lots of old rows. So found this script which solved it. Adding as an additional script and setting up a time trigger works to delete older data automatically. This assumes you don’t want to archive anything but just keep a several day rolling window of data in the pivot table charts.
function DeleteOldRows() {
var SPREADSHEET_KEY = “Your Key…”;
// Replace with your spreadsheet Key, find inside sheet URL
var SHEET_NAME = “Data”; // Put yur sheet name here
var rowsToKeep = 15000; // Will keep bottom (your number) Rows
var sheet = SpreadsheetApp.openById(SPREADSHEET_KEY).getSheetByName(SHEET_NAME);
var rows = sheet.getLastRow();
var numToDelete = rows - rowsToKeep -1;
if (numToDelete > 0) sheet.deleteRows(2, numToDelete);
// Set a trigger to execute when you want
}
This should work, although if this is your first time installing this SmartApp you’ll most likely have to mess with the url to get it to work. You can find the workaround for the url issue a few posts above.
If you’re able to get the google script to display the version then open live in the IDE before opening and saving the SmartApp in the mobile app and it should provide additional information about why it’s not working.
Just to clarify for other users, the SmartApp has several choices for automatically archiving old data which will prevent pivot tables from crashing, but the main sheet gets cleared every time the data is archived.
The script that was posted will delete the oldest data, but keep the latest data so that your pivot tables will always have data. I like the idea and when I have some time I’ll build something similar into the SmartApp.
I don’t see any issues with the script, but if you’re going to use it make sure the auto archive feature in the SmartApp is disabled.
I don’t recommend scheduling this script to run frequently because if the SmartApp’s script and this script execute at the same time you could end up with a lot of empty rows in the middle of the sheet. That’s highly unlikely, but it is possible…