Google Sheets Writer
In this article
Overview
You can use Google Sheets Writer Snap to write to a worksheet in a Google sheet.
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.
- If the spreadsheet name contains leading or trailing spaces, the Snap displays a "
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 specifiedSpreadsheetID
and it only considers theSpreadsheetName
and creates a new worksheet for theSpreadsheetName
. Hence, we recommend that if you want append or insert data for the specifiedSpreadsheet ID
, ensure that you specify a uniqueSpreadsheetName
orSpreadsheetName
withSpreadsheet ID
.
Known Issue
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 | Format | Number of Views | Examples of Upstream and Downstream Snaps | Description |
---|---|---|---|---|
Input | Document |
|
| The name of the worksheet and spreadsheet information to be written to. |
Output | Document |
|
| 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:
Learn more about Error handling in Pipelines. |
Snap Settings
Field Name | Field Type | Field Dependency | Description | |
---|---|---|---|---|
Label* Default Value: Google Sheets Writer | String | 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. | |
Include Shared Drives Default Value: Deselected | 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 | 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 | String/Expression/Suggestion | N/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 | String/Expression/Suggestion | 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.
| |
Write Mode* Default Value: Create new worksheet | Dropdown list | N/A | Choose the type of write operation to perform for the specified worksheet. The available options are:
| |
Overwrite worksheet if it exists Default Value: Not 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 | 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 header row Default Value: Selected | Checkbox | N/A | Select this checkbox if the row containing column names should be written to the worksheet. | |
Parse data Default value: RAW | Expression/Dropdown list | N/A | Specify whether the input value must be preserved or parsed. Available options are:
| |
Number of Retries Default value: 3 | Integer/Expression | N/A | Specify the maximum number of retry attempts that the Snap must make to write into the worksheet. | |
Retry Interval (seconds) Default value: 1 | Integer/Expression | N/A | Specify the time interval in seconds for each retry attempt. | |
Connection properties | Use this field set to configure the connection properties to specify the read and connection timeouts. | |||
Read timeout in seconds Default value:180 | Integer/Expression | 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. | |
Connection timeout in seconds Default value: 20 | Integer/Expression | 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. | |
Snap Execution Default value: Execute only | Dropdown list | N/A | Select one of the three modes in which the Snap executes. Available options are:
|
Troubleshooting
Error | Reason | Resolution |
---|---|---|
Spreadsheet not found: | The spreadsheet may not exist or cannot | 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 < | Range end column exceeds max column ZZZ. | Reduce the number of |
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 key is not allowed. | Verify that input document data do not contain any null keys. |
Failed to initialize existing worksheet < | Worksheet is not found. | Verify that the worksheet exists on the specified spreadsheet. |
Failed to create a new worksheet for < | 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:
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.
Related Content
Snap Pack History
Have feedback? Email documentation@snaplogic.com | Ask a question in the SnapLogic Community
© 2017-2024 SnapLogic, Inc.