Here I want to show you how easy it is to write some code using Apps Script. This is the code that sits in the background of some of the Google Apps. This post is for those taking their first steps into writing code, so I will explain everything step-by-step.
First Google Sheets Script
First, you’ll need to open the Script editor. This can be found in various places, but here we’re going to be using a spreadsheet, so we’ll open the one connected to that.
Let’s dive straight in and write our first bit of code. We’ll start off in a Sheet. So, open a new Sheet from Drive. Then from the “Tools” menu, select the Script editor.
Open the Script editor and you’ll be presented with a screen like this:
The main white part of the page is where the script is written. By default, it already adds a bit of code in there for you. In a later post, I’ll explain the editor a bit more, but for now you’ll just need to type in the code, save it and then run it.
Let’s go through it step-by-step.
We’ll leave what’s already on the page, as all programs need a function to run the code. A function is just a way of grouping a bunch of code together, then by giving it a name will let the computer know that running that function, will run the code inside it.
Line 1-3: The syntax for this is, function + the name of your function (we’ll leave it as myFunction but you can name whatever you like) + two parenthesis and then two curly brackets. In between the two curly brackets, is where you put your code. They show the start and end of it.
What we’re going to do in this little piece of code, is add the text Hello! to cell A1. To do this, we will need to:
-
- get the active spreadsheet
-
- get the active sheet
-
- get the range (cell)
- add the text to the cell
Note, the structure here, we’re getting the overall spreadsheet, then getting a particular sheet within that spreadsheet. Then we’re telling the computer which particular range we want on that sheet, then finally stating what text we want to add.
In this example, we’re going to add just one line of code.
Line 2: First, type in SpreadsheetApp then follow it with a dot. This is what’s called a Class and is the top-level of the structure and the SpreadsheetApp tells the computer that we want to work with a spreadsheet. Make sure you use a capital S and capital A.
When you type the full stop a menu will appear with various options. This is the content assist, which helps you write the code quicker by offering you possible options and where you can click on them to save you typing them.
We want to get the active spreadsheet, so scroll down the list until you find getActiveSpreadsheet(), then click on it. This will add it next to what you’ve already written. The level below a class are the methods. These are just specific instructions telling the computer what to do. What you will see in Apps Script is that the names of the methods are very intuitive and in most cases are written in plain English.
The methods are written in Camel Case. What’s that? The first word is in lowercase and the first letter of words after it have a capital letter. So, the method we just used was written like this getActiveSpreadsheet(). This makes it easier to read and must be written like this as JavaScript and Apps Script are case-sensitive.
Add a dot to the end of the row so far and another menu will appear, this time with different options. These are the things we could do to the spreadsheet.
Now, we want to get the active sheet. So, as you might have guessed we want the getActiveSheet() method. Scroll down the list and click on it, or alternatively you can start writing the method, for example, getA, and this will filter the list to the methods starting with those letters.
Add a dot again and another menu will appear.
Now we want to get a particular range on the sheet. The method we want is getRange(), but as you’ll see there are 4 different options. For now, we’re going to use the bottom one, as in between the brackets we’ll add a normal cell reference, i.e. A1.
Delete the part in between the brackets that says “a1Notation”.
And write the cell reference with quote marks.
Add a dot again.
This time we want to state what we want to put in the cell. We can do all sorts of things, like add colours, borders, change the format, but here we want to add a piece of text, so we will need to set its value, then state what that value is.
So, from the menu, choose the setValue() method.
Then in between the brackets write “Hello!”, remembering to include the quote marks, as this is text.
Generally, when we end a line of code, you should add a semi-colon.
So, the code should now look like this. So, as we go from left to right, it’s drilling down from the overall spreadsheet to the specific cell and then stating what we want to do with that cell.
Now, we’re ready to run our code. First, make sure you save it, by clicking on the disk icon under View.
If you haven’t already, you’ll need to name your script project. Just type a name into the box and click OK.
Then click the play button to run the code. The first time you run any code, Google will require you to authorise it. This is a safety mechanism and allows you to check that you are giving access to the parts that you were expecting to.
To see how, click here and follow the instructions. It’s easy to do.
At the top of the screen you’ll see the status messages as the code is running. Once the code has finished they will disappear.
Open the spreadsheet tab and you’ll see in cell A1 your text.
Well done! You’ve created your first script! Not the most exciting I know, but it introduces you to not only how to write a script but also the basic structure of building up a piece of code.
If you’re just starting out, I would recommend starting off in Sheets, as personally, I found it the easiest to get my head around how Apps Scripts works and the sheet and cell structure of a spreadsheet is easier to understand, but it depends on your circumstances.
function myFunction(){
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1").setValue("Hello!");
}
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
-
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
-
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
-
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
-
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
-
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store
- “Google Sheet Functions – A step-by-step guide” – iBooks Store / Kindle Store
Baz Roberts (Flipboard / Twitter)
2 comments
Comments are closed.