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.

Shreyas Kapur · Answer 1 · 2015-09-28T06:01:50.837

18

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.

edited Sep 28 '15 at 06:01

answered Sep 28 '15 at 05:54

Shreyas Kapur

669
4
15

12

This is for a specific cell, 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? – Marlon Mar 19 '19 at 02:33
1

@Marlon If you are still looking for a solution or rather a workaround, I posted an answer which might be useful for you. – Marios Nov 23 '20 at 21:12
1

Thanks for putting this out there but you left off one key detail - where do I save this function. Thanks for the guidance. Nothing is obvious. – rashadb Jun 20 '21 at 01:52

score 11 · Answer 2 · edited Jul 18 '21 at 09:04

11

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:

There is a small delay between the clicks and the updates.
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.

edited Jul 18 '21 at 09:04

MohitC

4,541
2
34
55

answered Nov 23 '20 at 21:09

Marios

26,333
8
32
52

I think a checkbox+edit trigger approach would be a better simulation of a button. – TheMaster Jan 20 '21 at 20:58
1

@TheMaster in the scenario I present in this answer yes. But if you put a button image in every cell then you can have something more fancy. You are not restricted to checkboxes. But I agree, for the scenario that I show, an `onEdit` would be more suitable. – Marios Jan 20 '21 at 21:06
@soMario: I've tried to use the script, but parameter **e** is undefined and generates error message "*TypeError: Cannot read property 'source' of undefined Information*". You cannot set this parameter when binding the script with a drawing (button) so I wonder where it comes from. – Kalle Svensson Dec 24 '21 at 14:16
@KalleSvensson I would advice you to just Google this error message. There are **hundreds** of stackoverflow questions dedicated to this. Nevertheless, triggers are not meant to be ran manually as you did. They are executed upon user actions in this case when you select a cell. Also that is mentioned in my answer itself. And also there is a video that explains how to use it. Did you see me in the video run the function manually ? – Marios Dec 24 '21 at 18:18
1

@soMario: You are right, the script works when selection changes. You have to refresh the sheet to get it work. There is no Refresh button in my sheet, so this is tricky. In your first answer you assign the script to a drawing so I followed this and got the problem. But I modified the script using **SpreadsheetApp** and removing the parameter and had it work too. – Kalle Svensson Dec 25 '21 at 09:53

score 5 · Answer 3 · answered Nov 08 '20 at 16:44

Assign the following to a drawing inside your sheet and it will increase the value of the currently selected cell by 1:

function plusOne() {
 
  var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var value = cell.getValue() * 1;
  cell.setValue(value+1);
}

Increment Cell value by one by clicking

3 Answers3

Update 2020

Motivation:

Solution:

Demonstration:

Restrictions:

Linked

Related