0

I'm running this script on a Google Form responses sheet:

function autoSort(sheet) {
  var SORT_COLUMN_INDEX = 10;
  var ASCENDING = true;
  var NUMBER_OF_HEADER_ROWS = 1;
  var range = sheet.getDataRange();
  if (NUMBER_OF_HEADER_ROWS > 0) {
    range = range.offset(NUMBER_OF_HEADER_ROWS, 0);
  }
  range.sort( {
    column: SORT_COLUMN_INDEX,
    ascending: ASCENDING
  } );
}

function onOpen(event) {
  autoSort(event.source);
  }

I set a trigger to run the script whenever the sheet is opened. The script is functioning properly, but I'm getting emails telling me it failed. In the Execution list the error reported is:

TypeError: sheet.getDataRange is not a function
    at autoSort(Autosort:5:21)

I used the same script in another very similar form and I didn't have these errors. Can anyone illuminate me what is wrong? Why is a failure being reported while the script works as requested?

  • In onOpen(), event.source is a reference to the Spreadsheet doc ref. However autosort() assumes it is a reference to the sheet. Consider defining the sheet name as a variable and using getSheetByName(name) doc ref. – Tedinoz Aug 12 at 9:29
  • So, should I add a line to the onOpen function that says var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet name"); before the autoSort(event.source); line? – To Do Aug 12 at 10:02
  • It occurred to me that you might want to use autoSort(sheet) elsewhere in the project. So, rather than use getSheetByName() (which would be a reasonable approach), I have posted an answer that uses the event object: source plus getActiveSheet() to "define" the sheet. – Tedinoz Aug 13 at 3:09
1

Your script fails because the event object source refers to the Spreadsheet and not the sheet.

There are many ways to resolve this particular problem, please consider this answer as just one solution.


function onOpen(e) {
  var sheet = e.source.getActiveSheet(); // get the default sheet when the spreadsheet opens
  autoSort(sheet);
}

function autoSort(sheet) {
  var SORT_COLUMN_INDEX = 10;
  var ASCENDING = true;
  var NUMBER_OF_HEADER_ROWS = 1;
  var range = sheet.getDataRange();
  if (NUMBER_OF_HEADER_ROWS > 0) {
    range = range.offset(NUMBER_OF_HEADER_ROWS, 0); // rowOffset, columnOffset
  }
  range.sort( {
    column: SORT_COLUMN_INDEX,
    ascending: ASCENDING
  } );
  return;
}

Trivia:

range.offset(NUMBER_OF_HEADER_ROWS, 0); is effective in removing the header row from the sort range. However, the offset method maintains the same number of rows in the data range, and the last/bottom row is blank. An option would be to use offset(rowOffset, columnOffset, numRows) so that the number of rows can be adjusted.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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