When I type "product" in a cell. The cell next to it would show a list of available variations via a drop-down menu?

Something like this? This is the data sheet

DATA SHEET:

When I type "product" in a cell. The cell next to it would show a list of available variations via a drop-down menu?

Something like this? This is the data sheet

DATA SHEET:

And I was hoping to do something like this When I type "ROBOT" in a cell. The cell next to it would limit my choices/data validation or filter the choices/data validation to only the "Variation" under Robot.

NEW SHEET:

Would it be possible?

• google-sheets-api
• google-workspace

• Please provide a sample sheet and a minimal reproducible example. Also, show us what you have tried so far. – 4thAnd1 Commented Mar 18 at 6:41
• Please make use of the code block fences (```) so your example table is at least legible. – Stelio Kontos Commented Mar 18 at 21:36
• See table syntax. (I'd edit the question, but a pending edit is already queued) – Stelio Kontos Commented Mar 18 at 21:49

1 Answer 1

Create a drop down of variations for a product

You can achieve this using Google Apps Script, Inserting a value from DATA SHEET to NEW SHEET and creating a drop-down in the next cell in any cell.

Script Used

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var targetSheetName = "NEW SHEET";
  var dataSheetName = "DATA SHEET";

  if (sheet.getName() !== targetSheetName) {
    return;
  }

  var editedCell = e.range;
  var product = editedCell.getValue().toString().toLowerCase();
  var nextColumn = editedCell.getColumn() + 1;

  if (editedCell.getRow() === 1) {
    return;
  }

  var dataSheet = e.source.getSheetByName(dataSheetName);
  var dataRange = dataSheet.getDataRange().getValues();
  var variations = [];

  for (var i = 1; i < dataRange.length; i++) {
    if (dataRange[i][0].toString().toLowerCase() === product) {
      variations.push(dataRange[i][1]);
    }
  }

  var validationRange = sheet.getRange(editedCell.getRow(), nextColumn);

  if (variations.length > 0) {
    var rule = SpreadsheetApp.newDataValidation()
      .requireValueInList(variations, true)
      .build();
    validationRange.setDataValidation(rule);
  } else {
    validationRange.setDataValidation(null);
  }
}

Sample Output

References

• Data Validation

• Event Object