Home > Software design >  Google Spreadsheets - Create increment cell's value button
Google Spreadsheets - Create increment cell's value button

Time:03-31

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?

CodePudding user response:

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

CodePudding user response:

Simple Increment

function incr() {
  const ss = SpreadsheetApp.getActive();
  const cell = ss.getActiveCell();
  cell.setValue(cell.getValue()   1);
}
  • Related