Fundamentals of Apps Script with Google Sheets #2: Spreadsheets, Sheets, and Ranges

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 and Spreadsheet 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.

f00cc1a9eb606f77.png

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.

39dbb10f83e3082.png

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.

5376f721894b10d9.png

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.

  1. 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");
}
  1. To save your script, click Save save.
  2. To rename your Apps Script project, click Untitled project, enter "Avocado prices" as the new project name and click Rename.
  3. To run your script, select renameSpreadsheet from the functions list and click Run.
  4. 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:

226c7bc3c2fbf33e.png

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:

  1. Add the following new function below the renameSpreadsheet() function already in your script project:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}
  1. Save your script project.
  2. 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.

d24f9f4ae20bf7d4.gif

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.

  1. In Google Sheets, click the Sheet_Original sheet to activate it.
  2. 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());
}
  1. Save and run the function.

In Google Sheets, a duplicate sheet is created and renamed when you run the function:

91295f42354f62e7.gif

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:

  1. In Google Sheets, click the Sheet_Original sheet to activate it.
  2. 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);
}
  1. Save and run the function.

In Google Sheets, a duplicate sheet is created, renamed, activated, and formatted:

2e57c917ab157dad.gif

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:

  1. In Google Sheets, click the Sheet_Original sheet to activate it.
  2. 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"));
}
  1. 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:

10ea483aec52457e.gif

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:

  1. In Google Sheets, click the Sheet_Original sheet to activate it.
  2. 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);
}
  1. 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:

a6cc9710245fae8d.png

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?

Yes No

What we've covered

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.