GAS – Create a Unique list from Cell based csv type Values – two ways

In my endeavours to move away from what might be a costly exercise in providing tables and charts for my users at work, I am delving heavily into google visualisations as a replacement. One feature I have yet to replicate is the “csvFilter”, whereby a column of cells in a table contains more than one value, separated by commas:

csvlist

The filter does all the hard work of creating a drop down list to select from with unique values. This post sets out to resolve this, in order that a CategoryFilter can be loaded with unique values to allow items to be selected from the column. I said two ways, so one will be by selecting a distinct range, the second will be to select the entire data range, then grab the column with the csv values.

1. Create Unique Values from csv lists in column

What are we doing?
We get the values from the range, then we have to dive in deep to the array of arrays to access each item of each inner array, then push this out to a new array after removing commas which separate the inner parts of the inner array. Then we find all the unique values and return them in a new array, sorting alphabetically and removing any empty array elements.

2. Call all data values from the entire range, then parse the column containing the csv values and create a unique list

What are we doing?

After fetching all the data, we use a helper function (getCol) to create an array of just the column we need. This done we only have to dive down one level (instead of two as in the previous example, to access the inner parts of the array to push to a new one. After that it is the same as the first. Please note the two “column.shift()” entries in the helper function, this is to remove the two header rows from the original data.

Next job is to figure out how to add this new array to a CategoryFilter and then see if it works!!

One thought on “GAS – Create a Unique list from Cell based csv type Values – two ways

  1. Pingback: Google Visualization – CategoryFilter with csvFilter | METRIC RAT

Leave a Reply

Your email address will not be published. Required fields are marked *