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 in Prismatic. 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	Default	Notes
Authorize URL string / Required Hidden Field authorizeUrl	https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&prompt=consent	The OAuth 2.0 Authorization URL for Google Sheets
Client ID string / Required clientId
Client Secret password / Required clientSecret
Scopes string scopes	https://www.googleapis.com/auth/spreadsheets	A space-delimited set of one or more scopes to get the user's permission to access.
Token URL string / Required Hidden Field tokenUrl	https://oauth2.googleapis.com/token	The OAuth 2.0 Token URL for Google Sheets

Data Sources

Select Column

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

Input	Notes	Example
Connection connection / Required connection
Spreadsheet ID string / Required spreadsheetId	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 string / Required title	Specifies the title of the sheet.	MySpreadSheet

Example Payload for Select Column

{
  "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 connection / Required connection
Spreadsheet ID string / Required spreadsheetId	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 string / Required title	Specifies the title of the sheet.	MySpreadSheet

Example Payload for Select Columns

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

---

Select Worksheet

Select a Worksheet | key: selectWorksheet | type: picklist

Input	Default	Notes	Example
Connection connection / Required connection
Spreadsheet ID string / Required spreadsheetId		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 string / Required worksheetReturnName	false	Select whether to return the Worksheet ID or Name.

Example Payload for Select Worksheet

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

---

Actions

Add Worksheet

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

Input	Notes	Example
Connection connection / Required connection
Column Headings code / Required headers	An array of strings that are the column headings.
Spreadsheet ID string / Required spreadsheetId	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 string / Required title	Specifies the title of the sheet.	MySpreadSheet

---

Append Rows

Append new rows to a Worksheet | key: appendRows

Input	Default	Notes	Example
Connection connection / Required connection
Rows code / Required 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 string / Required spreadsheetId		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 boolean / Required storeRawValues	false	Specifies whether values should be converted as if they were typed into the spreadsheet or whether to store the raw values as provided.
Worksheet Title string / Required title		Specifies the title of the sheet.	MySpreadSheet

---

Clear Worksheet

Clear all data in the a Worksheet | key: clearSheet

Input	Notes	Example
Connection connection / Required connection
Spreadsheet ID string / Required spreadsheetId	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 string / Required title	Specifies the title of the sheet.	MySpreadSheet

---

Create Spreadsheet

Create a new Google Sheet Document | key: createDocument

Input	Notes	Example
Connection connection / Required connection
Document Title string / Required title	Specifies the title of the document.	MySpreadSheet

---

List Columns

Get the headers of a Worksheet | key: listColumns

Input	Notes	Example
Connection connection / Required connection
Spreadsheet ID string / Required spreadsheetId	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 string / Required title	Specifies the title of the sheet.	MySpreadSheet

Example Payload for List Columns

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

---

List Rows

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

Input	Default	Notes	Example
Connection connection / Required connection
Limit string limit	100	The number of rows to retrieve at once.	100
Offset string offset	0	The number of rows to skip from the top.	0
Spreadsheet ID string / Required spreadsheetId		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 string / Required title		Specifies the title of the sheet.	MySpreadSheet

Example Payload for List Rows

{
  "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 connection / Required connection
Spreadsheet ID string / Required spreadsheetId	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

Example Payload for List Worksheets

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

---

Raw Request

Send raw HTTP request to Google Sheets | key: rawRequest

Input	Default	Notes	Example
Connection connection / Required connection
Data string data		The HTTP body payload to send to the URL.	{"exampleKey": "Example Data"}
Debug Request boolean debugRequest	false	Enabling this flag will log out the current request.
File Data string Key Value List fileData		File Data to be sent as a multipart form upload.	[{key: "example.txt", value: "My File Contents"}]
File Data File Names string Key Value List fileDataFileNames		File names to apply to the file data inputs. Keys must match the file data keys above.
Form Data string Key Value List formData		The Form Data to be sent as a multipart form upload.	[{"key": "Example Key", "value": new Buffer("Hello World")}]
Header string Key Value List headers		A list of headers to send with the request.	User-Agent: curl/7.64.1
Max Retry Count string maxRetries	0	The maximum number of retries to attempt.
Method string / Required method		The HTTP method to use.
Query Parameter string Key Value List queryParams		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 string / Required responseType	json	The type of data you expect in the response. You can request json, text, or binary data.
Retry On All Errors boolean retryAllErrors	false	If true, retries on all erroneous responses regardless of type.
Retry Delay (ms) string retryDelayMS	0	The delay in milliseconds between retries.
Timeout string timeout		The maximum time that a client will await a response to its request	2000
URL string / Required 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 boolean useExponentialBackoff	false	Specifies whether to use a pre-defined exponential backoff strategy for retries.

---

Remove Worksheet

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

Input	Notes	Example
Connection connection / Required connection
Spreadsheet ID string / Required spreadsheetId	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 string / Required title	Specifies the title of the sheet.	MySpreadSheet

---

Set Header Row

Set the column headings in a Worksheet | key: setHeaderRow

Input	Notes	Example
Connection connection / Required connection
Column Headings code / Required headers	An array of strings that are the column headings.
Spreadsheet ID string / Required spreadsheetId	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 string / Required title	Specifies the title of the sheet.	MySpreadSheet

---

Update Rows

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

Input	Default	Notes	Example
Connection connection / Required connection
Spreadsheet ID string / Required spreadsheetId		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 boolean / Required storeRawValues	false	Specifies whether values should be converted as if they were typed into the spreadsheet or whether to store the raw values as provided.
Worksheet Title string / Required title		Specifies the title of the sheet.	MySpreadSheet
Values code / Required 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.

---