Spent many hours creating an online scoresheet in excel for a Pool League just to find out I cannot run Macros on mobile. Enter Google sheets. I am having a difficult time creating a script that would add 1 to the value in a12 when a checkbox is checked in a14. The sheet is currently named sheet1 Everything works as planned except it runs when ANY cell is changed. How do I change the code so it works when only the check box in a14 is checked/unchecked?

function onEdit(e) {
  // Define the cell that triggers the change (e.g., A1)
  var triggerCell = 'A14';
  
  // Define the cell to increment (e.g., B1)
  var incrementCell = 'a12';

  // Get the active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get the range of the edited cell

  var editedCell = ss.getRange("a14")

  // Check if the edited cell is the trigger cell

  if (editedCell.getA1Notation() === triggerCell) {

    // Get the current value of the increment cell

    var currentValue = ss.getRange(incrementCell).getValue();
    
    // Increment the value by 1

    var newValue = currentValue + 1;
    
    // Set the new value to the increment cell

    ss.getRange(incrementCell).setValue(newValue);
  }
}

Spent many hours creating an online scoresheet in excel for a Pool League just to find out I cannot run Macros on mobile. Enter Google sheets. I am having a difficult time creating a script that would add 1 to the value in a12 when a checkbox is checked in a14. The sheet is currently named sheet1 Everything works as planned except it runs when ANY cell is changed. How do I change the code so it works when only the check box in a14 is checked/unchecked?

function onEdit(e) {
  // Define the cell that triggers the change (e.g., A1)
  var triggerCell = 'A14';
  
  // Define the cell to increment (e.g., B1)
  var incrementCell = 'a12';

  // Get the active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get the range of the edited cell

  var editedCell = ss.getRange("a14")

  // Check if the edited cell is the trigger cell

  if (editedCell.getA1Notation() === triggerCell) {

    // Get the current value of the increment cell

    var currentValue = ss.getRange(incrementCell).getValue();
    
    // Increment the value by 1

    var newValue = currentValue + 1;
    
    // Set the new value to the increment cell

    ss.getRange(incrementCell).setValue(newValue);
  }
}

• google-sheets
• google-apps-script

1 Answer 1

To get the edited cell, use the range property of event object e:

var editedCell = e.range;

To check whether checkbox is checked, use .isChecked():

if (editedCell.getA1Notation() === triggerCell && editedCell.isChecked()) {

Also, .uncheck() it inside the if block after processing.