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.

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

Actions

Add Worksheet

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

Input	Notes	Example
Input Connection connection / Required	Notes	Example
Input Column Headings data / Required	Notes An array of strings that are the column headings.	Example ["Column 1","Column 2","Column 3"]
Input Spreadsheet ID string / Required	Notes 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.	Example 12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Input Worksheet Title string / Required	Notes Specifies the title of the sheet.	Example MySpreadSheet

---

Append Rows

Append new rows to a Worksheet | key: appendRows

Input	Default	Notes	Example
Input Connection connection / Required	Default	Notes	Example
Input Rows code / Required	Default	Notes 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.	Example [[1,2,3], [4,5,6]] or [ { "Column 1": "a", "Column 2": "b", "Column 3": "c" }, { "Column 1": "d", "Column 2": "e", "Column 3": "f" }]
Input Spreadsheet ID string / Required	Default	Notes 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.	Example 12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Input Store Raw Values boolean / Required	Default false	Notes Specifies whether values should be converted as if they were typed into the spreadsheet or whether to store the raw values as provided.	Example
Input Worksheet Title string / Required	Default	Notes Specifies the title of the sheet.	Example MySpreadSheet

---

Clear Worksheet

Clear all data in the a Worksheet | key: clearSheet

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

---

Create Spreadsheet

Create a new Google Sheet Document | key: createDocument

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

---

Get Rows

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

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

Output Example Payload

{  "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
Input Connection connection / Required	Notes	Example
Input Spreadsheet ID string / Required	Notes 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.	Example 12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI

---

Remove Worksheet

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

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

---

Set Header Row

Set the column headings in a Worksheet | key: setHeaderRow

Input	Notes	Example
Input Connection connection / Required	Notes	Example
Input Column Headings data / Required	Notes An array of strings that are the column headings.	Example ["Column 1","Column 2","Column 3"]
Input Spreadsheet ID string / Required	Notes 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.	Example 12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Input Worksheet Title string / Required	Notes Specifies the title of the sheet.	Example MySpreadSheet

---

Update Rows

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

Input	Default	Notes	Example
Input Connection connection / Required	Default	Notes	Example
Input Spreadsheet ID string / Required	Default	Notes 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.	Example 12SawrJM-AcbLMgorjcyaGpPrKnNTp7FPot4yvM1ehbI
Input Store Raw Values boolean / Required	Default false	Notes Specifies whether values should be converted as if they were typed into the spreadsheet or whether to store the raw values as provided.	Example
Input Worksheet Title string / Required	Default	Notes Specifies the title of the sheet.	Example MySpreadSheet
Input Values code / Required	Default	Notes 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.	Example { "1": { "Column 1": "a", "Column 2": "b", "Column 3": "c" }, "2": { "Column 1": "d", "Column 2": "e", "Column 3": "f" }}

---