Apps Script - Validate dropdown input value

[SANPERD]

Hello eveyone, 

Any expert on Apps Script for Google Sheets? I need help.

This is my scenario:

I have a Google Sheet with 4 dropdowns (all depended one from each other) and I need to clean the next ones when one is edited, this I already have it.

However it shouldn't work always, just when the user changes the value manually for a specific one. If the user copies and pastes the values from another file (the 4 at same time) it shouldn't clean them UNLESS ONE HAVE AN INVALID VALUE FROM THE LIST.

This is the code I have so far:

function installedOnEdit(e) {

if(e.range.rowStart!=1) {
// create dynamic and filtered dropdowns
dropdown(e);

var s = SpreadsheetApp.getActive();
var r = s.getActiveCell();

// clean depended dropdowns

if( r.getColumn() == 1 ) {
r.offset(0, 1).setValue('');
r.offset(0, 2).setValue('');
r.offset(0, 3).setValue('');
}
if( r.getColumn() == 2 ) {
r.offset(0, 1).setValue('');
r.offset(0, 2).setValue('');
}
if( r.getColumn() == 3 ) {
r.offset(0, 1).setValue('');
}
}
}

I appreciate your help.
Regards.

[sahilnaircool]

To handle this requirement, we can modify your installedOnEdit function to detect manual changes specifically. When values are pasted, the changeType is set to "PASTE," so you can use that property to prevent clearing the dropdowns in a paste scenario. Here’s an enhanced version of your script :-

function installedOnEdit(e) {
const sheet = e.source.getActiveSheet();
const r = e.range;

// Only proceed if editing happens below row 1
if (r.getRow() > 1) {
const changeType = e.changeType;

// Get all values in columns with dropdowns to check for invalid entries
const values = r.getValues().flat();
const allowedValuesCol1 = ["Value1", "Value2"]; // replace with actual values
const allowedValuesCol2 = ["ValueA", "ValueB"]; // replace with actual values
const allowedValuesCol3 = ["OptionX", "OptionY"]; // replace with actual values
const allowedValuesCol4 = ["Choice1", "Choice2"]; // replace with actual values

const isInvalidValue =
(r.getColumn() === 1 && !allowedValuesCol1.includes(values[0])) ||
(r.getColumn() === 2 && !allowedValuesCol2.includes(values[0])) ||
(r.getColumn() === 3 && !allowedValuesCol3.includes(values[0])) ||
(r.getColumn() === 4 && !allowedValuesCol4.includes(values[0]));

// Only trigger the dropdown clear logic on manual edit or invalid value
if (changeType !== 'PASTE' || isInvalidValue) {
// clear dependent dropdowns based on which column was edited
if (r.getColumn() === 1) {
r.offset(0, 1).clearContent();
r.offset(0, 2).clearContent();
r.offset(0, 3).clearContent();
} else if (r.getColumn() === 2) {
r.offset(0, 1).clearContent();
r.offset(0, 2).clearContent();
} else if (r.getColumn() === 3) {
r.offset(0, 1).clearContent();
}
}
}
}