Script is completing but nothing is happening in my Sheets

[Stuart Cooper]

I have this script for sorting ranges in two tables across two separate sheets in the same Spreadsheet, so Sheet1 or Sheet2 depending on which is active - the execution is completing just fine, but nothing is changing in my sheets onEdit, can anyone suggest why this might be?

function onEdit(event){

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var firstSheet = spreadsheet.getSheets()[0];

var secondSheet = spreadsheet.getSheets()[1];

var activeSheetName = spreadsheet.getActiveSheet().getName();

var columnToSortBy = 26;

var tableRange = "A3:Z13";

if (activeSheetName == firstSheet.getName()) {

var editedCell = firstSheet.getActiveCell();

if (editedCell.getColumn() == columnToSortBy) {

var range = firstSheet.getRange(tableRange);

range.sort( { column : columnToSortBy, ascending: true } )

}

} else {

var editedCell = secondSheet.getActiveCell();

if (editedCell.getColumn() == columnToSortBy) {

var range = secondSheet.getRange(tableRange);

range.sort( { column : columnToSortBy, ascending: true } )

}

}

}

[Nerio Villalobos]

Hay un error en el código que puede estar causando el problema. La variable "activeSheetName" se define pero nunca se usa en el código.

Además, en el segundo "if" del código, la variable "editedCell" se define como "celda editada", pero debería ser "editedCell" como se definió anteriormente.

Aquí hay una versión corregida del código:

function onEdit(event) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var firstSheet = spreadsheet.getSheets()[0];
  var secondSheet = spreadsheet.getSheets()[1];

  var activeSheetName = spreadsheet.getActiveSheet().getName();

  var columnToSortBy = 26;
  var tableRange = "A3:Z13";

  if (activeSheetName == firstSheet.getName()) {
    var editedCell = firstSheet.getActiveCell();
    if (editedCell.getColumn() == columnToSortBy) {
      var range = firstSheet.getRange(tableRange);
      range.sort({column: columnToSortBy, ascending: true});
    }
  } else {
    var editedCell = secondSheet.getActiveCell();
    if (editedCell.getColumn() == columnToSortBy) {
      var range = secondSheet.getRange(tableRange);

      range.sort({column: columnToSortBy, ascending: true});
    }
  }
}

Asegúrate de guardar los cambios en el editor de scripts y de habilitar el gatillo "onEdit" en el proyecto de Google Apps Script para que se active cuando se realicen ediciones en las hojas de cálculo.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, 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/71d1fce1-0929-4a6c-984c-a11d041b7119n%40googlegroups.com.

--

__________________________

Nerio Enrique Villalobos Morillo

+54 9 11 68851063 

Buenos Aires, Argentina

[cwl...@gmail.com]

You might consider adding some explicit instructions...  

function onEdit(event){

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var firstSheet = spreadsheet.getSheets()[0];

  var secondSheet = spreadsheet.getSheets()[1];

  var activeSheet = spreadsheet.getActiveSheet()

  var activeSheetName = activeSheet.getName();

  var columnToSortBy = 26;

  var tableRange = "A3:Z13";

  if (activeSheetName == firstSheet.getName()) {

    var editedCell = firstSheet.getActiveCell();

    if (editedCell.getColumn() == columnToSortBy) {    

      firstSheet.getRange(tableRange).activate()

      activeSheet.getActiveRange().sort({ column : columnToSortBy, ascending: true })

    //  range.sort( { column : columnToSortBy, ascending: true } ) (delete)

    }

  } else {

    var editedCell = secondSheet.getActiveCell();

    if (editedCell.getColumn() == columnToSortBy) { 

      secondSheet.getRange(tableRange).activate();

      activeSheet.getActiveRange().sort({ column : columnToSortBy, ascending: true })

   //   range.sort( { column : columnToSortBy, ascending: true } ) (delete)

    }

  }

}

[Stuart Cooper]

As the attachments show, the trigger is setup and the script executes just fine, but nothing actually happens in the sheet. 

Is there some other config that I am perhaps misisng?

[cbmserv...@gmail.com]

This is a simple trigger. So no need to setup the trigger manually as you have done. Just naming the function onEdit is enough.

 

Add in some UI to validate that it is doing something.

 

Add the following:

 

var ui = SpreadsheetApp.getUi();

 

ui.alert(“Trigger invoked”);

 

put these two lines right after the function declaration.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, 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/bc58612c-da09-4d6e-b18e-70ae5f1efff9n%40googlegroups.com.