Google Sheets Reader
- Kalpana Malladi
- Aparna Tayi (Unlicensed)
- Lakshmi Manda
In this article
Overview
You can use Google Sheets Reader Snap to read a worksheet from the specified spreadsheet.
Snap Type
Google Sheets Reader Snap is a READ-type Snap that reads a worksheet in the given spreadsheet.
Prerequisites
A valid Google Spreadsheet account.
Support for Ultra Pipelines
Works in Ultra Pipelines.
Limitations
- If the spreadsheet or worksheet that you want to read contains one or more leading or trailing spaces, the Google Sheets Reader Snap displays an error.
Workaround: Enclose the names of the spreadsheet and worksheet in quotes (") and enable expressions for both Spreadsheet and Worksheet fields. For example, "Sheet 1." - If you select Preserve Data Types and name the worksheet similar to the Google spreadsheet column-row range; for example, AB123, then you may receive a bad request error.
When you provide a Spreadsheet ID to fetch data, and if a Spreadsheet with the same ID already exists as the Spreadsheet name, then the Google Sheets Reader Snap fails to fetch the data of the specified Spreadsheet ID. The Snap fails with an error:
Failed to read the worksheet '<spreadsheetID>' in the spreadsheet.Hence, if you want to fetch data of the specified Spreadsheet ID, ensure that you specify a uniqueSpreadsheetNameorSpreadsheetNamewithSpreadsheet ID.
- Inputs without any format restrictions are accepted when the expression toggle is enabled and a Pipeline parameter is provided for the following fields:
- Number of Retries
- Retry Interval
- Read timeout in seconds
- Connection timeout in seconds
- For an expression input, the value is evaluated and accepted if it meets the expression criteria; if not, then the default value is used.
Known Issue
A long-standing bug​ in the Google Sheets endpoint does not allow the use of a colon ':' in the worksheet names; hence, these inputs are not supported.
Snap Views
| Type | Format | Number of Views | Examples of Upstream and Downstream Snaps | Description |
|---|---|---|---|---|
| Input | Document |
|
| The name of the worksheet to be read, including the base folder and spreadsheet information. |
| Output | Document |
|
| The data of the specified worksheet. |
Error | Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter while running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab. The available options are:
Learn more about Error handling in Pipelines. | |||
Snap Settings
Field Name | Field Type | Description | |
|---|---|---|---|
Label* Default Name: Google Sheets Reader | String | Specify a unique and meaningful name for the Snap. | |
Include Shared Drives Default Value: Deselected | Checkbox | Select this checkbox to read files from the shared drive. Deselect this checkbox if you do not want the Snap to read files from the shared drive. | |
Display Spreadsheet ID in Suggestions Default Value: Deselected | Checkbox | Select this checkbox to display Spreadsheets ID along with the Spreadsheet name in the Spreadsheets Suggestions list. | |
Spreadsheet* Default Value: [None] | String/Expression/Suggestion | Specify the name of the spreadsheet containing the worksheet to be read. Alternatively, click the Suggestion | |
Worksheet* Default Value: None | String/Expression/Suggestion | Specify the name of the worksheet to be read. Alternatively, click the Suggestion
| |
Header exists Default Value: Selected | Checkbox | Select this checkbox if the worksheet's first row is a header. | |
Preserve data types Default Value: Deselected | Checkbox | This Snap converts all input data types to strings. Select this check box to preserve the data type of the data coming into this Snap. We recommend that you deselect this checkbox if the file to be read is large (more than 50K rows in one sheet), as it makes the Snap read the data faster. | |
Allow empty rows Default Value: Deselected | Checkbox | Google Sheets Reader Snap fetches only those rows that have data and ignores the empty rows. Select this checkbox to fetch empty rows between the rows that contain data. | |
Number of Retries Default Value: 3 | Integer/Expression | Specify the maximum number of retry attempts that the Snap must make to read the worksheet. | |
Retry Interval (seconds) Default Value: 1 | Integer/Expression | Specify the time interval in seconds for each retry attempt. | |
| Connection properties | |||
Read timeout in seconds Default Value: 180 | Integer/Expression | Specify the time in seconds. In the event of a connection failure, the Snaps stops trying to read the data after the specified time. | |
Connection timeout in seconds
| Integer/Expression | Specify the time in seconds. In the event of a connection failure, the Snaps stops a connection attempt after the specified time. For example, if the HTTP timeout is set to 30 seconds, and the Snap fails to establish a connection until 30 seconds, the pipeline logs a timeout exception and aborts execution. | |
Snap Execution Default Value: Validate & Execute | Dropdown list | Select one of the following three modes in which the Snap executes:
| |
Snap Pack History
Have feedback? Email documentation@snaplogic.com | Ask a question in the SnapLogic Community
© 2017-2024 SnapLogic, Inc.