how to use a project on more than one sheet-CodePudding

I have the code below in my project, that helps me autofill the timestamp in "datetime" column in "test" sheet. I want it to work for other sheets too. But I couldn't get it to work. Any help?

var SHEET_NAME = 'test';
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() {  
  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");
  }
};

CodePudding user response：

If you want to have an onEdit() trigger runs on multiple sheets use installable trigger.

Take your code and put it in a standalone script, not bounded to a sheet.

Then create function to setup onOpen Trigger

/**
 * Creates a trigger for when a spreadsheet opens.
 */
function createSpreadsheetOnOpenTrigger() {
  var id = 'YOUR_SHEET_ID';
  var ss = SpreadsheetApp.openById(id);
  ScriptApp.newTrigger('NAME_OF_FUNCTION_TO_RUN')
      .forSpreadsheet(ss)
      .onOpen()
      .create();
}

reference : link

Then you just have to change id to setup trigger for all sheets you want the code run.

In the code take care to change to get infomration regarding celle and sheet from the event object :

function functionToRunOnEdit() {  
  var sheet = **e.range.getSheet()**;
  var cell = **e.range**;
  var name = sheet.getName();
  var datecell = ss.getRange(cell.getRowIndex(), getDatetimeCol(sheet));
  if (SHEET_NAMES.includes(name) && cell.getColumn() == 1 && !cell.isBlank() && datecell.isBlank()) {      
    datecell.setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm");
  }
};

Reference : link

CodePudding user response：

I have also changed the variable name ss into sheet becausse it is a sheet but not a spreadsheet.

var SHEET_NAMES = ['test', 'test2'];
var DATETIME_HEADER = 'datetime';

function getDatetimeCol(sheet){
  var headers = sheet.getDataRange().getValues().shift();
  var colindex = headers.indexOf(DATETIME_HEADER);
  return colindex 1;
}

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