Google Sheets Writer

In this article

Overview

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

Impact on Migration of API from version 3 to 4:

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

Prerequisites

A valid Google Spreadsheet account. 

Support for Ultra Pipelines

Does not work in Ultra Pipelines.

Limitations

  • 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 Google Sheets 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.
  • You cannot create a new Spreadsheet in shared drives using the Google Sheets Writer Snap—the spreadsheet can only be overwritten.

  • You cannot write new Spreadsheets to any location except the default location (that is, the user’s root drive).

  • Once a Spreadsheet is available, you can modify and read it irrespective of the location by specifying the sheet name or sheet ID or both.

  • If the Starting cell reference field is left blank and if the table containing the last row of data in the worksheet contains a leftmost column greater than column A, then the Snap appends new rows below the last row at the table’s leftmost column instead of column A. For example, if a worksheet has existing data written in cells B2:D4, the Snap starts appending data at B5. However, if there is also existing data in cell A2, it joins the table formed by B2:D4, and the Snap starts appending data at A5.

  • This Snap expects the key sets of subsequent input documents to be identical with the subset of the key set of the first processed document. If a subsequent input document contains a key that is not present in the first processed document, then the data referenced by that key is not written.

  • When you provide SpreadsheetID to append or insert data, and if a Spreadsheet with the same ID already exists as Spreadsheet name, then the Google Sheets Writer Snap fails to append or insert the data for the specified SpreadsheetID and it only considers the SpreadsheetName and creates a new worksheet for the SpreadsheetName. Hence, we recommend that if you want append or insert data for the specified Spreadsheet ID, ensure that you specify a uniqueSpreadsheetName or SpreadsheetName with Spreadsheet ID.

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 Views

TypeFormatNumber 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
  • None
Statistics (Number of records written successfully and number of invalid records.)
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 NameField TypeField DependencyDescription

Label*


Default Value: Google Sheets Writer
Example
Google Sheets Writer

StringN/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.

Include Shared Drives


Default Value: Deselected
Example
: Selected

Checkbox

N/A

Select this checkbox to write files from the shared drive.

Deselect this checkbox if you do not want the Snap to write files from the shared drive.


Display Spreadsheet ID in Suggestions


Default Value: Deselected
Example
: Selected

Checkbox

N/A

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


Spreadsheet*


Default value: N/A
Example
: My Google Sheet

String/Expression/SuggestionN/A

Specify or select the spreadsheet name or ID (or both) 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 (My Drive).


Worksheet*


Default value: N/A
Example
Sheet1


String/Expression/SuggestionN/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.

  • 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 Google Sheets Writer Snap. A valid header is one that has unique and non-null values, for instance, as highlighted in the following worksheet :

    For more information, see the example below.
  • Hidden and Protected sheets are not 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.

Write Mode*


Default Value: Create new worksheet
Example
: Append/edit existing worksheet

Dropdown list

N/A

Choose the type of write operation to perform for the specified worksheet. The available options are:

  • Create New Worksheet: Creates a new worksheet.

  • Append or Edit Existing Worksheet: Appends data to an existing worksheet or modifies an existing worksheet. Learn more through an example below.


Overwrite worksheet if it exists


Default Value: Not selected
Example
: Selected

Checkbox

Appears when you select Create new worksheet option.

Select this checkbox to overwrite existing data if the worksheet exists.


Starting Cell Reference


Default Value: N/A
Example
: A3

String/Expression

Appears when you select Append/edit existing worksheet option.

Specify the cell reference in A1 notation where the Snap should start writing the data in the existing worksheet. You can perform either of the following:

  • Write new data to the worksheet by specifying a cell reference as the starting point.

  • Append data to the worksheet at the last row in the worksheet containing content.

  • If the cell reference is left blank and data is appended to an existing worksheet that has existing data in center of the rows, then a new row is added below the last worksheet row with content, but not from the column A.
  • If you provide a cell reference in this field, new data overwrites any existing data within the written cell range.

Write header row


Default Value: Selected
Example: Selected

CheckboxN/A

Select this checkbox if the row containing column names should be written to the worksheet.


Parse data


Default value: RAW
ExampleUSER_ENTERED

Expression/Dropdown listN/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.

Number of Retries


Default value: 3
Example
: 5

Integer/ExpressionN/A

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


Retry Interval (seconds)


Default value: 1
Example
: 4

Integer/ExpressionN/A

Specify the time interval in seconds for each retry attempt.


Connection propertiesUse this field set to configure the connection properties to specify the read and connection timeouts.

Read timeout in seconds


Default value:180
Example
: 190

Integer/ExpressionN/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.


Connection timeout in seconds


Default value: 20
Example
: 10

Integer/ExpressionN/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.


Snap Execution


Default value: Execute only
Example
: Validate & Execute

Dropdown listN/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.

Troubleshooting

ErrorReasonResolution

Spreadsheet not found: <spreadsheet name>.

The spreadsheet may not exist or cannot
be accessed.

Verify that the spreadsheet exists and retry.

Invalid Write Mode.

Current Write Mode is not recognized.

Provide a valid Write Mode and run the Pipeline again.

Input data error.

Data is not a key-value pair.

Check the input data and try again.

Cell reference is invalid.

Cell reference does not follow A1 notation.

Provide the cell reference in A1 notation and retry.

Invalid range to write <number> columns starting at cell <cell reference>.

Range end column exceeds max column ZZZ.

Reduce the number of
columns in the written data or adjust the starting cell reference to fit all columns.

Failed to connect to Google account.

Google account is required

Add a valid Google account and retry.

Spreadsheet or Worksheet value is blank.

Spreadsheet and worksheet names are required

Verify that spreadsheet and worksheet names are provided

Invalid key name: null.

Null key is not allowed.

Verify that input document data do not contain any null keys.

Failed to initialize existing worksheet <worksheet name> for batch write.

Worksheet is not found.

Verify that the worksheet exists on the specified spreadsheet.

Failed to create a new worksheet for <worksheet name>.

Worksheet with the given name already exists.

Select the Overwrite worksheet if it exists checkbox or enter a different name for the worksheet then retry.

Examples

Appending or Editing Data in the Existing Worksheet

This example Pipeline demonstrates how to append data in an existing worksheet without specifying a cell reference and by specifying a cell reference.

First, we configure the JSON Generator Snap to pass sample data as follows:


Next, we configure the Mapper Snap to combine the message and number fields as shown below.

Then, we configure the Google Sheets Writer Snap as shown below. We set the Write Mode to Append or Edit Existing Worksheet and leave the Starting Cell Reference blank.

After validating the Pipeline, the Snap writes the data after the last row following the existing content in the worksheet.


To demonstrate the functionality of Starting cell reference, we specify B2 in the Starting Cell Reference field as shown below.

After validation, the Snap writes the data starting from the specified cell reference as shown below.


Populating Target Schema in an Upstream Snap

The following example Pipeline demonstrates the population of target schema in the upstream Mapper Snap when the data is written to an existing worksheet using the Google Sheets Writer Snap.

First, we configure the Google Sheets Writer Snap to write the data on an existing worksheet.

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 (highlighted below) in the Target Schema.

Download this Pipeline.

Related Content

Snap Pack History

 Click to view/expand


ReleaseSnap Pack VersionDateTypeUpdates
November 2024main29029 StableUpdated and certified against the current SnapLogic Platform release.
August 2024438patches28762 Latest
August 2024438patches27861 Latest

Added the Google Sheets – Google Service Account to connect Google Sheets Snaps with data sources that use Google Service Account.

August 2024main27765 StableUpdated and certified against the current SnapLogic Platform release.
July 2024437patches27197 LatestFixed an issue with the Google Sheets Writer Snap where using cell names as worksheet names caused incorrect data writing behavior.
May 2024main26341 StableUpdated and certified against the current SnapLogic Platform release.
February 2024main25112 StableUpdated and certified against the current SnapLogic Platform release.
November 2023main23721 StableUpdated the Google Spreadsheet Snap Pack and Snap names to Google Sheets.
August 2023main22460 StableUpdated and certified against the current SnapLogic Platform release.

May 2023

main21015 

Stable

Upgraded with the latest SnapLogic Platform release.

February 2023main19844 StableUpgraded with the latest SnapLogic Platform release.
November 2022main18944 StableUpgraded with the latest SnapLogic Platform release.
August 2022main17386 Stable
  • The Allow empty rows checkbox in the Worksheet Reader Snap allows you to output empty rows. Select this checkbox to include empty rows between other rows that contain data.

  • The Worksheet Writer Snap supports expressions for the Starting Cell Reference field. This Snap also includes a warning about potential overwriting of existing data for the Starting Cell Reference field when writing data in the same range of cells.

4.29429patches16975 Latest
  • Fixed an issue with the Worksheet Writer Snap where the Snap failed to write data correctly after each batch write. When the Write Mode is Create New Worksheet and Overwrite worksheet if it exists checkbox is selected, the cell data is shifted by five columns to the right after each batch in the resulting worksheet. Now, the data is written correctly in the worksheet.
  • Introduced the following Snaps:

4.29main15993 Stable
  • Enhanced the Spreadsheets Browser, Worksheet Reader, and Worksheet Writer Snaps with the Include Shared Drives checkbox to support working with the Google Shared Drives.

  • Enhanced the Worksheet Reader and Worksheet Writer Snaps with the Display Spreadsheet ID in Suggestions checkbox. You can select this checkbox to display both spreadsheet ID and the spreadsheet name in the suggestions. This helps you to choose among the spreadsheets having the same name.

  • Enhanced the Worksheet Writer Snap with the Write Mode dropdown list to perform the desired write option for the given worksheet—that is either create a new worksheet or append or edit an existing worksheet. You can write new data to the worksheet by specifying a cell reference as the starting point or append data to the worksheet at the last row in the worksheet containing content.

4.28 Patches428 patches14755 Latest
  • Enhanced the Spreadsheet Browser Snap with the Fetch Mode dropdown list. You can fetch:

    • Spreadsheets and worksheets within that Spreadsheet, or

    • Only Spreadsheets.

4.28main14627 StableUpgraded with the latest SnapLogic Platform release.

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.