Multiple Dependent Dropdown Lists on Every Line In Google Sheets with Google Apps Script

Got a request? Ask down here ↓

hey, can you check the script? It is not working for me for some reason. Thanks

will this method still work if I duplicate the "products" sheet? I need to be able to make multiple inventory sheets, aka every time I make a new batch of inventory.

Needing some assistance... I have edited the script as follows so that my data starts in X2 but it is not updating. Can you assit? Also, not sure if it is going to work but my final column should be a link to a photo. Is this doable?
function onEdit(e) {
const sheet = e.source.getActiveSheet();
const editColumn = e.range.columnStart;
const editRow = e.range.rowStart;
const dataRangeAddress = 'X2:AC';

if (sheet.getName() === 'Data') {
const productsSheet = e.source.getSheetByName('Products');
const dataRange = productsSheet.getRange(dataRangeAddress) .getValues();

const columnOffset = productsSheet.getRange(dataRangeAddress) .getColumn() - 1;
const maxColumnIndex = productsSheet.getRange(dataRangeAddress) .getLastColumn () - columnOffset;

clearDropdowns(sheet, editedRow, editedColumn, maxColumnIndex +1);

if (editedColumn >= 24 && editedColumn < maxColumnIndex +1 ) {
const selectedValues = sheet.getRange(editedRow, 2, 1, editedColumn - 1).getValue()[0];
const nextColumnData = getNextColumnData(selectedValues, dataRange, editedColumn - 1);

if (nextColumnData.length > 0) {
const targetCell = e.range.offset(0, 1);
var rule = SpreadsheetApp.newDataValidation()
.requireValueInList(nextColumnData, true)
.build();
targetCell.setDataValidation(rule);
} else {
e.range.offset(0, 1).clearContent().clearDataValidations();
}
}
}
}

function getNextColumnData(selectedValues, dataRange, columnIndex) {
let filteredData = dataRange;
for (let i = 0; i< selectedValues.length; i++) {
filteredData = filteredData.filter(row => row[i] === selectedValues[i]);
}
const uniqueNextColumnValues = [...new Set(filteredData.map(row[columnIndex]))];
return uniqueNextColumnValues;
}

function clearDropdowns(sheet, row, startColumn, maxColumn) {
for (let col = startColumn + 1; col <=maxColumn; col++) {
let cell = sheet.getRange(row, col);
cell.clearContent().clearDataValidation();
}
}

Hi, this is great, thank you, but I'm getting two errors: "TypeError: Cannot read properties of undefined (reading 'source')" with
the onEdit function and "TypeError: Cannot read properties of undefined (reading 'length')" with the getNextColumnData function. Can you help?

Awesome video! On my 'home' page my drop down starts in column E. How do I modify the code for that difference? I've been trying to figure it but to no avail.

If I want to change the initial data fill column from Col2 to Col4 then what value should I adjust in the code you shared.

Your content is phenomenal. The explanation, visuals, pace, just about everything is brilliant. Subscribed in an instance!

Great 👍👍👍

Hi HashAli, Great video. Can you please share the script so that its easier to copy / paste and edit the values. Thanks!