0

I have a Google sheets that has the responses from a Google Form. I want the responses to be sorted according to contents of three columns (9, 12 and 13) when the sheet is opened. I'm using this script, but it is failing. Can anyone suggest why?

function autoSort(sheet) {
  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: 8, ascending: true}, {column: 11, ascending: true}, {column: 12, ascending: true}]);
//  range.sort( {
//   column: SORT_COLUMN_INDEX,
//    ascending: ASCENDING
//  } );
}

// function onOpen(event) {
//  var sheet = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
// autoSort(event.source);
//  }
function onOpen(e) {
  var sheet = e.source.getActiveSheet(); // get the default sheet when the spreadsheet opens
  autoSort(sheet);
}
4
  • The sort columns specified in the question are different from the columns shown in the code. Sep 5, 2023 at 17:02
  • The first column is 0 as far as I know.
    – To Do
    Sep 6, 2023 at 8:21
  • I've been using this numbering (0 = first column for years). Now it seems something has changed. All I had to do for the script to work is to add 1 to the column number. Strange, but the script is working.
    – To Do
    Sep 6, 2023 at 10:49
  • 1
    Sheet.sort() and other SpreadsheetApp methods use 1-indexed column and row numbers where column A is column 1, column B is 2 and so on. Sep 6, 2023 at 17:36

1 Answer 1

0

The code in the question errors out with Exception: Cell reference out of range because range.offset(NUMBER_OF_HEADER_ROWS, 0) will always get a range that is partially outside the data range that actually exists.

When you are sorting a whole sheet, it usually makes sense to use Sheet.sort() instead of Range.sort(). To sort by multiple columns, you can take advantage of the fact that Sheet.sort() is a stable sort, so you can sort by the most significant column last, like this:

/**
* Simple trigger that runs each time the user opens
* the spreadsheet.
*
* @param {Object} e The onOpen() event object.
*/
function onOpen(e) {
  autoSort();
}


/**
* Sorts a sheet by certain columns.
* If there are no frozen rows, adds one frozen row.
*/
function autoSort(sheet = SpreadsheetApp.getActiveSheet()) {
  if (!sheet.getFrozenRows()) sheet.setFrozenRows(1);
  [
    { column: 13, ascending: true },
    { column: 12, ascending: true },
    { column: 9, ascending: true },
  ].map(spec => sheet.sort(spec.column, spec.ascending));
}
3
  • What about the header row? Won't that be sorted too if I use Sheet.sort()?
    – To Do
    Sep 6, 2023 at 8:11
  • I realised that the script you propose freezes the first row. I tried the script, but it doesn't seem to be working. Tried changing the column numbers to 12, 11, and 8 with the same result.
    – To Do
    Sep 6, 2023 at 8:24
  • Sheet.sort() and other SpreadsheetApp methods use 1-indexed column and row numbers where column A is column 1, column B is 2 and so on. Sep 6, 2023 at 17:36

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.