Google-sheets – Capture Created Date from form that allows for editing

google sheetsgoogle-formsgoogle-sheets-timestamp

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

Best Answer

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]);
  }
}

Related Solutions

Google-sheets – Parsing timestamp in Google Sheets with script

The following piece of code will set the background color of the entire row:

function onFormSubmit(e) {
  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get the minutes and hours of the timestamp (time[0]) 
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    e.range.setBackgroundColor('yellow');
  } else {
    e.range.setBackgroundColor('red'); 
  }  
}

UPDATE
This code will set only the back ground color of the timestamp:

function onFormSubmit(e) {
  // get active spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get row index
  var row = e.range.getRowIndex();

  // get the minutes and hours  
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // set range to timestamp
  var range = sh.setActiveCell("A"+row);

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    range.setBackgroundColor('yellow');
  } else {
    range.setBackgroundColor('red'); 
  }  
}

Add this script, via the script editor, to your spreadsheet and set the trigger:
enter image description here

Here's the form: Test For Web Applications and here's the sheet

Google-sheets – Calculate monthly sum() on pivote table

You can use =text(number,format) to add a column to your original data set.

You can then use the pivot table to group them by year-month or you can use =sumif(range,criteria,sum_range) to calculate the sum based on the data in the new year-month column.

Best Answer

Related Solutions

Google-sheets – Parsing timestamp in Google Sheets with script

Google-sheets – Calculate monthly sum() on pivote table

Related Topic