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.

Documentation for the Node.js client used in this component can be found at https://googleapis.dev/nodejs/storage/latest.

Pair this with the Google Drive component to list the available Google Sheets attached to your account.

A common integration pattern involves listing files in a file store, and performing a series of actions on the array of files that are returned. See our looping over files quickstart for information about how to create a loop over an array of files.

Authorization Notes#

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

Google Sheets Connections#

Google Sheets OAuth 2.0#

InputKeyDefaultNotes

Authorize URL

string
/ Required
Hidden Field
authorizeUrlhttps://accounts.google.com/o/oauth2/authThe OAuth 2.0 Authorization URL for Google Sheets

Client Id

string
/ Required
clientId

Client Secret

string
/ Required
clientSecret

Scopes

string
scopesA space-delimited set of one or more scopes to get the user's permission to access.

Token URL

string
/ Required
Hidden Field
tokenUrlhttps://oauth2.googleapis.com/tokenThe OAuth 2.0 Token URL for Google Sheets

Actions#

Append To Spreadsheet#

Append values to a new row in a Google Sheet | key: appendToSheet

InputKeyDefaultNotesExample

Connection Input

connection
/ Required
googleConnection

Insert Data Option

string
/ Required
insertDataOptionDetermines how existing data is changed when new data is input.

Major Dimension

string
/ Required
majorDimensionRowsThe major dimension that results should use. For example, if the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then a request that selects that range and sets majorDimension=ROWS returns [[1,2],[3,4]], whereas a request that sets majorDimension=COLUMNS returns [[1,3],[2,4]].

Range

string
/ Required
rangeGoogle Sheets allows for 2 different formats of cell ranges: A1 Notation and R1C1 Notation.A1:A25

Date Time Render Option

string
/ Required
responseDateTimeRenderOptionDetermines how dates should be rendered in the output.

Value Render Option

string
/ Required
responseValueRenderOptionDetermines how values should be rendered in the output.

Spreadsheet ID

string
sheetIdEvery 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. my-example-sheet

Value Input Option

string
/ Required
valueInputOptionRawDetermines how input data should be interpreted.

Values

string
/ Required
Value List
valuesThe data that was read or to be written. This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension. Each item in the inner array corresponds with one cell.[1,2,3,4,5,6]

Clear Spreadsheet#

Clears all values from a spreadsheet -- all other properties of the cell (such as formatting, data validation, etc) are kept | key: clearSheet

InputKeyNotesExample

Connection Input

connection
/ Required
googleConnection

Range

string
/ Required
rangeGoogle Sheets allows for 2 different formats of cell ranges: A1 Notation and R1C1 Notation.A1:A25

Spreadsheet ID

string
sheetIdEvery 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. my-example-sheet

Create Spreadsheet#

Create a new Google Sheet | key: createSheet

InputKeyNotesExample

Connection Input

connection
/ Required
googleConnection

Spreadsheet Title

string
/ Required
sheetTitleProvide a string for the name of the new spreadsheet.MySpreadSheet

Get Spreadsheet#

Read the contents of a Google Sheet | key: getSheet

InputKeyDefaultNotesExample

Connection Input

connection
/ Required
googleConnection

Major Dimension

string
/ Required
majorDimensionRowsThe major dimension that results should use. For example, if the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then a request that selects that range and sets majorDimension=ROWS returns [[1,2],[3,4]], whereas a request that sets majorDimension=COLUMNS returns [[1,3],[2,4]].

Cell Ranges

string
/ Required
Value List
rangesGoogle Sheets allows for 2 different formats of cell ranges: A1 Notation and R1C1 NotationA1:A25

Date Time Render Option

string
/ Required
responseDateTimeRenderOptionDetermines how dates should be rendered in the output.

Value Render Option

string
/ Required
responseValueRenderOptionDetermines how values should be rendered in the output.

Spreadsheet ID

string
sheetIdEvery 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. my-example-sheet

Read Spreadsheet Properties#

Read the contents and metadata of a Google Sheet | key: readSheet

InputKeyNotesExample

Connection Input

connection
/ Required
googleConnection

Cell Ranges

string
Value List
rangesGoogle Sheets allows for 2 different formats of cell ranges: A1 Notation and R1C1 NotationA1:A25

Spreadsheet ID

string
sheetIdEvery 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. my-example-sheet

Update Spreadsheet#

Update the contents of an existing Google Sheet | key: updateSheet

InputKeyDefaultNotesExample

Connection Input

connection
/ Required
googleConnection

Major Dimension

string
majorDimensionRowsThe major dimension that results should use. For example, if the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then a request that selects that range and sets majorDimension=ROWS returns [[1,2],[3,4]], whereas a request that sets majorDimension=COLUMNS returns [[1,3],[2,4]].

Range

string
/ Required
rangeGoogle Sheets allows for 2 different formats of cell ranges: A1 Notation and R1C1 Notation.A1:A25

Spreadsheet ID

string
sheetIdEvery 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. my-example-sheet

Value Input Option

string
valueInputOptionRawDetermines how input data should be interpreted.

Values

string
Value List
valuesThe data that was read or to be written. This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension. Each item in the inner array corresponds with one cell.[1,2,3,4,5,6]