Friday, July 26, 2019

Google Sheets - Data Validation (Dynamic Dropdown) - using Google Apps Script

In Google Sheets, if you want to have the dynamic dropdown, that is data validation based on the some other (adjacent) cell, then it is possible using Google Apps Script.

Consider the following example:

Screenshot of Sheet "ROI" which has the data from which we have to get the value of dependent data validation:



Screenshot of Sheet "Sales" in which Data Validation is required:



Following is the script code which will get us the desired dynamic data validation (drop down):

//////////////////////////////////////////////////////

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  if( activeSheet.getName() === 'Sales' ) {
    var activeCell = activeSheet.getActiveCell();
    var activeRow = activeCell.getRow();
    if( activeRow >= 3 ) {
      var activeColumn = activeCell.getColumn();
      if( activeColumn === 5 || activeColumn === 6 ) {
        var activeCellValue = activeCell.getValue();
        var v = ss.getSheetByName('ROI').getDataRange().getValues();
        if( activeColumn === 5 ) {
          var f = v.filter(function (currentValue,index,arr) {
            return arr[index][0] === activeCellValue
          });
          var option = f.map(function (currentValue,index,arr) {
            return arr[index][1]
          });
        }
        else if( activeColumn === 6 ) {
          var prevCellValue = activeSheet.getRange(activeRow,activeColumn-1).getValue();
          var f = v.filter(function (currentValue,index,arr) {
            return arr[index][0] === prevCellValue && arr[index][1] === activeCellValue
          });
          var option = f.map(function (currentValue,index,arr) {
            return arr[index][2]
          });
        }
        var dv = SpreadsheetApp.newDataValidation();
        dv.setAllowInvalid(false);
        //dv.setHelpText("Some help text here");
        dv.requireValueInList(option, true);
        activeSheet.getRange(activeRow,activeColumn+1).setDataValidation(dv);
      }
    }
  }
};

//////////////////////////////////////////////////////