I want to apply script to multiple subsheet in one Sheet

user_17237959 0
Viewed 72

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?

google-apps-script google-sheets javascript
1 Answers

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

user_156111530
Related