3

I've looked through the previous posts and can only find information where it looks like the responder assumes that the form is completed in one go, so the TimeStamp column is the obvious answer to the question.

I have a form that will be updated multiple times, and have noticed during testing that the TimeStamp is updated every time the form is submitted. I would like to capture the date that the entry is first created, not when it was last updated (although that will be of value later).

Is there a way - other than asking the first user to select today's date in a separate field - to capture when the form is submitted the first time? I'm thinking either scripting on the spreadsheet (copying the TimeStamp over into a separate column if the field is blank) or some form of script on the first submit (although I struggle to see how it can 'see' that it is the first submit).

0

This is possible with a script. You'll need to choose a column in your spreadsheet in which the first submission time will be stored. Below, I suppose this is Column D (i.e., 4th column). When a form is submitted, the script checks whether the corresponding cell in the "first submission" column is empty. If it's empty, the timestamp is copied there. Otherwise, the script does nothing.

This logic is contained in the function recordFirstTime. The other two functions are technical: run "Install" to install the script (it will prompt for authorization). If you want to stop the script from recording first submission time, run "Uninstall".

function recordFirstTime() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var row = sheet.getLastRow();
  var timestamp = sheet.getRange(row, 1);   // automatic timestamp
  var record = sheet.getRange(row, 4);   // stored timestamp, edit to change the column
  if (!record.getValue()) {
    timestamp.copyTo(record);
  }
}

function Install() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger("recordFirstTime").forSpreadsheet(ss).onFormSubmit().create();
}

function Uninstall() {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    ScriptApp.deleteTrigger(allTriggers[i]);
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.