0

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

7
  • It can be done like this. Configure data validation in the header line from the lists "PLAYERS ↓, PLAYERS ↑", "AT BATS ↓, AT BATS ↑", "HITS ↓, HITS ↑". Activate the OnEdit trigger in the script. It will determine if an arrow is set up or down and start sorting on the column where the change occurred. Jul 11 '21 at 0:39
  • @BorisBaublys With the greatest respect, it would probably be more useful for the questioner, AND others who follow later, to submit this as an answer rather than a comment. On a tangent, you mention an onEdit script (and the OP doesn't mention a script), so it would be helpful if you were to flesh out how the script determine[s] if an arrow is set up or down and start sorting on the column where the change occurred.
    – Tedinoz
    Jul 11 '21 at 0:50
  • Welcome, Joe. May I clarify something? Did you try Sort (Data, Sort Range) &/or Filter View (Data, Filter views)? If so, did you reject them because they require the user to make further selections which might make the process more complicated than you wish? So, your goal is simply that the user should click the header and the data will automatically re-sort. Is that righ?. Would you please add a brief description of your research efforts How do I ask a good question? and also describe your own efforts to solve your problem.
    – Tedinoz
    Jul 11 '21 at 1:11
  • @Tedinoz Sorry, I didn't notice the Formula tag when I wrote my comment about the script. Jul 11 '21 at 4:27
  • @Tedinoz Try it Jul 11 '21 at 4:39
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.

3
  • Yes, it looks like using filters are the only way to solve it. So I think it will be helpful to clarify what gid & vid are in your example. Jul 11 '21 at 14:15
  • 1
    OK, good idea, done. Jul 11 '21 at 15:14
  • Excellent thank you. It would be great if we could read about these links in Google Help. Do you have a link to this section of the help system? Suddenly there you can set the sorting parameters (I dream). Jul 11 '21 at 16:11
0

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 ↑".

7
  • 1
    The question specifies that the spreadsheet is view-only to the users. Scripts can only run when the user has edit access to the spreadsheet. Jul 11 '21 at 4:52
  • The answer mentions that the script would require authorization. The onEdit(e) function is a simple trigger that does not require authorization. Jul 11 '21 at 4:52
  • Oh sorry, I'm new to this section. Data validation can be used in view-only? Jul 11 '21 at 4:56
  • You are right, corrected. Jul 11 '21 at 5:00
  • The Data > Data validation feature is not related to this question. It requires edit access to the spreadsheet and does nothing when the spreadsheet is view-only. Jul 11 '21 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.