How to Insert Spin Button in Google Sheets (+1 Increment Buttons)

Recently, I was working on creating a leave tracker template in Google Sheets.

One of the interactive features that I wanted to create was a spin button (or scroll bar) functionality, where the user can simply click on it and the value of a cell change.

While it's easy to insert and use spin buttons or a scroll bar in Excel, unfortunately, the same is not available in Google Sheets.

So I had to take the longer route of using two shapes and adding some Google Script at the back end.

This is what I created using it:

Demo of spin button in Google Sheets

Click here to access this Google Sheets document!

In this tutorial, I will show you the exact steps that I followed to create this kind of spin button functionality in Google Sheets.

Spin Button in Google Sheets

There are three parts to creating a spin button in Google Sheets:

  1. Inserting triangles that look like Spin Button
  2. Writing the Google Script
  3. Assign the Google Script to the shape

Inserting triangles that look like Spin Button

Here are the steps to insert shapes (triangles) in Google Sheets:

  1. Go to the Insert tab.insert Button in Google Sheets Ribbon
  2. Click on ‘Drawing'.Insert Drawing to create spin buttons scroll bar in Google Sheets 2
  3. In the Drawing dialog box, click on the Shape icon.Shape Icon in Drawing boc xdialog box
  4. Go to ‘Shapes' and select the shape that you want to insert. I have used ‘Flowchart: Extract' and ‘Flowchart: Merge'.Insert triangle shape to create spin buttons scroll bar in Google Sheets
  5. Click anywhere in the dialog box working area and it will insert the triangle. You can resize and format the shape here.
  6. Click on Save and Close.

This will insert the shape in the Google Sheets.

You can resize it and place it where ever you want on the sheet.

The result of this section would be something as shown below. Here I have the two triangles (the yellow cell is the one I will link to these triangles in the next two sections).

Triangles inserted in Google Sheets

Writing the Google Script

Now we need to add a Google Script code for each button.

To do this, we will create two functions – ‘Increment' and ‘Decrement'.

Here are the codes for each function:

Increment by 1:

function increment() {
 SpreadsheetApp.getActiveSheet().getRange('C5').setValue(SpreadsheetApp.getActiveSheet().getRange('C5').getValue() + 1);
}

Decrement by 1:

function decrement() {
 SpreadsheetApp.getActiveSheet().getRange('C5').setValue(SpreadsheetApp.getActiveSheet().getRange('C5').getValue() - 1);
}

Here are the steps to add these scripts to the backend of Google Sheets:

  1. Go to the Tools tab.Tools in the ribbon Google Sheets
  2. Click on Script Editor.Script Editor to insert Scripts in Google Sheets
  3. In the Script Editor backend, copy paste both the function in the Code.gs window.Code in backend of Google Sheets
  4. Click on the save icon.Save the script to create spin button - scroll bar in Google Sheets
  5. Close the Script Editor.

The above steps create two new functions in the Google Sheets.

Now we need to assign these functions to the shapes (triangles) that we inserted.

Assign the Google Script to the shape

Here are the steps to assign a script to an object:

  1. Select the triangle to which you want to assign the increment function.
  2. Click on the three dots at the top right of the triangle shape.
  3. Click on assign script.Assign Script to the shape
  4. In the Assign Script dialog box, manually enter the name of the function (‘increment' in this case).Assign the custom function to the shape
  5. Click OK.

Repeat the same process for the other triangle, and assign ‘decrement' script to it.

That's it!

Now when you click on the triangle with ‘Increment' function assigned to it, it will increment the value in cell C5 by 1. Similarly, clicking on the other triangle will decrement in the value in cell C5 by 1.

Demo of spin button in Google Sheets

Click here to access this Google Sheets document!

Additional Notes:

  • You can assign the script to any shape using the above steps.
  • While running a script, it may take Google Sheets a couple of seconds to execute the script. You may see a ‘Running Script' prompt when it's running. once done, you will see a ‘Finished Script' prompt'.Spin Button in Google Sheets - scroll bar - running script

You May Also Like the Following Tutorials:

  • >