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:

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

So first of all, what is a variable? It’s basically a container that stores information. You give it a name and state what’s in the container. There are three keywords to do this, var, let, and const. As a general rule, use const as this will help prevent errors, unless the variable value will change and then you should use let. var is you will see in older code examples on the web.

All variables start with the keyword followed by the name for that variable. You can call your variable pretty much anything you like, except using keywords, like function, or starting it with a character that isn’t a letter, for example a number. Variables are usually written in camel case. You’ll notice keywords in the script editor are shown in blue.

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

Note, that text (or a “string”) is written between quote marks. Note, single or double quote marks can be used. 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 Apps Script editor from the Extensions menu.

1.	function collateMarks() {
2.	  const ss = SpreadsheetApp.getActiveSpreadsheet();
3.	
4.	}

Line 1: Leave the default function text in there and change the function name from myFunction to collateMarks.

Line 2: In between the curly brackets, I’ve created a variable called ss and I’ve assigned the SpreadsheetApp class and the getActiveSpreadsheet() method to it.

So, why have I done that? SpreadsheetApp.getActiveSpreadsheet() refers to the current active spreadsheet, i.e. the one we’re currently using. What a variable allows me to do is, instead of writing out SpreadsheetApp.getActiveSpreadsheet() every time I want to refer to it, I can put it in a variable and just refer to that variable from then on. So, every time I refer to ss from then on, I’m actually referring to the active spreadsheet.

It’s common practice to see the variable ss used to refer to the spreadsheet. These common practices help you read and understand other people’s code and for them to understand yours.

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.

1.	function collateMarks() {
2.	  const ss = SpreadsheetApp.getActiveSpreadsheet();
3.	  const sheet = ss.getActiveSheet();
4.	}

Line 3: Type in const and sheet followed by the equals sign. Then let’s define this variable.  Type ss followed by a dot. As we can see below it brings up the possible methods we can use with the spreadsheet.

Start typing the method we want, i.e. getActiveSheet() and when you see it in the menu click on it to add it.

Then end the line with a semi-colon.

OK, now we want to start to create our table in a sheet. At the end of line 3, press enter a couple of times, so that you produce a couple of empty lines and which will move the curly bracket down a few lines.

Line 5: Type sheet followed by a dot.

1.	function collateMarks() {
2.	  const ss = SpreadsheetApp.getActiveSpreadsheet();
3.	  const sheet = ss.getActiveSheet();
4.	 
5.	  sheet.getRange("A1").setValue("Name");

This refers to the current active sheet. We then need to get a range on that sheet.

After the dot, type in getRange() and in the brackets add the cell reference “A1”. Then type a dot, setValue() and in the brackets type “Name”. Finally, end the line with a semi-colon.

This will get the cell 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:

5.	  sheet.getRange("A1").setValue("Name");
6.	  sheet.getRange("B1").setValue("Subject");
7.	  sheet.getRange("C1").setValue("Mark");

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:

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

9.	  const student1 = sheet.getRange("F2:H2").getValues();

Line 9: Here, I’m going to 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(). Note, the ‘s’ on the end, it’s a common mistake to forget to add the ‘s’.

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!

9.	  const student1 = sheet.getRange("F2:H2").getValues();
10.	  const student2 = sheet.getRange("F5:H5").getValues();
11.	  const student3 = sheet.getRange("F8:H8").getValues();

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, student1, student2, and student3.

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

13.	  sheet.getRange("A2:C2").setValues(student1);

Line 13: 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 John will be written, in B2 Maths and in C2 65, as we can see below:

13.	  sheet.getRange("A2:C2").setValues(student1);
14.	  sheet.getRange("A3:C3").setValues(student2);
15.	  sheet.getRange("A4:C4").setValues(student3);

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:

That’s how to get and set values on a sheet but let’s make the table a little prettier by adding some formatting.

17.	  const header = sheet.getRange("A1:C1");

Line 17: 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. This means we don’t have to get that range every time we use it, we can just refer to the variable header. Now, we have that header range we can use it to then add some formatting to it.

17.	  header.setBackground(“yellow”)
18.	            .setFontWeight(“bold”);

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. Note, I’ve not added a semi-colon at the end. This is because I’m going to connect another method to it. This is called chaining.

Line 19: Let’s also add bolding to the words. To do this, we use the setFontWeight() method and in the brackets we add the word bold in quote marks. This time we end it with a semi-colon.

As we can see, this has formatted the header.

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

21.	  const table = sheet.getRange("A1:C4");

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.

21.	  const table = sheet.getRange("A1:C4");  
22.	  table.setHorizontalAlignment(“center”);

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 from one part of the sheet, write the data to another part, and finally format it all in one go. The first time we run it, it will ask us to authorize the access we want.

So, let’s run the program. This will read the data from one part of the sheet, write the data to another part, and finally format it all in one go. The first time we run it, it will ask us to authorize the access we want (remember to click the Advanced link).

As we can see this has got the information on the 3 students, added a header, added the information on a different part of the sheet, added a yellow background and bolding to the header, and centered all our cells and the table looks much better.

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.

Hopefully, you can see how easy it is to get data from a sheet and to write data to it.

When I was first learning JavaScript, I found the W3schools.com site extremely useful. It explains the language really well and every part has examples for you to try out. Bearing in mind, these are more focused towards using JavaScript with web pages, but the site taught me a lot very quickly.

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

https://www.w3schools.com/js/js_const.asp

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

1. function myFunction() {
2.  const ss = SpreadsheetApp.getActiveSpreadsheet();
3.  const sheet = ss.getActiveSheet();
4. 
5.  sheet.getRange("A1").setValue("Name");
6.  sheet.getRange("B1").setValue("Subject");
7.  sheet.getRange("C1").setValue("Mark");
8.
9.  var student1 = sheet.getRange("F2:H2").getValues();
10.  var student2 = sheet.getRange("F5:H5").getValues();
11.  var student3 = sheet.getRange("F8:H8").getValues();
12.
13.  sheet.getRange("A2:C2").setValues(student1);
14.  sheet.getRange("A3:C3").setValues(student2);
15.  sheet.getRange("A4:C4").setValues(student3);
16.
17.  const header = sheet.getRange("A1:C1");
18.  header.setBackground('yellow');
19.  header.setFontWeight('bold');
20.
21.  const table = sheet.getRange("A1:C4");  
22.  table.setHorizontalAlignment('center'); 
23.
24. }

a