CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-apispreadsheet
Simonas Balnys
Simonas Balnys

Reputation: 21

Don't allow droplist if there's any unchecked checkbox?

I'm trying to solve this problem in Google Sheets. I have a Data Validation based droplist on column A (each row contains new droplist). I need it to be visible only when all of the checkboxes ABOVE it in column F are TRUE. If they are FALSE, then I need the A column droplist to be invisible/ blank. If user would select all checkboxes, the A column droplist would become visible again.

Example: https://docs.google.com/spreadsheets/d/12BlvudCrSFIGJI9s41mFOPqTRml4uLTQ4ODRtLKgBd0/edit?usp=sharing

Upvotes: 0

Views: 100

Answers (2)

The God of Biscuits
The God of Biscuits

Reputation: 3187

Here's a non script-based approach. As per your example file:

  • In A1:A4, change the data validation drop down range to D1:G4
  • Retain tick boxes in B1:B4
  • In D1, enter the formula =arrayformula(ifna(if(match(FALSE,B1:B4,0)>row(B1:B4),na(),),{"ABC1","ABC2","ABC3","ABC4"}))

This approach results in the validation range only being populated for each row if all checkboxes above that row are TRUE, so for any row which is FALSE the validation range is empty.

Upvotes: 0

Cooper
Cooper

Reputation: 64100

function onEdit(e) {
  e.source.toast("Entry");
  const sh = e.range.getSheet();
  if(sh.getName() == "Sheet0" && e.range.columnStart == 2 && e.value == "TRUE") {
    e.source.toast('Gate1');
    let arr = [...Array.from(new Array(e.range.rowStart).keys(),x => x)];
    if(arr.every(r => e.range.offset(-r,0).getValue() == true)) {
      //Logger.log(arr.map(r => e.range.offset(-r,0).getValue()));
      let rule = SpreadsheetApp.newDataValidation().setAllowInvalid(false).requireValueInList(["A","B","C"]).build();
      sh.getRange(1,1,e.range.rowStart).clearDataValidations();
      sh.getRange(1,1,e.range.rowStart).setDataValidation(rule);
    }
  }
  if(sh.getName() == "Sheet0" && e.range.columnStart == 2 && e.value == "FALSE") {
    e.source.toast('Gate2');
    let arr = [...Array.from(new Array(e.range.rowStart).keys(),x => x)];
    if(arr.some(r => e.range.offset(-r,0).getValue() == false)) {
      Logger.log(arr.map(r => e.range.offset(-r,0).getValue()));
      sh.getRange(e.range.rowStart,1).clearDataValidations().clearContent();;
    }
  }
}

Demo:

enter image description here

Upvotes: 1

Related Questions