Timestamp via Call a Script Automation

[Ramitch_Palmare]

Hi guys~

I have a Daily Time Record App and here is the scenario:

Under the Table 'LOGSHEET'

We have a single device wherein each logging employee will input his/her:
[Employee ID]
[Employee Image]
[Location]

[DATE] and [TIMESTAMP] are ticked as not Editable of course to avoid changes..

However, it's been discovered recently that some employees manipulate their logs by changing the device's time so their logs show they're on time when they're actually not.

I have read in previous posts/threads that it has not yet been resolved so far...

I was thinking doing another timestamp type that we can call [ACTUAL TIMESTAMP] but its formula will not be under App Formula or Initial Value but rather a Timestamp value that will be derived by using automation (Call a Script) function.

My current Appscript is shown below:

function onEdit(e) {

var ss = SpreadsheetApp.getActiveSheet();

var r = ss.getActiveCell();

if (r.getColumn() == 1 && r.getRow() > 1 && ss.getName()=='LOGSHEET') {

var celladdress ='p'+ r.getRowIndex()

ss.getRange(celladdress).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm");

}

};

 Where Column 1 = is the [LOG ID] and this is the determining factor of the script if a record has been added; and

Where Column 'p' is the column of [ACTUAL TIMESTAMP]

What I want to happen is, every time a row is added, saved and synced on google sheets, a workflow will run by calling a script and it encodes a timestamp on [ACTUAL TIMESTAMP]

Please note that [TIMESTAMP] and [ACTUAL TIMESTAMP] are two different columns. [TIMESTAMP] may show the manipulated time by the employee and [ACTUAL TIMESTAMP] will reflect the time via appscript. The difference will then be considered as time tampered and Employee will be subject to an explanation.

As you guys can see, my current appscript creates an error when I check it on Appsheet Monitoring. Something is just wrong with my function and I hope that you can help me!

P.S I got my current appscript from my other google sheet scripts wherein changes are made directly in the sheet.. It works perfectly on Google Sheets.

Thank you!

[JSO]

When you edit a cell getActiveCell() returns a value.
When you are in the AppSheet and from there the value of a cell is changed, the OnEdit() is not activated and even if it does, there is no active cell.

Try to get Greenwich Mean Time from a webpage similar to: https://time.is/
(but return it to the script) and correct it based on the local differential.

[JSO]

Maybe it will help you:

function getRemoteDateTime(url) {
   // Create a new XMLHttpRequest object.
   var xhr = new XMLHttpRequest();

   // Set the request method to GET.
   xhr.open("GET", url);

   // Send the request.
   xhr. send();

   // Wait for the response.
   xhr.onload = function() {
      if (xhr.status === 200) {
         // The request was successful.
         var date = new Date(xhr.responseText);
         return date;
      } else {
         // The request failed.
         throw new Error("Error getting remote date and time: " + xhr.status);
      }
   };

   // Handle the error case.
   xhr.onerror = function() {
   throw new Error("Error getting remote date and time: " + xhr.status);
};
}

and call:

var date = getRemoteDateTime("https://www.google.com");

from Google Script

The function is called from an AppSheet bot and the returned value is stored in the Sheet from the AppSheet.

[Marc_Dillon]

The solution here is way easier than you're making it.

Use the 'return value' option in the call-a-script task. Your app script function can be as simple as:

function getTimestamp(){
  return (new Date()).toLocaleString()
}

 Then add a 2nd task to your bot to set the column value to the value returned from the script task.

Additional info: onEdit() doesn't catch changes made by Appsheet, you'd have to use onChange()

[Suvrutt_Gurjar]

Another possible option could be to use a webhook bot using AppSheet API to set the [ACTUAL TIMESTAMP] . The bot can trigger on add of a new record.

The webhook body can be something like below

{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time",

},
"Rows": [
{
"Key column": "<<[Key Column]>>",
"ACTUAL TIMESTAMP": "<<NOW()>>"
}
]
}

Please change locale, location, Timezone  and key column parameters as per requirement.