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
Set column - Time zone
#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