I want to get a timestamp on edit in my datetime row, but I need it to also timestamp when I copy/paste multiple rows in google script

260 views Asked by At

I'm trying to add timestamps to my datetime row on edit. What I'm getting right now is if one row is edited at a time it works. However, what I need is to timestamp every row when I copy/paste values in over multiple rows.

function getDatetimeCol(){
  var SHEET_NAME = 'Queue';
  var DATETIME_HEADER = 'datetime (+48h for archive)';
  var headers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME).getDataRange().getValues().shift();
  var colindex = headers.indexOf(DATETIME_HEADER);
  return colindex+1;}
  

function onEdit(e) {  
  var SHEET_NAME = 'Queue';
  var ss = SpreadsheetApp.getActiveSheet();
  var cell = ss.getActiveCell();
  var datecell = ss.getRange(cell.getRowIndex(), getDatetimeCol());
  if (ss.getName() == SHEET_NAME && cell.getColumn() == 1 && !cell.isBlank() && datecell.isBlank()) {      
    datecell.setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm");
  }
  };
1

There are 1 answers

0
Cooper On BEST ANSWER
function onEdit(e) {
  //e.source.toast('entry');
  const sh = e.range.getSheet();
  if (sh.getName() == 'Queue' && e.range.columnStart == 1) {
    //e.source.toast('cond');
    let col = {};
    sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0].forEach((h, i) => { col[h] = i + 1 });
    for (var i = 0; i < e.range.rowEnd - e.range.rowStart + 1; i++) {
      let rg = sh.getRange(e.range.rowStart + i, col['datetime (+48h for archive)']);
      if (rg.isBlank() && sh.getRange(e.range.rowStart,1).getValue() != '') {
        //e.source.toast('if');
        rg.setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm");
      }
    }
  }
}