I followed youtube instruction and copied this script
JavaScript
x
17
17
1
var SHEET_NAME = 'Sheet1';
2
var DATETIME_HEADER = '입력일시';
3
function getDatetimeCol(){
4
var headers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME).getDataRange().getValues().shift();
5
var colindex = headers.indexOf(DATETIME_HEADER);
6
return colindex+1;
7
}
8
function onEdit(e) {
9
var ss = SpreadsheetApp.getActiveSheet();
10
var cell = ss.getActiveCell();
11
var datecell = ss.getRange(cell.getRowIndex(), getDatetimeCol());
12
if (ss.getName() == SHEET_NAME && cell.getColumn() == 1 && !cell.isBlank() && datecell.isBlank()) {
13
datecell.setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm:ss");
14
}
15
};
16
17
I want to apply this script to all subsheet in the file.
so I tried to add more sheet name like
JavaScript
1
2
1
var SHEET_NAME = 'Sheet1'; => var SHEET_NAME = ['Sheet1','Sheet2','Sheet3',]
2
or
JavaScript
1
4
1
var SHEET_NAME = 'Sheet1';
2
var SHEET_NAME = 'Sheet2';
3
var SHEET_NAME = 'Sheet3';
4
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?
Advertisement
Answer
I have changed your code slightly according to the task at hand:
JavaScript
1
14
14
1
SHEET_NAMES = ['Sheet1','Sheet2','Sheet3'];
2
DATETIME_HEADER = '입력일시';
3
4
function onEdit(e) {
5
let range = e.range,
6
sheet = range.getSheet();
7
8
if (SHEET_NAMES.includes(sheet.getName()) && range.rowStart > 1 && range.columnStart == 1 && !e.value == '') {
9
let colindex = sheet.getDataRange().getValues().shift().indexOf(DATETIME_HEADER)+1,
10
datecell = sheet.getRange(range.rowStart,colindex);
11
if (datecell.isBlank()) datecell.setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm:ss");
12
}
13
};
14
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