Skip to main content

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
InputDefaultNotes
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

InputNotesExample
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

InputDefaultNotesExample
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

InputNotesExample
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

InputNotesExample
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

InputNotesExample
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

InputNotesExample
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

InputNotesExample
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

InputNotesExample
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

InputDefaultNotesExample
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"  }}