Making a bunch of dropdowns from Google Sheets data

Posted on by Tom Woodward

For some ongoing work with the AI Persona bot, I needed to create a drop down that would let you choose a country and get the cultural dimension scores. Turns out there were 120 something countries, each with six possible scores. Luckily, they were given to me in a Google Spreadsheet.

Make the data

In the old days, I’d have used the sheet’s built in JSON but Google sucks so that’s not an easy path any longer. Instead, I just wrote a little function to make the stuff into javascript friendly data like I did in the old days.

="{'"&A2&"':"&"'"&B2&","&C2&","&D2&","&E2&","&F2&","&G2&"'},"

I could then just cut/paste them into my javascript code like you see below. I cut out the middle so scrolling wouldn’t be a hassle. Since the [] brackets hold the {}, that’s an array of stuff and the country name is the key to the values. Maybe I should have made it more like {‘country’:’Albania’, ‘scores’:’90,27,80,70,56,15′} but this way works.

const countryValues = [
        {'Albania':'90,27,80,70,56,15'},
        {'Algeria':'80,29,35,70,25,32'},
        {'Angola':'83,18,20,60,15,83'},
        {'United Arab Emirates':'74,36,52,66,22,22'},
        {'United Kingdom':'35,76,66,35,60,69'},
        {'Unites States':'40,60,62,46,50,68'},
        {'Uruguay':'61,60,38,98,28,53'},
        {'Venezuela':'81,26,73,76,16,100'},
        {'Vietnam':'70,30,40,30,47,35'},
        {'Zambia':'60,35,40,50,30,42'}
    ];

Make the dropdown

First I’ve got my HTML select stuff with the id of countries. I’ve got one null value in there. Everything will stack up just below that element.

<select name="countries" id="countries">
  <option value="null"></option>

</select>

The following code loops through all the country data and creates and option that displays the country name and writes the data to a data attribute called value. I love data attributes. I’ve got a partially built demo in CodePen but I was having some account issues last week so I didn’t do as much prototyping there.

//MAKE HTML DROP DOWN OF COUNTRIES
function buildCountryList(countryValues){
    const countryDropDown = document.querySelector('#countries');
     countryValues.forEach((country)=>{
       const name = Object.keys(country)[0];//get key/country name from data
       const measures = country[name];//get scores
       countryPicker.insertAdjacentHTML('beforeend',`<option data-measures="${measures}" value="${name}">${name}</value>`);//make the html
     })
}

Setting the sliders with the data

Now we want our profile sliders to be set based on the data associated with the countries.

This part got a little messy. I found out that some of the countries wouldn’t have scores in all the columns and that input ranges can’t have null values. So I needed a way to flag those sliders as not being part of the persona generation. I opted to add a data attribute called ignore to those sliders. I left the slider alone but put an X in the numerical display area to indicate it was not active. I also set it so that if the slider was set manually later then the data attribute would be set to false and it’d be back in the mix for persona generation.

I also struggled a bit because I was not paying close enough attention. I kept getting the select element (which holds options) and think it would return the selected option. That’s clearly not how it works. I had to take it a step further . . . countryPicker.querySelector(‘:checked’).getAttribute(‘data-measures’). That code is getting the select element, then the checked option, then the data attribute named data-measures. That gets me the numbers I want. Little things like that can drive you crazy. I was doing all sorts of stuff because I thought it was a async issue or something. As usual, the mantra of slowing down, going back to basics and slowly working your way up the chain of variables with lots of console.log verification is the path. It always works.


//do stuff when an item is chosen
countryPicker.addEventListener("change", (event) => {
  const countryName = event.target.value;//country name
  const countryNumbers = countryPicker.querySelector(':checked').getAttribute('data-measures');//this was a bit tricky
  const sliders = document.querySelectorAll('.slider'); 
   sliders.forEach((slider, index) => { 
    if(index != 0){//skip the first slider because it's happiness 
     const sliderValue =  countryNumbers.split(',')[index-1];  //adjust the index because we skipped 1
     countryDataSetter(sliderValue, slider);
     finalPromptCreation(slider);   
    }
     
   })
});


//set the sliders
function countryDataSetter(value, slider){
    let sliderValue = value;
    const sliderId = slider.id;
    const displayDiv = document.querySelector(`#${sliderId}-value`)
    if (value == 'null'){
        slider.dataset.ignore = 'true';//add an ignore data attribute
        displayDiv.innerHTML = 'x';//if it has no value put the number to x
    } else {
        displayDiv.innerHTML = sliderValue;
        slider.value = value
    }

    //setSliderStorage(sliderId, sliderValue);
}

Leave a Reply