Google Sheets - Data Validation - Forcing Uniqueness from a drop down list

Question

I have column in a google sheet where the user needs to from select a list of items from a drop down, but each value in the column needs to be unique.

I have found several ways of doing this but none are that great.

To start with I tried using this solution - https://infoinspired.com/google-docs/spreadsheet/distinct-values-in-drop-down-list-in-google-sheets/ - which uses the query function to make a dynamically changing column which the data validation points to. It works but the problem is that as soon as you enter a value it becomes invalid and throws an error which doesn't look professional.

I also tried an onEdit function with constantly updates the cell validation with the list of options:

function onEdit(){
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
 var original = sheet.getRange(7,8,20,1).getValues(); /list of cells for data validation to be added
 var option = new Array();
 dvSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Drop Downs");
 option = dvSheet.getRange('G1:G250').getValues();
 option.push(["dummy"])
 var i
 var dv = SpreadsheetApp.newDataValidation();
 for(i=0;i<20;i++){
    option[250][0] = original[i][0];
    dv.setAllowInvalid(false);  
 // dv.setHelpText("Some help text here");
    dv.requireValueInList(option, true);
    sheet.getRange(i+7,8).setDataValidation(dv.build());
  }
}

'Drop Downs'!G1:G250 is the list generated from solution 1. The code takes this list and adds the contents of the cell to that list and then sets the list as the data validation.

Again this works but there's a delay in updating the validation lists, which means if the user is fast enough they can add multiple values that are the same.

Another option I thought of would be to point each cell to a unique column made up from solution 1 with that cell's contents added to the end. But I'm not keen on making columns for each individual cell as there will be a lot (more than the 20 implied in the code).

What would be great is that if google allowed you to combine ranges in the data validation, then I could combine the column from solution 1 with the current cell. But as far as I can tell it doesn't.

Can anyone think of anything else? My feeling is that I've either over complicated it and there's a simple solution - or I'm being too picky.

that as soon as you enter a value it becomes invalid and throws an error which doesn't look professional. why not use onEdit() to remove previous data validation ? — TheMaster

Daniel Manship Daniel Manship · Accepted Answer · 2019-05-03T15:54:34

For anyone else stumbling across this in the future:

Following the TheMaster's suggestion I wrote an onEdit function that removes validation from full cell and adds validation to empty cells:

function onEdit(){
    var spreadsheet = SpreadsheetApp.getActive();
  var i
  var j
   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var original = sheet.getRange(7,8,20,1).getValues();
  var emptyarray = new Array();
  var fullarray = new Array();
  for (i=0;i<20;i++){
    if (original[i][0] === ""){
emptyarray.push(i);
  }
  else {
fullarray.push(i);
  }
}

      for each(i in fullarray){
        j=Number(i)+7
        sheet.getRange('H'+j).clearDataValidations()
      }
        for each(i in emptyarray){
          j=Number(i)+7
          sheet.getRange('\'Sheet 1\'!H'+j).setDataValidation(SpreadsheetApp.newDataValidation()
          .setAllowInvalid(true)
          .requireValueInRange(spreadsheet.getRange('\'Drop Downs\'!$G:$G'), true)
          .build());
      }
}

Google Sheets - Data Validation - Forcing Uniqueness from a drop down list

1 Answers