google sheets - How to use apps script to work on mobie using a checkbox, Need to add 1 to a certain cell when the box is checke

admin管理员组

文章数量:1244272

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

Share Improve this question Follow edited Feb 16 at 15:40 Wicket 38.3k99 gold badges7777 silver badges192192 bronze badges asked Feb 16 at 7:30 uncleuhlsuncleuhls 1577 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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.

本文标签：