Create an in-cell drop-down (Google Sheet Data Validation) from a CSV list with Apps Script

Question

I have a spreadsheet that has multiple editors. I get a list of all users separated by comma using:

var users = ss.getEditors().toString();

Rather than having a list of users separated by comma, which looks ugly (I have 100+ users), I would like to set up a drop down menu to view users. To do that, I have written this:

function Dropdown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var TestSheetName = "Test"
  var TestSheet = ss.getSheetByName(TestSheetName);
  var users = ss.getEditors().toString();
  TestSheet.getRange(1, 1).setDataValidation(SpreadsheetApp.newDataValidation()
    .setAllowInvalid(true)
    .requireValueInList([users], true)
    .build()
  );
}

The drop down menu created shows the list with commas. What is bizarre is when I right click on the cell and go to "Data Validation," I can see that the users are put in correctly. When I then click 'OK' to close the menu, the dropdown corrects itself and I have the list of users correctly formatted.

Am I doing something wrong with my code? Or is this a bug?

Appreciate your insights.

tehhowch tehhowch · Accepted Answer · 2018-04-26T23:38:09

The expected input format for requireValueInList is an array of Strings. As you mention, ss.getEditors().toString() creates a String of comma-separated emails. You then wrap this single String object into an array, and pass it to the data validation builder. Effectively, this is akin to:

.requireValueInList(["[email protected],[email protected]"])

which is decidedly different than

.requireValueInList(["[email protected]", "[email protected]"])

The solution is to not call .toString() on the array of User objects that is returned by ss.getEditors(). Instead, convert the User[] to String[] by calling its method getEmail() on each user, and then pass this array of Strings to the Data Validation builder:

var emails = ss.getEditors().map(function (user) { return user.getEmail(); });
/*...*/
.requireValueInList(emails, true)
/*...*/

should be sufficient.

When you manually open the Data Validation UI, it splits the input single string as many different strings, which is why when you click OK, the drop-down populates as you originally intended.

Create an in-cell drop-down (Google Sheet Data Validation) from a CSV list with Apps Script

1 Answers