CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Juan
Juan

Reputation: 43

Combine two almost identical scripts into one

I am trying to copy a script that sends rows that are set to "Closed" from the active sheet to the archive. The thing is my manager wants us to have another active sheet that sends them to the same archive, but I tried copy pasting the code below and just changing the sheet for the copy and it didn't work. When I did that the code only worked for second sheet. Is it possible that this code can only work once at a time? (As you can probably tell I'm super novice in all of this)

    function onEdit(event) {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with yes/no is col 4 or D
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if(s.getName() == "Client Issues and Requests" && r.getColumn() == 1 && r.getValue() == "Closed / Move to Archive") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Archive");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

How can I set it so It looks at the current sheet "Client Issues and Requests" and the second sheet "sheet 2" (Uses the same exact template as the first one)

Thanks.

Upvotes: 0

Views: 38

Answers (2)

Cooper
Cooper

Reputation: 64032

function onEdit(e) {
  const s = e.range.getSheet();
  const shts = ["Client Issues and Requests","other sheet name"]
  if(~shts.indexOf(s.getName()) && e.range.columnStart == 1 && e.value == "Closed / Move to Archive") {
    const tsh = e.source.getSheetByName("Archive");
    const tgt = tsh.getRange(tsh.getLastRow() + 1, 1);
    s.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).moveTo(tgt);
    s.deleteRow(e.range.rowStart);
  }
}

Upvotes: 1

JPV
JPV

Reputation: 27242

Maybe try something like this:

function onEdit(event) {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with yes/no is col 4 or D
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  var sheets = ["Client Issues and Requests", "sheet 2"];
  if(sheets.includes(s.getName()) && r.getColumn() == 1 && r.getValue() == "Closed / Move to Archive") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Archive");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Upvotes: 0

Related Questions