Versions Compared

Old Version 42

changes.mady.by.user Kalpana Malladi

Saved on

compared with

New Version 43

changes.mady.by.user Kalpana Malladi

Saved on

Key

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

On this Page

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

Overview

You can use this Snap to write to a worksheet in a Google sheet.

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

Enhances the Google 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 Spreadsheet Pipelines from V3 to V4.

Snap type:

Write

Description:

This Snap writes to a worksheet in a Google sheet.

Image Modified

  • Expected upstream Snaps:  Generators (CSV, JSON), Mapper.
  • Expected downstream Snaps: None.
  • Expected input: The name of the worksheet and spreadsheet information to be written to.
    • Expected output:  Statistics (Number of records written successfully, Number of invalid records)

    Prerequisites

    :

    A valid Google Spreadsheet account. 

    Support and Limitations:

    Support for Ultra Pipelines


    Limitations

    Multiexcerpt include macro
    nameME_GS_V3toV4
    pageSpreadsheets Browser

    • The Snap conforms to the limitations imposed by Google Drive service. A spreadsheet may contain multiple worksheets. The cell count limit applies to the sum of the cells in all the worksheets in a spreadsheet.
    • If the spreadsheet or worksheet to which you want to write contains one or more leading or trailing spaces, the Worksheet Writer Snap does not function as expected:
      • If the spreadsheet name contains leading or trailing spaces, the Snap displays a "Spreadsheet not found" error.
      • If the worksheet name contains leading or trailing spaces, the Snap creates a new worksheet in the target spreadsheet using the worksheet name specified, without the extra space. 

    Workaround: Enclose the names of the spreadsheet and worksheet in quotes (") and enable expressions for both Spreadsheet and Worksheet fields.

    Known Issue

    :

    Multiexcerpt include macro
    nameME_GS_KI
    pageSpreadsheets Browser

    Snap Input and Output

    Input/OutputType of ViewNumber of ViewsExamples of Upstream and Downstream SnapsDescription
    InputDocument
    • Min:1
    • Max:1
    • Generators (CSV, JSON)
    • Mapper
    		The name of the worksheet and spreadsheet information to be written to.
    OutputDocument
    • Min:0
    • Max:1
    		NoneStatistics (Number of records written successfully, Number of invalid records)


    Impact on Migration of API from v3 to v4:
    		Enhances the Google Spreadsheet Snap Pack by migrating from v3 to v4 API as Google announces sunsetting the v3 API on June 8, 2021. For more information, see Impact on Migrating Google Spreadsheet Pipelines from v3 to v4.
    Account

    This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. This Snap uses a Google OAuth2 Account (Client ID, Client Secret, etc) or Google Dynamic OAuth2 Account (Access Token). See Configuring Google Spreadsheet Accounts for more information.

    Views
    InputThis Snap has exactly one document input view.
    OutputThis Snap has at most one document output view.
    ErrorThis Snap has at most one document error view and produces zero or more documents in the view.

    Settings

    Label*

    Settings

    Field NameField DependencyDescription

    Label*

    		N/ASpecify 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.

    Spreadsheet*



    		N/A

    Specify the name of the spreadsheet containing the worksheet to be written to. If the spreadsheet does not exist, you can type the name of the sheet to be created at the root level.

    Default value: None
    Example: My Google Sheet

    Worksheet*


    		N/A

    Specify the name of the worksheet to be written to. If the worksheet does not exist, you can type the name of the worksheet to be created.

    Note
    • If you are writing data to an existing worksheet that has a valid header, then the input schema is populated in the target schema of the upstream (Mapper) Snap. This enables you to view the input schema that is available in the worksheet used in the Worksheet Writer Snap. A valid header is one that has unique and non-null values, for instance the following worksheet :
      Image Modified
      For more information, see the example below.
    • 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.

    Example: Sheet1

    Default value:  None

    Overwrite worksheet if it existsN/A

    Select

    this option

    this checkbox to overwrite existing information if the worksheet exists.

    Default value: Not selected

    Write header rowN/A

    Select this

    option

    checkbox if the row containing column names should be written to the worksheet.
    Default value: Selected

    Parse dataN/A

    Specify whether the input value must be preserved or parsed. Available options are:

    • RAW: The input is not parsed and is inserted as a string. For example, "=1+2" places the string "=1+2" in the cell, not the implied result.
    • USER_ENTERED: The input is parsed exactly as if it were entered into the Google Sheets UI. For example, "Mar 1 2016" becomes a date, and "=1+2" becomes a formula. Formats may also be inferred; so "$100.15" becomes a number with currency formatting.

    Default value: RAW

    Number of
    retries
    RetriesN/A

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

    Default value: 3

    Retry
    interval
    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 and connection timeouts.
    Read timeout in seconds
    The
    N/A

    Specify the time limit in seconds for the Snap to read the data. If this limit is crossed, the Snap skips to the next batch of records.

    Default value: 180

    Connection timeout in seconds
    Time
    N/A

    Specify the time in seconds. In the event of a connection failure, the Snap 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 ExecutionN/A
    Multiexcerpt include macro
    nameExecution_Detail_Write
    pageSOAP Execute

    Example

    Populating Target Schema in an Upstream Snap

    The following example Pipeline demonstrates how the Worksheet Writer Snap populates the target Schema in an upstream Snap when the data is written to an existing worksheet. We use Worksheet Writer and Mapper Snaps to accomplish this task.

    Image Modified

    First, we configure the Worksheet Writer Snap to write the data on an existing worksheet.
    Image Modified

    Next, we configure the Mapper Snap to map the fields to the respective columns using the fields from the Target Schema. Note that the headers and the respective data types are populated in the Target Schema.

    Image Modified

    Download this Pipeline.

    Attachments
    patterns*.slp, *.zip

    Insert excerpt
    Google Sheets Snap Pack
    Google Sheets Snap Pack
    nopaneltrue