Google Apps Script to clear ranges based on a single TRUE/FALSE cell in another sheet

Question

I am trying to clear the contents of 4 columns of data based on a true/false value of a single cell in another sheet. This is my second script ever. I tried using some examples from these two references: [Google Apps Script to clear multiple ranges] and i need a button to clear multiple ranges in a google spreadsheet.2

I don't think I am calling the value of my trigger cell correctly. I would like the script to run any time the value of this cell is changed.Here is my code:

function clearautocratFields(e) {

  /*  variables for the function: triggerCell: is contained in the 'Meet Ranges' sheet.  It is cell k2.  Can be TRUE/FALSE.
      dataSheet: is the sheet 'Form Responses 1'.  Thats where the cells are that I want to clear.
      The Ranges I want to change are 'fg2:fg, fh2:fh, fi2:fi, fj2:fj'*/

  var triggerCell = SpreadsheetApp.getActive().getSheetByName('Meet Ranges').getSheetValues('k', '2', 1, 1); 

  if (triggerCell == 'TRUE'){} else  // If the cell is true do nothing
  if (triggerCell == 'FALSE'){   // If the cell is false run the script



  var dataSheet = SpreadsheetApp.getActive().getSheetByName('Form Responses 1');// Range to clear
    sheet.getRange('fg2:fg').clearcontents();
    sheet.getRange('fh2:fh').clearcontents();
    sheet.getRange('fi2:fi').clearcontents();
    sheet.getRange('fj2:fj').clearcontents();
    }

}

Answer 1

The syntax for the `getSheetValues() method is:

getSheetValues(startRow, startColumn, number of Rows, number of Columns)

The startRow parameter must be an integer. You have a string.

getSheetValues('k', '2', 1, 1)

Answer 2

Aside from Sandy Good's clarification on the input parameters for getSheetValues you are looking for a single value and that function returns a two-dimensional array.

More straightforwardly and perhaps intuitively use:

var sheet = SpreadsheetApp.getActive().getSheetByName('Meet Ranges')
var triggercell = sheet.getRange(2, 11).getValue(); // 'k' being column 11

This will return the single value you are looking for.

Depending on how the value is in the Spreadsheet, you may need to be careful in how you are handling the Falsey value in K2. If you are looking for explicit "FALSE" as text values then your script works.

Your comparison of an unused "TRUE" value suggests you may have other options in mind? If you don't then just make a comparison against the value you are explicitly looking for. It will make your script easier to understand when you come back to it.

Answer 3

I had the same problem and found the solution and it is very simple.

When you are trying to use a boolean TRUE or FALSE variable within an IF you should not do any logic, because the variable TriggerCell already has the logical answer to your question.

If your variable TriggerCell is taking the value of a checkbox, for example, that returns TRUE or FALSE you must replace your code with this:

    if (triggerCell){} // If TriggerCell is True, do nothiing.  
    else {             // If the cell is false run the script bellow

    var dataSheet = SpreadsheetApp.getActive().getSheetByName('Form Responses 1');
    sheet.getRange('fg2:fg').clearcontents();
    sheet.getRange('fh2:fh').clearcontents();
    sheet.getRange('fi2:fi').clearcontents();
    sheet.getRange('fj2:fj').clearcontents();
    }