8

I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B). I have used the following code:

function onEdit(event){


  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 1;
  var tableRange = "A2:B500";

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

      var range = sheet.getRange(tableRange);
      range.sort( { column : columnToSortBy } );
  }
}

However, when I run it, I get the following message: Cannot read property "source" from undefined. (line 2)

Could someone please help me figure out what I am doing wrong?

https://i.sstatic.net/ctZjB.png https://i.sstatic.net/ZuBpT.png

5 Answers 5

1

This piece of code works:

function onEdit(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var editedCell = sh.getActiveRange().getColumnIndex();

  if(editedCell == 2) { 
    var range = sh.getRange("A2:B10");
    range.sort({column: 2});
  }
}

One key difference is the .getColumnIndex. Without it you don't receive an integer !!

See example file I've created: sorting

1
0

Here's a generic script that will autosort based on the 1st column, and assumes a Header row.

To create a script:

In the menu, go to Tools -> Script Editor... In the empty code window, paste the following code, which will run automatically whenever a cell is edited:

 /**
 * Automatically sorts the 1st column (not the header row) Ascending.
 */
function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 1;
  var tableRange = "A2:T99"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );
  }
}
1
0

Preamble

Included because it's a new answer to an old question

The previous answers focused on providing a script that does what the question title describe but at this time they didn't explain why the error occurred didn't analysed if it could work so they didn't answer the right question

Could someone please help me figure out what I am doing wrong?

In consideration of those who find this question when searching for how to autosort, at the end I added a section with links to questions about that.

Short answer

The error was calling the onEdit(e) function from the script editor. The script works fine when it's called from an edit event.

Extended answer

The error

Cannot read property something from undefined. (line something)

on simple functions like onOpen and onEdit and any other function that had specified and argument, like e on function onEdit(e) {...}, occurs when they are ran directly from the script editor because the script editor do not assigns an object to the argument, so in the previous example e is undefined.

When we want to test something like function onEdit(e){...} directly from the script editor, we should add a function that assigns an object to the argument and then call the function to test, something like the following:

function test_onEdit(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // Create the object to be passed as an argument
  var e = {
    range: spreadsheet.getActiveRange(),
    source: spreadsheet
  }
  onEdit(e); // This is the call to the function that we want to test/debug
} 

The above script assumes that we want to test the onEdit function taking the currently active range as the edited range.

Then on the script editor function drop-down selector, select test_onEdit and click the Run button.

On more complex simple or installable onEdit functions to test, it could be necessary to include other properties to emulate correctly the event object. Let say, that we need the the value property of the event object only when the edited range has a size of 1 x 1 (a single cell)

function test_advanced_onEdit(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = spreadsheet.getActiveRange();
  var values = range.getValues();
  var e = {
    range: range,
    source: spreadsheet
  }
 /**
  * The value property is included only when the edited range has a size of 1 x 1 (one cell)
  * The oldValue property is included only when one cell is edited and it before and 
  * after of the edit it is'n a black cell.
  */
 if(values.length === 1 && values[0].length === 1) {
   e.value = values[0][0];
   e.oldValue = 1; // Set the old value as you need it.
 }
  onEdit(e); // This is the call to the function that we want to test/debug
} 

References

Questions about autorsorting

0

This script will sort the column of the edited cell automatically (alphabetically). You only need to change the value of 'headerRows'.

function onEdit(e) {
  //setup spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var editedCell = sheet.getActiveCell();
  
  // allow for headers
  var headerRows = 1;
  var firstRow = 1+headerRows;
  var lastRow = sheet.getLastRow()-1;
  var column = editedCell.getColumn();
  
  var range = sheet.getRange(firstRow,column,lastRow);
  range.sort({ column : column, ascending: true });
}
-1

I used this piece of code:

/**
 * Automatically sorts the 1st column (not the header row) Descending.
 */
function onEdit(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var editedCell = sh.getActiveRange().getColumnIndex();

  if(editedCell == 2) { 
    var range = sh.getRange("A2:B100");
    range.sort({column: 2, ascending: false });
  }
}

and it works!

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.