Google Sheets ETL Target

Gathr provides Google Sheets emitter to write data to Google Sheets.

Target Configuration #

Configure the target parameters that are explained below.

Connection Name #

Connections are the service identifiers. A connection name can be selected from the list if you have created and saved connection details for Google Sheets earlier. Or create one as explained in the topic - Google Sheets Connection →

Use the Test Connection option to ensure that the connection with the GCS channel is established successfully.

A success message states that the connection is available. In case of any error in test connection, edit the connection to resolve the issue before proceeding further.

Spreadsheet(s) #

Select how the data will be emitted to the target. The supported options are:

• Create a new spreadsheet: Use this option to write data to a new spreadsheet.

• Use an existing spreadsheet: Use this option to write data to an existing spreadsheet.

Create New Spreadsheet #

If the option to write in the Spreadsheet(s) is selected as Create New, additional fields will be displayed as given below:

Spreadsheet Location #

Option to browse the file explorer and select a directory where the new spreadsheet will be created.

The application will create a new directory if a directory name provided here does not exist in the drive.

Spreadsheet(s) Creation Method #

Select the method to determine the number of new spreadsheets to be created in Google sheets while emitting data.

• Create Single File: Create a single spreadsheet to emit the entire processed data.

  If the option Create Single File is selected, then update the following fields:

  • Spreadsheet Name: A spreadsheet name should be provided to be used for creating the new spreadsheet.

  • Append UUID: Select whether a unique identifier should be suffixed with the new spreadsheet’s name.

• Calculate Automatically: The application automatically determines the required number of new spreadsheets to emit the data.

  If the option Calculate Automatically is selected, then update the following field:

  • Spreadsheet Prefix: The spreadsheets name will start with the prefix that is provided here, followed by the partition ID and timestamp.

    If the spreadsheet prefix is left blank, then the names of the Google spreadsheets will be autogenerated as GSheet_1_<timestamp> For example, GSheet_1_20220307132500, GSheet_2_20220307132500.

• Specify Number of Files: Specify the number of spreadsheets that should be created to emit the data.

  If the option Specify Number of Files is selected, then update the following fields:

  • Spreadsheet Prefix: The spreadsheets name will start with the prefix that is provided here, followed by the partition ID and timestamp.

    If the spreadsheet prefix is left blank, then the names of the Google spreadsheets will be autogenerated as GSheet_1_<timestamp> For example, GSheet_1_20220307132500, GSheet_2_20220307132500.

  • Number of Spreadsheets: Specify the number of spreadsheets to be created.

Use Existing Spreadsheet #

If the option to write in the Spreadsheet(s) is selected as Use Existing, additional fields will be displayed as given below:

Select Spreadsheet #

Browse using the file explorer option and select an existing spreadsheet to emit the data.

Output Mode #

Specifies how to emit the data in the target.

• Overwrite: Existing data will be overwritten.

• Append: Data fetched from the source will be appended to existing data.

Output Fields #

Fields in the message that needs to be written into files must be selected.

Parse Input Value #

Control how the input data should be interpreted.

Select one of the below options to choose how the input values should be parsed:

• No: The input is not parsed and is inserted as a string.

  Example: The input “=5+5” places the string, not the formula, “=5+5” in the cell.

• Yes: The input is parsed exactly as if it were entered into the Sheets UI.

  Example: “Mar 15 2023” becomes a date, and “=5+5” becomes a formula.

Share Directory #

The directory specified here will be shared with the users mentioned in the Share with Users field.

Share with Users #

The directory/spreadsheet(s) will be shared with the specified users as per the access granted.

Email ID #

Provide the email ID of the user to share the directory/spreadsheet(s).

Access #

Select the type of access that should be granted to the user with whom the directory/spreadsheet(s) will be shared.

• Viewer: Users with this access can view but can’t change or share the file with others. 

• Commenter: Users with this access can make comments and suggestions but can’t change or share the file with others. 

• Editor: Users with this access can make changes, accept or reject suggestions, and share the file with others.

Add configuration: Additional properties can be added using Add Configuration link.

Post Action #

To understand how to provide SQL queries or Stored Procedures that will be executed during pipeline run, see Post-Actions →

Notes #

Optionally, enter notes in the Notes → tab and save the configuration.

Google Sheets Usage Limits #

There is a Google Sheets cell limit:

• A Google Sheet can contain up to 10 million cells, or

• A maximum number of 18,278 columns (column ZZZ)

To understand the usage limits that apply to Google Sheets, please refer to the link below:

Google Sheets Usage Limits.