validation - Display a dropdown menu only when a different cell contains a certain value

Question

I'm very new to writing script for Google Sheets, and I'm attempting to create a spreadsheet that will only display a dropdown in a column ("Provisional Notes") if the value in a column ("Certified or Provisional" is "Provisional." If it is "Certified," the user is be able to enter data freely. I'm also wanting to remove the validation if the value changes from "Provisional". I also need the solution to run on the Google Sheets App, as this spreadsheet will be run on an iPad and/or a smartphone.

I've done quite a bit of searching, and only seem to find dropdowns that depend on other dropdowns, rather than leaving a cell blank if the initial dropdown is a certain value.

What I have come up with so far only runs once (even though I've attempted to have it occur on every edit?) Also, if the value changes from Provisional to Certified, the validation does not get removed.

For my practice, I've applied it to only 2 specific cells, rather than the entire 2 columns.

function onEdit2(e){
var dropdownCell = SpreadsheetApp.getActive().getRange('P2');
var certCell = SpreadsheetApp.getActive().getRange('J2'); 
var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Test 1', 'Test 2'], true).build();

function insertDropdown(){
if(e.certCell.getValue() === "P"){

dropdownCell.setDataValidation(rule);

  }
 }
}

I greatly appreciate all suggestions!

Answer 1

To delete a data validation you just need to use the method setDataValidation() and set its value to null to delete the data validation present on that cell.

In the following code example, depending on the value inserted I am adding or deleting the data validation. This code example has self explanatory comments:

function onEdit(e) {
  // get sheet
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');

// if modifications happen in A
  if(e.range.getColumn()==1){
    // check inserted value
    if(e.range.getValue()=='Provisional'){
      // create rule from a list range and set it to the same row in column B
      var rule = SpreadsheetApp.newDataValidation().requireValueInList(['A','B']).setAllowInvalid(false).build();
      sheet.getRange(e.range.getRow(),2).setDataValidation(rule); 

    }
    // if it is set to certified
    if(e.range.getValue()=='Certified'){
      // delete any data validation that may have been in its adjacent cell
      sheet.getRange(e.range.getRow(),2).setDataValidation(null);
    }
  }
}