Google Script: InputBox with drop down list spreadsheet (Not FORM)

Question

I have this simple script:

function myFunction() {
  var ssh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = ssh.getActiveSheet();
  var s = ss.getActiveRange();


  var rowIndex = s.getRowIndex();
  var colIndex = s.getColumnIndex();

  // Get the number of columns in the active sheet.
  var colNumber = ss.getLastColumn();
  if (colIndex == 1 && rowIndex != 1) {

  //Get User Name from inputBox
  var name = Browser.inputBox('Owner', 'Enter your Name', Browser.Buttons.OK_CANCEL);
  var r1 = ss.getActiveRange().getRow();
  //Insert the Name in the active row in the column 6
  ss.getRange(r1, 6).setValue(name)

  //Here I have other part of the code but is for copy rows to other sheet.

}
}

In column 6 have active data validation (not permit insert values that are not in the list of items). Example: Charles, Oscar, Paul, Other. If I enter the names all in lower case do not enter. If the value entered is identical as "Charles" the value is entered.

Now, is possible these values may appear as a drop down list in the inputbox? I mean, the user can select from that list and then press Enter or Ok and the value insert into the cell.

Note: This is not a form. This is the spreadsheet.

Thanks,

UPDATED: 01/09/2014

function Historic(e) { //CopyRows
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
    if (s.getName() == 'ISP1'){
  var r = SpreadsheetApp.getActiveRange();

  // Get the row and column of the active cell.
  var rowIndex = r.getRowIndex();
  var colIndex = r.getColumnIndex();

  // Get the number of columns in the active sheet.
  var colNumber = s.getLastColumn();


    // Move row based on criteria in column 1, and if row is not the header.
    if (colIndex == 1 && rowIndex != 1) { // 1 Is the Comment Column

    //Call the Function Menu List
    showNamePicker();

    //Get values for the active row bases on the criteria.
    var status = s.getRange(rowIndex, colIndex).getValue();

// --------------- Copy ROW only when someone modify the Column1 --------------------------

       // Do nothing if criteria value is not actually changed to something else.
    if (s.getName() != 'Historic') { 
      // The target sheet is the one with the same name as the criteria value.
      var targetSheet = ss.getSheetByName('Historic');
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

      //=====Copy the Row from ISP1 to Historic=======
      s.getRange(rowIndex, 1, 1, colNumber).copyTo(target);
}      
}
}
}




function showNamePicker() {
  var app = UiApp.createApplication().setHeight(100).setWidth(180);
  var handler = app.createServerHandler('setName');
  var list = app.createListBox().setName('list').addChangeHandler(handler);
  var names = ['Choose a name here','Charles', 'Oscar', 'Paul', 'Other'];
  for(var n in names){
    list.addItem(names[n]);
  }
  handler.addCallbackElement(list);
  app.add(list);
  SpreadsheetApp.getActive().show(app);
  }


function setName(e){
  var ssh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = ssh.getActiveSheet();
  var r1 = ss.getActiveRange().getRow();
  ss.getRange(r1, 6).setValue(e.parameter.list);
}

Function Historic: If someone modify the Column 1, the script will copy all data in the row that was modify to historic.

Needed:

The script should be insert the name in the column 6 for that row before the script copy the row to the historic.

Issue:

When someone modify the column 1 the user press ENTER and the new active row change because is not the same and the script not insert the name in the row that was modify. For example: I modify the A2 and press ENTER the new row is A3, and the script will insert the information into F3 and not in F2.

I tried to call the function before this line:
//Get values for the active row bases on the criteria.
var status = s.getRange(rowIndex, colIndex).getValue();
but I'm still newbie... I this is very easy but I can't get with the solution.

I will appreciate if you can help me.

EXAMPLE:

Change Cell A2 = ID#12578 The script should be insert the name in the column F2 and then copy all row include the F2 in the sheet called Historic. (The script is not inserting the name into F2 is inserting in F3

Serge insas Serge insas · Accepted Answer · 2014-08-31T13:55:36

You will have to replace you Browser.inputBox with a dialog box that will hold a list box with the desired values.

You can build this dialog box either using UiApp or HTML service.

The problem will be that nothing will prevent to enter a value directly in the cell but it was already the case in your code...

You can store the list values wherever you want, in a hidden column, another sheet, another spreadsheet or in ScriptProperties or in the code itself... that's a matter of choice :-)

Edit : a small example using UiApp :

function showNamePicker() {
  var app = UiApp.createApplication().setHeight(100).setWidth(120);
  var handler = app.createServerHandler('setName');
  var list = app.createListBox().setName('list').addChangeHandler(handler);
  var names = ['Choose a name here','Charles', 'Oscar', 'Paul', 'Other'];
  for(var n in names){
    list.addItem(names[n]);
  }
  handler.addCallbackElement(list);
  app.add(list);
  SpreadsheetApp.getActive().show(app);
  }


function setName(e){
  var ssh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = ssh.getActiveSheet();
  var r1 = ss.getActiveRange().getRow();
  ss.getRange(r1-1, 6).setValue(e.parameter.list);
  return UiApp.getActiveApplication().close();// close the Ui
}

Google Script: InputBox with drop down list spreadsheet (Not FORM)

UPDATED: 01/09/2014

1 Answers