This might sound dumb but question for you: the original spreadsheet was deleted off of Google Drive and wondering how to change the link in SmartThings smartapp to the new spreadsheet.
The url field is in the āOther Optionsā section near the bottom.
1 Like
I went to google sheets built the sheet tested the url and made myapp in smartthings. i went to the mobile phone and added the smart app but am unable to save the changes- āerror saving pageā ???
I was able to fix it, there was a space in the url
thanks
Is this still correct? I get āGitHub repository krlaframboise/SmartThings not foundā when trying to add it.
SmartThings broke GitHub integration yesterday so they probably havenāt fixed it yet.
1 Like
My first try on this and looks greatā¦
Is there a way to delete the excel data from time to time? I would like to see for instance only the last 30 records.
Any ideias?
thanks
No, it only has an archive feature. I think someone posted a modified version of the Google script that will do that, but itās buried somewhere in this topic.
1 Like
I have my smartapp set up to log data (from my energy meter) every 5 minutes. However, when I look at the sheet it seems to be recording data several times per minute. Anyone run into this before? I am not sure what else could affect this other than the setting that I have at 5 minutes.
I wrote this SmartApp because I wanted to log all of my events.
If you only want the most recent event at set intervals you should try the other google sheets logging app.
Thinking about it now, maybe I misunderstood what that setting does.
If I set it to 5 minutes does that mean it writes to the file every 5 minutes or does it mean that it records a snapshot of the current value in 5 minute intervals?
At the 5 minute interval it logs all the events it finds that havenāt been logged yet.
Can this be made to log only one line per device and just re-write that line in the spread sheet with the next log event?
Try thisā¦ create a new tab/sheet, then add this equation in the A1 cell (update the Sheet1 and column B as needed for your data):
=FILTER( Sheet1!B:B , ROW(Sheet1!B:B) =MAX( FILTER( ROW(Sheet1!B:B) , NOT(ISBLANK(Sheet1!B:B)))))
1 Like
Thanks for the great code Kevin. I know you designed it for logging every event, but I wanted to get your opinion this modification to the google sheets script to reduce the number of very similar events. I am using it just to log energy from a single outlet connected to my electric car. I donāt care about small variations in the power, just the big change from not charging to charging to calculate energy used.
Can you make any recommendations if this is a good way to keep small event variations from logging?
In the logEvent function I added these lines:
// New code to reduce number of entries to sheet
// Get the last row
var lastRow = sheet.getLastRow();
// Get previous event.value
var lastCell = sheet.getRange(lastRow, 4);
var lastValue = lastCell.getValue();
// Check if last value was a number. If text, change to 0
if (typeof lastValue != ānumberā) { lastValue=0; }
// Compare previous event.value to current event.value
// If absolute difference bettween the two values is less than 5, do not log.
if (Math.abs(lastValue-event.value)>5){sheet.appendRow(newRow)}
Does anyone know how to change the code so that the Date/Time column is in dd/MM/yyyy format rather than MM/dd/yyyy? Iām not that familiar with Google Sheets so Iām not sure if that column is a date-time value that can be formatted in the sheet or whether itās just a text value.
If I needed to change the code, would it be the SmartApp or the Google script, or both? Both have date formats in.
EDIT: Worked out how to format the Sheets columns. I also changed the formats in the smart app but I think thatās just for displaying dates in the progress log/updated.
1 Like
Thank you. I misunderstood what the setting meant.
I had this smart app ruining from 2 hubs, 2 different logging Google sheets. One stopped logging over a week ago and the 2nd stopped logging a couple days ago. I have a script that keeps my data to 6000 rows max so I know its not a data overload issue. Did something change recently that I need to update or Re-Auth?
I just tried to implement the web app in Google docs and when I got to the Deploy step, and chose āReview Permissionsā, I got āThis app isnāt verified
This app hasnāt been verified by Google yet. Only proceed if you know and trust the developer.ā It does not give an option to proceed, but suggests the following:
āIf youāre the developer, submit a verification request to remove this screen.ā
I clicked on āAdvancedā and it brought me to a screen which read
āGoogle hasnāt reviewed this app yet and canāt confirm itās authentic. Unverified apps may pose a threat to your personal data. Learn moreā
It also gave me an opportunity to āGo to APP NAMEā I clicked this link and it proceeded from there. Might want to add this to the workflow, developers!
That said, I completed all steps, published the app to myself and it is not showing up on my ST app on my Android. It has been about 30 minutes since I published the app and itās just not showing up. I have other custom apps, and I can see this app in the MyApp list on graph.api but my phone app isnāt showing it. Will wait a bit longer, donāt know what else to doā¦
Enable all the logging options and then post the live logging results. If there are urls in the results you should remove or change the ids before posting them.
During those 2 days did you open the sheet and make any changes?