Yeah, might be tricky if you are trying to calculate it on the fly, but if you have it calculated in the user profile, then you could readily pull it whenever you need it and could possibly avoid any race conditions.
Congrats… that is a great solution.
I’m most happy people have stopped talking about Zulu time and are giving Greenwich the respect it deserves (I expect nobody from Paris will agree!)…
OK, I think there is still a bug. I think it will completely freak out when the month rolls over to a new month. I’ll have to take a look at that and fix it.
mmm, in our case the issue isn’t so much not knowing each users relative time, but more about dealing with the fact that we can have multiple users all trying to do the same thing at the same time, and because of sync delays they end up tripping over each other.
For example, record X becomes up for grabs, and 3 users all try to claim it at the same time. Because the initial change is local to each users device, it appears to each one that they’ve claimed X, and so they move on to the next step. But then everything eventually syncs up with Glide and only one of them ends up with X (which usually seems to be the last one in - which I guess makes sense), and so the other two get booted out and wonder what the hell is going on.
The way we’ve “solved” it is by deliberately introducing a delay, and a 3rd party to act as an arbitrator.
In this case the 3rd party is Integromat, but it could have just as easily been a bit of Apps Script. So what happens is:
- Users A, B & C all try and claim X at about the same time by tapping a button
- That button sends a webhook to Integromat, and our users are put in a holding pattern whilst we determine a “winner”
- Integromat writes two values back to the Users GSheet for each user: X, and a UTC timestamp (NOW())
- From another table, we build a relation to X, and then we do a rollup through that relation and take the earliest UTC timestamp
- That timestamp is then combined with X in a template, and that template is used to build another relation back to the Users sheet.
- Because only one of our users will have that exact timestamp, the relation will only be not empty for one user - and that user becomes our “winner”
So now I’m wondering whether or not your solution could be applied to the above scenario - what do you think?
I’ll ponder that…and probably reread it a few times. Someone else asked about that same situation here (Help with Users Overriding Each Other - #11 by Jeff_Hager), but none of my solutions were really that great. If the only thing Integromat is doing is assigning a UTC time, then I would think that step could be eliminated and you still go with the winner. The delay could possibly be introduced by some date math. But I’ll think about it some more.
My thinking is that pulling the time stamp from an external source is the key here. Because if the time stamp is applied immediately to the users local device, then we’re more or less back to square one, as each one will think that they’ve won the race. But as always, I’d love to be proven wrong on this
I think I have this fixed now.
@Jeff_Hager just one more point to consider… when we take each users local time and use that to determine GMT, we are relying on the fact that all of our users clocks will be correct. Whilst that’s probably generally true - as most devices will be set by default to use NTP - I don’t think it can be guaranteed 100% of the time. And when you are dealing with race conditions, it only takes one user to be a minute or two slow and another to be a minute or two fast - and everything flies out the window.
This again is where I think using an external source as a reference point is key. It doesn’t even have to be GMT, it could be anything - Mickey Mouse time, whatever. The important thing is that it’s a single point of reference.
I’ll shut up now and go to bed
This is fantastic, Jeff! I’ll test with an app that I’m using currently that depends on calculating system time and get back to you. Well done brother!
My results from the UK. It’s correct because we’re in BST right now. This is a great piece of work @Jeff_Hager. I think you’re right in your assumptions about JS and browser capabilities. I can’t think it would be too difficult for @Mark to add to Glide somehow.
It’s good for France.
Good job @Jeff_Hager
I think I’ll have to make some additional updates to account for those Aussies that like to throw time around like they have no wuckas. Geez, 30, 45 (kinda), and 00 minutes…really?
This is great. I integrated some services in my app that automatically writes GMT/PST alongside the timestamp that comes back, and I usually convert that to a date/time column like what you did here so it converts to the signed-in user’s timezone, it is good.
However it never occurred to me about this offset time, thanks for putting all the work into this.
I am thinking about an Integromat flow where you can use this offset time to do a weekly reminder. My client’s users need to be reminded about doing an in-app quiz and they can choose a date of the week to be reminded. I have done that already, but it’s stressing me about a timezone problem that this may fix.
In Integromat, I can only run it based on the timezone chosen in the account (in my case it’s Mexico City, GMT-5). If I run that scenario 7am everyday to check for every row in the Sheet and sends an email if the reminder weekday is the same as Integromat’s “current” weekday, timezones which at that point are not in the “current” day wouldn’t have their reminder delivered correctly.
Do you have any further thoughts about this use case?
Fixed! Now this should account for timezones that fall into half hour or 3/4 hour offsets. Also fancied up the display of the offset a little.
Wow, I guess I didn’t realize that this would work for other timezones. So essentially, if you append the timezone code, such as PST, PDT, MST, MDT, CST, CDT, EST, EDT, etc. or if you append +0, +5, +8, etc. (not sure about negatives or decimals), or better yet, GMT+5, GMT+8, GMT-5, GMT-8, GMT+12:30, etc. after any date/time, it will lock that date time to that timezone, and what’s displayed to the user will adjust accordingly to the user’s local time. This could be a huge advantage for creating meetings or appointments that cross timezones as every user would see a time according to their local timezone.
I’d probably need a better visual, but I would think that you may be able to convert any user entered date/time to or from GMT-5., so if I understand correctly, it may work for you.
Currently the flow’s like this:
Every 7am Mexico City time I run through all the rows in the Profiles sheet, then pickout only the ones that has the same “Reminder weekday” as the “Now” weekday of Integromat, then send them emails.
With your method here, I’m thinking about an extra offset column to let me calculate the “current” time of each user, in the Sheet. I would run the scenario every hour to check for rows that have the “calculated” time as 7am, for example.
So it comes from sending everyone emails at 7am Mexico City, to being able to send emails at 7am their time.
Yeah, I think that would work. Trying to run it through my head, but I’m sure there’s a lot of parts to it.
So basically you are figuring out the user’s current day. I fall into the same time as Mexico City. So for example, I assume you are 12 hours ahead. I get confused with the international date line, but as of right now you are a day ahead from me, but at 7:00am my time, we would still be within the same day. With that I wonder if you would have a problem at all. But ignoring that, lets say your process runs in the next half hour and you picked Thursday as your day. As it stands, it would not pick up your day because Mexico is still on Wednesday. So if you had your offset of +7 hours combined with the -5 for Mexico, you would have a +12 hour offset, so you would need to add 12 hours to Mexico local time and then check the weekday of that calculated date. In theory, I think it should work out pretty well.
Some timezones have daylight savings so the time shifts by an hour, so I’m not sure if that would cause some rare situations where the calculated date would jump by an extra hour, but hopefully that wouldn’t be much of an issue.
Thanks for thinking through Jeff! Another great method from our Chuck Norris!