Google-apps-script – Script Execution succeeds but reports failure

google-apps-scriptgoogle-apps-script-triggersgoogle-forms

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?

Best Answer

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.

Related Topic