Google Sheets – How to Sort Query Results Manually

google sheetsgoogle-sheets-querysorting

My google sheet is importing data from another tab just for sorting purpose, however, when the sort is done manually (through Google Sheet menus), results are unpredictable.

The sheet is populated through:

=query(tabname!A4:G2000)

Best Answer

you can't use Data > Sort sheet by...

your sheet needs to be manually sorted by creating a filtered view by selecting your column and then: Data > Filter views... > Create new filter view > Sort A - Z

a downside is that it's a view so it won't stay like that after closing your spreadsheet.
also, note that formula needs to be outside or inside of the filtered area (eg. filter can't be created right on the formula)

EXAMPLE:

another approach would be to use:

=QUERY() in =SORT() like: =SORT(QUERY(tabname!A4:G2000); 1; 1)
=QUERY(tabname!A4:G2000; "select * order by A desc, B asc"; 1)

Short Answer

Use arrays in Google Sheets and SORT() function.

Example

To combine the non continuous ranges A1:D5, H1:K100, P1:S15, the following formula will return a 4 X 120 array sorted by the first column in ascending order.

=SORT({A1:D5;H1:K100;P1:S15},1,TRUE)

The first parameter also could be any function that returns an array like IMPORTFEED.

Remarks

The URLs provided in example are not working because the YouTube Data API (v2) has been officially deprecated as of March 4, 2014.
IMPORTJSON() was retired of the new Google Sheets.

References

Google-sheets – Auto sort data but by sheet not range

The sort method belongs to the class Range, not to the class Sheet. Simply put, it always operates on a range. If you want that range to include all the data in a sheet, use the method getDataRange of the class Sheet.

var range = sh0.getDataRange();
range.sort({column: 2, ascending: false});

For completeness, I present a customizable script for automatic sorting.

  function onEdit() {
    var sheetToSort = "Sheet Name"; // enter name of sheet 
    var columnEdited = 1;           // sort when this column is edited
    var columnSort = 2;             // sort by this column
    var sortAscending = false;      // true or false here

    var sheet = SpreadsheetApp.getActiveSheet();
    var editedCell = sheet.getActiveRange().getColumnIndex();
    if (sheet.getSheetName() == sheetToSort && editedCell == columnEdited) { 
      var range = sheet.getDataRange();
      range.sort({column: columnSort, ascending: sortAscending});
    }
  }