1. Introduction
Welcome to the second part of the Fundamentals of Apps Script with Google Sheets codelab playlist. The previous codelab focused on the concepts of the script editor, macros, and custom functions. This codelab delves into the Spreadsheet service that you can use to read, write, and manipulate data in Google Sheets.
What you'll learn
- How spreadsheets, sheets, and ranges are represented in Apps Script.
- How to access, create, and rename the active (open) spreadsheet with the
SpreadsheetApp
andSpreadsheet
class. - How to change a sheet's name and a range's column/row orientation, using the
Sheet
class. - How to specify, activate, move, and sort a group of cells or range of data using the
Range
class.
Before you begin
This is the second codelab in the Fundamentals of Apps Script with Google Sheets playlist. Before starting, be sure to complete the first codelab: Macros and Custom Functions.
What you'll need
- An understanding of the basic Apps Script topics explored in the previous codelab of this playlist.
- Basic familiarity with the Apps Script editor
- Basic familiarity with Google Sheets
- Ability to read Sheets A1 Notation
- Basic familiarity with JavaScript and its
String
class
The next section introduces the core classes of the Spreadsheet service.
2. Introduction to the Spreadsheet service
Four classes encompass the foundation of the Spreadsheet service: SpreadsheetApp
, Spreadsheet
, Sheet
, and Range
. This section describes these classes and what they're used for.
The SpreadsheetApp Class
Before delving into spreadsheets, sheets, and ranges, you should review their parent class: SpreadsheetApp
. Many scripts begin by calling SpreadsheetApp
methods, as they can provide the initial point of access to your Google Sheets files. You can think of SpreadsheetApp
as the main class of the Spreadsheet service. The SpreadsheetApp
class is not explored in depth here. However, later in this codelab you can find both examples and exercises to help you understand this class.
Spreadsheets, sheets, and their classes
As a Sheets term, a spreadsheet is a Google Sheets file (stored in Google Drive) that contains data organized by rows and columns. A spreadsheet is sometimes referred to as a ‘Google Sheet', in the same way that a document is referred to as a ‘Google Doc'.
You can use the Spreadsheet
class to access and modify Google Sheets file data. You can also use this class for other file-level operations, such as adding collaborators.
A sheet** represents the individual page of a spreadsheet, sometimes referred to as a "tab." Each spreadsheet can contain one or more sheets. You can use the Sheet
** class to access and modify sheet-level data and settings, such as moving rows or columns of data.
In summary, the Spreadsheet
class operates on the collection of sheets and defines a Google Sheets file in Google Drive. The Sheet
class operates on individual sheets within a spreadsheet.
The Range class
Most data manipulation operations (for example, reading, writing, or formatting cell data) require you to define what cells the operation applies to. You can use the Range
class to select specific sets of cells within a sheet. Instances of this class represent a range—a group of one or more adjacent cells in a sheet. You can specify ranges by their row and column numbers, or by using A1 notation.
The rest of the codelab shows examples of scripts that work with these classes and their methods.
3. Set up
Before you continue, you need a spreadsheet with some data. We've provided one for you: click this link to copy the data sheet and then click Make a copy.
A copy of the example spreadsheet for you to use is placed in your Google Drive folder and named "Copy of Untitled spreadsheet." Use this spreadsheet to complete this codelab's exercises.
As a reminder, you can open the script editor from Google Sheets by clicking Extensions > Apps Script.
When opening an Apps Script project in the script editor for the first time, the script editor creates both a script project and a script file for you.
The next section shows you how to use the Spreadsheet
class to improve this spreadsheet.
4. Access and modify spreadsheets
In this section, you can learn how to use the SpreadsheetApp
and Spreadsheet
classes to access and modify spreadsheets. Specifically, the exercises teach you how to rename a spreadsheet and to duplicate sheets within a spreadsheet.
These are simple operations, but they're often part of a larger, more complex workflow. Once you can understand how to automate these tasks with script code, it will be easier to learn how to automate more elaborate operations.
Rename the active spreadsheet
Suppose you wanted to change the default name, "Copy of Untitled spreadsheet" to a title that better reflects your spreadsheet's purpose. You can do this with the SpreadsheetApp
and Spreadsheet
classes.
- In the script editor, replace the default
myFunction()
code block with the following code:
function renameSpreadsheet() {
var mySS = SpreadsheetApp.getActiveSpreadsheet();
mySS.rename("2017 Avocado Prices in Portland, Seattle");
}
- To save your script, click Save .
- To rename your Apps Script project, click Untitled project, enter "Avocado prices" as the new project name and click Rename.
- To run your script, select
renameSpreadsheet
from the functions list and click Run. - Authorize the macro by following the on-screen instructions. If you get a "This app isn't verified" message, click Advanced, then click Go to Avocado prices (unsafe). On the next screen, click Allow.
Once the function executes, your spreadsheet's filename should change:
Let's look at the code you entered. The getActiveSpreadsheet()
method returns an object representing the active spreadsheet; that is, the copy of the exercise spreadsheet you made. This spreadsheet object is stored in the mySS
variable. Calling rename(newName)
on mySS
changes the name of the spreadsheet file in Google Drive to "2017 Avocado Prices in Portland, Seattle."
Since the mySS
variable is a reference to the spreadsheet, you can make your code cleaner and more efficient by calling Spreadsheet
methods on mySS
instead of calling getActiveSpreadsheet()
repeatedly.
Duplicate the active sheet
In your current spreadsheet, you only have one sheet. You can call the Spreadsheet.duplicateActiveSheet()
method to make a copy of the sheet:
- Add the following new function below the
renameSpreadsheet()
function already in your script project:
function duplicateAndOrganizeActiveSheet() {
var mySS = SpreadsheetApp.getActiveSpreadsheet();
var duplicateSheet = mySS.duplicateActiveSheet();
}
- Save your script project.
- To run your script, select
duplicateAndOrganizeActiveSheet
from the functions list and click Run.
Return to Sheets to see a new "Copy of Sheet_Original" sheet tab is added to your spreadsheet.
In this new function, the duplicateActiveSheet()
method creates, activates, and returns the duplicate sheet in your spreadsheet. This resulting sheet is stored in duplicateSheet
, but the code isn't doing anything with that variable yet.
In the next section, you'll use the Sheet
class to rename and format the duplicate sheet.
5. Format your sheet with the Sheet class
The Sheet
class provides methods that allow scripts to read and update sheets. In this section, you can learn how to change a sheet's name and column widths with methods from the Sheet
class.
Change the sheet's name
Renaming sheets is as simple as renaming the spreadsheet was in renameSpreadsheet()
. Only a single method call is required.
- In Google Sheets, click the
Sheet_Original
sheet to activate it. - In Apps Script, modify your
duplicateAndOrganizeActiveSheet()
function to match the following:
function duplicateAndOrganizeActiveSheet() {
var mySS = SpreadsheetApp.getActiveSpreadsheet();
var duplicateSheet = mySS.duplicateActiveSheet();
// Change the name of the new sheet.
duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}
- Save and run the function.
In Google Sheets, a duplicate sheet is created and renamed when you run the function:
In the added code, the setName(name)
method changes the name of the duplicateSheet
, using getSheetID()
to get the sheet's unique ID number. The +
operator concatenates the sheet ID to the end of the "Sheet_"
string.
Modify a sheet's columns and rows
You can also use the Sheet
class to format your sheet. For example, we can update your duplicateAndOrganizeActiveSheet()
function to also resize the columns of the duplicate sheet, and add frozen rows:
- In Google Sheets, click the
Sheet_Original
sheet to activate it. - In Apps Script, modify your
duplicateAndOrganizeActiveSheet()
function to match the following:
function duplicateAndOrganizeActiveSheet() {
var mySS = SpreadsheetApp.getActiveSpreadsheet();
var duplicateSheet = mySS.duplicateActiveSheet();
// Change the name of the new sheet.
duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
// Format the new sheet.
duplicateSheet.autoResizeColumns(1, 5);
duplicateSheet.setFrozenRows(2);
}
- Save and run the function.
In Google Sheets, a duplicate sheet is created, renamed, activated, and formatted:
The code you added uses autoResizeColumns(startColumn, numColumns)
to resize the columns of the sheet for readability. The setFrozenRows(rows)
method freezes the given number of rows (two, in this case) which keeps the header rows visible as the reader scrolls down the spreadsheet.
In the next section, you'll learn about ranges and basic data manipulation.
6. Rearrange data with the Range class
The Range
class and its methods provide most of the data manipulation and formatting options in the Spreadsheet service.
This section introduces basic data manipulation with ranges. These exercises focus on how to utilize ranges in Apps Script while other codelabs in this playlist go into greater depth on data manipulation and data formatting.
Move ranges
You can activate and move ranges of data with class methods and A1 notation, a shorthand for identifying specific sets of cells within spreadsheets. If you need to re-familiarize yourself, you can check out this description of A1 notation.
Let's update your duplicateAndOrganizeActiveSheet()
method to also move some data around:
- In Google Sheets, click the
Sheet_Original
sheet to activate it. - In Apps Script, modify your
duplicateAndOrganizeActiveSheet()
function to match the following:
function duplicateAndOrganizeActiveSheet() {
var mySS = SpreadsheetApp.getActiveSpreadsheet();
var duplicateSheet = mySS.duplicateActiveSheet();
// Change the name of the new sheet.
duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
// Format the new sheet.
duplicateSheet.autoResizeColumns(1, 5);
duplicateSheet.setFrozenRows(2);
// Move column F to column C.
var myRange = duplicateSheet.getRange("F2:F");
myRange.moveTo(duplicateSheet.getRange("C2"));
}
- Save and run the function.
When you run this function, a duplicate sheet is created, activated, and formatted. And, the contents of column F are moved to column C:
The new code uses the method getRange(a1Notation)
to identify the range of data to move. By inputting A1 notation "F2:F" as the method's parameter, you specify column F (excluding F1). If the specified range exists, the getRange(a1Notation)
method returns its Range
instance. The code stores the instance in the myRange
variable for ease of use.
Once the range is identified, the moveTo(target)
method takes the contents of myRange
(both the values and the formatting) and moves them. The destination (column C) is specified using the A1 notation "C2". This is a single cell, rather than a column. When moving data, you don't need to match the sizes to the target and destination ranges. Apps Script simply aligns the first cell of each.
Sort ranges
The Range
class allows you to read, update, and organize groups of cells. For example, you can sort a data range by using the Range.sort(sortSpecObj)
method:
- In Google Sheets, click the
Sheet_Original
sheet to activate it. - In Apps Script, modify your
duplicateAndOrganizeActiveSheet()
function to match the following:
function duplicateAndOrganizeActiveSheet() {
var mySS = SpreadsheetApp.getActiveSpreadsheet();
var duplicateSheet = mySS.duplicateActiveSheet();
// Change the name of the new sheet.
duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
// Format the new sheet.
duplicateSheet.autoResizeColumns(1, 5);
duplicateSheet.setFrozenRows(2);
// Move column F to column C.
var myRange = duplicateSheet.getRange("F2:F");
myRange.moveTo(duplicateSheet.getRange("C2"));
// Sort all the data using column C (Price information).
myRange = duplicateSheet.getRange("A3:D55");
myRange.sort(3);
}
- Save and run the function.
Now the function, in addition to our previous formatting, sorts all the data in the table using the price information in column C:
The new code uses getRange(a1Notation)
to specify a new range covering A3:D55 (the whole table excluding the column headers). The code then calls the sort(sortSpecObj)
method to sort the table. Here, the sortSpecObj
parameter is the column number to sort by. The method sorts the range so the indicated column values go from lowest to highest (ascending values). The sort(sortSpecObj)
method can perform more complex sorting requirements, but you don't need them here. You can view all the different ways you can call sort ranges in the method reference documentation.
Congrats, you've successfully completed all the exercises in the codelab. The next section reviews the key points of this codelab and previews the next codelab in this playlist.
7. Conclusion
You've reached the end of this codelab. You can now use and define the essential classes and terms of the Spreadsheet service in Apps Script.
You're ready to move on to the next codelab.
Did you find this codelab helpful?
What we've covered
- How spreadsheets, sheets, and ranges are represented in Apps Script.
- Some basic uses of the
SpreadsheetApp
,Spreadsheet
,Sheet
, andRange
classes.
What's next
The next codelab in this playlist goes into more depth on how to read, write, and modify data within a spreadsheet.
Find the next codelab at Working with data.