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});
}
In your situation, how about modifying clearItemMessage()
as follows?
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.");
}
}
clearRowContents
works. So I proposed to modify clearItemMessage
.clearRowContents
, var col = 7
is used. So I think that function clearRowContents (col){
can be modified to function clearRowContents (){
.