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?
Advertisement
Answer
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