How to watch multiple tabs using onEdit function

Question

I came across this script that moves rows from one tab to another tab depending on the values from a specific column. It's working perfectly but this can only work on a single tab. Is it possible for this script to run on multiple tabs and move records to a specific tab only?

For example, here is the list of tabs to be watched:

AAA
BBB
CCC
DDD
EEE
etc...

When the value in Column N (contains =IF formula that determines the value depending on the condition set) is changed to Completed, the script will automatically copy the row to the DONE sheet and then delete the row.

I am not sure if onEdit() will work.

Note: All of the tabs are identical.

Here is the script I found here in the forum:

function onEdit() {

  var app = SpreadsheetApp;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetNameToWatch = ss.getSheetByName("Current")
  var columnNumberToWatch = 8;
  var valueToWatch = "DONE";
  var sheetNameToMoveTheRowTo = "OLD";

  //var ss = SpreadsheetApp.getActiveSpreadsheet();//this has already been declared
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();

  if (sheet.getName() == "Current" && range.getColumn() == 8 && range
  .getValue() == "DONE") {
    var targetSheet = ss.getSheetByName("OLD");
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(
      targetRange);
    sheet.deleteRow(range.getRow());
  }

}

Welcome to Stack Overflow. onEdit is triggered when any sheet (tab) is edited. Please show what you tried to adapt the script to your needs. — Rubén
I am actually using the script now that whole script except it only works on a single tab. How should I modify the script so that it will work on all my tabs except for the DONE tab? — Ante
Also, I forgot to tell you that the watched column has a formula that automatically change to "Completed" when certain conditions are met. I am not sure if onEdit() is the correct function. — Ante
Please edit the question to add to it all the relevant details. — Rubén

Jacques-Guzel Heron Jacques-Guzel Heron · Accepted Answer · 2021-02-15T15:41:04

I get that you want to move some rows from different sheets if they meet a criteria, and you need it every time that the spreadsheet is modified. If my comprehension of your question is correct, then you can easily modify the code to put the event object into action by capturing the modified sheet with Range.getSheet(). At that point you already have a variable with the sheet, so the code can proceed to search for the Completed keyword and copy the row to the DONE sheet if necessary.

How to watch multiple tabs using onEdit function

1 Answers