Worksheet Reader

On this Page

Overview

You can use this Snap to read a worksheet in a spreadsheet and write the data to the output view.

Impact on Migration of API from v3 to v4:

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 Worksheet Reader Snap throws 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.

  • 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

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 Input and Output

Input/OutputType of ViewNumber of ViewsExamples of Upstream and Downstream SnapsDescription
InputDocument
  • Min:0
  • Max:1
  • None
The name of the worksheet to be read, including the base folder and spreadsheet information.
OutputDocument
  • Min:1
  • Max:1
  • File Writer
  • Transform Snap

The data of the provided worksheet.

Snap Settings

Field Name

Field Dependency

Description


Label*


N/A

Specify the name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your Pipeline.

Default Name: Worksheet Reader
Example: Read Sheet1

Spreadsheet*



N/A

Specify the name of the spreadsheet containing the worksheet to be read.  It is suggestible based on the base folder.

Default value: [None]
Example: My Google Sheet

Worksheet*


N/A

Specify the name of the worksheet to be read. It is suggestible based on the spreadsheet.

  • Hidden and Protected sheets are not a part of worksheet suggestions. However, you can manually enter the name of the existing worksheet that is hidden or protected to enable the Snap to overwrite based on your account permissions.
    • Worksheets with hidden range appear in the suggestion list.
    • Worksheets with protected range do not appear in the suggestion list.
  • You can view the list of hidden sheets in the Google Sheets UI by clicking View and then selecting Hidden Sheets.
  • You can view the protected ranges in Google Sheets UI by clicking Data and then selecting Protected sheets and ranges.

Default value: None
ExampleSheet 1

Header existsN/A

Select this checkbox if the worksheet's first row is a header.

Default value: Selected

Preserve data typesN/A

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 Preserve data types if the file to be read is large (>50K rows in one sheet), as it makes the Snap read the data faster.

Default value: Not selected

Number of RetriesN/A

Specify the maximum number of retry attempts that the Snap must make to read the worksheet.

Default value: 3

Retry Interval (seconds)N/A

Specify the time interval in seconds for each retry attempt.

Default value: 1

Connection propertiesN/AConfigure the connection properties to specify the read timeout and connection timeouts.
Read timeout in secondsN/A

Specify the time in seconds. In the event of a connection failure, the Snaps stops trying to read the data after the specified time.

Default value: 180

Connection timeout in secondsN/A

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.

Default value: 20 

Snap Execution

N/A

Select one of the three modes in which the Snap executes. Available options are:

  • Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.
  • Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.
  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Snap Pack History

 Click to view/expand


ReleaseSnap Pack VersionDateTypeUpdates

4.27

main12833

 

Stable

Upgraded with the latest SnapLogic Platform release.
4.26main11181 LatestEnhanced the Worksheet Writer Snap to populate the target schema preview with headers and associated data types (when the data is written to an existing worksheet with a valid header) in the upstream Snap.
4.25main9554
 
Stable

Upgraded with the latest SnapLogic Platform release.

4.24main8556
StableUpgraded with the latest SnapLogic Platform release.
4.23 Patch423patches7848 Latest

Enhanced the Snap Pack by migrating from version 3 to version 4 API as Google announces sunsetting the v3 API on June 8, 2021. For more information, see Migrating Google Spreadsheet Pipelines from V3 to V4.

4.23main7430
 
StableUpgraded with the latest SnapLogic Platform release.
4.22main6403
 
StableUpgraded with the latest SnapLogic Platform release.
4.21snapsmrc542

 

StableUpgraded with the latest SnapLogic Platform release.
4.20snapsmrc535
 
StableUpgraded with the latest SnapLogic Platform release.
4.19 Patch google/spreadsheet8333 Latest

Fixed an issue with the Google Worksheet Reader Snap wherein the Snap produces output documents for empty rows.

4.19snaprsmrc528
 
StableUpgraded with the latest SnapLogic Platform release.
4.18snapsmrc523
 
StableUpgraded with the latest SnapLogic Platform release.
4.17 Patchgoogle/spreadsheet7283 Latest

Fixed an issue with the Google Worksheet Writer Snap wherein the No of valid records written field in the output document would count the header row.

4.17ALL7402
 
Latest

Pushed automatic rebuild of the latest version of each Snap Pack to SnapLogic UAT and Elastic servers.

4.17snapsmrc515
 
Latest

Added the Snap Execution field to all Standard-mode Snaps. In some Snaps, this field replaces the existing Execute during preview check box.

4.16 Patchgoogle/spreadsheet6995 Latest

Fixed an issue wherein the Spreadsheets Browser Snap fails if a spreadsheet name contains / or : in Windows plex.

4.16snapsmrc508
 
StableUpgraded with the latest SnapLogic Platform release.
4.15 Patchgoogle/spreadsheet6835 Latest

Added a new property, Parse data, to the Worksheet Writer Snap which helps preserve the date data type.

4.15 Patchgoogle/spreadsheet6639 Latest

Added new properties, Preserve data typesConnection propertiesRead timeout in seconds, and Connection timeout in seconds, to the Worksheet Reader Snap. These properties let you convert input data types to strings and handle connection timeouts. Also, fixed issues with header count, column count mismatch and rendering of columns with no headers.

4.15snapsmrc500
 
StableUpgraded with the latest SnapLogic Platform release.
4.14 Patch google/spreadsheet5730  Latest

Migrated Google Sheets from API v3 to v4. Implemented retries to overcome defects in Sheets API.

4.14snapsmrc490
 
StableUpgraded with the latest SnapLogic Platform release.
4.13 Patch spreadsheet5293 Latest

Fixed the Google Worksheet Writer Snap that incorrectly displays numeric values as a string in the spreadsheet when writing in batch mode. The spreadsheet now preserves the exact data type of the input value.  

4.13

snapsmrc486

 
StableUpgraded with the latest SnapLogic Platform release.
4.12

snapsmrc480

 
Stable

Added the Batch write property  in Worksheet Writer to enable writing the input documents into the worksheet as a single batch instead of writing the input data one cell at a time.

4.11snapsmrc465
 
StableUpgraded with the latest SnapLogic Platform release.
4.10 Patch spreadsheet3626
LatestAddressed an issue where Worksheet Reader failed unexpectedly when validation mode input exceeded limitations.
4.10

snapsmrc414

 
Stable

Updated the Google Worksheet Writer Snap with Row count property to resolve the ' Data writing error'.

4.9.0 Patch spreadsheet3104 Latest

Addressed an issue with Google Worksheet writer: Fails with " Data writing error "

4.9snapsmrc405
 
StableUpgraded with the latest SnapLogic Platform release.
4.8

snapsmrc398

 
StableUpgraded with the latest SnapLogic Platform release.
4.7

snapsmrc382

 
StableUpgraded with the latest SnapLogic Platform release.
4.6snapsmrc362
 
StableUpgraded with the latest SnapLogic Platform release.
4.5.1

snapsmrc344

 
StableUpgraded with the latest SnapLogic Platform release.
4.5

snapsmrc344

Stable

Enhanced the error view output to include error, reason, and resolution.