How to do drop-down data validation in a prompt dialogue using google script?

Question

I'm trying to use google script to create a prompt dialogue for the user with a drop-down list.

Here's where I'm at so far:

function addProject() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
      'Select project from drop-down',
      '(use "Project Index" sheet to add a project)',
      ui.ButtonSet.OK);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    ui.alert('You selected ' + text + '.');
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
    ui.alert('I didn\'t get your project.');
  }
}

A button in my sheet activates this function. All this does is create a prompt that you can enter a string into. But I need to use data validation to create a drop-down list that references a range elsewhere in my sheet so that the user can enter anything they want.

Can someone help me get a drop-down list in a prompt dialogue?

Thanks!

Answer 1

Unfortunately you can not use ui.prompt in this way. Your best bet is to use a Custom Dialog in which you can put a dropdown.

edit: For a partial code answer, check out the answer here, How to create a drop down list in (App Script) Spreadsheet Input Box?

Answer 2

function NewItem() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication().setTitle('My Application');
  var panel = app.createVerticalPanel();
  var lb = app.createListBox(true).setId('myId').setName('myLbName');
var sh = SpreadsheetApp.getUi();
var response = sh.alert("Confirm!", "This will delete the selected item in all sheets, do you want to continue?", sh.ButtonSet.OK_CANCEL)

if (response == sh.Button.OK)
    {

  // List of categories for user to select
  lb.setVisibleItemCount(9);
  lb.addItem('Category 1');
  lb.addItem('Category 2');
  lb.addItem('Category 3');
  lb.addItem('Category 4');


  panel.add(lb);
  var button = app.createButton('OK');
  var handler = app.createServerClickHandler('click').addCallbackElement(panel);
  button.addClickHandler(handler);
  panel.add(button);
  app.add(panel);
  doc.show(app);
  }
    if (response == sh.Button.CANCEL)
    {
      sh.alert("Canceled", "The action is canceled", sh.ButtonSet.OK)
    }

}


function click(eventInfo) {
  var app = UiApp.getActiveApplication();
  // get category that user chose
  var value = eventInfo.parameter.myLbName;
  var doc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("items_with_category");
  var RevenueItem = Browser.inputBox("Copy paste the name of the revenue item from the QB report:");
  var COSItem = Browser.inputBox("Copy paste the name of the COS item from the QB report:");
  var Client = Browser.inputBox("Copy past the client from QB report:");
  var RevAcct = Browser.inputBox("Revenue Account Code (eg. 4001):");  
  var COSAcct = Browser.inputBox("Revenue Account Code (eg. 5001):");  
  var lastRow = doc.getLastRow()+1;
  //doc.getRange(lastRow,1).setValue(value);

  doc.getRange(lastRow,1).setValue(COSItem);
  doc.getRange(lastRow,2).setValue(RevenueItem);
  doc.getRange(lastRow,3).setValue(Client);
  doc.getRange(lastRow,4).setValue(value);
}