Increment Cell value by one by clicking

Question

I want to write a formula in Google sheets so that I can attach to a button to make the value in a cell increase by 1 each time you click it. Here is the best I could find on this topic.

I attempted to do this, but to no avail. I am using Windows 7 and Chrome.

Answer 1

First create a script to increment the cell value. For instance, to increment cell "A1" by one the following script would work:

function increment() {
  SpreadsheetApp.getActiveSheet().getRange('A1').setValue(SpreadsheetApp.getActiveSheet().getRange('A1').getValue() + 1);
}

Save this script and open your spreadsheet and follow these steps:

• Go to "Insert" > "Drawing" and draw a button using shapes and text.
• Position your button accordingly, and right-click on it to display an arrow on the side of it.
• In the drop-down menu, select "Assign Script." and type the name of your function. (In this case "increment")
• The first time when you click on it, it'll ask for permission but should work subsequently.

Answer 2

Update 2020

Motivation:

I would like to provide a different approach based on Marlon's comment:

what if we want to create multiple buttons to different rows and each button modifies its own row? Is there a way not create this manually?

Solution:

You can use onSelectionChange to capture single click events and mimic the behaviour of a button.

• The following script will increment the value of a cell in column A upon clicking on a cell in column B of the same row.

• It also creates a "button" text on the fly but this is optional and up to the user.

To use this solution, simply copy & paste it to the script editor and save the changes. After that, it will automatically be used upon single click events. In this solution I used Sheet1 as the sheet name.

function onSelectionChange(e) {
  const as = e.source.getActiveSheet();
  const col = e.range.getColumn();
  const row = e.range.getRow();
  if (as.getName() == 'Sheet1' && col == 2){ 
        const range = as.getRange(row,1);
        range.setValue(range.getValue()+1);
        e.range.setValue('Button');
  }
}

Demonstration:

Restrictions:

While this approach works pretty well, there are two drawbacks:

1. There is a small delay between the clicks and the updates.

2. The trigger is activated when selecting a cell. If a cell is already selected, if you click it again the function won't trigger. You have to remove the current selection and then select it again.