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

Question

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

I have to apologize for my poor English skill. Unfortunately, I cannot understand `However, if no item is checked (col F == 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?`. And, when I saw your sample Spreadsheet, I couldn't find `the "clear" button` you say. I apologize for this. Can I ask you about the detail of your question? — Tanaike, Jan 18 '22 at 01:59
And, in your title, you say `If any row in range (G11:G25) contains boolean (true) then run function, else msgBox`. But in your question, you say `if no item is checked (col F == false) and the "clear" button is pressed`. When I saw your sample Spreadsheet, I couldn't find the values in the column "F" and the clear button. So I'm worried that you might have mistaken the sample Spreadsheet. — Tanaike, Jan 18 '22 at 02:11
Hi @Tanaike- first thank you for responding (and part of my clearRowContents function comes from code that you wrote to help someone else!). I have two functions. If any row in range G11:G25 (Boolean) is true, I want to run clearRowContents function, otherwise (‘else’) if range G11:G25 (Boolean) is false or empty, then I want to show a msgBox with instructions. — WhatData, Jan 18 '22 at 02:43
@Tanaike I also corrected some errors in my original question. The Boolean column (with checkboxes) is Col G (G11:G25) and the corresponding list of items is in Col H (H11:H25). — WhatData, Jan 18 '22 at 02:46
The “clear” button is an image of circle with a “minus” sign inside of it with the phrase “Delete selected items” next to it. — WhatData, Jan 18 '22 at 02:48
Thank you for replying and adding more information. From your replying, I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your question, I apologize. — Tanaike, Jan 18 '22 at 02:57
@Tanaike. Brilliant! I tested out your fixes to the run both functions and it works wonderfully. Thank you for your kind help. — WhatData, Jan 18 '22 at 03:03
Thank you for replying and testing it. I'm glad your issue was resolved. I could correctly understand your question with your cooperation. Thank you, too. — Tanaike, Jan 18 '22 at 03:05

Tanaike · Accepted Answer · 2022-01-18T03:02:46.813

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

From your question, I understood your clearRowContents works. So I proposed to modify clearItemMessage.
In your clearRowContents, var col = 7 is used. So I think that function clearRowContents (col){ can be modified to function clearRowContents (){.

Reference:

filter()

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

1 Answers1

Modified script:

Reference: