Create drop down list which will show the first item from the drop down menu when it is generated Using Google App Script

Anushka Nahid 0
Viewed 27

I am trying to create a drop down list in google sheet using google app script. Sheet Link: https://docs.google.com/spreadsheets/d/1TESZNYgSJC-ye8r9CcW57-Vt1yU5VcvELiJw4oRwAZQ/edit#gid=0

In this sheet, in column B there is responsible person. Whenever I will select any name from the drop down list of this column, the another drop down list will create in column C and in column D the current date will appear. For example, If I select 'Anushka' in cell B2, a drop down list 'New', 'on going', 'done', 'on hold' will create in cell C2 with the first value from the list of the drop down and a current date will appear in cell D2. Till now, I can create the drop down menu in cell C2 and also current date in cell D2 based one cell B2. But I don't know how to show always the first item from the drop down list in cell C2. Now it's giving me blank cell and I need to select something from the menu but I want in a way that when drop down menu will create, it will show the first item not empty cell. I have attached the code also. Can anyone please help me where I should modify?

  var sheet = event.source.getActiveSheet();
  var colrange=sheet.getRange("B2:B").isBlank()
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();

   if(colrange){
    sheet.getRange(index,4).clearContent()
    sheet.getRange(index,3).clearDataValidations()
   } else{
    var date = Utilities.formatDate(new Date(),"EST", "MMMM-dd-yyyy");
    var list=["New", "Ongoing", "Done", "On-Hold"]

    var rule=SpreadsheetApp.newDataValidation().requireValueInList(list).build()
    sheet.getRange(index,3).setDataValidation(rule)
    sheet.getRange(index,4).setValue(date)
   }
drop-down-menu google-apps-script
1 Answers

Custom Validations

function onEdit(e) {
  e.source.toast("Entry");
  const sh = e.range.getSheet();
  if(sh.getName() == "Sheet0" && e.range.columnStart == 2 && e.value) {
    e.source.toast("Flag1");
    let rg = e.source.getRangeByName("Status");
    e.range.offset(0,1).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInRange(rg));
    e.range.offset(0,2).setValue(new Date());
    Logger.log(e.source.getRangeByName("Status").getValues()[0][0])
    e.range.offset(0,1).setValue(e.source.getRangeByName("Status").getValues()[0][0]);
  }
}

Demo:

enter image description here

Named Ranges:

enter image description here

In answer to your comment below:

function onEdit(e) {
  //e.source.toast("Entry");
  const sh = e.range.getSheet();
  if(sh.getName() == "Sheet0" && e.range.columnStart == 2 && e.value) {
    //e.source.toast("Flag1");
    let rg = e.source.getRangeByName("Status");
    e.range.offset(0,1).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInRange(rg));
    e.range.offset(0,2).setValue(new Date());
    //Logger.log(e.source.getRangeByName("Status").getValues()[0][0])
    e.range.offset(0,1).setValue(e.source.getRangeByName("Status").getValues()[0][0]);
  }
  if(sh.getName() == "Sheet0" && e.range.columnStart == 2 && !e.value) {
    //e.source.toast("Flag2");
    let rg = e.source.getRangeByName("Status");
    e.range.offset(0,1).clearDataValidations();
    e.range.offset(0,2).setValue('');
  }
}
user30hu0
Related