if-statementgoogle-apps-scriptbooleanrangemsgbox

If any row in range (G11:G25) contains boolean (true) then run function, else msgBox

The function I'm running (clearRowContents) in sheet 'Section 2' will clear contents and validation for any checked item (col H) in a list as well as the checkbox itself (col G). The remaining unchecked boxes and list items will then be sorted to clear any blank rows just created by the clearRowContents function. This functions works as tested.

However, if no item is checked (col G == false) and the "clear" button is pressed, how can I have a message pop up letting the user know that they must first check the box next to the item and then press the button to clear its contents from the list? I'm trying to figure out how to write the script for the clearItemMessage function.

Also, for script writing purposes, this sheet will be duplicated many times to create various validation menus for different topics... each sheet will be a different "chapter" in a manual with its own unique set of drop downs (in a MASTER DROPDOWN tab).

link to sheet: https://docs.google.com/spreadsheets/d/1ZdlJdhA0ZJOIwLA9dw5-y5v1FyLfRSywjmQ543EwMFQ/edit?usp=sharing

code:

      function clearItemMessage(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var checkboxRange = ss.getRangeList("$G$11:$G$25").getValues();
  if (checkboxRange == true){
    clearRowContents (col);
  } else (Browser.msgBox("To delete items, select the box next to the items and then press the delete button."));
}


function clearRowContents (col){ // col is the index of the column to check for checkbox being true
  var col = 7; //col G
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var data = ss.getDataRange().getValues();

//Format font & size
  var sheetFont = ss.getRange("A:Z");
  var boxFont = ss.getRange("$G$11:$G$25");
  var listFont = ss.getRange("$H$11:$H$25");
  
  sheetFont.setFontFamily("Montserrat");
  boxFont.setFontSize(8)
       .setFontColor("#434343")
       .setBackground("#ffffff");
  listFont.setFontSize(12)
       .setFontColor("#434343")
       .setBackground("#ffffff");

  //clear 'true' data validations     
  var deleteRanges = data.reduce(function(ar, e, i) {
    if (e[col - 1] === true) { 
      return ar.concat(["H" + (i + 1), "G" + (i + 1)]);
    }
    return ar;
  }, []);
  if (deleteRanges.length > 0) { 
    ss.getRangeList(deleteRanges).clearContent().clearDataValidations();
  }

  //sort based on checkbox value
  var range = ss.getRange("$G$11:$H$25");
  range.sort({column: 7, ascending: false});
 
}
Solution

  • In your situation, how about modifying clearItemMessage() as follows?

    Modified script:

    function clearItemMessage(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var checkboxes = ss.getRange("$G$11:$G$25").getValues();
  if (checkboxes.filter(([g]) => g === true).length > 0){ // or if (checkboxes.some(([g]) => g === true)) {
    clearRowContents();
  } else {
    Browser.msgBox("To delete items, select the box next to the items and then press the delete button.");
  }
}

    Reference: