Autocomplete drop-down options from spreadsheet data

of late, i've been a tad bit fascinated by all the ways you could design a front-end dashboard; specifically, using material design (from google) and a framework build on it  - materialize.

context

while working on a specific project, i stumbled upon the need to create a way for folks to enter their choice(s), but of course in a way that could easily be quantified i.e. the input for the same/similar option had to be uniform across the board and for 'em to be able to even choose the right options (say, avoid typos).
this led me to explore the autocomplete module of materializecss and the two formats with which we could achieve this viz. the chips & select components.

architecture

the easy part was to gather data from spreadsheet, which anyway happens as part of an array structure; however, the chips and select components of materialize framework accepts two very different form of values (albeit, they're the object type) as input for dynamic options.

the overall structure can be broken down into 3 essentials parts -

1. fetch data from spreadsheet: if it's a single column, use the default methodology (as shown in the code below) and if specific columns are to be chosen for input, then create an array structure to accept values from only those columns.
2. return them as an array to the functions being invoked form the client side (i.e. the <script> inside the html file)
3. process the array on the client side to the required format

so far, this should seem like the standard approach; however, while there is a specific "option" that has been defined for chips - autocompleteOptions - along with the object structure that it accepts, it is still not clear to me on how is the same to be processed for the select component, even though it too has a parameter for dropdownOptions (i'm assuming this is to accommodate dynamic values, which in turn needs to be passed as an object but for clarity, i've opened a github request here).

in the meantime, thanks to a colleague from work (and of course - this stackoverflow solution), we bask in the workaround with jquery!

codebase

you can access the entire script on my github repository here.

the code.gs part of it is pretty simple:

var ID = 'Enter-Your-SpreadSheetID-Here';
var ss = SpreadsheetApp.openById(ID);
var sheetName = 'Sheet1';
var activeSheet = ss.getSheetByName(sheetName);

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('Index').setTitle('Index');
}

function getOptions() {
  var rangeValues = activeSheet.getRange(2,1,activeSheet.getLastRow()-1,1).getValues();
  return rangeValues;
}

a huge chunk of code rather goes behind accepting the right format of values to initialise dynamic options for the individual components.

here's for the chips module:

<script>
    document.addEventListener('DOMContentLoaded', function() {
        var chipElems = document.querySelectorAll('.chips');
        var autoCompElements = google.script.run.withSuccessHandler(function(value) {
            var dynamicOptions = {};
            value.forEach(function(data) {
                dynamicOptions[data[0]] = null
            });
            var chipOptions = {
                autocompleteOptions: {
                    data: dynamicOptions,
                    limit: Infinity,
                    minLength: 1
                },
                placeholder: 'Options',
                secondaryPlaceholder: 'Add more'
            }
            M.Chips.init(chipElems, chipOptions);
        }).getOptions();
    });
</script>

and this is the select module:

<script>
    document.addEventListener('DOMContentLoaded', function() {
        var optionsDropDown = google.script.run.withSuccessHandler(function(value) {
            var optionsJSON = value;
            $.each(optionsJSON, function(index, data) {
                $('#optionsSelect').append($('<option/>', {
                    value: data,
                    text: data
                }));
            });
            var elems = document.querySelectorAll('select');
            var instances = M.FormSelect.init(elems);
        }).getOptions();
    });
</script>

demo

here's the dummy data being used and the actual usage can be viewed here.

credits

• cover image by louise chang as seen on dribbble.