The Beginner’s Guide to Google Sheets and App Script-2
What is Google App Script?
Google App Script (GAS for short) is a tool from Google that helps people make special computer programs for Google Workspace. You can use a computer language called JavaScript to write your program. With your program, you can make tools and cool things for Google Sheets, Google Forms, Google Docs, and other Google apps!
That’s why Google App Script is really great! It’s easy to use, and it works with lots of different Google apps. You can use it on the internet, and you can use it elsewhere you want. With Google App Script, you can make cool tools and things for Google apps.
GAS and Google Sheets
As mentioned in the previous article, but it’s important to remember: Google Sheets is kind of like Microsoft Excel. They both let you put information and math or formulas in little boxes, make charts from data in cells, and even import or export datas from different file formats.
If you use Google App Script (GAS) with Google Sheets, you can make your computer do some of your work for you! This can help you get your work done faster and easier.
There are a lot of ways to use GAS with Google Sheets to make your work easier. Some are simple and some are hard, but we’ll show you more of them in the next parts. So, don’t worry if you don’t understand everything yet — we’ll keep explaining things step by step!
4 ways to open or connect Sheets
In Google App Script, there are four different ways to connect to a Google Sheet using APIs. You may already be familiar with two of them — openById and openByUrl — from our previous article. But there are two more ways you should know: getActiveSheet() and getActiveSpreadsheet(). These methods all have their own advantages and can be useful in different situations.
What is the difference between them?
Remember that in the last article, we talked about how Google App Script can work in two different ways. The first one is to be attached to Google Workspace services, which means the script is bound to the sheet. The second one is for the script to work independently as a standalone file.
In Google App Script, there are four ways to connect to a Google Sheet, depending on how the script is set up. If the script is a standalone file, you can use the methods openById and openByUrl to connect to a separate Google sheet. On the other hand, if the script is bound to a Google Spreadsheet, you can use the methods getActiveSheet() and getActiveSpreadsheet() to connect to the sheet. This means that the script was created from the document itself, and not as a separate file. The following sections will explain these two methods in more detail:
getActiveSpreadsheet()
The method “getActiveSpreadsheet()” is used in a type of GAS script called a container-bound script. It helps the script to access all the sheets inside a spreadsheet.
As we mentioned before, you can use the method “getActiveSpreadsheet()” to retrieve all the sheets within a Google Spreadsheet, but you can only use it if you have a bound script. That means the script must be written within the Spreadsheet, rather than operating independently as a file outside the Spreadsheet. If your script is not bound to the Spreadsheet, you can’t use “getActiveSpreadsheet()”.
Because this method must exist within a inbound GAS in the Spreadsheet, it is not necessary to specify the spreadsheet’s ID (openById) and URL (openByUrl) to manipulate it from within the Spreadsheet.
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
Logger.log(ss.getUrl());
}
getActiveSheet()
getActiveSheet()retrieves the currently active sheet page from a Google Sheets file.
However, just like the getActiveSpreadsheet(), it needs to be written in the bound app script in the Google Sheets file. Otherwise, it cannot be used. Nevertheless, once it is written inside the Google Spreadsheet file, it will be automatically connected to the file.
var ss = SpreadsheetApp.getActiveSheet();Unlike getActiveSpreadsheet(), getActiveSheet()can only retrieve the currently active sheet in a Google Spreadsheet.
function myFunction() {
var ss = SpreadsheetApp.getActiveSheet();
Logger.log(ss.getSheetName());
}Executing the script above gives the following result:
Since the current active sheet in the Spreadsheet is “Sheet1”, the script returns a string of the sheet‘s name .
getSheets()
After connecting to the spreadsheet, we can use getSheets() to get all the worksheets in the spreadsheet. The official documentation of Google App Script provides the following sample:
// The code below logs the name of the second sheet
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
if (sheets.length > 1) {
Logger.log(sheets[1].getName());
}
To retrieve a specific sheet from a Spreadsheet that contains multiple sheets, you can use the position of the sheet by adding an index to getSheets(). For example, getSheets()[0] represents the first sheet in the spreadsheet and getSheets()[1] represents the second sheet.
var ss = SpreadsheetApp.getActiveSheet();
var sheet = ss.getSheets()[0];Accessing cells
Once we get into the Google Spreadsheet using one of the methods mentioned before, we can change things in each worksheet and its cells. But before you can make a change to a cell, you have to tell the computer which cell you want to change and which group of cells it is in.
getRange()
Before you change something in a cell or check what’s in a cell, you have to tell the computer which cell you want to work with. You can use something called “.getRange()” to do that. With “.getRange()”, you can choose just one cell or a group of cells. This method has some settings that help you get data from different groups of cells.
Accessing Single Cells
To obtain a specific cell in a Google Sheet, you can use .getRange()the following ways.
# getRange(row, column)
To select a specific cell in a Google Sheet, you can use a function called getRange(). This function takes two parameters — the row number and column letter of the cell you want to select. With these values, the function can locate the exact cell you want to work with.
function selectCell() {
// open Google Sheet
var ss = SpreadsheetApp.openById('E8uDhFNqg5r1gSbTKX-73azlCoTaC-9Yj').getSheets()[0];
// select first row and column, and set the background color as yellow
var cell = ss.getRange(1, 1);
cell.setBackground('#ffff00');
}The code above is a script that changes the background color of a specific cell in a Google Sheets spreadsheet.
After running the script, the script changes the color of a cell in a Google Sheets document. The cell it changes is located in the first row and first column. By default, it changes the color of the cell to yellow, but you can change the color to whatever you like.
# getRange(a1Notation)
This example script shows how to select a specific cell in a Google Sheet using the getRange() function and set its background color.
function selectCell() {
// open Google Sheet
var ss = SpreadsheetApp.openById('aLphDhFNqg5gSbTKX-3azlCo').getSheets()[0];
// Select Cell A2, and set its background color to yellow
var cell = ss.getRange('A2');
cell.setBackground('#ffff00');
}
The execution result is as follows, the background color of the specific cell A2 is set to yellow.
Note that, in the computer program we’re using, there’s something called the getRange() function. You can use this function to pick a specific cell or a bunch of cells at once. When you use this function, you have to give it a string of text that tells it which cell or cells you want. For example, if you just want one cell, you could give it the location of that cell like ‘A2’. But if you need more than one cell, you can give it a range of cells like ‘A1:B2’.
Accessing Multiple Cells
The getRange() function can not only retrieve a single cell, but also all cells within a certain range. Here's how to retrieve cells within a specific range:
# By specifying the starting cell position and the size of the range (number of rows and columns), for example, getRange(row, column, numRows, numColumns)
The following example demonstrates how to use getRange() function to select a specified range of cells in a spreadsheet and set their background color:
function selectRange() {
// Open Google Sheet
var ss = SpreadsheetApp.openById('phMHQE8uDhFNgSbTKTaC-A').getSheets()[0];
// Select cells from row1,column1 to row 3 and set background to yellow.
var cell = ss.getRange(1, 1, 3, 1);
cell.setBackground('#ffff00');
}
The following is the result of the execution, selecting a range of cells starting from row 1, column 1, spanning three rows and one column, and setting their background color to yellow.
In the above example, we used four parameters of the getRange() function, which are the column and row of the starting cell (1,1), and the size of the cell range (3,1). This means that the cell range we selected starts from the cell in the first row and first column, and continues with three rows and one column of cells. If you need to select a larger cell range, you can adjust the parameters of the getRange() function to fit your needs.
# By specifying the starting cell position and range size using getRange(row, column, numRows, numColumns), we can also enter only three parameters, as shown in the following script:
function selectRange() {
// Open Google Sheet
var ss = SpreadsheetApp.openById('phMHQE8uDhFNgSbTKTaC-A').getSheets()[0];
// Select cells from row1,column1 to row 3 and set background to yellow.
var cell = ss.getRange(1, 1, 3);
cell.setBackground('#ffff00');
}
The third line of the script uses the getRange method to select a cell range of 3 rows and 1 column. The first parameter, 1, represents the starting row number of the range to be selected, the second parameter, 1, represents the starting column number of the range to be selected, and the third parameter, 3, represents the number of rows to be selected in the range. This will select a cell range of 3 rows and 1 column starting from the first row and first column. The fourth line of the script uses the setBackground method to set the background color of the selected cell range to light yellow. The result of the program execution is the same as using four parameters as described earlier.
Edit Cells
The things we do beforehand help us change, view and edit information in cells. We use different techniques to get data from cells or put new data into them when we edit cells. In this article, we’ll explain two different parts — one for getting data and one for writing data.
Getting Data
When we change information in cells, we might need to read the information that’s already there. We use two methods to do this: getValue() and getValues(). getValue() shows the information in one cell, while getValues() shows information from many cells in a two-dimensional list.
# getValue()
To read cell data, you can use the getValue() method. For instance, if you want to read the value in cell A1 (as displayed in the image above), you can use the following script:
var sheet = SpreadsheetApp.getActiveSheet();
var value = sheet.getRange("A1").getValue();In this code, we use the getRange() method to find cell A1 and create an object called “Range”. Then we use the getValue() method to read the information in that cell and save it as “value” in our program.
To try it out yourself, open Google Sheets and go to the “Extensions” menu. Click “Apps Script” to open the editor and then write the code above in the editor.
Finally, output the read value using the Logger.log() method to check if the read value is correct.
Logger.log(value);The script’s execution should result in the appearance of the word ‘Bien’ in the “Execution log” because the string ‘Bien’ is located in cell A1 of the spreadsheet.
# getValues()
The getValues() method allows you to read data from multiple cells in Google Sheets. Here's an example script that uses getValues() to read a range of cells:
To read data from a range of cells, first use the getRange() method in the editor to select the desired range.
To read the values of a cell range A1:B2 using the script below, you can utilize the following method:
var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange("A1:B2").getValues();In the above script, the getRange() method returns a Range object representing the A1:B2 range of cells. Then, the getValues() method is used to read all the values in the cell range, and the obtained values are assigned to a variable called values. The getValues() method returns a two-dimensional array ([[Hello, Hola], [こんにちは,您好]]), where each element represents a cell value. Finally, the Logger.log() method is used to output the read values for checking whether they are correct.
Logger.log(values);A two-dimensional list is structured with rows and columns, much like a table. It can contain numbers, true or false statements, dates, or text. Even if a cell is blank, the list still reserves a place for it, although the information inside will simply be an empty space.
To complete the process, execute the script and read the data within the designated cell range.
The “Execution log” will return a two-dimensional array ([[Hello, Hola], [こんにちは, 您好]]).
In summary, using the getValue() and getValues() methods can make it easy for you to read cell data in Google Sheets. This, in turn, makes it easier for you to process and analyze data.
Writing Data
When we change information in cells, sometimes we also need to put new information in them. We use two methods to do this: setValue() and setValues(). setValue() adds one piece of information to one cell, while setValues() adds lots of information to one or many cells. Here are some instructions on how to use these methods to add information to one cell or many cells.
# setValue()
To write data to a cell, first use the getRange() method in the IDE editor to select the desired cell. For example, to write the value “Hola” to cell A1, use the following script:
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("A1").setValue("Hola");In this script, we use getRange() to find cell A1 and make an object called “Range”. Then we use the setValue() method to add new information to that cell.
After execution, the result “Hola” appears in cell A1.
# setValues()
If you want to add new information to lots of cells at once, you can use the setValues() method. Here’s an example: If you want to write a table with four pieces of information into the area between A1 and B2, you can use the following script:
var sheet = SpreadsheetApp.getActiveSheet();
var values = [["1A", "1B"], ["2A", "2B"]];
sheet.getRange("A1:B2").setValues(values);In this script, we use setValues() to add a lot of new information to a range of cells. We give the method a two-dimensional list, and each piece of information in the list represents a cell in the range we want to add information to.
We can use the getValues() method to check if we added the right information to our cells. Here’s an example: The following script can be used to get the information from the range of cells between A1 and B2:
var sheet = SpreadsheetApp.getActiveSheet();
var results = sheet.getRange("A1:B2").getValues();
Logger.log(results);Finally, execute the script and write the specified data into the cells.
After executing the script, the results will appear in the cells A1 to B2, with “1A” and “1B” in the first row and “2A” and “2B” in the second row.
In the examples above, we used setValue() and setValues() to add new information to cells in Google Sheets. These methods make it simple to add lots of information at once, and make it easier to work with your data.
Summary
To sum up, Google Apps Script is a powerful tool that lets users edit Google Spreadsheets in a variety of ways. Users can open and link to a spreadsheet using different methods, such as the spreadsheet ID, URL, or via getActiveSheet() and getActiveSpreadsheet() functions within the spreadsheet.
To edit cells, users can use getRange() to select cells or ranges, getValue() and getValues() to get data from cells, and setValue() and setValues() to write data. These methods make it easy to operate spreadsheet data and speed up tasks.
For users working with large amounts of data, Google Apps Script can be a highly useful tool to improve efficiency. Although this article only introduces a few features of Google Apps Script, there are many other powerful features that can be discovered and used. If interested, stay tuned for more articles introducing other ways to operate Google Spreadsheets.
如果要看中文版，請點選這裡。
