CODEWITHSUNDEEP
google-apps-script
Azualend
Azualend

Reputation: 21

How to add a date picker to a drop down list

Hello and thanks in advance for the assistance.

What I am trying to do in Google Sheets is to add the data verification date picker to the data verification drop down selection.

I would like to do this for use in an employee start and end date situation. Specifically for the end date. The idea is that from the drop down selection I could pick the options "Choose One", "Present", or "Date", where the "Date" option enables the data verification date picker.

Better yet if choosing "Date" triggers the date picker right away.

Below is the code that I have been trying to get to work for this function:

function listCalendar() {

 // Set the data validation for cells in column H6 thru H1000 to require "Choose One", "Present", or "Date", with a dropdown menu.
 var cell = SpreadsheetApp.getActive().getSheetByName('Employees').getRange('H6:H100');
 var rule1 = SpreadsheetApp.newDataValidation().requireValueInList(['Choose One', 'Present', 'Date']).build();

 // Set the "Date" option in the dropdown menu to activate a date picker.
 var rule2 = SpreadsheetApp.newDataValidation().requireDate().build();
   if (cell == 'Date') {cell.setDataValidation(rule2);}
     else {cell.setDataValidation(rule1);}

}

Thank you again for the assistance!

Upvotes: 2

Views: 492

Answers (2)

Azualend
Azualend

Reputation: 21

Huge thanks to Ritz^^
After looking at the code that Ritz posted I realized that I was using getRange in a place where I should have been using getValue and the onEdit part of the function allowed for the rules to switch back and forth between one another. So, just in case this is usfull to someone else, below is the code that I ended up with:

function onEdit() {

var ss = SpreadsheetApp.getActive();
var employees = ss.getSheetByName('Employees');
var cellrange = employees.getRange('H6');
var cellvalue = cellrange.getValue();

// Set the data validation to require "Choose One", "Present", or "Date", with a dropdown menu.
var rule1 = SpreadsheetApp.newDataValidation().requireValueInList(['Choose One', 'Present', 'Date']).build();

// Set the "Date" option in the dropdown menu to activate a date picker.
var rule2 = SpreadsheetApp.newDataValidation().requireDate().build();

if (cellvalue == 'Date') {cellrange.setDataValidation(rule2);}
else {cellrange.setDataValidation(rule1);}

}

Upvotes: 0

Ritesh Nair
Ritesh Nair

Reputation: 3355

Add the below code and set spreadsheet onEdit trigger. Hope this gives you a start.

function onEdit() {
 // Set the data validation for cells in column H6 thru H1000 to require "Choose One", "Present", or "Date", with a dropdown menu.
 var cell = SpreadsheetApp.getActive().getActiveSheet().getRange("H6:H100");
 var rule1 = SpreadsheetApp.newDataValidation().requireValueInList(['Choose One', 'Present', 'Date']).build();
 var rule2 = SpreadsheetApp.newDataValidation().requireDate().build();
      if (SpreadsheetApp.getActive().getActiveSheet().getActiveCell().getValue() == 'Date') {
        SpreadsheetApp.getActive().getActiveSheet().getActiveCell().setDataValidation(rule2);
      }
     else {
       cell.setDataValidation(rule1);
      }
}

Upvotes: 2

Related Questions