Google Sheets onEdit script issue - dropdown selection not working properly

Other Questions Google Sheets
1

I need help fixing my Google Apps Script for dropdown changes.

I’m working on a Google Sheets project where I want cell F1 to change based on what gets selected in dropdown E4 on my ‘Show SCM Category’ sheet.

Here’s what should happen:

  • When ‘Updated’ is picked → F1 shows ‘Col1’
  • When ‘SCM Category’ is picked → F1 shows ‘Col4’
  • When ‘Company’ is picked → F1 shows ‘Col6’

I want to use this in a query formula like:

=query({'Data Sheet'!A1:M}, "select * where Col4 contains '"&B1&"' order by '"&F1&"',Col1,Col5",0)

My current script handles other stuff but I added these lines at the bottom and they don’t work:

function onEdit(e) {
    var targetSheets = ['Alice', 'Bob', 'Charlie', 'David', 'Emma','Show SCM Category'],
        checkCols = [1, 6],
        outputCols = [15, 11],
        colIndex = checkCols.indexOf(e.range.columnStart);
    if (targetSheets.indexOf(e.source.getActiveSheet().getName()) === -1 || colIndex === -1 || !e.value) return;
    
    if (colIndex === 0 && e.value === 'Update') {
        e.range.setValue(new Date());
    } else if (colIndex === 1) {
        if (e.range.offset(0, 5).getValue() === '') e.range.offset(0, 5).setValue(2);
        if (e.range.offset(0, 9).getValue() === '') e.range.offset(0, 9).setValue(new Date());
        if (e.range.offset(0, -5).getValue() === '') e.range.offset(0, -5).setValue(new Date());
    }
    else if (colIndex === 1 && e.value === 'Updated') {
        e.range.setValue(Col1); 
    }
    else if (colIndex === 1 && e.value === 'SCM Category') {
        e.range.setValue(Col4);
    }
    else if (colIndex === 1 && e.value === 'Company') {
        e.range.setValue(Col6);
    }
}

Maybe there’s an easier way with IF formulas instead? Any ideas would be great!

2

It seems like your script has a logical flaw. You’re checking for column 6 (colIndex 1), but the dropdown is located in column E, which is actually column 5. Additionally, instead of assigning values to F1, you’re mistakenly trying to set the value of the dropdown itself. Remember to put ‘Col1’, ‘Col4’, and ‘Col6’ in quotes because they are strings. Here’s a corrected version of your script:

function onEdit(e) {
  if (e.source.getActiveSheet().getName() !== 'Show SCM Category' || e.range.getA1Notation() !== 'E4') return;
  
  var sheet = e.source.getActiveSheet();
  var cellF1 = sheet.getRange('F1');
  
  switch(e.value) {
    case 'Updated':
      cellF1.setValue('Col1');
      break;
    case 'SCM Category':
      cellF1.setValue('Col4');
      break;
    case 'Company':
      cellF1.setValue('Col6');
      break;
  }
}

This approach specifically watches cell E4 on the target sheet and updates F1 accordingly, providing a cleaner solution than merging it with your existing code.

3

honestly the formula approach is way better here but if you really want the script fix, your main problem is ‘Col1’,‘Col4’,‘Col6’ need quotes around them and youre setting the wrong cell. try sheet.getRange(‘F1’).setValue(‘Col1’) instead of e.range.setValue(Col1) - that should work better than messing with the existing logic

4

The main issue is that you’re targeting the wrong column and trying to modify the dropdown cell instead of F1. Your checkCols array includes column 6, but E4 is column 5. Also, you’re using e.range.setValue() which changes the dropdown itself rather than cell F1. Honestly, for something this straightforward, a formula approach would be much simpler and less error-prone. Just put this formula directly in F1:

=IF(E4="Updated","Col1",IF(E4="SCM Category","Col4",IF(E4="Company","Col6","")))

This eliminates the need for any script modifications and updates automatically whenever E4 changes. I’ve used similar setups in my sheets and found formulas more reliable than trying to integrate dropdown logic into existing onEdit functions. The formula will handle all three cases and return blank if none match.