The spin button is a type of scroll bar function. By using the spin button, one can change the cell value by clicking on it. However, this spin button function is not available in Google Sheets as it exists in Excel. Thus to overcome this issue, we can use the Google Script editor. In this article, let us understand how to create a spin button with the help of Google Sheet tips provided on this page.
|
Table of Contents |
How to Create Spin Button in Google Sheets?
There are three parts in creating the Spin button in Google Sheets and they are:
- Insert triangles like the Spin button.
- Drafting the Google Script.
- Assigning the Google Script to the shape triangle.
How to Insert Triangles Like Spin Button in Google Sheets?
The steps to insert Triangle like Spin button in Google Sheets are given below:
- Step 1: Open the Google Sheets.
- Step 2: Click on the “Insert” tab from the menubar.
- Step 3: Now select “Drawing” from the drop-down menu.
- Step 4: Here the Drawing dialog box will open on the screen. Now click on the “Shape” icon.
- Step 5: Select the shape you want to insert from the ‘Shapes‘ menu. ‘Flowchart: Extract‘ and ‘Flowchart: Merge‘ were the tools used.
- Step 6: The triangle will appear wherever you click in the dialogue box’s working area. The shape can be resized and formatted here.
- Step 7: Save and close the window.
This will add the shape to Google Sheets.
Now you can resize the image by clicking on the image inserted in the Google Spreadsheet.
- How To Insert Indents in Google Sheets? – Know How to Tab Down in Google Sheets
- How to Extract the Year from Google Sheets-YEAR Function in Google Sheets
- How to Make an Organization Chart in Google Sheets? – Create an Org Chart in Google Sheets
Adding Google Script to the Triangle Icon Inserted in Google Sheets
For each button, we must now add a Google Script code. We will do this by creating two functions: increment and decrement.
The codes for each function are as follows:
Increment Number by 1 Script Code
| function increment() {
SpreadsheetApp.getActiveSheet().getRange(‘d10’).setValue(SpreadsheetApp.getActiveSheet().getRange(‘d10’).getValue() + 1); } |
Decrement Number by 1 Script Code
| function decrement() { SpreadsheetApp.getActiveSheet().getRange(‘d10’).setValue(SpreadsheetApp.getActiveSheet().getRange(‘d10’).getValue() – 1); } |
Now we need to add these script codes to the shapes which we have created in Google Sheets. The steps to add the Script Codes to the shapes are listed below:
- Step 1: Click on the “Tools” tab in the menubar.
- Step 2: Now on the homepage, click on the “Script Editor” from the drop-down menu.
- Step 3: Copy and paste both functions into the Code.gs window from the Script Editor backend.
- Step 4: Click on the “Save” icon. Now close the Script Editor.
In Google Sheets, the above steps will create two new functions.
Now we must assign these functions to the inserted shapes (triangles).
Assign Google Script to the Shapes Created in Google Sheets
The steps to assign a script to the shape in Google Sheets are given below:
- Step 1: Click on the Triangle shape which we have created in Google Sheets to assign the increment functions.
- Step 2: Click on the three dots in the triangle’s top right corner.
- Step 3: Select the assign script option.
- Step 4: In the Assign Script dialogue box, type the name of the function. In our case, we are using the name “Increment“.
- Step 5: Now click on another Triangle shape and choose three dots from the triangle’s top left corner.
- Step 6: Choose assign script and type the function name in the script dialogue box. In our case, it is decrement.
That’s it, the spin button is created in the Google Sheets. When you click on the triangle with the ‘Increment’ function assigned to it, the numerical value in cell d10 will be increased by one.
Likewise, clicking on the other triangle will reduce the value in cell d10 by one.
