How to Insert a Spin Button in Google Sheets

Spin Button in Google Sheets

A spin button in a worksheet lets you quickly change and select new values for a cell. You can do this by clicking the button. Unfortunately, this feature is only available in Microsoft Excel. But, Sheets is a worksheet app. So, how can you insert a spin button in Google Sheets?

  1. Insert a triangle that looks like a Spin Button
  2. Add the Google Script for the increment and decrement function
  3. Assign the Google Scripts to the triangles

To help make things easier to follow along, you may make a copy of the spreadsheet I’ve linked below.

If you’re ready, let’s go!

Inserting Triangles That Look like Spin Button in Google Sheets

The closest shape you can use in Google Sheets to use as the Spin Button is a triangle. To insert this shape, follow the steps below.

  1. Go to the Menu Bar and click Insert. From the dropdown menu, press Drawing. The Drawing dialog box should pop open.

Spin Button in Google Sheets

  1. Click the icon to the left of the Textbox option to insert a Shape.

Spin Button in Google Sheets

  1. From the dropdown menu, you’ll see different categories. These include Shapes, Arrows, Callouts, or Equations. Hover your cursor to Shapes.

Spin Button in Google Sheets

  1. Select Flowchart: Extract for an upward triangle.

Spin Button in Google Sheets


 Or, Select or Flowchart: Merge for a downward triangle. 

Spin Button in Google Sheets


Of course, you may also choose a different shape as you need. For instance, let’s say you want to insert a shape that resembles a scroll bar. Select Rectangle from the Shape options. 

Spin Button in Google Sheets


5. Click anywhere on the Drawing dialog box to insert the shape you have previously selected. You can’t insert two shapes at once. So, you’ll have to select one first, then click on the dialog box to insert it before you get to the next shape.

Triangle

6. If you’re satisfied with the shape’s appearance, then you can just click the Save and Close button at the upper right corner of the Drawing dialog box. 

Save and Close

 

Otherwise, select the shape you’d want to edit. You can directly click on the shape or press the Cursor icon from the toolbar.

 

Customizing the Spin Button (or Scroll Bar) in Google Sheets

Let’s take the Rectangle shape for your scroll bar.

When you insert it in the Drawing dialog box, it looks like this.

Square

 

Definitely, not a rectangle, right? Click on one of the small blue squares and drag it across the screen until it looks like the shape you want to insert. 

Rectangle

 

Plus, you can still change the Fill Color, Border Color, Border Weight, and Border Dash of the shape before clicking Save and Close. 

Customize Shape

 

For example, you might want to change the Fill Color of the upward triangle to Green. Or the downward triangle’s to Red. This is to reflect the increase or decrease in the values of your selected cell.

Spin Button and Scroll Bar shapes

 

Once you’ve clicked Save and Close, the shapes should already be inserted into your Google Sheets. Easy, right? But we’ve only just added the shapes for your Spin Button in Google Sheets. It doesn’t have the same function as the real thing yet.

 

 

Adding the Google Script to the Triangles that look like Spin Button

What can actually make this triangle function the same way as Spin Button in Excel does is its Google Script. An Apps Script is capable of writing menus, dialogs, functions, and even macros in Google Sheets and other Google Apps. It might seem intimidating at first. But if you have the codes for the function you want to execute, it shouldn’t be a problem.

Remember that a spin button gives you a range of values by clicking the arrows up or down. So, you’ll need the increment and decrement functions for your upward and downward triangles, respectively.

Here are the function codes that you need to write in.

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);
}
  • Go to the Menu Bar and click Extensions. From the dropdown menu, click Apps Script. You should be redirected to a new tab on your browser.
    Apps Script
  • Copy the increment and decrement function codes and paste them onto the Code.gs field. Then click the Save Project icon on the toolbar.
    increment and decrement codes
  • Close the Apps Script tab. 

Now, you have added the script to the function you want your triangles to execute. 

 

 

Assigning the Google Script to the Triangles that Look like Spin Button

The last step to mimicking the Spin Button in Google Sheets is to assign these scripts to the shapes.

  1. Select the shapes that you have inserted.
  2. Click the three vertical dots in the upper right corner.Three vertical dots
  3. From the dropdown menu, click Assign Script.Spin Button in Google Sheets
  4. On the Assign Script dialog box, type in the script you want to assign. Make sure to copy exactly how you’ve inputted the function to your script as it is case-sensitive.

Spin Button in Google Sheets

 

You might encounter a hiccup along the way such as this Authorization Required dialog box.

Spin Button in Google Sheets

5. When you see this, just click Continue because it’s just basically asking your permission to run the script you’ve written. You will then be asked to sign in to your Google Account.

Google Sign in

6. Choose the account you’re using for your Google Sheets. When you see the dialog box that says “Google hasn’t verified this app”, click Advanced.

Google verification

7. Make sure that the developer that Google is referring to is your email address. If it is, then, click Go to Spin Button. Otherwise, you may choose to repeat the process and use your trusted email address to access your Google Sheets and Apps Script.

Go to Spin Button

 

IMPORTANT: Clicking the Go to Spin Button allows Spin Button to see, edit, create and delete all your Google Sheets spreadsheets. 

 

 

You Might Also Ask

  1. What is the difference between a spin button and a scroll bar?

A spin button gives you a range of values by clicking the arrows up or down. On the other hand, you can change the cell value by moving the scroll bar to the left or to the right.

  1. Which is better to use?

You should determine which function suits your needs best. For example, if you only need a rough estimate for a value, say Percentages, you may use a scroll bar. It can provide greater intervals that you can still work around with. But, if you’re looking for exact number values, like Age perhaps, then you might find spin buttons to be more fitting. 

Just a little bit more resizing and the triangles that look like Spin Buttons are ready for use on your Google Sheets. 

So, are you ready to give it a try?

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like