Introduction
Google Sheets API allows the users to create, read, and edit google sheets with the right access set to their google accounts. The Goal of this connector is to leverage Tulip's Oauth 2.0 to integrate with Google Sheets using HTTP Connectors & provide our customers with an out of box connector that can be imported into their instance.
Purpose
We are going to highlight different HTTP connector functions that can be built by doing HTTP calls to Google Sheets API(v4) endpoints to interact with the Google spreadsheet to try out various read and write actions on the spreadsheet that could be leveraged for various use cases.
API Doc
Pre-Requisites
Create an account on Google's cloud Console & configure the OAuth 2.0 credentials. More information can be found in Google's API docs.
Add the instances redirect URL on google cloud console
Tulip Connection Details
The following information would need to be configured on Tulip's connectors.
Running On: Cloud Connector Host
Host:sheets.googleapis.com
Port:443
TLS: Yes
OAuth 2(Admin): Authentication will be carried forward to all the Connector Functions
Connector Functions
Google New SpreadSheet
Purpose
Users can pass a Spreadsheet Name and SheetName as input & Tulip will create a new Spreadsheet with the sheet name provided. The response of the API will be the metadata and various properties of the spreadsheet, This can be further used as filters to fetch and Update data from the spreadsheets.
Information is pulled from Google sheets V4 API can then be further used either within Tulip apps, Tulip tables, or Tulip variables which would be piped into other connector functions.
Endpoint
HTTP POST call to the endpoint https://sheets.googleapis.com/v4/spreadsheets
Required Input
The following are the parameters that are sent as the Request Body
SpreadSheet Name - The Spreadsheet Title
Sheet Name - Initial Sheet Title
Output Extractor
spreadsheet: Unique identifier of the SpreadSheet which will be used to carry out the other tasks
spreadsheet URL: The URL of the newly created Spreadsheet
Google Get SpreadSheet Data
Purpose
This connector will fetch the data from the spreadsheetId provided within the given range. The Data will be supplied as an array of rows or columns based on the query parameters supplied to the connector function
Endpoint
HTTP GET call to the endpoint -https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}
Required Input
The following are the URL parameters that are to be passed
SpreadSheet Id
Range - A String in A1 Format
The additional query parameters that will be supplied from the connector functions are
majorDimension : ROWS or COLUMNS decides what the internal data array should be
dateTimeRenderOption specifies the format of DateTime fields default value:FORMATTED_STRING
valueRenderOption specifies if the data has to be formatted or unformatted in the response default value: FORMATTED_VALUE
Output Extractor
Values: This is an Array of Arrays, each row of data will be an array.
Google SpreadSheet InsertRow
Purpose
Appends values to a spreadsheet. The input range is used to search for existing data and find a "table" within that range. Values will be appended to the next row of the table, starting with the first column of the table. The Connector will take spreadsheetId, Range and the list of values to be inserted in the cells.
Endpoint
HTTP POST call to the endpoint -https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append
Required Input
The following are the query parameters that are to be passed
Spreadsheet ID
Range
Values: for this connector, We have considered 8 column values with each parameter named against the column they will be inserted. This will be passed to the API as an array of values
The additional query parameters that will be supplied from the connector functions are :
valueInputOption: This is a mandatory field and will be sent from the connector, Default value set: USER_ENTERED
Output Extractor
spreadsheetId
UpdatedRows: Specifies the number of rows that were inserted
Google SpreadSheet Clear
Purpose
Clears values from a spreadsheet. The caller must specify the spreadsheet ID and range. Only values are cleared -- all other properties of the cell (such as formatting, data validation, etc..) are kept. The Connector will take spreadsheetId, Range and the list of values to be inserted in the cells.
Endpoint
HTTP POST call to the endpoint -https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:clear
Required Input
The following are the query parameters that are to be passed
Spreadsheet ID
Range
Google Get SpreadSheet Details byFilter
Purpose
This connector will fetch the spreadsheet detail with the provided spreadsheetId and specific condition specified within the data filter.
Endpoint
HTTP POST call to the endpoint -https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:getbyDataFilter
Required Input
The following are the query parameters that are to be passed
spreadSheetId
includeGridData: Default value is False
The following parameters are sent as part of the Request body in JSON format
gridRange
Output Extractors
spreadSheetId
Title
Locale
auto recalc
timeZone
sheetId
sheetTitle
sheetType
sheetRowCount
sheetColumnCount
spreadSheetUrl
There are other parameters that can be included as part of output based on user use case
Google Get Batch Data
Purpose
This connector will fetch the data from the spreadsheetId provided within The Data will be supplied as an array of rows or columns based on the query parameters supplied to the connector
Endpoint
HTTP GET call to the endpoint -https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGet
Required Input
The following are the query parameters that are to be passed
SpreadSheet Id
Range - A String in A1Format
The additional query parameters that will be supplied from the connector functions are
majorDimension : ROWS or COLUMNS decides what the internal data array should be
dateTimeRenderOption specifies the format of DateTime fields default value:FORMATTED_STRING
valueRenderOption specifies if the data has to be formatted or unformatted in the response default value:FORMATTED_VALUE
Output Extractor
valueRanges: This is an Array of objects. Each object details one range
Google Get Batch Data filter
Purpose
This connector will fetch the data from the spreadsheetId provided within the given filter condition as a batch of data. The Data will be supplied as array of rows or columns based on the query parameters supplied to the connector
Endpoint
HTTP POST call to the endpoint -https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGetByDataFilter
Required Input
The following are the query parameters that are to be passed
SpreadSheet Id
The additional Request Body parameters that will be supplied from the connector functions are
Datfilters
majorDimension : ROWS or COLUMNS decides what the internal data array should be
dateTimeRenderOption specifies the format of DateTime fields default value:FORMATTED_STRING
valueRenderOption specifies if the data has to be formatted or unformatted in the response default value: FORMATTED_VALUE
Output Extractor
valueRanges: This is an Array of objects. Each object details one range