Google Sheets - multiple increment buttons

Question

I would like to add buttons to a Google Sheet that allow me to increment or decrement a cell value.

I have gone through tutorials like the one here, and answers on Stack Overflow like the one here, however each of these talks about manually drawing shapes and assigning code to those shapes.

My situation has 2 problems with this approach:

I want to be able to add increment and decrement buttons to lots of cells without having to draw and encode each button.
The drawing tools in Google Sheets is fairly basic and redrawing the same sized button each time is proving to be difficult at best.

In trying to come up with a solution to this situation I have considered a few things. The "buttons" don't have to look like buttons for my purposes, if I can trigger code by clicking on a cell this will suffice.

Hopefully there is a programatic way to create these buttons in Google Sheets, that would be ideal. However at this point I would just settle for better drawing tools so I can at least have identical shaped buttons.

Is there a better approach to adding multiple increment buttons to Google Sheets?

Would you mind using the sidebar and making the button like this `` and it increments the value of the current selected cell by one if it is a number. You can use the same button on all pages — Cooper, Aug 29 '22 at 22:35

Cooper · Answer 1 · 2022-08-29T23:05:56.963

Using the SideBar

Works on any page

Apps Script:

function increment() {
  const ss = SpreadsheetApp.getActive();
  const cell = ss.getActiveCell();
  const v = cell.getValue();
  if (!isNaN(v)) {
    cell.setValue(Number(v) + 1);
  }
}

function showMySidebar() {
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("sidebar"))
}

HTML: (sidebar.html)

<!DOCTYPE html>
<html>
  <head>
    <title>Sidebar Buttons</title>
  </head>
  <body>
    <input type="button" value="increment" onClick="google.script.run.increment();" />
  </body>
</html>

Demo:

With what you have here adding the decrement should be a breeze

PatrickdC · Answer 2 · 2022-08-30T01:51:49.777

Alternative Solution #1: Register Scripts as Macros

You may bind scripts as Google Sheets macros for faster results. For a quick guide on macros with Google Apps Script, you may view the Google Sheets Macro page.

Script

You may bind the following scripts:

function increaseValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var values = ss.getActiveSheet().getActiveRange().getValues();
  var out = values.map(x=>x.map(y=>parseInt(y)+1));
  ss.getActiveSheet().getActiveRange().setValues(out);
}

function decreaseValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var values = ss.getActiveSheet().getActiveRange().getValues();
  var out = values.map(x=>x.map(y=>parseInt(y)-1));
  ss.getActiveSheet().getActiveRange().setValues(out);
}

Output:

For the script's function, I bound them to ctrl+alt+shift+1 and ctrl+alt+shift+2 macros.

NOTE: As what can be seen in the demonstration, the script would return #NUM! when the cell is empty.

Alternative Solution #2: You may use Check Boxes

I noticed that you can automate the addition and removal of check boxes using the insertCheckboxes() and removeCheckboxes() functions, respectively. So I had this idea wherein you may use check boxes as a substitute for buttons if you want to generate multiple check boxes.

Script

For this, an onEdit() script (which you may use as the basis for your code) is used.

function onEdit(e) {
  var column = e.range.getColumn();
  var row = e.range.getRow();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (column == 1) {
    try {
      if (e.value.length > 0) {
        ss.toast('Adding Checkboxes');
        ss.getActiveSheet().getRange(row,2,1,2).insertCheckboxes();
      }
    }
    catch {
     ss.toast('Removing Checkboxes');
     ss.getActiveSheet().getRange(row,2,1,2).removeCheckboxes();
    }
  }
  else if (column == 2) {
    var value = ss.getActiveSheet().getRange(row,1,).getValue() + 1;
    ss.toast('Increase Value');
    ss.getActiveSheet().getRange(row,1,).setValue(value);
  }
  else if (column == 3) {
    var value = ss.getActiveSheet().getRange(row,1,).getValue() -1;
    ss.toast('Decrease Value');
    ss.getActiveSheet().getRange(row,1,).setValue(value);
  }
}

Output

I have made a test case for the script wherein a user will add numerical values to column A which triggers the generation of check boxes in its corresponding columns B and C.

Removing a value in column A would remove the check boxes.

Changing the values of the check boxes from false to true and vice versa will trigger the increment or decrement of values (as seen below):

References:

Feel free to ask if you have other questions. – PatrickdC Aug 30 '22 at 01:47 — PatrickdC, Aug 30 '22 at 01:47

Google Sheets - multiple increment buttons

2 Answers2

Using the SideBar

Alternative Solution #1: Register Scripts as Macros

Script

Output:

Alternative Solution #2: You may use Check Boxes

Script

Output