Google sheets Sort by Header in View Mode

Question

I wanna create a sheet with multiple columns of data... I would like to put sort options on the header columns.. so if one of the column is named PLAYERS NAME and then another Header is named AT BATS and then another header is named HITS, I want the viewer to sort any column he wants just by clicking the header either in ascending order or descending order. So if he wants to see who has the most hits, the the viewer should click on Hits and have it go from ascending or descending order. If he then wants to click on Players name it should sort the same way for that column and keep its data in the correct row. How can I achieve this?

edit: This must work in View Mode

Answer 1

Use filter views to let view-only users sort the sheet. This feature lets multiple simultaneous users sort and filter the sheet without disturbing each other.

For additional ease of use, insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.

The links look like this:

=hyperlink("#gid=2048818001&fvid=336145647", "Show all")

...where gid=... identifies the sheet and fvid=... identifies the filter view. You can get these identifiers from the browser address bar when a filter view is active.

Answer 2

In case you cannot find a solution using formulas I will give a solution using the following code:

function onEdit(e) {  
  const as = e.source.getActiveSheet();
  const row = e.range.getRow();
  const col = e.range.getColumn();
  const lc = as.getLastColumn();
  const lr = as.getLastRow();
  const rg = as.getRange(2, 1, lr, lc);
  let asc = true;
  let val = as.getRange(row, col).getValue();
  if (as.getName() == 'Sort' && row == 1 && val.match(/↓/) != null) {
    asc = false;
    rg.sort({ column: col, ascending: asc });
  } else if (as.getName() == 'Sort' && row == 1 && val.match(/↑/) != null) {
    asc = true;
    rg.sort({ column: col, ascending: asc });
  }
}

Configure data validation in the header's cells from the lists "PLAYERS ↓, PLAYERS ↑", "AT BATS ↓, AT BATS ↑", "HITS ↓, HITS ↑".