Recently, I was working on creating a leave tracker template in Google Sheets. Show 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.
There are three parts to creating a spin button in Google Sheets:
Inserting triangles that look like Spin ButtonHere are the steps to insert shapes (triangles) in Google Sheets:
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 ScriptNow 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:
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 shapeHere are the steps to assign a script to an object:
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:
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.
Sumit
Spreadsheet Expert at Productivity Spot | Website | + posts Google Sheets and Microsoft Excel Expert. Apa saja yang bisa dilakukan dengan Apps Script?Apps Script mendukung pengguna untuk berfokus pada hal-hal penting dan mengoptimalkan Google Workspace dengan mengotomatiskan tugas yang membosankan dan memakan waktu, menciptakan integrasi yang menghubungkan aplikasi yang dipakai sehari-hari, serta menambahkan fungsi atau penyesuaian yang dibutuhkan.
Apa fungsi dari formula Randbetween () di Google Sheets?Menampilkan bilangan bulat acak yang sama antara dua nilai, inklusif.
Google Script untuk apa?Google Apps Script adalah bahasa skrip berbasis cloud untuk memperluas fungsionalitas Google Apps dan membangun aplikasi berbasis cloud yang ringan. Apa artinya ini dalam praktik? Ini berarti Anda menggunakan Script Aplikasi untuk menulis program kecil yang memperluas fitur standar Google Apps.
Apa itu script editor?Editor skrip memungkinkan Anda untuk menulis kode dalam Apps Script dan menjalankan skrip tersebut di server Google. Editor skrip yang ditunjukkan di atas memiliki beberapa komponen: Sebuah judul proyek atau nama proyek naskah yang berisi script Anda. Sebuah menu atau daftar operasi yang berkaitan dengan proyek Anda.
|