Google Sheet Time Function NOW()

[Muhammad_Hamada]

I have been trying to use the google sheet’s function NOW() as column 1 value.
But unfortunately after trying a lot I have not been able to copy column 1 value to column 2.
I want to accomplish this functionality because column 1 value changes continuously due to google sheet’s NOW() function.

[Lynn]

Hi @Muhammad_Hamadan
Could you please explain further what you are trying to do and why. Maybe you could use App formulas or initial value??

[Muhammad_Hamada]

sure @Lynn. thanks for your response.
I don’t want the user’s device time to be recorded. Rather I want the time from google sheet to be recorded at the time of submitting the report.

Appsheet’s time functions return the device time as far as I know.

[LeventK]

@Muhammad_Hamadan
Why don’t use UTCNOW()? It returns the current date&time value from the AppSheet server as per UTC. If the NOW() value has an importance to you (as per user) you can get that with concatenating UTCNOW() and USERTZOFFSET() expressions.

[Muhammad_Hamada]

thanks a lot @LeventK. I didn’t know UTCNOW() could be used.
I hope it will solve the problem of user changing the device time and then submitting the report.
I’ll try it.

[LeventK]

@Muhammad_Hamadan
UTCNOW() value directly generated from the AppSheet server, not from the user’s device, thus it doesn’t affect the returning value provided the user changes the device’s date&time or even regional settings.

[Steve]

LeventK:

Why don’t use UTCNOW()? It returns the current date&time value from the AppSheet server as per UTC.

No it does not, nor has it ever done so.

LeventK:

UTCNOW() value directly generated from the AppSheet server, not from the user’s device,

Nope.

[Muhammad_Hamada]

Thanks @Steve for your reply.
Then what should I do @Steve ?

[Steve · Accepted solution]

The best you can get is the time the AppSheet server received the new or updated row. The time recorded by the AppSheet server cannot be manipulated by the user and is not subject to the user’s device’s time. But, the server-provided time reflects the current server time when the data was received by the server, not the time when the data was actually added or updated by the user on their device. There will always be at least a small delay between the user input and the server timestamp because the user’s change must sync to the server. If the user is offline and doesn’t sync for three days (for example), the server-provided time will be three days after the actual change.

To capture the server time, create a workflow on the table that triggers on adds and/or updates and sets a column in the added or updated row to either NOW() or UTCNOW() (depending on your comfort level dealing with time zones).

[Muhammad_Hamada]

All right @Steve. Thank you so much. I’ll try it.
Thank you @LeventK for your help also.

[LeventK]

@Muhammad_Hamadan
You may also take a look at this:

Device Independent TimeCapture Tips & Tricks 💡

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.get…

[Muhammad_Hamada]

Thanks @LeventK

[LeventK]

You’re welcome

[Muhammad_Hamada]

Thanks a lot @Steve It worked.

[Muhammad_Hamada]

@Steve Is it possible to add a time duration to the date that I get from the server?

I want to add five hours to the date of the server.
I have tried DATE(NOW()) + “005:00:00” but it only returns the date of the server and does not add the time duration.

[Steve]

Muhammad_Hamadan:

I want to add five hours to the date of the server.

To the date? Or to the date & time?

Muhammad_Hamadan:

I have tried DATE(NOW()) + “005:00:00”

NOW() produces a DateTime value. DATE(...) converts that DateTime value to a Date value, which removes the time component. Adding 5 hours to a Date value produces a Date value.

Try this instead:

NOW() + "005:00:00"

See also:

help.appsheet.com

Date and Time Expressions

Incorporate dates and times into the behavior of your app.

[Muhammad_Hamada]

@steve I somehow want to add 5 hours to the date of server.
If the server’s date and time are 19th Jan 2021, 7:00 pm respectively, the date and time in my country would be 20th Jan 2021, 12:00 am.
My country is 5 hours ahead of the server’s time.
I want to accomplish this using a workflow rule so that the date cannot be manipulated by the user.

[Manoj_Huria]

Hi! Steve! I am also struck in a similar situation and want to get the Server DateTime, however the workflow as suggested by us is not getting activated in my app. Could you elaborate further. 

[Mleiva550]

@Steve , I would like to ask you (if you know this information):

If I have a column with an auto compute formula with the now() function, the time that is recorded is the device time?

If so, and assuming that the device has a delay of 2 hours (for whatever reason), those 2 hours of delay are the ones that are recorded when recording that record in appsheet?

thanks, marcos

[LeventK]

Thank you very much for correcting my mis-information @Steve.

[Muhammad_Hamada]

@LeventK It isn’t working. I changed my device’s date and time to check if the time recorded would not be affected.

[Steve]

---

@Mleiva550 wrote:

If I have a column with an auto compute formula with the now() function, the time that is recorded is the device time?

---

Correct.

---

@Mleiva550 wrote:

If so, and assuming that the device has a delay of 2 hours (for whatever reason), those 2 hours of delay are the ones that are recorded when recording that record in appsheet?

---

Correct.