Google Scripts - Use a Script to find/replace to clear check boxes

0
votes

I have a dashboard on Google sheets with various checkboxes distributed around the place.

Using the find and replace feature, you can change TRUE to FALSE to uncheck the checkboxes. I am trying to do this with a script. Here is what I have:

function Refresh() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sh.getSheetByName("1");
  var range = sheet.getRange("Y4:Y33");
  var to_replace = true;
  var replace_with = false;
  replaceInSheet(sheet,range, to_replace, replace_with);
}




function replaceInSheet(sheet, range, to_replace, replace_with) {
  //Confirm
  var ui = SpreadsheetApp.getUi(); 
  var spread = SpreadsheetApp.getActiveSpreadsheet();

  var result = ui.alert(
     "Will reset sheet for new week ",
     'Are you sure you want to continue?',
      ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {

    // User clicked "Yes".
    spread.toast("Will update " + to_replace + " to " + replace_with + " ", "ALERT");

    var data  = range.getValues();

    var oldValue="";
    var newValue="";
    var cellsChanged = 0;

    for (var row=0; row<data.length; row++) {
      for (var item=0; item<data[row].length; item++) {
        oldValue = data[row][item];
        newValue = data[row][item].replace(to_replace, replace_with);
        if (oldValue!=newValue)
        {
          cellsChanged++;
          data[row][item] = newValue;
        }
      }
    }
    range.setValues(data);
    spread.toast(cellsChanged + " cells changed", "STATUS");
  }
  else {
    // User clicked "No" or X in the title bar.
    spread.toast("No action taken", "ABANDONED");
  }
}

If the var to_replace and var replace_with is a piece of text, works no problem. However if I try to make it true and false, as I've done above, I get an error.

TypeError: Cannot find function replace in object true

What am I doing wrong? I know it is something about a boolean V string.

2
google-apps-scriptgoogle-sheets

2 Answers

2
votes

Boolean types doesn't have replace() method. Use toString() to convert to string type:

newValue = data[row][item].toString().replace(to_replace, replace_with);

To uncheck the checkboxes, a easier method is to use range.uncheck():

function Refresh() {
 SpreadsheetApp.getActive()
      .getSheetByName("1")
      .getRange("Y4:Y33")
      .uncheck()
}
1
votes

The reason it doesn't work is because boolean does not have a replace function. To achieve your objective you can do something like below.

for (var row=0; row<data.length; row++) {
  for (var item=0; item<data[row].length; item++) {
    oldValue = data[row][item];
    if(oldValue === to_replace) {
      cellsChanged++;
      data[row][item] = replace_with;
    }
  }
}