Google spreadsheets script onEdit

Question

I have a very simple question.

I´ve found this script that works perfect, and i don´t know programming. This script searches for any date value in an specific column inside the sheet called Needed and when it detects it then it moves the that row to another sheet called Acquired. I just need to do the same with other source sheets. For example: I have 4 sheets called Needed1, Needed2, Needed3 and Needed4. All of them have to move the the rows where a date is entered to the same Acquired sheet. I hope someone could help me.

function onEdit() {
  // moves a row from a sheet to another sheet when a date is entered in a column
  // adjust the following variables to fit your needs
  // version 1.0, written by --Hyde, 24 September 2014
  // see https://productforums.google.com/d/topic/docs/wtzlc_ludTw/discussion

  var sheetToWatch = "Needed";
  var columnNumberToWatch = /* column G */ 4; // column A = 1, B = 2, etc.
  var sheetToMoveTheRowTo = "Acquired";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();

  if ( sheet.getName() == sheetToWatch && cell.getColumn() == columnNumberToWatch && cell.getValue() instanceof Date) {
    var targetSheet = ss.getSheetByName(sheetToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(cell.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(cell.getRow());
  }
}

Max Makhrov Max Makhrov · Accepted Answer · 2016-05-07T14:01:51

Short answer:

function onEdit() {

  var sheetsToWatch = ["Needed1", "Needed2", "Needed3", "Needed4"];

  for (var i = 0; i < sheetsToWatch.length; i++) {
    var sheetToWatch = sheetsToWatch[i]; 

    // all good code goes here

  }     
}

And the whole code is:

function onEdit() {
  // moves a row from a sheet to another sheet when a date is entered in a column
  // adjust the following variables to fit your needs
  // version 1.0, written by --Hyde, 24 September 2014
  // see https://productforums.google.com/d/topic/docs/wtzlc_ludTw/discussion

  var sheetsToWatch = ["Needed1", "Needed2", "Needed3", "Needed4"];

  for (var i = 0; i < sheetsToWatch.length; i++) {
    var sheetToWatch = sheetsToWatch[i];
    var columnNumberToWatch = /* column G */ 4; // column A = 1, B = 2, etc.
    var sheetToMoveTheRowTo = "Acquired";

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = SpreadsheetApp.getActiveSheet();
    var cell = sheet.getActiveCell();

    if ( sheet.getName() == sheetToWatch && cell.getColumn() == columnNumberToWatch && cell.getValue() instanceof Date) {
      var targetSheet = ss.getSheetByName(sheetToMoveTheRowTo);
      var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      sheet.getRange(cell.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
      sheet.deleteRow(cell.getRow());
    }

  }

}

You may add new sheet names into array:

var sheetsToWatch = ["Needed1", "Needed2", "Needed3", "Needed4", "New Name"];

Google spreadsheets script onEdit

1 Answers