Cara menggunakan google sheets button

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:

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.

This Article Covers:

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.
   
2. Click on ‘Drawing’.
   
3. In the Drawing dialog box, click on the Shape icon.
   
4. Go to ‘Shapes’ and select the shape that you want to insert. I have used ‘Flowchart: Extract’ and ‘Flowchart: Merge’.
   
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).

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 Number by 1:

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

Decrement Number 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.
   
2. Click on Script Editor.
   
3. In the Script Editor backend, copy paste both the function in the Code.gs window.
   
4. Click on the save icon.
   
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 the assign script.
   
4. In the Assign Script dialog box, manually enter the name of the function (‘increment’ in this case).
   
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 number value in cell C5 by 1. Similarly, clicking on the other triangle will decrement in the value in cell C5 by 1.

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’.
  

So this is how you can create a spin button in Google Sheets (some people also call it a spinner) and use it to increment/decrement the value by a specific margin.

Want to become a Google Sheets expert? We highly recommend checking out Udemy’s Google Sheets Comprehensive Masterclass and the Complete Google Sheets Course.

Sumit

Spreadsheet Expert at Productivity Spot |+ posts

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

• Sumit

  https://productivityspot.com/author/sumitbansal23/

  3 Easy Ways to Set Print Area in Google Sheets

• Sumit

  https://productivityspot.com/author/sumitbansal23/

  How to Make a Line Graph in Google Sheets (Step-by-Step)

• Sumit

  https://productivityspot.com/author/sumitbansal23/

  How to Split Text to Columns in Google Sheets (with Examples)

• Sumit

  https://productivityspot.com/author/sumitbansal23/

  How to Convert Time to Military Time Format in Google Sheets?

Popular Posts

How to Duplicate a Google Sheet: Quick & Easy Guide

Six Sigma Certification: The Ultimate Guide

How to Use the DATE Google Sheets Function [Easy Examples]

How to Find a WiFi Password on an iPhone [5 Easy Ways]

8 thoughts on “How to Insert Spin Button in Google Sheets (+1 Increment Buttons)”

1. Nicole Burman

   This is great but how do I do it for many cells on the same sheet. I have tried creating the triangles and then changing the cell in the code but it only seems to work for one cell?

   Thanks.

2. Clarissa A Pelly

   How can I do this to any active cell on a sheet? Do i have to create an arrow for each cell?

   • LaN

     Is this something you want? It will increase the active cell’s value by 1.

     function increment() {
     var spreadsheet = SpreadsheetApp.getActive();
     var sheet = spreadsheet.getActiveSheet();
     var cellRange = sheet.getActiveCell();
     var selectedColumn = cellRange.getColumn();
     var selectedRow = cellRange.getRow();
     Logger.log(`selectedColumn: ${selectedColumn}`);
     Logger.log(`selectedRow: ${selectedRow}`);
     Logger.log(`selected cell vale: ${cellRange.getValue()}`);
     cellRange.setValue(cellRange.getValue() + 1);
     }

3. Fernando Jibaja

   This is great!

   So if I wanted to hace this function for multiple cells…would I need to creat a new script for each cell it will count on?

4. John Jones

   Quick Question. This is a good tutorial and was able to follow easily. I have a question though. If I create a simple counter to increment and decrement a value, can it be used that way on a mobile device? I tried it by accessing on my iphone and when I tap the triangle it doesn’t run the script, just selects the triangle. Thanks,

5. Lew

   When running the script the message “Running script” appears, then the message “Finished”. Can we prevent this from appearing?

   Bagaimana cara menggunakan Google Spreadsheet?

   Berikut adalah cara membuat spreadsheet di Google Sheets dengan mudah:.

   Buka Google Sheets melalui alamat sheets.google.com. ... .

   Klik tombol '+' bertuliskan Blank yang ada di bagian atas kiri. ... .

   3. Edit dan format spreadsheet. ... .

   4. Simpan ke komputer, print atau bagikan. ... .

   3 Tips atau Skill Free Fire yang Wajib Diketahui oleh Pemula..

   Bagaimana cara membuat link Spreadsheet?

   Link ke data di spreadsheet.

   Di Spreadsheet, klik sel tempat tujuan Anda ingin menambahkan link..

   Klik Sisipkan. Link..

   Di kotak Link, klik Pilih rentang sel untuk ditautkan..

   Tandai sel atau rentang sel yang ingin ditautkan. Pindahkan jendela Data yang mana? ... .

   Klik Oke..

   (Opsional) Ubah teks link..

   Klik Terapkan..

   Fitur apa saja yang ada di Google sheet?

   Simak beberapa fitur unggulan Google Sheets berikut ini..

   Kolaborasi bersama tim / pengguna lain. Google Sheets memungkinkan pengguna untuk melakukan kolaborasi bersama pengguna lain. ... .

   Terdapat riwayat revisi. ... .

   Melakukan editing secara real-time. ... .

   Integrasi dengan Google Forms. ... .

   Bagaimana cara wrap text di Spreadsheet?

   Klik menu “Format”, maka akan muncul ragam pilihan fitur lain yang bisa kamu pilih sesuai kebutuhan. Arahkan panah mouse atau jari kamu ke bagian bawah. Temukan fitur “Wrap Text”. Jika Spreadsheet kamu menggunakan Bahasa Indonesia, maka nama fiturnya akan berubah menjadi “Pengemasan Teks”.