GAS: Populating a dropdown list from Spreadsheet data

Question

I'm trying to populate a dropdown list on my web app based on information in a Google Spreadsheet sheet

Here is my code:

Function that gets the data from Spreadsheet:

function getBotIDDropDownArray(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("(GetBots)");
  var wslr = ws.getLastRow();
  return ws.getRange(1, 2, wslr - 1, 1).getValues();
}

On the Javascript code section in my web app I have the following:

First, make sure the sidebar loads:

document.addEventListener("DOMContentLoaded", afterSidebarLoads);

After that, call the function that will populate the dropdown

function afterSidebarLoads(){
      google.script.run.withFailureHandler(function(e){console.log("failure handler",e)}).withSuccessHandler(afterBotIDDropDownArrayReturned).getBotIDDropDownArray();
}
//Validating afterBotIDDropDownArrayReturned

function afterBotIDDropDownArrayReturned(arrayOfArrays){
      var item = document.getElementById("bot-id");
      arrayOfArrays.forEach(function(r){
        var option = document.createElement("option");
        option.textContent = r[0];
        item.appendChild(option);
      });
 };

Html Code:

<div class="form-group">
  <label for="bot-id">Bot ID</label>
    <select class="form-control" id="bot-id">
    </select>
</div>

First Issue:

After checking the Developer console first I noticed the following error:

According to this question: There was an error during the transport or processing of this request. Error code = 10, Path = /wardeninit

The solution was to enclose my function afterBotIDDropDownArrayReturned into () and call for its execution like this:

(function afterBotIDDropDownArrayReturned(arrayOfArrays){
  var item = document.getElementById("bot-id");
  arrayOfArrays.forEach(function(r){
    var option = document.createElement("option");
    option.textContent = r[0];
    item.appendChild(option);
  });
})();

Second Issue:

However, after the previous fix now I get this error:

ArrayOfArrays undefined.

Which is odd since getBotIDDropDownArray its actually catching the information.

Also, withFailureHandler() was getting called before.. but without any description on it.

https://stackoverflow.com/a/45828559/7215091 – Cooper Oct 15 '20 at 21:47 — Cooper, Oct 15 '20 at 21:47

score 1 · Answer 1 · answered Oct 15 '20 at 22:10

I followed this in order to gain more information about the type of error.

Response and Error handling in Google Apps Scripts. doPost? withFailureHandler()?

Updated code:

function afterSidebarLoads(){
  google.script.run.withFailureHandler(e => {
        console.error(e.message);
    }).withSuccessHandler(afterBotIDDropDownArrayReturned).getBotIDDropDownArray();
};

After that I got the following error message:

authorization is required to perform that action

However, I didn't get any notification that I needed to update the permissions given.

Anyway, that solved the problem.

GAS: Populating a dropdown list from Spreadsheet data

1 Answers1