Only one TRUE checkbox

问题:

I have a column of check boxes:

.

If a box is checked it sets a value to a cell in another sheet.

If I check box no.1 ,it turns true and the remaining still false then if I check box no.2 it also turns true long with box no.1 and the remaining still false. This is the normal operation but I need that, when I check a box it turns true and all the other boxes turn false, either they are checked or not.In other words, I want one box to be checked at a time.

Can I do that?

This is my code to set a value if the box is checked:

var hasValue = sheet.getRange("B2:B").getValues();
for (var i = 0; i < hasValue.length; i++) {
    if (hasValue[i][0] == true) {
        var transfer = sheet2.getRange(2, 2, 1, 1).setValue(i + 1);
    }
}

回答1:

This kind of behavior is known as a "radio button".

The simplest method to achieve it is to bind the simple edit trigger:

1. inspect the edited range to determine if it was to your checkbox region and quit if not.
2. set all checkboxes to false
3. set the edited cell to the appropriate value from the event object
4. if required, perform the update

An extremely minimal sample which you will have to configure, and which is only configured for single-cell edits.

function onEdit(e) {
  if (!e || e.value === undefined)
    return; // The function was run from the Script Editor, or a multi-cell range was edited.
  const edited = e.range;
  const s = edited.getSheet();
  if (s.getName() !== "some name")
    return; // A cell on the wrong sheet was edited
  if (isCheckboxRow_(edited.getRow()) && isCheckboxCol_(edited.getColumn())) {
    // The cell edited was in a row and a column that contains a checkbox
    updateCheckboxes_(s, edited, e);
  }
}

function isCheckboxRow_(row) {
  // Assumes checkboxes are only in rows 5, 6, 7, 8, 9, and 10
  return row >= 5 && row <= 10;
}
function isCheckboxCol_(col) {
  // Assumes checkboxes are in column A
  return col === 1; 
}
function updateCheckboxes_(sheet, editRange, eventObject) {
  if (!sheet || !edit || !eventObject)
    return; // Make sure all required arguments are defined (i.e. this was called and not run from the Script Editor)
  const cbRange = sheet.getRange("A5:A10"); // location of the checkboxes in a radio group.
  cbRange.setValue(false);
  editRange.setValue(eventObject.value);
  // Reference some other sheet
  const targetSheet = eventObject.source.getSheetByName("some other sheet name")
  if (!targetSheet)
    return; // the sheet name didn't exist in the workbook we edited.
  // Reference a cell in the same row as the cell we edited, in column 1
  const targetCell = targetSheet.getRange(editRange.getRow(), 1);
  if (eventObject.value) {
    // when true, give the target cell the value of the cell next to the edited checkbox
    targetCell.setValue(editRange.offset(0, 1).getValue());
    // do other stuff that should be done when a checkbox is made true
  } else {
    // the checkbox was toggled to false, so clear the target cell
    targetCell.clear();
    // do other stuff that should be done when a checkbox is made false
  }
}

The above hints at some suggested practices, such as using helper functions to encapsulate and abstract logic, resulting in easier to understand functions.

Review:

• Simple Triggers
• Event Objects
• Spreadsheet Service

回答2:

As I mentioned I would us an onEdit(event) to monitor which checkbox has been checked and loop through the column and only set one checkbox to true. Note that in your code snippet, getRange("B2:B") could be 999 rows. I use getDataRange() to limit to only the rows that are used. And I use getCriteriaType() to check that it is a checkbox not some other data type. And I'm assuming on your sheet2 you want to record which box was last checked true. tehhowch's answer is more generic and maybe more than what you need so here is a limited specific answer.

function onEdit(event) {
  try {
    var sheet = event.range.getSheet();
    // Limit the following code to a particular sheet
    if( sheet.getName() === "Sheet5" ) {
      // Limit the following code to column B
      if( event.range.getColumn() === 2 ) {
        var range = sheet.getRange(2,2,sheet.getLastRow()-1,1);
        var checks = range.getValues();
        var valid = range.getDataValidations();
        for( var i=0; i<checks.length; i++ ) {
          if( valid[i][0].getCriteriaType() === SpreadsheetApp.DataValidationCriteria.CHECKBOX ) checks[i][0] = false;
        }
        // Assuming there are no formulas in this range
        range.setValues(checks);
        event.range.setValue(event.value);
        if( event.value === true ) {
          event.source.getSheetByName("Sheet6").getRange(2,2,1,1).setValue(event.range.getRow());
        }
      }
    }
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}