Copy Row Google Sheets

Question

This Script is not working on below file

[https://docs.google.com/spreadsheets/d/1SJUYN1wayOVzBGWRXNyCDmvoxyanFii6Qy5EBa1pjLo/edit?usp=sharing][1]

I use this script to move row to another sheet when value in column 21 =Delivered

i'm not a programer I try to understand how the script work and embedding code in other code

so if you can help me to rewrite this script to be faster and working better

Thanks in advance

/* 
 **** Move a row onEdit determined by a specific Cell value***
 */

// Names of sheets

var destinationSheet = "Collate"

/* col: the column to watch,  
 * changeVal: what value you want to change,
 * del: do you want to delete after the change?
 */
var check = {
  "col":17,
  "changeVal": "DELIVERED",
  "del": true ,
  };

/* What you want to paste into the other sheet.
 * start: start column
 * cols: how many columns you want to copy
 */
var pasteRange = {
  "start": 1, 
  "cols": 35
  };
 

function onEdit(e) {
  var sheets = ['FOOD', 'PET', 'KIND', 'Campbell', 'Other Clients']
for (var i = 0; i < sheets.length; i++) {
  var sheetName = sheets[i]
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (sheet != null) {
  
  if(sheet.getName() === sheets){
    //Get active cell
    var cell = sheet.getActiveCell();
    var cellCol = cell.getColumn();
    var cellRow = cell.getRow(); 
    
    if(cellCol === check.col){
      if(cell.getValue() === check.changeVal){
        
        //Select the range you want to export
        var exportRange = sheet.getRange(cellRow,pasteRange.start,1,pasteRange.cols);
        
        //Select the past destination
        var pasteDestination = ss.getSheetByName(destinationSheet);
        var pasteEmptyBottomRow = pasteDestination.getLastRow() + 1;
        
        //Copy the row to the new destination
        exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,1),
                           SpreadsheetApp.CopyPasteType.PASTE_VALUES);
        
        //If delete is true delete after copying
        if(check.del){
          sheet.deleteRow(cellRow);
        };
      };
    };
  };
};
};
};

Answer 1

You can get information from the event object [1] like the edited range or the bound Spreadsheet. On the range use getSheet() function [2] and compare the sheet name with the ones in the array using indexOf() [3]. The following code moves the row if the value "DELIVERED" is put in the Q (17th) column for any of the sheets in the sheets array:

/* 
 **** Move a row onEdit determined by a specific Cell value***
 */

// Names of sheets

var destinationSheet = "Collate"

/* col: the column to watch,  
 * changeVal: what value you want to change,
 * del: do you want to delete after the change?
 */
var check = {
  "col":17,
  "changeVal": "DELIVERED",
  "del": true ,
  };

/* What you want to paste into the other sheet.
 * start: start column
 * cols: how many columns you want to copy
 */
var pasteRange = {
  "start": 1, 
  "cols": 35
  };


function onEdit(e) {
  var sheets = ['FOOD', 'PET', 'KIND', 'Campbell', 'Other Clients'];
  var sheet =  e.range.getSheet();
  var sheetName = sheet.getSheetName();

  if(sheets.indexOf(sheetName) != -1){
    //Get active cell
    var cell = e.range;
    var cellCol = cell.getColumn();
    var cellRow = cell.getRow(); 

    if(cellCol === check.col){
      if(cell.getValue() === check.changeVal){

        //Select the range you want to export
        var exportRange = sheet.getRange(cellRow,pasteRange.start,1,pasteRange.cols);

        //Select the past destination
        var pasteDestination = e.source.getSheetByName(destinationSheet);
        var pasteEmptyBottomRow = pasteDestination.getLastRow() + 1;

        //Copy the row to the new destination
        exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,1),
                           SpreadsheetApp.CopyPasteType.PASTE_VALUES);

        //If delete is true delete after copying
        if(check.del){
          var sheet = e.range.getSheet();
          sheet.deleteRow(cellRow);
        };
      };
    };
  };
};

In your example Spreadsheet there are 2 projects bound. Also, in each project there are 2 scripts with the same variables which is causing problems. Be sure to only have one onEdit() function and one script with the code I put above when you test it.

[1] https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events

[2] https://developers.google.com/apps-script/reference/spreadsheet/range#getsheet

[3] https://www.w3schools.com/jsref/jsref_indexof_array.asp