Enhance Google Sheets With Apps Script PART II
Creating a clickable button in Google Sheets is as simple as:
- Creating a function
- Inserting a drawing
- Assigning the function to the drawing
*Please note that this method of creating a button in Google Sheets uses a user interface that is not available in the Sheets mobile app. Creating a button that triggers functions in the Sheets app is described in Part V of this series of tutorials.
Create a Function
Part I of this series discusses the very basics of Apps Script, how to access a spreadsheet bound script, and how to write a basic function. With this basic understanding, we can now write a more complicated function called addEmployee that prompts the user for information and relays this information to a specific tab. In this example we will add a second employee to My Awesome Company on the Employee List tab.
The following JavaScript code snippet creates an addEmployee function that prompts the user to enter a new employee along with the employee position and ID#. If the user clicks OK through all of the dialogue boxes, then the new employee?s information is added as a new row in the Employee List tab. If the user clicks ?Cancel? or the ?X? at the top of any of the dialogue boxes, then the user is alerted that it was cancelled and the employee?s information will not be added.
Create the Clickable Button
Now that the function has been created, its time to create the button. Creating the clickable button is as simple as clicking ?Drawing? under Insert.
Inserting a shape of your choice and a text box within the shape will create the appearance of your button. This is customizable to be as creative as you want your button to look 🙂
Assign the Function to the Button
Clicking ?Save & Close? will add your drawing to your spreadsheet and you can place it wherever you want. After inserting the button you can assign the previously created addEmployee function to your button by typing the function name into the dialogue box after clicking ?Assign script??.
Now that your button is assigned to your addEmployee function, clicking on your button will run the user through its prompts and add the new employee to the Employee List sheet.
Functional Button Video
Create Button In Google Sheets
As mentioned before, this method of clickable button does not function in the Sheets mobile apps, but luckily there is a workaround to create a functional button in the mobile app versions of Sheets as described in Part V.
Even though we are still only skimming the surface of Apps Script?s potential, I hope that this helps with understanding the basics of how Apps Script works. Feel free to copy and paste the code snippet into your spreadsheet?s script editor and impress your friends (or your boss)! The results of the user input could also be dumped into a tab in a completely different workbook as well. That way one person could have control of the database workbook, but share the input workbook with others, prompting for them to add more data.
I truly hope you learned something from this article and I encourage you to give it a try! If the code part of it seems overwhelming, I promise you that the language and the API are easy to catch on to and is nowhere near the difficulty level that it may appear.
Part III will demonstrate adding the data to a separate spreadsheet and how to add your button as part of your spreadsheet?s user interface.
Please feel free to comment if you have any questions or suggestions for future articles! To learn more about what I do and how we can work together, find out here.
Thank you!
