0

I have 6 tab Google Spreadsheet that is actively being updated with data into each individual tab.
What I am looking for is to enter date-time stamp after each and every entry across all tabs.

I have the following code to update information in the 1st sheet:

var SHEET_NAME = 'Sheet1';
var DATETIME_HEADER = 'DateTime';

function getDatetimeCol(){
  var headers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME).getDataRange().getValues().shift();
  var colindex = headers.indexOf(DATETIME_HEADER);
  return colindex+1;
}

function onEdit(e) {  
  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 Answer 1

0

To make your script work on each and every "tab" (sheet) just remove the following

ss.getName() == SHEET_NAME &&
2
  • 1
    would you not also make a similar change in "getDatetimeCol()"? Say, var headers = SpreadsheetApp.getActiveSheet().getDataRange().getValues().shift().
    – Tedinoz
    Apr 13, 2022 at 0:18
  • @Tedinoz Thanks you. You are right :) (I can't edit the answer right now)
    – Rubén
    Apr 13, 2022 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.