If you are like me and you are a heavy user of google sheets, you may begin to think of ways to automate your workflow and have more control over your sheets.If you are used to using Excel, then you must be familiar with VBA which is the official language used in writing Excel Macros, Google has created its own for automating G Suites. That’s where Google Apps Script comes in, it is a scripting language for lightweight application development for Google’s products. You can use it to automate Google Docs, Sheets, Forms, Slides etc. It was written in Javascript but it stripped away many of the ‘difficult’ aspects of Javascript and made it simple to use.
In this tutorial, I aim to show you how to write your first ‘Hello World’ in Apps Script. Little programming experience is required, if you are a total newbie you can still read on or you can check out Freecodecamp or Codecademy for an introduction to javascript tutorial.
To start, we will first create a new sheet , to do that, just simply type in sheets.new into your browser. This will open an Untitled Spreadsheet. Click on Tools > Script Editor. This will open a new tab script.google.com , this is the online script editor where we will write our apps script.
There is already a pre written function called myFunction. The first thing we are going to do is give the project a title. You can name it anything, but for the purpose of this tutorial, I’m going to name it “Hello World”. Now that is done, we will write our own function.
This function prints “Hello World!” in your spreadsheet. In Javascript ‘var’ is used to declare variable. You can name your variable anything you want. . In Javascript, you end a line with ; so it is important you end your lines with a semi-colon. Another thing that you may have noticed from the above code is that it is written using camel case.
Next, we declared the variable ‘helloWorld’ that actually prints the text “Hello World!” in our spreadsheet.
Once we have written this code, we will save our code then press run and switch over to the spreadsheet to run this sheet. However, this code only runs when we run our script from the code editor. We can improve upon this code and then add a Menu on the google sheet so we can run the script on the sheet.
In the code above, we added a new function “onOpen” , this function is what enables us to run the script straight from our google sheet. First, we declare a variable called ‘ui’. ‘SpreadsheetApp’ is because we are working with google spreadsheet. You’ll use ‘DocumentApp’ if you are working with google docs. After the SpreadsheetApp, we have a “.getUi()” which simply fetches the spreadsheet Ui, it has nothing to do with our variable(you don’t have to name your variable ‘ui’ for the ‘getUi’ method to work). Now that we have assigned the variable ‘ui’ to this method. We then use “.createMenu(“Hello World”) to be able to create a menu which we will for the purpose of this tutorial name “Hello World”. The addItem method accepts two parameters a String and then the name of a function. Finally, we use ‘addToUi’ to append it to our Spreadsheet.
A common mistake might be to add the semi-colon at the end of the first line but it is important to note that I could have written the code as
ui.CreateMenu(‘Hello World’).addItem(‘Say Hello’, ‘myHelloWorld’).addToUi();So you should only put the semicolon at the very end.
Finally, we click on where the arrow is pointed at and the select the function “onOpen” then run the code.
We will switch back to our spreadsheet now and we should see “Hello World” added to our menu, we can click on this to execute our script.
In future tutorials, we will build more complex things using Google Apps Script.
If you enjoyed reading this please clap and if you have any questions feel free to drop a comment. Thanks for reading!