Google-sheets – How to record the date/time when a new row is added

google sheetsgoogle-apps-script

I want to add a date value when a new row is added in Google spreadsheet.

I have seen the script on Add a 'creation date' value when a new line is edited, but I was not able to modify the code to my needs.

Which changes do I need to do in that script?

Best Answer

In the question you are linking to, the date stamping happens when a new line is edited. If you want the date to be inserted immediately when a new line is added, simply replace EDIT with INSERT_ROW:

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "INSERT_ROW") { // <-- This is true when a new row is inserted
    fillEmptyRows(sheet);
  }
}

function fillEmptyRows(sheet) {
    var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
    var firstColumnValues = range.getValues();
    for (var i = 0; i < firstColumnValues.length; i++) {
      if (firstColumnValues[i][0] == "") { // Column 0 is A
         firstColumnValues[i][0] = new Date();
      }
    }
    range.setValues(firstColumnValues);
}

The script must be installed as an event handler: Click Resources -> Triggers -> Add new trigger, select the onModification method and select On change from the right-most dropdown menu.

NOTE: This does not seem to work yet if you have opted in to the new Google Spreadsheets.