Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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).

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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]);
  }
}
share|improve this answer
    
Thank you! It works perfectly! –  AnneB Jun 9 at 8:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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