Google Sheets - multiple increment buttons-CodePudding

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

I have gone through

CodePudding user response：

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

CodePudding user response：

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

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: