Google Sheets API Connector Overview | Workflows

Home
Workflows
Documentation
Reference

Stay organized with collections Save and categorize content based on your preferences.

The Workflows connector defines the built-infunctions that can be used to access other Google Cloud products within aworkflow.

This page provides an overview of the individual connector.There is no need to import or load connector libraries in a workflow—connectorswork out of the box when used in a call step.

Google Sheets API

Reads and writes Google Sheets.To learn more, see the Google Sheets API documentation.

Spreadsheet ID

Note that every Google Sheets spreadsheet has a unique spreadsheetId value andthat when using the connector, the spreadsheetId can be retrieved from theresp result. By default, when you create a spreadsheet, the spreadsheet issaved to your root folder on Google Drive. For more information, seeWrite to Google Sheets from a workflow.

Google Sheets connector sample

YAML

# This workflow demonstrates how to use the Google Sheets connector:# Create, update, get, and delete a Google Sheets spreadsheet# Expected output: "SUCCESS"- init: assign: - createRange: "A20" # example range - getRange: "A1:B4" # example range - row: values: - {"userEnteredFormat": {"backgroundColor": {"red": 0, "green":1, "blue":0, "alpha":1}}} - start1: sheetId: 0 rowIndex: 2 columnIndex: 0 - request1: rows: - ${row} fields: userEnteredFormat.backgroundColor start: ${start1} - driveScope: https://www.googleapis.com/auth/drive- create_spreadsheet: call: googleapis.sheets.v4.spreadsheets.create args: body: connector_params: scopes: ${driveScope} result: resp- assign_sheet_id: assign: - sheetId: ${resp.spreadsheetId}- update_spreadsheet: call: googleapis.sheets.v4.spreadsheets.values.update args: spreadsheetId: ${sheetId} range: ${createRange} valueInputOption: RAW includeValuesInResponse: body: values: - ["text to update"]- batch_update: call: googleapis.sheets.v4.spreadsheets.batchUpdate args: spreadsheetId: ${sheetId} body: requests: - updateCells: ${request1} includeSpreadsheetInResponse: true connector_params: scopes: ${driveScope}- get_from_sheet: call: googleapis.sheets.v4.spreadsheets.get args: spreadsheetId: ${sheetId} ranges: - ${getRange} includeGridData: false connector_params: scopes: ${driveScope}- delete_sheet: call: http.delete args: url: ${"https://www.googleapis.com/drive/v2/files/" + sheetId} auth: type: OAuth2 scopes: ${driveScope}- return_step: return: SUCCESS

JSON

[ { "init": { "assign": [ { "createRange": "A20" }, { "getRange": "A1:B4" }, { "row": { "values": [ { "userEnteredFormat": { "backgroundColor": { "red": 0, "green": 1, "blue": 0, "alpha": 1 } } } ] } }, { "start1": { "sheetId": 0, "rowIndex": 2, "columnIndex": 0 } }, { "request1": { "rows": [ "${row}" ], "fields": "userEnteredFormat.backgroundColor", "start": "${start1}" } }, { "driveScope": "https://www.googleapis.com/auth/drive" } ] } }, { "create_spreadsheet": { "call": "googleapis.sheets.v4.spreadsheets.create", "args": { "body": null, "connector_params": { "scopes": "${driveScope}" } }, "result": "resp" } }, { "assign_sheet_id": { "assign": [ { "sheetId": "${resp.spreadsheetId}" } ] } }, { "update_spreadsheet": { "call": "googleapis.sheets.v4.spreadsheets.values.update", "args": { "spreadsheetId": "${sheetId}", "range": "${createRange}", "valueInputOption": "RAW", "includeValuesInResponse": null, "body": { "values": [ [ "text to update" ] ] } } } }, { "batch_update": { "call": "googleapis.sheets.v4.spreadsheets.batchUpdate", "args": { "spreadsheetId": "${sheetId}", "body": { "requests": [ { "updateCells": "${request1}" } ], "includeSpreadsheetInResponse": true }, "connector_params": { "scopes": "${driveScope}" } } } }, { "get_from_sheet": { "call": "googleapis.sheets.v4.spreadsheets.get", "args": { "spreadsheetId": "${sheetId}", "ranges": [ "${getRange}" ], "includeGridData": false, "connector_params": { "scopes": "${driveScope}" } } } }, { "delete_sheet": { "call": "http.delete", "args": { "url": "${\"https://www.googleapis.com/drive/v2/files/\" + sheetId}", "auth": { "type": "OAuth2", "scopes": "${driveScope}" } } } }, { "return_step": { "return": "SUCCESS" } }]

Module: googleapis.sheets.v4.spreadsheets

Functions
`batchUpdate`	Applies one or more updates to the spreadsheet. Each request isvalidated before being applied. If any request is not valid then theentire request will fail and nothing will be applied. Some requests havereplies to give you some information about how they are applied. Thereplies will mirror the requests. For example, if you applied 4 updatesand the 3rd one had a reply, then the response will have 2 emptyreplies, the actual reply, and another empty reply, in that order. Dueto the collaborative nature of spreadsheets, it is not guaranteed thatthe spreadsheet will reflect exactly your changes after this completes,however it is guaranteed that the updates in the request will be appliedtogether atomically. Your changes may be altered with respect tocollaborator changes. If there are no collaborators, the spreadsheetshould reflect your changes.
`create`	Creates a spreadsheet, returning the newly created spreadsheet.
`get`	Returns the spreadsheet at the given ID. The caller must specify thespreadsheet ID. By default, data within grids is not returned. You caninclude grid data in one of 2 ways: * Specify a field mask listing yourdesired fields using the `fields` URL parameter in HTTP * Set theincludeGridData URL parameter to true. If a field mask is set, the`includeGridData` parameter is ignored For large spreadsheets, as a bestpractice, retrieve only the specific spreadsheet fields that you want.To retrieve only subsets of spreadsheet data, use the ranges URLparameter. Ranges are specified using A1notation.You can define a single cell (for example, `A1`) or multiple cells (forexample, `A1:D5`). You can also get cells from other sheets within thesame spreadsheet (for example, `Sheet2!A1:C4`) or retrieve multipleranges at once (for example, `?ranges=A1:D5&ranges=Sheet2!A1:C4`).Limiting the range returns only the portions of the spreadsheet thatintersect the requested ranges.
`getByDataFilter`	Returns the spreadsheet at the given ID. The caller must specify thespreadsheet ID. This method differs from GetSpreadsheet in that itallows selecting which subsets of spreadsheet data to return byspecifying a dataFilters parameter. Multiple DataFilters can bespecified. Specifying one or more data filters returns the portions ofthe spreadsheet that intersect ranges matched by any of the filters. Bydefault, data within grids is not returned. You can include grid dataone of 2 ways: * Specify a field mask listing your desired fields usingthe `fields` URL parameter in HTTP * Set the includeGridData parameterto true. If a field mask is set, the `includeGridData` parameter isignored For large spreadsheets, as a best practice, retrieve only thespecific spreadsheet fields that you want.

Functions
`get`	Returns the developer metadata with the specified ID. The caller mustspecify the spreadsheet ID and the developer metadata's uniquemetadataId.
`search`	Returns all developer metadata matching the specified DataFilter. If theprovided DataFilter represents a DeveloperMetadataLookup object, thiswill return all DeveloperMetadata entries selected by it. If theDataFilter represents a location in a spreadsheet, this will return alldeveloper metadata associated with locations intersecting that region.

Module: googleapis.sheets.v4.spreadsheets.sheets

Functions
`copyTo`	Copies a single sheet from a spreadsheet to another spreadsheet. Returnsthe properties of the newly created sheet.

Module: googleapis.sheets.v4.spreadsheets.values

Functions
`append`	Appends values to a spreadsheet. The input range is used to search forexisting data and find a "table" within that range. Values will beappended to the next row of the table, starting with the first column ofthe table. See theguideand samplecodefor specific details of how tables are detected and data is appended.The caller must specify the spreadsheet ID, range, and avalueInputOption. The `valueInputOption` only controls how the inputdata will be added to the sheet (column-wise or row-wise), it does notinfluence what cell the data starts being written to.
`batchClear`	Clears one or more ranges of values from a spreadsheet. The caller mustspecify the spreadsheet ID and one or more ranges. Only values arecleared -- all other properties of the cell (such as formatting and datavalidation) are kept.
`batchClearByDataFilter`	Clears one or more ranges of values from a spreadsheet. The caller mustspecify the spreadsheet ID and one or more DataFilters. Ranges matchingany of the specified data filters will be cleared. Only values arecleared -- all other properties of the cell (such as formatting, datavalidation, etc..) are kept.
`batchGet`	Returns one or more ranges of values from a spreadsheet. The caller mustspecify the spreadsheet ID and one or more ranges.
`batchGetByDataFilter`	Returns one or more ranges of values that match the specified datafilters. The caller must specify the spreadsheet ID and one or moreDataFilters. Ranges that match any of the data filters in the requestwill be returned.
`batchUpdate`	Sets values in one or more ranges of a spreadsheet. The caller mustspecify the spreadsheet ID, a valueInputOption, and one or moreValueRanges.
`batchUpdateByDataFilter`	Sets values in one or more ranges of a spreadsheet. The caller mustspecify the spreadsheet ID, a valueInputOption, and one or moreDataFilterValueRanges.
`clear`	Clears values from a spreadsheet. The caller must specify thespreadsheet ID and range. Only values are cleared -- all otherproperties of the cell (such as formatting, data validation, etc..) arekept.
`get`	Returns a range of values from a spreadsheet. The caller must specifythe spreadsheet ID and a range.
`update`	Sets values in a range of a spreadsheet. The caller must specify thespreadsheet ID, range, and a valueInputOption.

Google Sheets API Connector Overview | Workflows | Google Cloud (2024)