Hi,
I am showing a ‘Form’ used for creating parking permits on a tablet in a public place.
When a truck-driver submits the form, the timestamp is inserted into my google spreadsheet.
If the form has been open for lets just say 3 hours without nobody using it, then when the next person enters their information, the timestamp that is inserted into the spreadsheet, is 3 hours behind as the time was from when the form was loaded 3 hours ago.
I have tried several things to make it work, but all of them gives me problems.
- I have tried creating a ‘Behavior’ in the Appsheet with
‘Data: Set the values of some columns in this row’
Then selected the ‘Timestamp’ column = Now() and then added this behavior to the ‘Save’ button of the app.
But 5/10 times the timestamp is not added to the column of the spreadsheet, it’s just blank. I cannot see any pattern of when or why it is added or not, it seems totally random to me. Therefor I have deleted this behavior again.
- In my spreadsheet I have several scripts running to handle the data from the Drivers when they fill out the form.
I have tried to add this in the beginning of the first script running:
// Add the current date and time to column A in ‘Filter’ sheet on the last row with data.
// This is updated with current date and time because the ‘Responses’ sheet can have old timestamps depending on for how long the Appsheet app has been open without inputs
//Get the last row with data
var lastRowWithData = targetSheet.getLastRow();
var date = new Date();
targetSheet.getRange(lastRowWithData, 1).setValue(Utilities.formatDate(date, “GMT+2”, “dd. MMM yyyy HH:mm”));
It works perfectly, HOWEVER, since I am also using this ‘onEdit’ to trigger my scripts when new data is entered in the App, I then get some ‘double’ rows with identical data in another sheet used to show data I have manipulated with.
function copyDataToFilter(e) {
var sourceSheet = e.source;
I am not sure, but I think my problem with the ‘Identical’ rows occurs because the script is triggered by a change, so when new comes in, the first script kicks off, then when it comes to the ‘New date’ update, the script see this as a change and the script is running again. Am I right?
Sorry I am very bad at explaining, but yeah.
Can someone tell me how I can make sure that I will always get the CURRENT timestamp in my spreadsheet, nomatter for how long time the App has been open without any inputs?
I dont care if the solution is made from the appsheet side or in the apps script.
all the best