Versions Compared

Old Version 37

changes.mady.by.user Kalpana Malladi

Saved on

compared with

New Version Current

changes.mady.by.user Gouri Bhagchandani

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

On In this Pagearticle

Table of Contents
maxLevel2
excludeOlder Versions|Additional Resources|Related Links|Related Information

Overview

Worksheet Reader is a Read-type Snap that enables you You can use Google Sheets Reader Snap to read a worksheet in a spreadsheet and write the data to the output viewfrom the specified spreadsheet.

Note
iconfalse
titleImpact on Migration of API from v3 to v4:

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

Image Removed

Image Added

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 Worksheet 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 uniqueSpreadsheetNameorSpreadsheetName with Spreadsheet ID.

Multiexcerpt include macro
nameME_GS_V3toV4
pageSpreadsheets Google Sheets Browser

Known Issue

Multiexcerpt include macro
nameME_GS_KI
pageSpreadsheets Google Sheets Browser

Snap

Input and OutputInput/OutputType of View

Views

TypeFormatNumber 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

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:

  • Stop Pipeline Execution: Stops the current pipeline execution when the Snap encounters an error.

  • Discard Error Data and Continue: Ignores the error, discards that record, and continues with the rest of the records.

  • Route Error Data to Error View: Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap Settings

Field Name

Field Type

Description


Label*


Default Name: Google Sheets Reader
Example: Read Sheet1


String

Specify a unique and meaningful name for the Snap.

Default Name: Worksheet Reader
Example: Read Sheet1

Spreadsheet*


Include Shared Drives


Default Value: Deselected
Example: Selected

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
Example: Selected

Checkbox

Select this checkbox to display Spreadsheets ID along with the Spreadsheet name in the Spreadsheets Suggestions list.


Spreadsheet*


Default Value: [None]
Example: My Google Sheet

String/Expression/Suggestion

Specify the name of the spreadsheet containing the worksheet to be read. Alternatively, click the Suggestion Image Modified icon to populate the list of spreadsheets associated to the account and select the required spreadsheet.


Worksheet*


Default

value

Value:

[

None

]


Example:

My Google Sheet
Worksheet*

 Sheet 1


String/Expression/Suggestion

Specify the name of the worksheet to be read. Alternatively, click the Suggestion Image Modified icon to populate the list of worksheets associated to the account and select the required worksheet.

Note
  • 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.


Header exists


Default

value

Value:

 NoneHeader exists

 Selected
Example: 

Sheet 1

Deselected

Checkbox

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


Preserve data types


Default

value

Value:

Selected

 Deselected
Example:

DeselectedPreserve data types

Selected

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.

Info

We recommend that you deselect

Preserve data types if

this checkbox if the file to be read is large (

>50K

more than 50K rows in one sheet), as it makes the Snap read the data faster.


Allow empty rows

Default

value

Value: Deselected
Example: Selected

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
Example: 3

Integer/Expression

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


Retry Interval (seconds)


Default

value

Value:

3

1
Example:

3Retry Interval (seconds)

1

Integer/Expression

Specify the time interval in seconds for each retry attempt.

Default value: 1
Example: 1


Connection properties

Use this field set to configure the connection properties. This field set contains the following fields:

Read timeout in seconds

  • Connection timeout in seconds
    • Read timeout in seconds


    Default Value: 180
    Example: 100

    		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


    Default

    value

    Value:

     180

    20 
    Example    :

    100

    10

    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

    Value:

    20 

    Validate & Execute 
    Example:

    10

    Execute Only

    Snap Execution

    Dropdown list

    Multiexcerpt include macro
    nameSnap_Execution_Introduced
    pageAnaplan Read

    Default value: Validate & Execute 
    Example: Execute Only



    Insert excerpt
    Google Sheets Snap Pack
    Google Sheets Snap Pack
    nopaneltrue