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

[Tomasz Szatkowski]

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. Also it produces an error " TypeError: Cannot read property 'hasOwnProperty' of undefined".  I appriciate your help in advance. 

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())

  }

}

[Stephen Barker]

Seems to be working fine, you can't execute this via the Editor you have to make an edit to your sheet then in the right menu, click on "Executions" or "e" will be null (See Attached).

As for your filter you can fiddle with it but I would just remove it and have it bail at the top if it hits a sheet I want to exclude because you'll have to filter on the delete too or your function will still delete on all sheets:

function onEdit(e) {

   //exclue sheet names

const excludeSheets = ['II Półrocze','I Półrocze','Kierowcy','Szablon','Instrukcja']

   //exit if on list

  if(excludeSheets.includes(SpreadsheetApp.getActiveSheet().getSheetName())) return

  // 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()))

[Tomasz Szatkowski]

Hey, that worked like a charm. It performs as I want. Thank you for help and explanations.

[Tomasz Szatkowski]

Hey Stephen,

I am getting back to you regarding this code. In general the exclusion works, I mean it does operate on all sheets except the ones names 3rd line. It is all great when I run it on my test spread sheet. However, when I run it on actual spread sheet, which is shared between 5 people, the exclusion does not work, and it creates a lot of chaos. Any idea why is that?

[Barker, Stephen]

Off the top of my head maybe the owner/creator of the sheet runs it maybe? Most of the scripts I use I create the sheet then the script then share it out. 

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/aEedZwGZi7o/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b9a12218-d94c-4b86-8021-d7268fc4a571n%40googlegroups.com.

--

---

Stephen Barker

ITS PROGRAMMER/ANALYST

ITS

Clay County District Schools

| phone 904-336-9579| ext 6-9630

| web oneclay.net | email stephen...@myoneclay.net

          

This Email Is Not An Acceptable Offer And Does Not Evidence Any Intention By The Sender To Enter Into A Contract.