Google-sheets – Add a date stamp when a new row is added from another tab

google sheetsgoogle-apps-script

I have been trying this for a few hours. I would like a date stamp inserted on column K when a new row is added (my data is on another sheet). I currently have the script below, however, it only works when I edit column 4 manually and not when the row is moved. I want the date stamp to be put when a new row is added. Could you please help me around this?

function onEdit() {

  var s = SpreadsheetApp.getActiveSheet();

    if( s.getName() == "Marketing Dept" ) { //checks that we're on Sheet1 or not

  var r = s.getActiveCell();

    if( r.getColumn() == 4 ) { //checks that the cell being edited is in column D

  var nextCell = r.offset(0, 7);

    if( nextCell.getValue() === '' ) //checks if the adjacent cell is empty or not?

nextCell.setValue(new Date());

}
}
}

Best Answer

Edit triggers (simple and installable) only are triggered by editing a cell or range by using the Google Sheets user interface. "by editing" we means changing the cell or range content.

There is another trigger that migth be what are you looking for, the change installable trigger.

I suggest you to study the event objects. In the case change event object it has the changeType property that could help your script to identify if the change made was to insert a new row. If you use e as the variable name for the event object the you could use

if(e.changeType === 'INSERT_ROW'){
  // Add here what you want to be done when a row is inserted
} else {
  // Add here what you want to be done in any other case
}

Resources

Google-sheets – Add a ‘creation date’ value when a new line is edited

You could add a trigger that runs when new rows are added to the spreadsheet:

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "EDIT") { // A cell is edited. Other values include "INSERT_ROW" (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);
}

You need to register this trigger manually: Click Resources -> Triggers -> Add new trigger, select the onModification method and select on modification from the right-most dropdown menu.

When a cell is edited, the script will fill all blank cells in column A with the current timestamp.

I have set up an example spreadsheet, feel free to play with it.

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

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.

Best Answer

Related Solutions

Google-sheets – Add a ‘creation date’ value when a new line is edited

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

Related Topic