Using JavaScript in Google Sheets
If you’re a user of Google Sheets you may not know that you can actually use JavaScript to make your sheets more powerful. On top of that, built in things like scriptable buttons help make your sheets less like your standard excel sheet and more like a customizable app.
While working in the auto industry, I realized my company needed an inexpensive way to collaborate. The buyers needed to log their cars and the office needed this information to track the location and profit margins of each car.
They were already using google sheets. So instead of reinventing the wheel, I decided to see what Sheets was capable of.
As it turns out, I was able to give buyers their own sheet where they could log their cars with a click of a button. This would automatically update a master sheet that the office had access to.
Getting Started
Finding the Script Editor:
To find the Script Editor simply click the Tools menu and click Script Editor. With this you will be brought to the script editor where you can start coding in Javascript.
Referencing the Spreadsheet Object
Google sheets uses Objects that can be manipulated with JavaScript. The first thing you will probably need to do is get a reference to the spreadsheet object.
let ss = SpreadsheetApp.getActiveSpreadsheet()From this Spreadsheet object we can dig deeper and find some of the objects we want to really manipulate. In a lot of documentation you will see the Spreadsheet object saved to a variable called “ss”.
From “ss” we can now find the tab we want to manipulate:
let firstTab = ss.getSheets()[0];“getSheets()” returns an array of all the sheets within the Spreadsheet.
Now if you type something into the first cell we can dive even deeper into.
Logger.log(firstTab.getRange('A1').getValue())Your console should return whatever you typed in to that first cell.
Head to “View” > “Logs” menu bar. (See “Using the Log” section for more info)
To get values in a range. use the “getValues()” function.
console.log(firstTab.getRange('A1:G1').getValues());Using the Log
Google sheets has two ways to log/debug. One is Logger. Which is the built in Logger object that you can use to see any logs you might log. (Tongue twister) The other is more complicated and not worth the time for this basic tutorial.
To use this type Logger.log(“Insert note here”). It works similar to console.log. To see the logs simply go into the script editor and click “View” > “Logs” to see your logs.
Using Buttons
To use a button, simply choose Insert > Drawing in the menu. The shape will act as your button. To assign the button a function, click the buttons menu and in the add function slot type in the name of your function.
Classes? Semicolons?
Unfortunately, Google sheets scripts does not allow you to create classes in this version.
I’ve found that you should use semicolons otherwise things just go haywire.
Alerts
Different than your typical javascript alert is the built in UI class. Grab a reference to the UI class for the alert. Here it takes a 3 parameters to create a Yes or No alert box that returns true or false.
var ui = SpreadsheetApp.getUi();var result = ui.alert(
'Please confirm',
'Are you sure you want to continue?',
ui.ButtonSet.YES_NO);
Creating Menus
Creating menus in Google Sheets is relatively easy.
Use the onOpen function to write code that will fire on open of the Spread Sheet. Use the “getUi()” again to get a reference to the Ui class. Then use the createMenu function. It takes the menu name. Then chain some items. The first parameter on .addItem is the item name. The second is the function you want to be .
function onOpen() {
let ui = SpreadsheetApp.getUi();
ui.createMenu('Flatiron')
.addItem('Check For New Vehicles', 'helloWorld')
.addToUi();
}Just the Beginning
What I’ve shown you is just the beginning! Take a look at the documentation to see what else is possible.
Thanks for reading!
The Documentation
https://developers.google.com/apps-script/reference/spreadsheet
Follow me @thedrewprint on Twitter
