Google Sheets: Problem with function onEdit in all sheets except excluded-CodePudding

The aim of the code is to move edited row (edit in column K) at the bottom of the table (below last row). I want it to work in all sheets (with yet unknown names) except sheets which are constant (sheetsToExclude). The code makes action as I want, but in all sheets, neglecting the exclusion. I am very basic to this, I found these codes using Google and just made them work via trials and errors, so I imagine there are more issues to correct.

function onEdit(e) {

  var allSheetTabs,i,L,thisSheet,thisSheetName,sheetsToExclude,value;

  sheetsToExclude = ['II Półrocze','I Półrocze','Kierowcy','Szablon','Instrukcja'];

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  allSheetTabs = ss.getSheets();

  L = allSheetTabs.length;

  for (i=0;i<L;i  ) {
    thisSheet = allSheetTabs[i];
    thisSheetName = thisSheet.getName();

    //continue to loop if this sheet is one to exclude
    if (sheetsToExclude.indexOf(thisSheetName) !== -1) {continue;}


  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();
  if(col == 11  && row > 1 && !as.getRange(row,col).getValue()=='') {  
    const row_new = as.getRange(row,1,1,col);
    row_new.copyTo(as.getRange(as.getLastRow() 1,1,1,col));
    as.deleteRow(row);
  }
  }}

CodePudding user response：

Does this work for you?

function onEdit(e) {

  if (!e.hasOwnProperty(`value`)) return

  if (e.range.getColumn() === 11 && e.range.getRow() > 1 && e.value.length) {

    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
    const excludeSheets = ['II Półrocze','I Półrocze','Kierowcy','Szablon','Instrukcja']

    const newRow = e.source.getActiveSheet()
                           .getRange(e.range.getRow(), 1, 1, 11)

    spreadsheet.getSheets()
               .filter(sheet => !excludeSheets.includes(sheet.getName()))
               .forEach(sheet => {

                 const destination = spreadsheet.getSheetByName(sheet.getName())

                 newRow.copyTo(destination.getRange(destination.getLastRow() 1, 1, 1, 11))
                
               })

    spreadsheet.getActiveSheet()
               .deleteRow(e.range.getRow())

  }

}

Commented:

function onEdit(e) {

  // If edit does not contain a new value, exit.
  if (!e.hasOwnProperty(`value`)) return

  // If edit was made in Column 11 beyond Row 1, and value exists...
  if (e.range.getColumn() === 11 && e.range.getRow() > 1 && e.value.length) {

    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
    const excludeSheets = ['II Półrocze','I Półrocze','Kierowcy','Szablon','Instrukcja']

    // Get row range...
    const newRow = e.source.getActiveSheet()
                           .getRange(e.range.getRow(), 1, 1, 11)

    // Get all sheets...
    spreadsheet.getSheets()
               // But, keep only sheets that aren't in the exclusion array.
               .filter(sheet => !excludeSheets.includes(sheet.getName()))
               // For each of these sheets...
               .forEach(sheet => {
                 // Get the Sheet
                 const destination = spreadsheet.getSheetByName(sheet.getName())
                 // And "append" new row.
                 newRow.copyTo(destination.getRange(destination.getLastRow() 1, 1, 1, 11))
                
               })

    // Once complete, delete the row from the active sheet.
    spreadsheet.getActiveSheet()
               .deleteRow(e.range.getRow())

  }

}