CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
DRTraveler
DRTraveler

Reputation: 21

How to create a dropdown prompt in Google App Script

I would like to have a ui dialog box consisting of all available sheets in a workbook, have the user select the sheet they want to operate on and the value returned to the script for processing.

I collect the sheet names into an Array and would like to present them in a dropdown list box for selection. Can someone point me to the docs for how to do this?

Upvotes: 2

Views: 5581

Answers (1)

TheWizEd
TheWizEd

Reputation: 8598

Here is an example as a script bound to a spreadsheet. For a web app it would be similar.

In Code.gs

function test() {
  try {
    var output = HtmlService.createHtmlOutputFromFile('HTML_Sidebar');
    SpreadsheetApp.getUi().showSidebar(output);
  }
  catch(err) {
    Logger.log(err);
  }
}

function getSheets() {
  try {
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var names = [];
    for( var i=0; i<sheets.length; i++ ) {
      names.push(sheets[i].getName());
    }
    return names;
  }
  catch(err) {
    Logger.log(err);
  }
}

function changeSheet(name) {
  try {
    var spread = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spread.getSheetByName(name);
    SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(sheet);
  }
  catch(err) {
    Logger.log(err);
  }
}

In HTML_Sidebar

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <select id="mySelect" onchange="selectChange(this)">
    </select>
    <script>
      function selectChange(select) {
        google.script.run.changeSheet(select.value);
      }
      function sheetNames(names) {
        var select = document.getElementById("mySelect");
        for( var i=0; i<names.length; i++ ) {
          var option = document.createElement("option");
          option.text = names[i];
          select.add(option);
        }
      }
      (function () { google.script.run.withSuccessHandler(sheetNames).getSheets(); }());
    </script>
  </body>
</html>

Upvotes: 4

Related Questions