Spotted a bug? Have a great idea? Help us make google.dev great!

Welcome to the second part of Fundamentals of Apps Script with Sheets playlist! From the previous codelab focused on the concepts of the script editor, macros, and custom functions, this codelab delves into the Spreadsheet service which 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 playlist. Before starting this codelab, 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.

Four classes encompass the foundation of the Spreadsheet service: SpreadsheetApp, Spreadsheet, Sheet, and Range. This section describes these classes and what they are used for.

The SpreadsheetApp Class

Before delving into spreadsheets, sheets, and ranges, you should look at their 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 lab 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" or "workbook". 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 next sections show examples of scripts that work with these classes and their methods.

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 the Make a copy button.

A copy of the example spreadsheet for you to use should be placed in your Google Drive home 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 by selecting Tools > Script editor in the Google Sheets menu:

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.

In the previous codelab, you had already created a macro script before entering the script editor. Your file does not have any macros, so the editor provides a script file called Code.gs with the pre-generated myFunction().

You're all set up! The next section shows you how to use the Spreadsheet class to improve this spreadsheet.

In this section, you can learn how to use the SpreadsheetApp and Spreadsheet classes to change and create spreadsheets on this page. Specifically, the exercises teach you how to rename a spreadsheet and duplicate sheets within a spreadsheet.

These are simple operations, but they are very 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.

Renaming 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. With the SpreadsheetApp and Spreadsheet class, you can do just that.

  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. Select , File > Save, or Ctrl+S (Cmd+S on Macs) to save your script.
  2. When asked to name the script project, name it "Avocado prices".
  3. Run your script by selecting function name in the toolbar dropdown and then clicking the button.
  4. When asked to authorize the script, follow the on-screen instructions to do so. If you get a "This app isn't verified" message, click Advanced, then click Go to Avocado prices (unsafe). In the next screen, click Allow.

Once the function executes, your spreadsheet's filename should change:

Let's take a 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.''

Because 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.

Duplicating the active sheet

In your current spreadsheet, you currently only have one sheet. You can call the Spreadsheet.duplicateActiveSheet() method to make a copy of that 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. Run the new function by selecting duplicateAndOrganizeActiveSheet in the toolbar and clicking the button.

You should now see a new sheet in your spreadsheet! Go back to Sheets, and a new "Copy of Sheet_Original" sheet tab should exist.

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 will use the Sheet class to rename and format the duplicate sheet.

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 spacing with methods from the Sheet class.

Changing 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 on the Sheets_Original sheet to activate it.
  2. Adjust 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, you can see another duplicate sheet get created and renamed when you run the function now:

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 "Sheet_".

Adjusting 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 some frozen rows:

  1. In Google Sheets, click on the Sheets_Original sheet to activate it.
  2. Adjust 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 you can see a new sheet get created, renamed, activated, and formatted:

The code you just 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 makes the rows remain visible as the reader scrolls down the spreadsheet.

In the next section you'll learn about ranges and basic data manipulation.

The Range class and its methods provide the bulk of most data manipulation and formatting in the Spreadsheet service.

This section introduces basic data manipulation with ranges. These exercises focus more on how to utilize ranges in Apps Script while codelabs in this playlist go into greater depth on data manipulation and data formatting.

Moving 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. You can check out this description of A1 notation if you need to re-familiarize yourself with the format.

Let's update your duplicateAndOrganizeActiveSheet() method to also move some data around:

  1. In Google Sheets, click on the Sheets_Original sheet to activate it.
  2. Adjust 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, you can see yet another duplicate sheet created, activated, and formatted. This time, though, the contents of column F are moved over to column C:

The new code uses the method getRange(a1Notation) to identify which range of data you want 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 by the A1 notation "C2". This is a single cell, rather than a column. When moving data you don't need to exactly match the sizes to the target and destination ranges; Apps Script will simply align the first cell of each.

Sorting 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 on the Sheets_Original sheet to activate it.
  2. Adjust 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, will sort 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 (that is, the whole table excluding the column headers). The code then calls the sort(sortSpecObj) method to sort the table. Here, the sortSpecObj parameter is just a column number to sort by; the method sorts the range so that the indicated column values go from lowest to highest (ascending values). The sort(sortSpecObj) method is capable of more complex sorting requirements, but you don't need them here; you can see all the different ways you can call sort ranges in the method reference documentation.

Congrats! You've successfully completed all of the exercises in the codelab. The next section reviews the key points of this codelab and previews the next codelab in this playlist.

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 more in depth on how to read, write, and modify data within a spreadsheet.

Find the next codelab at Working with data!