Populate HTML dropdown menu from column in google spreadsheet

2
votes

I´m new to .gs so this should not be difficult.

I have a Google Spreadsheet with values in a column (Lets say column A). I have created a custom menu using .gs where the user will select an option.

Clicking one of the options (new component) a popup appears with a dropdown menu where user should select from the values.

CustomMenu.gs

 function onOpen() {
 var ui = SpreadsheetApp.getUi();
 // Or DocumentApp or FormApp.
 ui.createMenu('bq Library Menu')
  .addItem('Add new component', 'newComponent')
  .addSeparator()
  .addSubMenu(ui.createMenu('Modify existing component')
      .addItem('Remove component', 'removeComponent'))
  .addToUi();
 }

 function newComponent() {

 var html = HtmlService.createHtmlOutputFromFile('NewComponentForm')
     .setWidth(400)
     .setHeight(300);
 SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
     .showModalDialog(html, 'New Component Form');
 }

NewComponentForm.html

<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
  <h2>Clickable Dropdown</h2>
  <p>Select manufacturer from the list</p>

  <div class="dropdown">
     <button onclick="loadManufacturers()" class="dropbtn">Dropdown</button>
     <div id="myDropdown" class="dropdown-content"></div>
  </div>

</body>

I want the dropdown menu to show all elements in column A from the spreadsheet. I´ve tried several options but have not obtained the required result.

So, how do I need to proceed to implement the dropdown populating process?

1
javascripthtmlgoogle-apps-scriptdrop-down-menugoogle-sheets

1 Answers

2
votes
  • You want to create a select box and put the values from the column "A" of Spreadsheet.
  • When a value from the select box is selected, you want to retrieve the value.

If my understanding is correct, how about this modification? I think that there are several answers for your situation, so please think of this as just one of them.

The flow of this modified script is as follows.

  1. Open a dialog.
  2. Click "Dropdown" button.
  3. By google.script.run, run the function of getValuesFromSpreadsheet() at Google Apps Script side.
  4. At getValuesFromSpreadsheet(), the values are retrieved from Spreadsheet and returned to Javascript.
  5. At withSuccessHandler(), the returned values are retrieved. Using the values, a select box is created.
  6. When a value of the select box is selected, selected() is run and retrieved the selected value.

When above flow is reflected, the modified script is as follows.

GAS:

Please add the following Google Apps Script to CustomMenu.gs.

function getValuesFromSpreadsheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  return sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues(); // Retrieve values and send to Javascript
}
  • In this sample script, the values are retrieved from "Sheet1" of the active Spreadsheet. The values are in the column "A".
    • If you want to retrieved from other range and sheet, please modify this.

HTML:

Please add the following Javascript to NewComponentForm.html.

<script>
  function loadManufacturers() {
    google.script.run.withSuccessHandler(function(ar) {
      let select = document.createElement("select");
      select.id = "select1";
      select.setAttribute("onchange", "selected()");
      document.getElementById("myDropdown").appendChild(select);
      ar.forEach(function(e, i) {
        let option = document.createElement("option");
        option.value = i;
        option.text = e;
        document.getElementById("select1").appendChild(option);
      });
    }).getValuesFromSpreadsheet();
  };

  function selected() {
    const value = document.getElementById("select1").value;
    console.log(value);
  }
</script>
  • Use google.script.run for retrieving the values from Spreadsheet.
  • Using withSuccessHandler, retrieve the values from Google Apps Script.
  • When "Dropdown" button is clicked, a select box is created.
  • The selected value can be seen at console.

Note:

  • This is a simple sample script. So please modify it for your situation.

References:

If I misunderstood your question, I'm sorry.