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