Device Independent TimeCapture

At times we find that the app needs to capture the time input by the user ( typically timesheet apps) but since the user’s device has different time setting ( I mean time zone ) or even if the users are in the same time zone, their device time settings could be leading or lagging the actual time. This may result in not actual time being recorded in the app.

The sample below is an attempt at capturing the “device independent” time in the app.

The important things to note are

  1. There are a series of actions bundeled into a group action.The actions essentially capture the Goggle sheet’s NOW()function rather than app’s NOW() function. So it essentially seprates the time capture from the device time.

However, since the Google Sheet’s NOW() function is volatile and changes the value on every edit of the sheet, the app captures this Google sheet’s NOW() ( Column name [G Sheet Time]) and copies it into another spreadsheet column [GSheet Time 2] through the app’s action, thereby making it a static value. This [GSheet Time 2] is used as the column to understand the device independent time that is outside user’s editable columns.

  1. The [Device Time] is captured through App’s NOW() function. The app then displays two time values along with time zone for that record to demonstrate that the Google Sheet time sticks to GMT time zone ,irrespective of the device time.

  2. I tested with 4 different devices with 4 different time zones set in them.

  3. In highlighted records in the picture below, I even manipulated the device time by advancing it by 5 minutes and one hour but the Gsheet time remained at GMT

  4. I had set the spreadsheet’s locale to GMT

I must mention that a very extensive testing remains. Also I believe this app will require online operation tocapture the correct time. I will request comunity collegaues to copy and test and please update with their findings whether the concept looks good, practical with any possible flaws.
Also,since the app captures the time columns through various actions, I believe there will be inherent difference of a few seconds between times. But I believe it should be acceptable for most practical needs.

The app name is " Device Independent Time Capture" at SG Portfolio

2 13 839
13 REPLIES 13

Swoopy
Participant V

Very helpful, thanks.

But the app is not working properly at my end. I’m not sure what wrong.

LeventK
Participant V

@Suvrutt_Gurjar

Hi @LeventK ,

Thank you. This is really crucial topic.

Regarding UTC(), as per the below post, it seems that it is still device-dependent. It seems to subtract the hours from the device time to get the UTC time with respect to that time zone based on which the device is set?

So If my device time is GMT+5.5 , I believe UTC will return My Device Time -5.5
But if I set my device time as GMT+ 9, I believe it will return My Device Time -9?

Will appreciate your further insights. I will also test on a device , if UTC remains constant even by changing device time.

Hello @Suvrutt_Gurjar ,
AFAIK, UTCNOW() value is coming from the AppSheet server directly, not calculated from the device. But I will investigate this a bit further both from the emulator and my device. It’s quite simple to test I assume.

Thanks Levent

yes, I agree it should be straightforward to test.

I will also test now.

Hi Levent,

Looks like UTCNOW() subtracts or adds the time based on the device 's set region. However if I change the device time setting itself, the UTC accordingly changes. I quickly changed the device time settings to different times and the UTC also changed accordingly , simply subtracting 5.5 hours from the set device time. My device region is set to GMT+5.5.

So essentially, it sounds that UTCNOW() simply subtracts/adds by whatever the device region’s hours from the set time of the device. So if the device time is changed, the UTC time also changes.

Hmmmm…That’s not good @Suvrutt_Gurjar, that’s not good.

True Levent. So I had thought of some way to capture Google sheets time stamp. But my approach is convoluted.

Instead, I believe the much better approach is as suggested by @Steve in the post thread below around post numbers 15 to 22 to capture the server time.

Basically a simple spreadsheet script with an onChange(e) trigger might solve this:

function get_UTC_DateTime(e) {
  var sSht = e.source;
  var sht = sSht.getActiveSheet();
  
  if (sht.getName() === "MySheet") {
    var rowNr = parseInt(sht.getActiveRange.getRow());
    var baseURL = "http://worldclockapi.com/api/json/utc/now"
    var response = UrlFetchApp.fetch(baseURL);
    var utcnow = Utilities.formatDate(new Date(JSON.parse(response).currentDateTime),"UTC","MM/DD/YYYY HH:MM")
    sht.getRange(rowNr, 2).setValue(utcnow); // 2 is the Column Index of a DateTime column in the sheet
  }
}

Hi Levent,

Great one.

Basically since the UTC or device time can be adjusted, we needed some alternative.

We all have our own approaches.

You nicely solved it with GAS to get the time from worldclockapi.com

We remained around AppSheet and Google sheet backend.

As usual, I think it is great for the community to be aware of multiple approaches. One can choose as per comfort level with the approach and requirement.

This is exactly how it works. I’ve confirmed with the developers.

Thank you @Steve for the guidance.

Top Labels in this Space