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?
- Insert a triangle that looks like a Spin Button
- Add the Google Script for the increment and decrement function
- 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!
Table of Contents
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.
- Go to the Menu Bar and click Insert. From the dropdown menu, press Drawing. The Drawing dialog box should pop open.
- Click the icon to the left of the Textbox option to insert a Shape.
- From the dropdown menu, you’ll see different categories. These include Shapes, Arrows, Callouts, or Equations. Hover your cursor to Shapes.
- Select Flowchart: Extract for an upward triangle.
Or, Select or Flowchart: Merge for a downward triangle.
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.
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.
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.
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.
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.
Plus, you can still change the Fill Color, Border Color, Border Weight, and Border Dash of the shape before clicking Save and Close.
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.
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.
- Copy the increment and decrement function codes and paste them onto the Code.gs field. Then click the Save Project icon on the toolbar.
- 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.
- Select the shapes that you have inserted.
- Click the three vertical dots in the upper right corner.
- From the dropdown menu, click Assign Script.
- 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.
You might encounter a hiccup along the way such as this Authorization Required dialog box.
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.
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.
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.
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
- 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.
- 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?
