ASS05: Introduction to Google Sheets API
Introduction
The Google Sheets API enables reading and modifying spreadsheet data through a RESTful interface. While Apps Script projects often utilize the SpreadsheetApp (a Spreadsheet Service) for this purpose, the Google Sheets API can also be accessed via the Advanced Sheets Service or by sending requests with the UrlFetchApp Service.
The Google Sheets API offers a valuable introduction to using API services for workflow automation. Grasping these principles will provide a foundation for exploring other Google APIs like Docs and Slides, building a strong programming proficiency within the Google Apps Script ecosystem.
YouTube (To be released)
CRUD
The fundamental data management operations in computer programming are Create, Read, Update, and Delete (CRUD). Simplifying tasks by categorizing them under these four actions allows for easier identification of the appropriate API services.
Fundamental Structure of Google Sheets
As a developer, understanding the Google Sheets hierarchy is crucial for efficiently utilizing the Spreadsheet Service. Familiarity with this structure allows for easier identification of the appropriate methods to accomplish desired tasks within the Google Sheets API.
Spreadsheet: The primary object in Google Sheets which contains a unique spreadsheet id and can have multiple Sheets.
Sheet: A page or tab within a spreadsheet which contains a unique numeric sheet id.
Range: A range of cells.
Rows: Rows of cells.
Columns: Columns of cells.
Cell: An individual field of text or data within a sheet.
This is a pseudo URL to a range in a Google Sheets, you can also see the basic structure of Google Sheets here.
https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit?gid=SHEET_ID&range=A1NOTATION
* Tips: Other Google APIs such as Google Docs and Slides have similar structures which need to be studied if you are not familiar with them.
Basic Operations
Google Sheets automation typically involves identifying the target Google Sheets hierarchy and then selecting the appropriate method within that object to execute the desired task.
SpreadsheetApp Object
The SpreadsheetApp service allows you to interact with Google Sheets within a script. You can access a spreadsheet by its ID or URL, create a new one, or use the spreadsheet the script is currently bound to.
openById(id) - Opens the spreadsheet with the given ID.
openByUrl(url) - Opens the spreadsheet with the given URL.
open(file) - Opens the spreadsheet that corresponds to the given File object.
create(name) - Creates a new spreadsheet with the given name.
create(name, rows, columns) - Creates a new spreadsheet with the given name and the specified number of rows and columns.
getActive() - Returns the currently active spreadsheet, or null if there is none.
getActiveSpreadsheet() - Returns the currently active spreadsheet, or null if there is none.
function getSpreadsheetMethods() {
// get the active spreadsheet when the script is bounded to a spreadsheet
let ss = SpreadsheetApp.getActive(); // use "let" here so we can reassign ss to a different spreadsheet object
console.info(`SpreadsheetApp.getActive(): ${ss.getUrl()}`);
// get the active spreadsheet when the script is bounded to a spreadsheet
ss = SpreadsheetApp.getActiveSpreadsheet();
console.info(`SpreadsheetApp.getActiveSpreadsheet(): ${ss.getUrl()}`);
const id = ss.getId();
const url = ss.getUrl();
// open a spreadsheet with given ID
ss = SpreadsheetApp.openById(id);
console.info(`SpreadsheetApp.openById(id): ${ss.getUrl()}`);
// open a spreadsheet with given URL
ss = SpreadsheetApp.openByUrl(url);
console.info(`SpreadsheetApp.openByUrl(url): ${ss.getUrl()}`);
// create a new spreadsheet on your Google Drive with a given name
const name = 'ASS05: Introduction to Google Sheets API ' + new Date().toLocaleDateString();
ss = SpreadsheetApp.create(name);
console.info(`SpreadsheetApp.create(name): ${ss.getUrl()}`);
}
Tips: All of these methods are included in the class SpreadsheetApp, so you should call them in the script like “SpreadsheetApp.methodName()”. And they all return a Spreadsheet object that you can assign to a variable that can be used later in your code.
Spreadsheet Object
There are multiple ways You can get the sheet from a spreadsheet with the given ID or name. Alternatively, you can iterate all sheets to find the one you need or create a new sheet.
getActiveSheet() - Gets the active sheet in a spreadsheet.
getSheetById(id) - Gets the sheet with the given ID.
getSheetByName(name) - Returns a sheet with the given name.
getSheets() - Gets all the sheets in this spreadsheet.
insertSheet() - Inserts a new sheet into the spreadsheet, using a default sheet name.
insertSheet(sheetName) - Inserts a new sheet into the spreadsheet with the given name.
function getSheetMethods() {
// get the active sheet in a spreadsheet.
let sheet = SpreadsheetApp.getActiveSheet();
console.log(`getActiveSheet(): ${sheet.getName()}`);
// get a sheet with the given ID.
const id = sheet.getSheetId();
sheet = ss.getSheetById(id);
console.log(`getSheetById(id): ${sheet.getName()}`);
// get a sheet with the given name.
const name = sheet.getName();
sheet = ss.getSheetByName(name);
console.log(`getSheetByName(name): ${sheet.getName()}`);
// insert a new sheet.
sheet = ss.insertSheet();
console.log(`insertSheet(): ${sheet.getName()}`);
// insert a new sheet with the given name.
sheet = ss.insertSheet('ASS05');
console.log(`insertSheet(name): ${sheet.getName()}`);
// list all sheets.
const sheets = ss.getSheets();
console.log(`getSheets(): ${sheets.map(sheet => sheet.getName()).join(", ")}`);
}
Tips: All of these methods are included in class Spreadsheet, so you should call them in the script like “Spreadsheet.methodName()”. And they all return a Sheet object that you can assign to a variable that can be used later in your code.
Sheet Object
After obtaining the sheet object, there are several methods to identify and retrieve specific target ranges within it.
getActiveRange() - Returns the selected range in the active sheet, or null if there is no active range.
getActiveCell() - Returns the active cell in this sheet.
getCurrentCell() - Returns the current cell in the active sheet or null if there is no current cell.
getDataRange() - Returns a Range corresponding to the dimensions in which data is present.
getRange(a1Notation) - Returns the range as specified in A1 notation or R1C1 notation.
getRange(row, column, numRows, numColumns) - Returns the range with the top left cell at the given coordinates with the given number of rows and columns.
function getRangeMethods(){
// get the active sheet.
const sheet = SpreadsheetApp.getActiveSheet();
// get the active range.
let range = sheet.getActiveRange(); // or SpreadsheetApp.getActiveRange().
console.log(`SpreadsheetApp.getActiveRange(): ${range.getA1Notation()}`);
// get the active cell.
range = sheet.getActiveCell();
console.log(`getActiveCell(): ${range.getA1Notation()}`);
// get the current cell.
range = sheet.getCurrentCell();
console.log(`getCurrentCell(): ${range.getA1Notation()}`);
// get data range.
range = sheet.getDataRange();
console.log(`getDataRange(): ${range.getA1Notation()}`);
// get range with given a1Notation like "A1:C5".
range = sheet.getRange("A1:C5");
console.log(`getRange(a1Notation): ${range.getA1Notation()}`);
// get range with given start row, start column, rows, and columns.
range = sheet.getRange(1, 1, 3, 4); // range starts from row 1, column 1, contains 3 rows and 4 columns (A1:D3).
console.log(`getRange(rows, columns, numRows, numColumns): ${range.getA1Notation()}`);
}
Tips: All of these methods are included in class Sheet, so you should call them in the script like “Sheet.methodName()”. And they all return a Range object that you can assign to a variable that can be used later in your code. There are more ways to reference ranges, like inserting or appending new rows, columns, activating a new range which are not listed here.
Range Object
We finally reached the cells in the sheet we usually need to manipulate in our automation. With these methods below, we can easily retrieve or update data to the target cells.
getValue() - Returns the value of the top-left cell in the range.
getValues() - Returns the rectangular grid of values for this range.
setValue(value) - Sets the value of the range (same value for all cells in the range).
setValues(values) - Sets a rectangular grid of values (must match dimensions of this range).
function workWithRangeMethods() {
// get the active sheet to work with.
const sheet = SpreadsheetApp.getActiveSheet();
// sample dataset.
const sampleValues = [
['Name', 'Email', 'Gender'],
['Ashton', 'ashton@test.com', 'Male'],
['Bob', 'bob@test.com', 'Male'],
['Chris', 'chris@test.com', 'Male'],
]
// rows of the sample dataset.
const rows = sampleValues.length;
// columns of the sample data set.
const columns = sampleValues[0].length;
// get a proper range for the sample dataset to fit the size.
let range = sheet.getRange(1, 1, rows, columns);
// update the range with the sample dataset
range.setValues(sampleValues);
// get the top-left cell value.
const value = range.getValue();
console.info('getValue():', value);
// get all values in the range.
const values = range.getValues();
console.info('getValues():', values);
}
Tips: All of these methods are included in class Range, so you should call them in the script like “Range.methodName()”. The Range object provides numerous methods for interacting with spreadsheet cells. The methods listed above are fundamental for working with cell values.
Resources and Further Learning
Apps Script Simplified
Links
Join Memberships Make a copy Github Hire Me Buy Me a Coffee
Previous Read
ASS04: Understanding Script Editor Features
Next Read
Comments
Post a Comment