Google Sheets Component

Create, read, and modify Google Spreadsheets

Component key: google-sheets

Description

Google Sheets is Google's spreadsheet service. This component allows you to create, read, and modify spreadsheets stored in a Google Drive Account.

Connections

Google Sheets OAuth 2.0

The Google Sheets component authenticates requests through Google's OAuth service. To create a Google Sheets developer account and authenticate using Google OAuth, follow directions here Now, you will have to configure OAuth 2.0 settings. Create a new Google Sheets connection.

For Client ID and Client Secret enter the values that you got from the Google Cloud Platform auth settings.
For Scopes choose from the list found on the Google docs

Input	Notes	Example
Authorize URL	The OAuth 2.0 Authorization URL for Google Sheets	https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&prompt=consent
Client ID
Client Secret
Scopes	A space-delimited set of one or more scopes to get the user's permission to access.	https://www.googleapis.com/auth/spreadsheets
Token URL	The OAuth 2.0 Token URL for Google Sheets	https://oauth2.googleapis.com/token

Data Sources

Select Column

Select a Column from a Worksheet | key: selectColumn | type: picklist

Input	Notes	Example
Connection
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Worksheet Title	Specifies the title of the sheet.	MySpreadSheet

{
  "result": [
    {
      "key": "Column 1",
      "label": "Column 1"
    },
    {
      "key": "Column 2",
      "label": "Column 2"
    }
  ]
}

Select Columns

Select Columns from a Worksheet | key: selectColumns | type: objectSelection

Input	Notes	Example
Connection
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Worksheet Title	Specifies the title of the sheet.	MySpreadSheet

{
  "result": [
    {
      "object": {
        "key": "id",
        "label": "id"
      }
    },
    {
      "object": {
        "key": "equipmentId",
        "label": "equipmentId"
      }
    }
  ]
}

Select Worksheet

Select a Worksheet | key: selectWorksheet | type: picklist

Input	Notes	Example
Connection
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Worksheet Return	Select whether to return the Worksheet ID or Name.	false

{
  "result": [
    {
      "key": "1",
      "label": "Sheet1"
    }
  ]
}

Actions

Add Worksheet

Add a new Worksheet to a Google Sheet Document | key: addSheet

Input	Notes	Example
Connection
Column Headings	An array of strings that are the column headings.
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Worksheet Title	Specifies the title of the sheet.	MySpreadSheet

Append Rows

Append new rows to a Worksheet | key: appendRows

Input	Notes	Example
Connection
Rows	Can be an array of arrays, with the outer array items representing the rows and the inner array values being the cell values. Can also be an array of objects, where the object keys are the corresponding column header values and the object values are the cell values.
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Store Raw Values	Specifies whether values should be converted as if they were typed into the spreadsheet or whether to store the raw values as provided.	false
Worksheet Title	Specifies the title of the sheet.	MySpreadSheet

Clear Worksheet

Clear all data in the a Worksheet | key: clearSheet

Input	Notes	Example
Connection
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Worksheet Title	Specifies the title of the sheet.	MySpreadSheet

Create Spreadsheet

Create a new Google Sheet Document | key: createDocument

Input	Notes	Example
Connection
Document Title	Specifies the title of the document.	MySpreadSheet

List Columns

Get the headers of a Worksheet | key: listColumns

Input	Notes	Example
Connection
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Worksheet Title	Specifies the title of the sheet.	MySpreadSheet

{
  "data": [
    "Column 1",
    "Column 2"
  ]
}

List Rows

List the cell values of rows in a Worksheet | key: getRows

Input	Notes	Example
Connection
Limit	The number of rows to retrieve at once.	100
Offset	The number of rows to skip from the top.	0
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Worksheet Title	Specifies the title of the sheet.	MySpreadSheet

{
  "data": [
    {
      "Column 1": "a",
      "Column 2": "b"
    },
    {
      "Column 1": "c",
      "Column 2": "d"
    }
  ]
}

List Worksheets

List information about all Worksheets in a Google Sheet Document | key: listSheets

Input	Notes	Example
Connection
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI

{
  "data": [
    {
      "spreadsheetId": "1K__zH9e2bd",
      "title": "Sheet1",
      "worksheetId": "od6"
    }
  ]
}

Raw Request

Send raw HTTP request to Google Sheets | key: rawRequest

Input	Notes	Example
Connection
Data	The HTTP body payload to send to the URL.	{"exampleKey": "Example Data"}
Debug Request	Enabling this flag will log out the current request.	false
File Data	File Data to be sent as a multipart form upload.	[{key: "example.txt", value: "My File Contents"}]
File Data File Names	File names to apply to the file data inputs. Keys must match the file data keys above.
Form Data	The Form Data to be sent as a multipart form upload.	[{"key": "Example Key", "value": new Buffer("Hello World")}]
Header	A list of headers to send with the request.	User-Agent: curl/7.64.1
Max Retry Count	The maximum number of retries to attempt. Specify 0 for no retries.	0
Method	The HTTP method to use.
Query Parameter	A list of query parameters to send with the request. This is the portion at the end of the URL similar to ?key1=value1&key2=value2.
Response Type	The type of data you expect in the response. You can request json, text, or binary data.	json
Retry On All Errors	If true, retries on all erroneous responses regardless of type. This is helpful when retrying after HTTP 429 or other 3xx or 4xx errors. Otherwise, only retries on HTTP 5xx and network errors.	false
Retry Delay (ms)	The delay in milliseconds between retries. This is used when 'Use Exponential Backoff' is disabled.	0
Timeout	The maximum time that a client will await a response to its request	2000
URL	Input the path only (/v4/spreadsheets/{spreadsheetId}), The base URL is already included (https://sheets.googleapis.com). For example, to connect to https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}, only /v4/spreadsheets/{spreadsheetId} is entered in this field.	/v4/spreadsheets/{spreadsheetId}
Use Exponential Backoff	Specifies whether to use a pre-defined exponential backoff strategy for retries. When enabled, 'Retry Delay (ms)' is ignored.	false

Remove Worksheet

Remove a Worksheet from a Google Sheet Document | key: removeSheet

Input	Notes	Example
Connection
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Worksheet Title	Specifies the title of the sheet.	MySpreadSheet

Set Header Row

Set the column headings in a Worksheet | key: setHeaderRow

Input	Notes	Example
Connection
Column Headings	An array of strings that are the column headings.
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Worksheet Title	Specifies the title of the sheet.	MySpreadSheet

Update Rows

Update call values of rows in a Worksheet | key: updateRows

Input	Notes	Example
Connection
Spreadsheet ID	Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL.	12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Store Raw Values	Specifies whether values should be converted as if they were typed into the spreadsheet or whether to store the raw values as provided.	false
Worksheet Title	Specifies the title of the sheet.	MySpreadSheet
Values	An object where the keys are row numbers and the values are objects where their keys are column names and their values are cell values.

Create, read, and modify Google Spreadsheets

Description​

Connections​

Google Sheets OAuth 2.0​

Data Sources​

Select Column​

Select Columns​

Select Worksheet​

Actions​

Add Worksheet​

Append Rows​

Clear Worksheet​

Create Spreadsheet​

List Columns​

List Rows​

List Worksheets​

Raw Request​

Remove Worksheet​

Set Header Row​

Update Rows​