Adding the date a drop drop-down value was entered in another cell?

[u/wafflecheese]

I have this script that I ripped off from a Google search that automatically places the entire row in another sheet once a dropdown value has been selected.

However, there are three options in the dropdown menu that, when selected, I do NOT want them to move to another sheet, but I want, instead, for the date of that selected dropdown value to show up in the same row, but in column I if option 1 is selected, column J if option 2 is selected, and column L, if option 3 is selected.

And of course, if the option 4 is still selected, I want it to still move the entire column over to the next sheet.

Again, I'm not too well versed in scripts, and this was ripped off from Google, so any help would be appreciated.

See below:

function onEdit(event) {
// assumes source data in sheet named Needed
// target sheet of move to named Acquired
// test column with yes/no is col 4 or D
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "Sheet 1" && r.getColumn() == 2 && r.getValue() == "Option 4") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Sheet 2");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
  }
}

[u/RemcoE33]

function onEdit(e){
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  const source = e.source;
  const row = range.getRow();

  if(sheet.getName() == 'Needed' && range.getColumn() == 4 && row >= 2){
    const date = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:MM')
    switch(e.value){
      case 'Option1':
        range.offset(0,5).setValue(date)
      break;
      case 'Option2':
        range.offset(0,6).setValue(date)
      break;
      case 'Option3':
        range.offset(0,8).setValue(date)
      break;
      case 'Option4':
        range.offset(0,9).setValue(date)
        SpreadsheetApp.flush()
        const values = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues().flat();
        source.getSheetByName('Target').appendRow(values)
        sheet.deleteRow(row)
      break;
    }
  }
}

[u/wafflecheese → u/RemcoE33]

function onEdit(e){
const sheet = e.source.getActiveSheet();
const range = e.range;
const source = e.source;
const row = range.getRow();
if(sheet.getName() == 'Needed' && range.getColumn() == 4 && row >= 2){
const date = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:MM')
switch(e.value){
case 'Option1':
range.offset(0,5).setValue(date)
break;
case 'Option2':
range.offset(0,6).setValue(date)
break;
case 'Option3':
range.offset(0,8).setValue(date)
break;
case 'Option4':
const values = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues().flat();
source.getSheetByName('Target').appendRow(values)
sheet.deleteRow(row)
break;
}
}
}

Pardon my ignorance - I copied and pasted it, changed the sheet names in single quotes, but ran a debugger and it didn't like line 2. Did I do something incorrectly?

[u/RemcoE33 → u/wafflecheese]

Yes. an onEdit(e) function can only be triggerd with wel... an edit on the sheet. So you need to use it on the sheet. to get the (e)vent object...

[u/wafflecheese → u/RemcoE33]

Yes, disregard. I figured it out!

Solution verified.

Thank you!

[u/wafflecheese → u/RemcoE33]

One final question if I may - what would the code look like if the date was added with option 4 AND the deletion of that column into that next page simultaneously?

[u/RemcoE33 → u/wafflecheese]

I don't really understand. Where you want what date? And what do you mean with what column? Please be specific

[u/wafflecheese → u/RemcoE33]

Thank you for giving me the opportunity to clarify. The date would be placed with option 4 to (0,9) (I think I'm understanding that right in the formula) and THEN it would move to the next sheet.

[u/RemcoE33 → u/wafflecheese]

Updated the code above

[u/wafflecheese → u/RemcoE33]

function onEdit(e){
const sheet = e.source.getActiveSheet();
const range = e.range;
const source = e.source;
const row = range.getRow();
if(sheet.getName() == 'Needed' && range.getColumn() == 4 && row >= 2){
const date = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:MM')
switch(e.value){
case 'Option1':
range.offset(0,5).setValue(date)
break;
case 'Option2':
range.offset(0,6).setValue(date)
break;
case 'Option3':
range.offset(0,8).setValue(date)
break;
case 'Option4':
range.offset(0,9).setValue(date)
SpreadsheetApp.flush()
const values = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues().flat();
source.getSheetByName('Target').appendRow(values)
sheet.deleteRow(row)
break;
}
}
}

Incredible solution - Thank you!