Use GSheet Interface to sort with data generated with ArrayFormula

0
votes

I have a google spreadsheet (example) that has

  • sources sheets (tasks, projects)
  • a computed sheet (project-enhanced) that uses formulas to aggregate data from several sheets (compute the sum of estimations of all tasks of a project)

I want my users to be able to sort easily the aggregated data, using the google interface (Menu data > Sort range). This is just an example so there's not a lof of data/columns, bu in the actual spreadsheet we used there are many many columns, the users may need to sort on any column.

I've seen multiple SO questions that deal with problems like sorting results computed from an arrayFormula, etc. but is there a solution out there, that is compatible with using filter from the GSheet interface ? So that someone with completely no knowledge of gsheet formulas cas still use the sheet ?

It's fine if there is a solution that involves creating a different sheet, or using different types of formula (query instead of arrayFormula, etc) that produce the same result.

1
google-sheetsgoogle-sheets-formulaarray-formulas
You'd better use Google Data Studio for things like this. Here's an example using your data. Just copy it and look inside. - kishkin
I do not think you'd be able to sort data generated by a formula using standard UI tools in a sensible way. You could write some code using Google App Script, make a custom menu for sorting... Or just aggregate your data using script instead of formula, so there will be static values which will be sortable. - kishkin

1 Answers

1
votes

Considerations

Using the User Interface you can't sort the output of an ARRAYFORMULA. You will lose the corresponding cell reference in result of the sorting operation.

Using Query

Using the QUERY formula you can easily sort by any column using the order by operator.

In your case:

=QUERY('project-enhanced'!A1:C4, "select * order by C")

Now, since you don't want your users to write any formulas nor code you could build a simple User Interface to tweak your query formula:

=QUERY('project-enhanced'!A1:C4, "select * order by "&B1)

You can then make the B1 cell a user input field to specify the column letter he wants to sort the data by.

SORT BY:    C   
Project ID  Start date  Sum of estimations
2           11/14/2020  5
1           11/13/2020  30
3           11/16/2020  90

Reference

QUERY formula