Submission time

[Himaladin]

Hi @Darren_Murphy , I have a problem: How to lock the “Done” button based on actual server time not based on the time of the user gadget?
Does your method still work or is there another way that is more certain?

YC for time zone change?

That integer represents the number of hours between UTC and the users local time. For example, I’m in Singapore which is UTC+8. So for me it returns -8. To use it to convert UTC to users local time, you actually need to invert it first and then add it to a date/time using a math column. Here is how I’ve used it in one of my apps: [Screen Shot 2021-11-14 at 2.08.45 PM] Note that I adjusted the code slightly to invert the result, because I want to use it as a correction: return -(new Date().g…

[Darren_Murphy]

The most reliable method to determine a users UTC offset at the moment would be @Jeff_Hager’s solution:

System/GMT Time - Solved!

This is an extension of the request for System Time, please. There’s been a lot of questions for a while about how to get a universal time that can be shared for multiple users across multiple time zones. Most of you will know that any time your get a date/time from one of the glide special values, it will always be the user’s local time. But if you don’t know which time zone they are in, then you have no easy way to convert that date/time into something standard, like Greenwich Mean Time, to…

[Jeff_Hager]

What’s considered server time here? UTC / GMT time, or local time for the developer?

@Darren_Murphy, have you experimented much with the new feature to respect timezones in a date column. Is that still in staging only? I wonder if that would be useful in some way, or could simplify some things. I haven’t had a chance to play with it, but I only see it in staging.

[Himaladin]

Thank you very much.

[Himaladin]

Sorry, I mean UTC/GMT time.
Maybe I need to explain what I’m doing.
I intend to fill the deadline according to my local time wherever I am, to determine the time limit for assignment submission to lock edit forms. I found the deadline can be cheated by changing the timezone on the user’s device. So what can I do?

[Darren_Murphy]

@Jeff_Hager

@Darren_Murphy, have you experimented much with the new feature to respect timezones in a date column. Is that still in staging only? I wonder if that would be useful in some way, or could simplify some things. I haven’t had a chance to play with it, but I only see it in staging.

No, I had a bit of a play with it when Mark first pointed it out, but other than that I haven’t touched it. To be honest, I found it a little confusing. I think I need to go back and watch Mark’s video a few times.

[Jeff_Hager]

So there is a new feature to respect timezones with a date column. So if the option is selected, whenever a user adds a specific date and time with respect to their time zone, it will display the date and time for other users in other timezones with the correct time for there timezones. I think this feature is still in staging though, and I think it will only work with glide tables, so it will depend on your data source. However, I’m not sure how well it would work for your use case. With the link that @Darren_Murphy shared, you can figure out UTC or GMT time regardless of which time zone a user is in, but I still think a user could manipulate the time on their device to get around it. Since computations occur on the user’s device, any date based math or comparisons are based on the end user’s date and time. That includes the date and time set on the user’s device, as well as the timezone of the device.

I’d have to think about it, but I don’t think there is an easy way to prevent a user from modifying their device time where it wouldn’t affect date based math or comparisons. I think in your case, if you used google sheets for example, you could possibly set up a script to run every few minutes and check the dates based on the sheet’s time, and then have it fill a checkbox once the time is expired. Then your condition would simply look at that checkbox value instead of the date. But you would also have to content with the delay of when that checkbox value syncs back to glide.

[Jeff_Hager]

@Darren_Murphy

To be honest, I found it a little confusing.

I think what it’s doing is saving the timezone of the user that entered the date. When you do that then glide (or rather the javascript) knows to convert that date time to your timezone. That’s basically what I’m doing in my example by tagging on GMT to the end of the date. The system converts to my local timezone which allows me to calculate the offset, so I can finally calculate the GMT based on that offset value.

Which actually surprises me why glide could make the Respect Timezone feature work with other data sources other than glide tables. I really think they could do it using a method similar to mine.

[Himaladin]

Very appreciate for your explanation.
Meanwhile I will use google script while waiting for updates from Glide.
Thank you Jeff

[Himaladin]

One additional question @Jeff_Hager, what if the calculation is done in a google sheet via the formula if(A2>now(), false, true), instead a script?
If I use a script then which current time is based on the time in the sheet?

[Jeff_Hager]

@Himaladin

One additional question @Jeff_Hager, what if the calculation is done in a google sheet via the formula if(A2>now(), false, true), instead a script?

I would definitely make sure you test it to be sure, but I feel like if you used a formula, it would only update if some other data changes in the sheet. With a script, you could set a trigger to run it at a certain time, or have it spin through the sheet and check all of the dates to see which ones have expired.

@Himaladin

If I use a script then which current time is based on the time in the sheet?

If you go into your sheet settings, you can set the timezone for your sheet. I’m assuming the script would use that same time.

[Darren_Murphy]

@Jeff_Hager

If you go into your sheet settings, you can set the timezone for your sheet. I’m assuming the script would use that same time.

Actually, it doesn’t.
The script timezone is independently set in the Project manifest file (appscript.json):

If you’re using the V8 runtimeVersion, then by default that file won’t be shown, but you can get at it via the Project Settings:

I believe that when a script project is first created, it will inherit the timezone setting from the parent document, but from that point forward it is independent. That is, if you change the timezone of the parent doc, the timezone of the script project remains unchanged.

This can be a HUGE gotcha if you don’t know about it. Trust me, I’ve been there. A lesson I will never forget

[Himaladin]

@Jeff_Hager

Then your condition would simply look at that checkbox value instead of the date. But you would also have to content with the delay of when that checkbox value syncs back to glide.

I noticed the duration when using google script until the glide did the update was about 3 minutes, plus if the time-driven trigger it might take 4-5 minutes. This misses very far from the deadline on the developer side.

Since this topic has been closed, so I edit the last section to convey the information, that it works well using your method instead of google script.
With a little walk around, it produces a constant time and can no longer be manipulated by changing the timezone setting from the users’ device.
This has solved my problem. I express many thanks to you and @Darren_Murphy who referred it.