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

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

Google Apps Script to delete all the filter views in a spreadsheet

If you have created lots of filter views in a Google Spreadsheet and find it hard to delete all of them one by one manually then you can use the following Google Apps Script code to delete all filter views in one go!

function deleteAllFilterViews() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var id = ss.getId();
 
  var myFilterViews = Sheets.Spreadsheets.get(id, {
    ranges: 'Sheet1',
    fields: 'sheets/filterViews/filterViewId',
  }).sheets[0].filterViews;
 
  Sheets.Spreadsheets.batchUpdate({
    requests: myFilterViews.map(function(e) {
      return { deleteFilterView: { filterId: e['filterViewId'] } };
    }),
  },id);
};

Note:
Paste the above code in the script editor of your spreadsheet and run the function "deleteAllFilterViews"You will need to enable "Google Sheets API", it can be done by navigating to "Resources" > "Advanced Google services..." in the script editor of your spreadsheet.

Also you will need to enable the service in GCP Console:
https://console.cloud.google.com/apis/