如何让指定列复选框实现计数功能（多工作表适配）

Here's a straightforward implementation that meets all your requirements:

1. Automatic Increment When Checking Green Column Checkboxes #

This script runs whenever you edit a cell, and only increments the right-hand cell if you check a checkbox in a green column.

function onEdit(e) {
  const editedCell = e.range;
  const activeSheet = editedCell.getSheet();
  const cellValue = e.value;

  // Only trigger when a checkbox is checked (value becomes "TRUE")
  if (cellValue !== "TRUE") return;

  // Replace this hex code with your exact green column color
  const targetGreen = "#b7e1cd";
  if (editedCell.getBackground() === targetGreen) {
    const rightCell = activeSheet.getRange(editedCell.getRow(), editedCell.getColumn() + 1);
    const currentCount = rightCell.getValue() || 0;
    rightCell.setValue(currentCount + 1);
    
    // Optional: Uncheck the checkbox after increment (remove // to enable)
    // editedCell.setValue(false);
  }
}

2. Manual ADD +1 Button Function #

This function lets you manually trigger increments either for a selected green column cell or all checked green column checkboxes in the active sheet.

function addOneToTargetColumns() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const targetGreen = "#b7e1cd"; // Match your green column color

  // Check if a cell is selected
  const selectedRange = activeSheet.getSelection().getActiveRange();
  if (selectedRange && selectedRange.getBackground() === targetGreen) {
    const rightCell = activeSheet.getRange(selectedRange.getRow(), selectedRange.getColumn() + 1);
    rightCell.setValue((rightCell.getValue() || 0) + 1);
    return;
  }

  // If no valid selection, process all checked checkboxes in green columns
  const dataRange = activeSheet.getDataRange();
  const allValues = dataRange.getValues();
  const allBackgrounds = dataRange.getBackgrounds();

  for (let row = 0; row < allValues.length; row++) {
    for (let col = 0; col < allValues[row].length; col++) {
      if (allValues[row][col] === true && allBackgrounds[row][col] === targetGreen) {
        const rightCell = activeSheet.getRange(row + 1, col + 2);
        rightCell.setValue((rightCell.getValue() || 0) + 1);
        
        // Optional: Uncheck the checkbox after increment (remove // to enable)
        // activeSheet.getRange(row + 1, col + 1).setValue(false);
      }
    }
  }
}

3. Setup Steps #

1. Open your Google Sheet.
2. Go to Extensions > Apps Script to open the script editor.
3. Delete the default myFunction() code and paste both functions above.
4. Save the script (give it a name like CheckboxCounter).
5. Add the ADD +1 button:
   • Click Insert > Drawing, create a button with the text "ADD +1", then save it to your sheet.
   • Click the drawing, then the three-dot menu > Assign script.
   • Type addOneToTargetColumns and click OK.

Important Notes #

• Adjust the green color code: Use the exact hex code of your green columns. To get it, select a green cell, click Fill color > Custom and copy the hex value.
• Cross-sheet support: The onEdit trigger works automatically for every sheet in your spreadsheet—no extra setup needed.
• Optional checkbox uncheck: If you want checkboxes to reset after incrementing, uncomment the relevant lines in both functions.

内容的提问来源于stack exchange，提问作者JEFFERSON LIMA