-1

Let's say I have in A1 some value - say 3, and I want to have a button when I click on it then 3 is incremented by 1 and as a result A1 becomes 4. How can I do this?

As much as I know I can do Insert -> Drawing and design the button view. Then I can assign a script to it as shown in the image below.enter image description here But I don't know what script to write to increment the target cell - A1. Would you please help?

Narek
  • 38,779
  • 79
  • 233
  • 389

2 Answers2

2

Description

You would need to create a function in script editor like the one I have shown below.

After assigning this function to the button, when the user presses the button the value in cell A1 is incremented and placed back in cell A1.

I may be long winded in using SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1") but that's the way I like to do it.

The Script Editor is available from the menu bar Extensions -> App Script.

Another way to use the button function is, if the button is in cell B1 and the cursor is in cell H22 (random), the active cell is the selected cell H22 and the value in H22 will be incremented.

Script (original)

function buttonFunction() {
  let range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1");
  let value = range.getValue();
  value++;
  range.setValue(value);
}

Script (modified)

function buttonFunction() {
  let sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let range = sh.getActiveCell();
  let value = range.getValue();
  value++;
  range.setValue(value);
}

Reference

TheWizEd
  • 7,517
  • 2
  • 11
  • 19
  • This is good, thanks. But can I assign `buttonFunction` function to the Drawing as a script and also pass the cell name I want to modify. In other words, now `A1` is hardcoded in `buttonFunction`. Can I make it configurable from the script call point? – Narek Mar 30 '22 at 16:51
  • Since the active cell is the button cell that was clicked you would need to open a dialog and ask the user which cell they want to modify. – TheWizEd Mar 30 '22 at 16:53
  • Sorry, I don't get what you mean. – Narek Mar 30 '22 at 16:55
1

Simple Increment

function incr() {
  const ss = SpreadsheetApp.getActive();
  const cell = ss.getActiveCell();
  cell.setValue(cell.getValue() + 1);
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • And how to I assign this script to the drawing so that when I click that drawing it changes the value of `A1`? – Narek Mar 30 '22 at 16:47
  • Right click on the crawing and click on the three dots. Type in function name only – Cooper Mar 30 '22 at 16:53
  • I increments the cell it is selected at the moment. Can I pass cell name to the Drawing Script too? Say here is your script: `incr(J7)` and it will increment `J7`? – Narek Mar 30 '22 at 16:57
  • You cannot pass parameters to a script by clicking on a button or menu selection. If you wish to make changes to the script feel free. – Cooper Mar 30 '22 at 16:59