This is my app: kitengacmh.glideapp.io
It’s used like a whiteboard to show where medical equipment is located within our department.
I’d like to add a field that shows how long since a piece of equipment was moved to a new location. This is to make it easier to see if someone has forgotten to return their equipment or update the app.
Can anyone think of a way of going this, without giving the user more to do?
Yes, this is pretty straight forward.
- Add a timestamp column to your sheet that is populated with the special current timestamp value when an item is edited.
- Then create another column that calculates the elapsed time using a math function, something like the following:
That should do it for you?
1 Like
Darren thanks for reply. I couldn’t see how to do the “special current timestamp value when an item is edited”. A bit of googling found this script to add to my google sheet:
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSheet();
var r = ss.getActiveCell();
//1.Change ‘Sheet1’ to be matching your sheet name
if (r.getColumn() < 9 && ss.getName()==‘Sheet1’) { // 2. If Edit is done in any column before Column (I) And sheet name is Sheet1 then:
var celladdress =‘I’+ r.getRowIndex()
ss.getRange(celladdress).setValue(new Date()).setNumberFormat(“MM/dd/yyyy hh:mm”);
}
};
Was this what you meant, or is there an easier way of doing it?
I’ve kind of got it working on a google sheet, except that when any record is updated the whole sheet recalculates and updates the NOW()-timestamp values too! I’m not that bothered, I can just give the user that time when an item was last moved.
Unfortunately however, none of this works on the app…
The locations change on the app, and the glide data sheet, and the google sheet, but the google sheet won’t update the timestamp unless I update the location directly from there.
Force-reloading the data from the glide website’s app page doesn’t work either.
Any thoughts?
You can use Special Values instead of trying to come up with a script.
As for the script, the function name doesn’t matter, but it will only work with triggers. In particular, the onChange trigger.
Steps 4 and 5 explain it more here.
1 Like