Get app user's real local time offset

[Joseph_Seddik]

Hello,

This is a very very useful, simple and accurate method to determine, the current offset of the user's device local time relative to your own local time as app creator or to GMT. 

Concepts: 

• App Script Time functions evaluate relative to the timezone of Google Cloud servers, which is apparently always: America/New_York.
• Google Sheets spreadsheet Time functions evaluate relative to the timezone configured in the sheet as the sheet's timezone.
• AppSheet Time expressions evaluate relative to the app user's device own timezone. 

Method: 

1. Here we will combine Sheet formulas with App formulas. Sheet formulas refer to the location of respective columns. For the purpose of this guide, we'll assume the following column order in the sheet: 
   sheetTime, userTime, userOffset, User  - Their respective Formats in Google Sheets should be:
   HOUR, HOUR, DURATION, TEXT
   
2. You can either write Sheet's formulas in AppSheet as Sheet formula or write them directly in the Sheet, then import the table in App Sheet, and it will do the conversion. I'll quote both versions. 
3. You need to enable Reiterative Calculations for you Sheet: File --> Settings --> Calculation. This is mandatory for the sheet formulas to work correctly. Otherwise you'll get an error. 
4. In your AppSheet table add column sheetTime type: Time, and set its Sheet Formula to: 

IF(RC<>0, RC, TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())))​

         or, write this formula in the Sheet's corresponding column, then import to AppSheet:

=IF(A2<>0, A2,TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())))

1. 5. Add another add column userTime,type: Time, and set its App Formula to: =TIMENOW()
2. 6. A third column userOffset, type: Duration, will calculate the user's real time offset using this this expression in the Sheet Formula: 

MROUND(ABS(RC[-1]-RC[-2]),"0:15")*IF((RC[-1]-RC[-2])<0,-1,1)​

          The sheet's equivalent is:

MROUND(ABS(B2-A2),"0:15")*IF((B2-A2)<0,-1,1)

Et voilà! 

Here's a sample app. You can experiment by changing your local time or time zone and see how the results is calculated correctly every time, and the best thing is that DST won't mess up your calculations any more. 

User's Local-Time GMT Offset

Enjoy!

[Steve]

Device's UTC offset:

(UTCNOW() - NOW())

Convert a saved UTC date/time to local date/time:

([UTCDateTime] + (UTCNOW() - NOW()))