Search code examples
google-apps-scriptdrop-down-menu

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


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)
   }

Solution

  • 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('');
      }
    }