Timestamp with your local time NOT user's time zone

Tutorials
#1

Happy to share a trick if you want to track activity in your App, e.g. how many users are active in the last X hours then how many the last Y minutes.

This solves the issue that when you push the current time into a cell using a ‘Set columns’ action Glide pushes the current time on the user’s device versus your Local time. This can throw up issues, for example it may appear that users in Asia are active on the App in the future!

So my solution is to leverage your Google sheet. The magic is to insert a formula with a circular reference. By default, your Sheet will give you an error #REF. However, if you go to your file settings and switch on ‘Iterative calculations’, the sheet will allow the formula to run X times (set X to 1). Moreover, it will run every time anything in your Sheet changes - a kind of dynamic timestamp. The video shows the formula I used.

I used the formula and added a set column that simply increments a counter in my Google sheet, i.e. changes something and triggers the Timestamp update. Now, instead of setting a column with ‘Current time’, instead I set it using my new dynamic Timestamp value.

*One caution, since the Google sheet updates a second or two after you set a column (e.g. the counter) then the Timestamp will also have a couple of seconds of delay.

6 Likes
System Time, please
Set column - Time zone
🕒 System/GMT Time - Solved!
#2

Nice job!

I still think there will be a way to do it in Glide, but I will admit I spent a while on it yesterday and drew a blank. Maybe @Jeff_Hager might be able to come up with some magic…

PS.

That of course is all relative. For some of us, users in the UK are living in the past :stuck_out_tongue:

4 Likes
#4

That’s the best solution I’ve seen short of a script.

I’ve thought about this before, but it always came down to needing a known timestamp, such as GMT, thats recorded at the same time as a timestamp from the user’s device.

I got to ask though, does that timestamp change when you make a change in any row, or only when you change that specific row? Say you have 4 rows each changed by different users in different timezones. Would the formula cause the time to update in all rows when the sheet changes, or does the time only update in the row that was changed?

3 Likes
#5

NOW() is one of a few GSheets functions that’s considered “volatile”, meaning that it recalculates with every edit, anywhere in the sheet.

https://support.google.com/docs/answer/3092981?hl=en

For that reason, it’s generally advised to use with caution. But this case is fine, as it’s just a single cell.

3 Likes
#6

I had to withdraw my post when I realised me in UK you too. :star_struck:

1 Like
#7

Yep, works with any cell, any row!

1 Like
#8

@Simon_Hill, that’s a cool trick!
Did I understand this correctly: the formula updates if there is a change in that sheet where the formula sits or in any sheet of google document.
What do you mean with “added a set column that simply increments a counter”?
Are you incrementing the counter with a script function or somehow from Glide?
Thank you

#9

Hi Christoph,

Thanks… it is quite a cool trick. The formula updates for any change anywhere in the google document (any tab, any sheet).

Re: What do you mean with “added a set column”…

I picked a few strategic places in my App (trip wires) where I could add a step to a custom action (in Glide), namely ‘Set column’. For example, if there was a button or a form then I would extend the custom action with a ‘set column’. You basically want to update something in your Google sheet to trigger the formula change. The set-column was simply setting the current date into a redundant cell in the sheet (Users). This change in turn triggers the formula update.

Originally, I started out with an increment action, but switched to using a set column. Apologies for the confusion.

Hope this helps.

1 Like