CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mysterious Otter
Mysterious Otter

Reputation: 4029

Increment Cell value by one by clicking

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.

Upvotes: 25

Views: 81884

Answers (3)

Marios
Marios

Reputation: 27348

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:

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.

Upvotes: 11

permafrost91
permafrost91

Reputation: 73

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);
}

Upvotes: 6

Shreyas Kapur
Shreyas Kapur

Reputation: 679

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.

Dropdown Menu

Result

Upvotes: 19

Related Questions