function onEdit(e) {
const sheetNames = ["sheet1","sheet2", "sheet3"];
const sheet = e.source.getActiveSheet();
if (sheetNames.includes(sheet.getName()) && e.range.columnStart === 17) {
const offset = e.range.offset(0,3)
if (!offset.getValue()) {
offset.setValue(new Date()).setNumberFormat("YY/MM/dd HH:mm:ss")
}
}
}
and
function onEdit(e) {
const sheetNames = ["sheet A","sheet B", "sheet C"];
const sheet = e.source.getActiveSheet();
if (sheetNames.includes(sheet.getName()) && e.range.columnStart === 1) {
const offset = e.range.offset(0,19)
if (!offset.getValue()) {
offset.setValue(new Date()).setNumberFormat("YY/MM/dd HH:mm:ss")
}
}
}
I apply those two codes to the two different spreadsheet, but now I need to combine them as one. And I don't know about this anything. So I need you guys help me fixing it.
there's 3 differences. It's the place I want to put sign , place get consquence and which sheets to apply them.
So I want sheet 1,2,3 and sheet A,B,C work independently but still work in the same spreadsheet.
Please let me know how can I do this. Thanks.
CodePudding user response:
In your scripts, how about merging them into one script as follows?
Modified script:
function onEdit(e) {
const sheetNames1 = ["sheet1", "sheet2", "sheet3"];
const column1 = 17;
const offset1 = [0, 3];
const sheetNames2 = ["sheet A", "sheet B", "sheet C"];
const column2 = 1;
const offset2 = [0, 19];
const obj = sheetNames1.reduce((o, s) => (o[s] = { column: column1, offset: offset1 }, o), {});
sheetNames2.forEach(s => obj[s] = { column: column2, offset: offset2 });
const sheet = e.source.getActiveSheet();
const sheetName = sheet.getName();
if (obj[sheetName] && e.range.columnStart === obj[sheetName].column) {
const offset = e.range.offset(...obj[sheetName].offset);
if (!offset.getValue()) {
offset.setValue(new Date()).setNumberFormat("YY/MM/dd HH:mm:ss");
}
}
}
- In this modification, each parameter for your 1st and 2nd scripts is put in an object. And using the created object, the script is run. By this, I thought that the scalability of the condition might be simple.