Apps Script Basics – Variables and getting & setting values

In this post, we’re going look at how variables are set up and how they can store various pieces of information. We’re also going to look at reading and writing data from a spreadsheet, which is one of the most common tasks when working with one.

I’m going to use a simple example, where we have some data about three different students and we want to put all that data together in one single table in a different part of a sheet. Then, we’re going to format it a little bit to make it look better.

So, we will be getting data from these columns:

3Variables - 17

And writing it into these columns, all with Apps Script.

3Variables - 16

So first, what is a variable? It’s basically a container that stores information. You give it a name and state what’s in the container. All variables start with the keyword var. Then followed by the name for that variable. Apart from using keywords, you can call your variable pretty much anything you like.

3Variables - 20

Above, we have two examples of variables. The first is variable called studentsName and we have assigned the name John to that variable. So, every time we refer to studentsName we are referring to the name John. The same goes for numbers. The second variable is called numberOfStudents and we have stated that the number of students is 10.

Note, that text (or a “string”) is written between quote marks. Whereas, numbers don’t need quote marks, if they are being used as numbers. We use the equals sign to show that the text or the number has been assigned to that variable. In other words, that is what has been put in that particular container. The equals sign doesn’t mean equals to as in maths. For that a double (==) or triple (===) equals sign is used.


The code

Create a new spreadsheet and open the script editor from the Tools menu.

3Variables - 3

Leave the default function text in there. In between the curly brackets, I’ve created a variable called ss and I’ve assigned the class SpreadsheetApp and getActiveSpreadsheet method to it.

So, why have I done that? SpreadsheetApp.getActiveSpreadsheet() refers to the current active sheet in my spreadsheet and if I want to do anything in that spreadsheet I need to refer to this. What a variable allows me to do is instead of writing out SpreadsheetApp, etc every time, I can put it in a variable and just refer to that variable from now on. So, every time I refer to ss now, I’m actually referring to the active spreadsheet.

Next, as we’re going to refer to the sheet numerous times when reading and writing data to and from it, let’s store the active sheet in a variable called sheet. We now need to get the active sheet, so we need the getActiveSheet() method. We could write it out long hand like this:

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() but as we already have the first part stored in the variable ss, we can just use that.

3Variables - 4

Start by typing ss followed by a dot. As we can see it brings up the possible methods we can use with getActiveSpreadsheet().

3Variables - 5

We scroll down and select the getActiveSheet() method.

OK, now we want to start to create our table in a sheet.

3Variables - 18

First, type sheet follow by a dot. This refers to the current active sheet. We then need to get a range on that sheet. As we can see above, there are various options with the method getRange. For now, let’s just use the last one, which will require us to state the cell references we want to get.

3Variables - 6

Now, type the rest of the line. This will get the range A1 and set its value to the word Name. In other words, it will write the word Name in cell A1 on our sheet, like this:

3Variables - 21

3Variables - 7

Lines 6 and 7 are similar. We get cells B1 and C1 and write the words Subject and Mark. It sets up the header row like this:

3Variables - 22

Now we have our header row, let’s get the student data from columns F to H.

3Variables - 8

Here I create another variable called student1. This time I’m going to get the data in a row of 3 cells (F2 to H2). As there is more than one piece of data, we use the method called getValues(). This will store the three pieces of information: John, Maths, 65 in the variable student1. Yes, variables can store more than one bit of information!

3Variables - 9

Lines 10 to 11: We then do the same for the other two students. Now we have the 3 details of the three students in three different variables.

Now we’ve read the data in the sheet and stored it, we need to write it on the other part of the sheet.

3Variables - 10

I want to write the first student’s data in row 2, so I get the range A2 to C2 and then set the values of those cells to the contents of the variable student1.So, what will happen is that in cell A2 the John will be written, in B2 Maths and in C2 65, as we can see below:

3Variables - 23

3Variables - 11

Lines 14-15: We carry out the same process for the other two students, this time writing the data to rows 3 and 4, by using the variables student2 and student3. So, now we have our basic table:

3Variables - 24

Let’s make the table a little prettier by adding some formatting.

3Variables - 12

First, let’s get the header row, which is A1 to C1. We use the getRange() method and store that range in the variable header. Now, we have that header range we can use it to then add some formatting to it.

3Variables - 13

Line 18: Let’s change the background colour to yellow. To do this, we get the header range (header) and use the method setBackground(). Then we put the colour we want in the brackets with quote marks.

Line 19: Let’s also add bolding to the words. To do this, we again get the header and this time use the setFontWeight() method and in the brackets we add the word bold in quote marks.

As we can see, this has formatted the header.

3Variables - 25

Finally, let’s align all the cells in the table centrally.

3Variables - 14

Line 21: First, we need to get the range of cells in the table (A1 to C4). Here I’ve stored that range in the variable table.

3Variables - 15

Line 22: We then get that table range, add the setHorizontalAlignment() method to it and in the brackets state center with quote marks.

So, let’s run the program. This will read the data, write the data, and finally format it all in one go. The first time we run it, it will ask us to authorise the access we want. Just click “Review permissions” and then the blue button “Allow”.

3Variables - 1

3Variables - 2

As we can see this has now centred all our cells and the table looks much better.

3Variables - 16

By using variables, we were able to write a shorter piece of code and this code now also allows us to create a table with data for a different set of students.

Apart from the use of variables, hopefully, you can see how easy it is to get data from a sheet and to write data to it.

Here’s a link to make a copy of the spreadsheet, which also contains the code.

https://docs.google.com/spreadsheets/d/1eB0HMYsfaJbL7snqVIJ7QI79pSRi-UC2m5c-Jtl6teU/copy

If you want to learn more about variables visit the W3schools site: Variables

If you’d like to learn more about formatting your sheet, check out my post: Pimp up your sheet


Here’s the full code:

3Variables - 19


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Google+Flipboard / Twitter)


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s