Part 3: How to Reference Tabs, Spreadsheets, and Cells with Google App Scripts

When working with Google App Scripts, one of the most fundamental tasks is referencing various elements within Google Sheets, such as tabs, spreadsheets, and cells. In this blog post, we will dive into the essentials of accessing and manipulating these elements to enhance your Google Sheets automation capabilities.

Accessing Spreadsheets

Before diving into specific elements within a sheet, let’s review how to access the spreadsheet itself. There are several ways to access a spreadsheet in Google App Scripts:

Active Spreadsheet

To access the currently active spreadsheet, you can use the SpreadsheetApp.getActiveSpreadsheet() method.

let activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

Spreadsheet by ID

If you want to access a specific spreadsheet that might not be the currently active one, you can use the SpreadsheetApp.openById() method with the spreadsheet’s unique ID.

let spreadsheetId = 'your_spreadsheet_id_here';
//You can find your spreadsheet id at the end of a link after /d/ and before /edit
let spreadsheet = SpreadsheetApp.openById(spreadsheetId);

Spreadsheet by URL

Alternatively, you can access a specific spreadsheet using its URL with the SpreadsheetApp.openByUrl() method.

let spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/your_spreadsheet_id_here/edit';
let spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

Accessing Sheets (Tabs)

Once you have a reference to a spreadsheet, the next step is to access the sheets (or tabs) within it. There are several ways to do this:

Active Sheet

To access the currently active sheet (tab) in the active spreadsheet, use the SpreadsheetApp.getActiveSheet() method.

let activeSheet = SpreadsheetApp.getActiveSheet();

Sheet by Name

To access a sheet by its name, use the getSheetByName() method on the spreadsheet object.

let sheetName = 'Sheet2';
let sheet = activeSpreadsheet.getSheetByName(sheetName);

All Sheets

To access all the sheets in a spreadsheet, use the getSheets() method on the spreadsheet object. This method returns an array of sheets.

let sheets = activeSpreadsheet.getSheets();

// Loop through all the sheets
sheets.forEach(sheet => {
  console.log(sheet.getName());
});

Accessing Cells and Ranges

To access cells and ranges within a sheet, you can use the getRange() method on the sheet object. This method accepts various types of input.

Single Cell by A1 Notation

To access a single cell using A1 notation, provide the cell’s address as a string to the getRange() method.

let cell = activeSheet.getRange('B2');

Range of Cells by A1 Notation

To access a range of cells using A1 notation, provide the range’s address as a string to the getRange() method.

let range = activeSheet.getRange('A1:C3');

Cell by Row and Column Indices

To access a single cell using row and column indices, provide the row and column numbers as separate arguments to the getRange() method.

let row = 2;
let column = 2;
let cellByIndex = activeSheet.getRange(row, column);

Range of Cells by Row and Column Indices

To access a range of cells using row and column indices, provide the starting row and column numbers, and the number of rows and columns in the range as separate arguments to the getRange() method.

let startRow = 1;
let startColumn = 1;
let numRows = 3;
let numColumns = 3;
let rangeByIndex = activeSheet.getRange(startRow, startColumn, numRows, numColumns);

Reading and Writing Cell Values

Now that you know how to access cells and ranges, let’s see how to read and write their values.

Reading a Single Cell Value

To read the value of a single cell, use the getValue() method on the range object.

let value = cell.getValue();
console.log(value);

Reading Values from a Range of Cells

To read values from a range of cells, use the getValues() method on the range object. This method returns a 2D array of values.

let values = range.getValues();
console.log(values);

Writing a Single Cell Value

To write a value to a single cell, use the setValue() method on the range object.

cell.setValue('Hello, world!');

Writing Values to a Range of Cells

To write values to a range of cells, use the setValues() method on the range object. This method accepts a 2D array of values.

range.setValues([
  [1, 2, 3],
  [4, 5, 6],
  [7, 8, 9]
]);

Modifying Cell Formats

Besides reading and writing cell values, you can also modify cell formats, such as font styles, background colors, and number formats.

Setting Font Styles

To set font styles, such as bold, italic, and font size, use the setFontWeight(), setFontStyle(), and setFontSize() methods on the range object.

range.setFontWeight('bold');
range.setFontStyle('italic');
range.setFontSize(12);

Setting Background Colors

To set background colors, use the setBackground() method on the range object. This method accepts a color string in various formats (e.g., ‘red’, ‘#FF0000’, ‘rgb(255, 0, 0)’).

range.setBackground('yellow');

Setting Number Formats

To set number formats, use the setNumberFormat() method on the range object. This method accepts a number format string.

range.setNumberFormat('#,##0.00');

Conclusion

Understanding how to reference tabs, spreadsheets, and cells in Google App Scripts is a crucial skill for automating tasks in Google Sheets. With this knowledge, you can now read and write data, change cell formats, and build custom functions to enhance your sheets.

Practice using these methods in your projects, and stay tuned for our next blog post, where we will discuss how to create custom functions in Google Sheets using Google App Scripts.