[SOLVED] Google Apps Script sort stopped working "Exception: This operation is not supported on a table header row."

Google Apps Script sort stopped working "Exception: This operation is not supported on a table header row."

I am manipulating a google spreadsheet table using Google Apps Script. For years, the same code worked without a problem, and recently, I started getting "Exception: This operation is not supported on a table header row." after doing .sort() on a Range:

var spreadsheet = SpreadsheetApp.openById(spreadsheet_id);
var sheet = spreadsheet.getSheetByName('Sheet 1');
var range = sheet.getDataRange();
range = range.sort({column: 1, ascending: false});

I did not find any relevant changes mentioned in the Google Apps Script Release Notes. I noticed some changes regarding the table header in the UI, but I am not sure how/why it should influence .sort().

Solution

For years, the same code worked without a problem

Chances are that you have recently formatted the data area as a table. Tables were introduced in May 2024.

A table can be sorted, but its header row cannot be sorted as if it was one of the data rows. This means that you cannot sort the whole table but must exclude the header row, like this:

  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet 1');
  sheet.getRange('A2:ZZZ').sort({ column: 1, ascending: false });