Search code examples
javascriptgoogle-apps-scriptweb-applications

How to get a list of Multiple Selected Items and output their Values as an Array using Javascript

I am teaching myself how to code. My first project is a webApp using Apps Script. I've managed to complete most of it on my own except for one thing... I can't seem to output the items from a Multiple Select box into a comma separated array of values.

Currently, I've managed to return a single value when only one of the list items is selected. Reference: example 1

But if I choose more than one, nothing happens. Reference: example 2

Essentially, what I'm trying to accomplish is this:

If... Bill and Mary] ... are selected from a list, their corresponding ages are output as... {33,44}. Or if... [Bill, Mary, and Joe] ...are selected, the result is {33,44,21}.

The data is pulled from a single sheet with 2 columns ('Staff' & 'Ages'). The list is also dynamically infinite.

In case my request is confusing, this resource clearly demonstrates what I'm trying to achieve but I just can't seem to crack it.

Hopefully this advanced community can help me solve this challenge.

Now, here's the Code.gs and Javascript for reference.

(NOTE: I'm looking for a Javascript solution only please. Also, pardon my formatting of this virgin post.)

Code.gs

function doGet(e){
  var ss = SpreadsheetApp.openById(ssID);
  var ws = ss.getSheetByName("Values");
  var posts = ws.getRange(1,1,ws.getRange("A1").getDataRegion().getLastRow(),1).getValues();
  var postListArray = posts.map(function(r){ return '<option>' + r[0] + '</option>'; }).join('');
  var tmp = HtmlService.createTemplateFromFile("page");
    tmp.posts = postListArray;
      return tmp.evaluate();
}

function getAges(uStaff){
  var ss = SpreadsheetApp.openById(ssID);
  var ws = ss.getSheetByName("Values");
  var data = ws.getRange(1, 1, ws.getLastRow(), 2).getValues();
  var staffList = data.map(function(r){ return r[0]; });
  var agesList = data.map(function(r){ return r[1]; }); 
  var position = staffList.indexOf(uStaff);  
      if(position > -1){
          return agesList[position];
        } else {
          return 'Invalid';
      }  
}

Javascript

document.getElementById("staff").addEventListener("change",getRecs);

function getRecs(){
  var uStaff = document.getElementById("staff").value;
    google.script.run.withSuccessHandler(updateAges).getAges(uStaff);    
}

function updateAges(ages){
  document.getElementById("age").value = ages;  
}

HTML

<div class="row">
    <div class="input-field col s6">
      <select id="staff" multiple>
        <?!= posts; ?>
      </select>
      <label for="staff" style="font-size: 20px;">Staff</label>
    </div>
    <div class="input-field col s6">
      <input type="text" id="age" class="validate" disabled>
    </div>
</div>
Solution

  • Issue:

    • HTMLSelectElement.value only returns the first selected value.

    Solution:

    • Use HTMLSelectElement.selectedOptions to get all selected HTMLOptionElement value

    Snippet#1:

    var uStaff = [...(document.getElementById("staff").selectedOptions)]
                        .map(e=>e.value);//uStaff is a array of staffs. eg ['Bill', 'Mary']

    You should then modify your getAges server function to work with a array argument instead of a single value. Alternatively, You should pass the whole data as 2D array to client side, use Map to get the age:

    Snippet#2:

    Server side:

    function getData(){
  var ss = SpreadsheetApp.openById(ssID);
  var ws = ss.getSheetByName("Values");
  var data = ws.getRange(1, 1, ws.getLastRow(), 2).getValues();
  return data;
}

    Client side: 

    document.getElementById("staff").addEventListener("change",getRecs);
var map = null;
function getRecs(){
  if(!map) {//call server if data is not retrieved previously
    google.script.run.withSuccessHandler(updateAges).getData()
  } else {updateAges()};    
}
function updateAges(arr){
  if(!map) map = new Map(arr);//map of [[staffs,ages]]
  let uStaff = document.getElementById("staff").selectedOptions;
  let ages = [...uStaff].map(staff=>map.get(staff.value)).join();
  document.getElementById("age").value = ages;  
}

    References: