Auto Set DateTime for New Rows in Google Sheets

google sheets

I have a sheet. What I am trying to do is when I click on a new row to automatically set the first column in the row to =now().

I tried setting =Now() in the cell but it appears to be updating it all time time.

So I tried doing some kind of if statement. IF the next cell is empty then I set the first cell to now. If its not then I leave it to the original value.

=IF(ISBLANK(CONCAT("b",ROW())), NOW(), CONCAT("a",ROW()))

This didn't work as it becomes a circular reference.

Is there a way to configure my sheet so that the first column is always filled out with the current date? the time stamp of when the row was created.

Best Answer

function onEdit(e) {
  e.range
   .getSheet()
   .getRange(e.range.getRow(), 1, e.range.getNumRows())
   .setValue(new Date())
   .setNumberFormat('dd.MM.yy, hh:mm:ss');
}

demo sheet

or try this:

function onEdit(e) {

  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var rows = range.getNumRows();
  var date = new Date();

  var dates = sheet
      .getRange(row, 1, rows)
      .getValues()
      .map(function(row) {return [row[0] || date];});

  sheet
   .getRange(row, 1, rows)
   .setValues(dates)
   .setNumberFormat('dd.MM.yy, hh:mm:ss');
}

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.