Dynamic data validation in Google Sheets

I have a Google Sheet with a column for company names. To ensure data quality, I prefer to select the company name in each row from a dropdown list so that the company is always spelled the same way. I could use the standard data validation in Google Sheet, but my list of companies contains over 35,000 names, and adding all of them into a data validation rule slows down Google Sheets considerably.

The solution I came up with is to type a few characters of the company name into the cell in the column and have a Google Apps Script dynamically set a data validation rule with only the company names that contain these characters:

For this to work, I have to hit the enter key after typing the characters since Google Apps Script executes server-side. The approach is a bit clunky and I have to wait about three seconds for the validation to be set, but it does work. Here's how I set it up.

I have a Google Sheet with two sheets: one named 'Edit' where I type my information, and the other named 'Companies', which has 35,000 companies listed in column A.

Inside a Google Apps Script, I create an onEdit(e) function that will be triggered automatically every time the spreadsheet is edited.

  function onEdit(e) {
    // triggered automatically by Google Apps Script
  }

The first thing I do is to get all the company names from the spreadsheet. I use a map to convert the nested array I get from the sheet into a flat array of names. I lower-case the names so that later I can ignore case when comparing the names to the characters entered in the cell.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allCompanies = ss
    .getSheetByName('Companies')
    .getDataRange()
    .getValues()
    .map((row) => row[0].toLowerCase());

I make sure that the user is editing the company name column. If the edit is done anywhere else then I exit the function.

  const inDroppableCell =
  ss.getActiveSheet().getName() === 'Edit' &&
  e.range.getColumn() === 1 &&
  e.range.getRow() !== 1;

  if (!inDroppableCell) {
    return;
  }

The next step is to get the entered characters. Again, I check if the change contains some characters, and if not then I exit the function:

  const userInput = e.range.getValue().toString().toLowerCase();

  if (userInput.toString().trim().length === 0) {
    return;
  }

I now filter the list of companies to those containing the user's input. I also limit the list to 500 names since Google Sheet doesn't support validation lists that are longer than 500 values:

  const matchedCompanies = allCompanies.filter(
    (row) => row.includes(userInput)
  );
  
  matchedCompanies.splice(500, matchedCompanies.length);

The last step is to set the validation rule on the edited cell:

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(matchedCompanies)
    .build();
  e.range.setDataValidation(rule);

The entire code is available in my repo.

I hope this helps you out.

– Ben