Database Type: Spreadsheet
Connector Supports: READ only
Google Sheet is an online spreadsheet application that serves as a collaborative tool allowing multiple users to create, update and modify spreadsheets simultaneously in real-time. It is hosted by Google within its Google Drive service. This cloud-based architecture is compatible with Microsoft Excel file formats which removes the friction of working with external sources. Google Sheets enable users to handle task lists, create project plans, analyze data with charts and filters as well as share data live online.
Google Sheet has a permission system which regulates a user’s data access rights ensuring data security. The users of the G sheet will be entitled to one or more of the permission levels listed here: Edit, Comment and View. VOGO Voice platform supports View permission level only, thereby restricting the permission level of the users to read data from the spreadsheet.
Spreadsheet ID, Google Grid ID
Anatomy of Settings
Defining Settings values for adding a Google Sheet
Name: It refers to the name assigned to the instance of the connector created.
Spreadsheet ID: This parameter determines the spreadsheet from which data has to be accessed. The spreadsheet ID serves as a unique identifier for a Google Sheet. The ID is the value between ‘/d/’ and ‘/edit’ in the URL of the Google Sheet. In the sample Google Sheet URL, the part that references the spreadsheet ID is highlighted in red https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0.
The spreadsheet ID is a string that contains letters, numbers and some special characters.
Now that a connector is established it can now be used within any interaction flow. Each usage of the connector will allow different configurations to retrieve different results. Those settings can be either static or dynamic based on the needs.
Anatomy of Configuration
Defining Configuration Values for a Google Sheet
Name: The name assigned to identify the connector and the results data. The assigned name is used every time the connector is called to action through the components in the interaction builder platform.
Google Grid ID: A spreadsheet can have multiple sheets with each sheet having a unique title and an ID as its reference point. Google Grid ID (gid) is the sheet ID (tab) used to specify the sheet from which data is being read. The sheetID of the open sheet is the value of the gid parameter at the end of the spreadsheet URL.
The following sample Google Sheet URL replicates the structure of the URL with the sheetID highlighted in red: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId
The gid parameter will be a numerical value.
Query: The syntax of the query language used to retrieve data from Google Sheets is similar to SQL. But there are some differences between the two which are mentioned in the syntax section of the reference link given below. To learn about the query language used for Google Sheets visit https://developers.google.com/chart/interactive/docs/querylanguage
👍 Note: The FROM clause has been eliminated from the language since the G sheet table is already included.
Caching: To know about caching, click here.