I'm writing a program in which one of the functionalities is to create a dropdown with some values (as an autocomplete). However, I would like to be able to, if I give a value that is not in the dropdown, add the element to the dropdown (instead of showing an error message).

I've spent a lot of time trying to figure out how I could do it but I'm completely stuck. I think that the best solution is to use a data Validation which works great except for the feature of adding new data.

I've also tried including an option labeled other in the dropdown. When I click on it, I remove the data validation from the cell, write the value and then update the data validation of all cells including that value. However, I think that that's not the best idea as it's not very user friendly.

Other possible solution would be to dynamically create the dropdown elements each time I write a letter (as I'm getting the info from a database, I could get the matching items from the db and then write them to the dropdown). However, I haven't found any trigger to do it every time I write a letter.

SUMMARY: a dropdown in all cells of the first column with the elements from a database. If the element I write is not in the dropdown, add it to the database and to the dropdown of all A cells.

If clarification or more information is needed, just ask me. I would be really thankful if someone could help me!

CodePudding user response：

When the options of a data validation should be dynamic, IMHO the best is to use a range as the source for the data validations options because the impact of this of the spreadsheet performance is smaller than updating the data validation option list by using script.

1. If you don't know how bit the options could become set your data validations to use a whole column, i.e. Options!A:A
2. On Options!A:A add the data from the database
3. Use an on edit trigger to add to the first empty cell of Options!A:A the new values

CodePudding user response：

I had to do exactly this in my project, so it was basically a copy-paste. log() is a custom function I made so don't worry about that. Obviously in my implementation, the dropdown contained a list of "flags" but name them what you wish. I did not include the code to create a button

/**
 * Function that handles button press to create a new flag
 */
function createFlagInteract(){
  var ui = SpreadsheetApp.getUi();

  //Prompt user for flag name
  var flagName = ui.prompt("Please enter a name for your new flag", ui.ButtonSet.OK).getResponseText();
  log("User's flag name input: \"" flagName "\"");

  //cancel if entry box is blank
  if(isBlank(flagName)||isEmpty(flagName)){
    ui.alert("No name was entered, please close this dialogue and try again.");
    log("User did not enter a flag name");
    return;
  }

  /**
   * This code block is modified from my implementation. I had a global array with all flags. This is a jerry-rigged simple implementation of getting all values.
   */
  //get an array of existing flags
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FlagsList");
  const emptyRow = sheet.getLastRow() 1;
  //returns n x 1 array
  var values = sheet.getRange(1,1,emptyRow).getValues();
  //flips array to have length of n
  values = values[0].map((col, i) => values.map(row => row[i]))[0];

  //Check if flag name already exists
  for(var i in values){
    if(values[i]==flagName){
      ui.alert("A flag with this name already exists, please close this dialogue and try again.");
      log("User entered name of flag that already exists.");
      return;
    }
  }

  //Add flag to spreadsheet
  createFlag(flagName);
}

/**
 * Adds flag to spreadsheet and updates dropdown
 * @param {String} flagName Name of flag
 */
function createFlag(flagName){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FlagsList");
  const emptyRow = sheet.getLastRow() 1;
  var range = sheet.getRange(emptyRow,1);

  range.setValue(flagName);

  //returns n x 1 array
  var values = sheet.getRange(1,1,emptyRow).getValues();
  //flips array to have length of n
  values = values[0].map((col, i) => values.map(row => row[i]))[0];

  updateDropdown(values);
}

/**
 * Updates dropdown
 */
function updateDropdown(values){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("User Input");
  var range = sheet.getRange("A:A");

  var rule = SpreadsheetApp.newDataValidation().requireValueInList(values,true).build();

  range.setDataValidation(rule);
}