Google Sheets Connector

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

1. 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

1. Google Sheets API Reference Doc

Pre-Requisites

1. Create an account on Google's cloud Console & configure the OAuth 2.0 credentials. More information can be found in Google's API docs.
2. Add the instances redirect URL on google cloud console

Tulip Connection Details

The following information would need to be configured on Tulip's connectors.

1. Running On: Cloud Connector Host
2. Host:sheets.googleapis.com
3. Port:443
4. TLS: Yes
5. OAuth 2(Admin): Authentication will be carried forward to all the Connector Functions

Connector Functions

Google New SpreadSheet

Purpose

1. 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.
2. 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

1. SpreadSheet Name - The Spreadsheet Title
2. Sheet Name - Initial Sheet Title

Output Extractor

1. spreadsheet: Unique identifier of the SpreadSheet which will be used to carry out the other tasks
2. 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

1. SpreadSheet Id
2. Range - A String in A1 Format

The additional query parameters that will be supplied from the connector functions are

1. majorDimension : ROWS or COLUMNS decides what the internal data array should be
2. dateTimeRenderOption specifies the format of DateTime fields default value:FORMATTED_STRING
3. valueRenderOption specifies if the data has to be formatted or unformatted in the response default value: FORMATTED_VALUE

Output Extractor

1. 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

1. Spreadsheet ID
2. Range
3. 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 :

1. valueInputOption: This is a mandatory field and will be sent from the connector, Default value set: USER_ENTERED

Output Extractor

1. spreadsheetId
2. 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

1. Spreadsheet ID
2. 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

1. spreadSheetId
2. includeGridData: Default value is False

The following parameters are sent as part of the Request body in JSON format

1. gridRange

Output Extractors

1. spreadSheetId
2. Title
3. Locale
4. auto recalc
5. timeZone
6. sheetId
7. sheetTitle
8. sheetType
9. sheetRowCount
10. sheetColumnCount
11. 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

1. SpreadSheet Id
2. Range - A String in A1Format

The additional query parameters that will be supplied from the connector functions are

1. majorDimension : ROWS or COLUMNS decides what the internal data array should be
2. dateTimeRenderOption specifies the format of DateTime fields default value:FORMATTED_STRING
3. valueRenderOption specifies if the data has to be formatted or unformatted in the response default value:FORMATTED_VALUE

Output Extractor

1. 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

1. SpreadSheet Id

The additional Request Body parameters that will be supplied from the connector functions are

1. Datfilters
2. majorDimension : ROWS or COLUMNS decides what the internal data array should be
3. dateTimeRenderOption specifies the format of DateTime fields default value:FORMATTED_STRING
4. valueRenderOption specifies if the data has to be formatted or unformatted in the response default value: FORMATTED_VALUE

Output Extractor

1. valueRanges: This is an Array of objects. Each object details one range

Video