I get a lot of requests for writing various queries, focusing on different information. Although I am always happy to help out, I am also interested in potential of creating spreadsheet templates that people can copy and use themselves. One template I developed recently was build around interpreting different data exports.
Often these export files have a lot of information, which people get lost in. Although they could just delete the columns that they do not want, the feedback is that that process is tedious. Therefore, I created a spreadsheet where users can import a CSV and then select the fields that they want to work with. I did this using a QUERY formula and a series of checkboxes associated with which columns to SELECT and which columns to ORDER BY.
="{QUERY(Sheet1!A1:CJ,"&char(34)&"SELECT "&JOIN(", ",QUERY(A3:D,"SELECT C WHERE D = TRUE"))&" ORDER BY "&JOIN(", ",IFNA(QUERY(A3:E,"SELECT C WHERE E = TRUE"),"Col1"))&char(34)&",1)}"
Users can then copy the query formula and run it in a separate tab.
The current workflow can be summarised as follows:
- Create a copy of the Google Sheet template.
- Import the data spreadsheet (File > Import > Upload > Browse > Open > Insert New Sheet(s) > Import data). This will bring the data into a new tab ‘Sheet1’
- In the SUMMARY tab, refresh the formula in cell A3, then use the checkboxes to select which fields to be displayed and the field(s) to order by.
- Copy the formula created in Cell B2 and paste it in Cell A1 in the data tab, adding ‘=’ at the start to activate it
Although this works fine, I was left wondering if it was possible to instead display the data dynamically, rather than copying and pasting the formula. I imagine that I would need some sort of ‘TO_FORMULA’ type of function, where the text is translated into a formula.
As always, thoughts and comments welcome.
Little Tools for Big Data – Creating a Flexible Spreadsheet Template for Different Purposes by Aaron Davis is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.