How can I combine those two code as one in spreadsheet apps script?

user_17237959 0
Viewed 25
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.

google-apps-script google-sheets
1 Answers

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.
user_71086530
Related