I followed youtube instruction and copied this script
var SHEET_NAME = 'Sheet1';
var DATETIME_HEADER = '입력일시';
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:ss");
}
};
I want to apply this script to all subsheet in the file.
so I tried to add more sheet name like
var SHEET_NAME = 'Sheet1'; => var SHEET_NAME = ['Sheet1','Sheet2','Sheet3',]
or
var SHEET_NAME = 'Sheet1';
var SHEET_NAME = 'Sheet2';
var SHEET_NAME = 'Sheet3';
and they didn' work.
I don't have any, even rudimentary knowledge to this area, could you teach me how can I apply this script on whole subsheet, please?
I have changed your code slightly according to the task at hand:
SHEET_NAMES = ['Sheet1','Sheet2','Sheet3'];
DATETIME_HEADER = '입력일시';
function onEdit(e) {
let range = e.range,
sheet = range.getSheet();
if (SHEET_NAMES.includes(sheet.getName()) && range.rowStart > 1 && range.columnStart == 1 && !e.value == '') {
let colindex = sheet.getDataRange().getValues().shift().indexOf(DATETIME_HEADER)+1,
datecell = sheet.getRange(range.rowStart,colindex);
if (datecell.isBlank()) datecell.setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm:ss");
}
};
I don't think it's a good idea to run getDatetimeCol() every time you make a change in the table - it's better to do it only when the changes occur in the right sheets and cells