Google Sheets Writer

Overview

The Google Sheets Writer Snap writes data to a worksheet in a Google Sheets spreadsheet.



  • This is a Write-type Snap.
  • Does not support Ultra Tasks

Prerequisites

  • A valid Google Sheets account.

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 unique SpreadsheetName or SpreadsheetName with Spreadsheet ID.

Known issues

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 Description Examples of upstream and downstream Snaps
Input

This Snap has exactly one document input view.

Expected input: The name of the worksheet and spreadsheet information to be written to.
Output

This Snap has at most one document output view.

Expected output: Statistics including the number of records written successfully and number of invalid records.
  • None
Learn more about Error handling.

Snap settings

Legend:
  • Expression icon (): Allows using pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions are not supported. If disabled, you can provide a static value.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.

The following expression fields in this Snap support expressions only for pipeline parameters, static values, and expression evaluation. Upstream values are not supported:

  • Spreadsheet
  • Worksheet
  • Parse data
  • Number of retries
  • Retry interval (seconds)
  • Read timeout (in seconds)
  • Connection timeout (in seconds)
Field/Field set Description
Label

String

Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline.

Default value: Google Sheets Writer
Include Shared Drives

Checkbox		 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.

Default value: Deselected
Display Spreadsheet ID in Suggestions

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

Default value: Deselected
Spreadsheet*

String/Expression/ Suggestion		 Required. 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).
Note: This field supports expressions only for Pipeline parameters, static values, and expression evaluation. It does not support upstream values.

Default value: N/A

Example: My Google Sheet
Worksheet*

String/Expression/ Suggestion		 Required. 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: This field supports expressions only for Pipeline parameters, static values, and expression evaluation. It does not support upstream values.
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 Google Sheets Writer Snap. A valid header is one that has unique and non-null values.
  • 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.

Default value: N/A

Example: Sheet1
Write Mode*

Dropdown list		 Required. 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.

Default value: Create new worksheet

Example: Append/edit existing worksheet
Overwrite worksheet if it exists

Checkbox		 Appears when you select Create new worksheet option. Select this checkbox to overwrite existing data if the worksheet exists.

Default value: Deselected
Starting Cell Reference

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

Default value: N/A

Example: A3
Write header row

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

Default value: Deselected
Parse data

Dropdown list		 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

Example: USER_ENTERED
Batch size*

Integer/Expression		 Required. Specify the number of rows to be updated in a single write request to Google Sheets. We recommend that you set the batch size when handling a large number of rows/records. Minimum value: 1000.

Default value: 1000

Example: 4000
Number of Retries

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

Default value: 3

Example: 5
Retry Interval (seconds)

Integer/Expression		 Specify the time interval in seconds for each retry attempt.

Default value: 1

Example: 4
Connection properties Use this field set to configure the connection properties to specify the read and connection timeouts.
Read timeout in seconds

Integer/Expression		 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

Example: 190
Connection timeout in seconds

Integer/Expression		 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

Example: 10
Snap execution

Dropdown list
Choose 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

Type Description Examples of upstream and downstream Snaps
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.