Skip to Content

Write data to a Google Sheets spreadsheet in Jitterbit Studio

Introduction

The Google Sheets connector writes tabular data to Google Sheets using a service account you configure in the Google Cloud Console. The Create Sheets activity creates a new spreadsheet and populates it with rows from the source each time the operation runs.

To write to an existing spreadsheet, use the Update Sheets activity instead.

This guide assumes the following:

  • A Google Cloud service account with the Google Sheets API enabled and credentials obtained, as described in Google Sheets prerequisites.
  • A source activity is already configured in the project.

Design pattern

The Create Sheets activity follows the transformation pattern:

flowchart LR A[Source activity] --> B[Transformation] --> C[Create Sheets activity]

Part 1: Configure the Google Sheets connection

Step 1: Create the connection

  1. On the Project endpoints and connectors tab of the design component palette, locate the Google Sheets connector and click it to open its configuration.

  2. Connection Name: Enter a unique name for the connection. The name must not contain forward slashes / or colons :.

  3. Project ID: Enter the project_id value from the service account credentials JSON file.

  4. Client Email: Enter the client_email value from the credentials JSON file.

  5. Private Key String: Enter the private_key value from the credentials JSON file.

  6. Key Algorithm: Select RSA.

  7. Click Test to verify the connection.

  8. Click Save Changes.

Part 2: Configure the Create Sheets activity

Step 1: Name the activity and configure the spreadsheet

  1. On the design canvas, drag a Create Sheets activity type from the Google Sheets connection onto the canvas.

  2. Double-click the activity to open its configuration.

  3. Name: Enter a descriptive name, such as Create Contacts Spreadsheet.

  4. Spreadsheet Name: Enter the name for the Google Sheets file, such as Contacts Export. This is the file name that appears in Google Drive.

    Tip

    Use a project variable for the spreadsheet name if the target file name needs to differ between environments.

  5. Number of Columns: Enter the number of columns your dataset requires. The default is 26, which provides columns A through Z. Increase this value if your source data has more than 26 fields.

  6. Continue on Error: Select to allow the operation to continue if an error is encountered in a batch request. Any errors are written to the operation log.

  7. Click Next.

Step 2: Review the data schema

  1. Review the request schema. The key structure is:

    createSheetsRequest > Entity > Sheets > Sheet > data > row

    The row node is the looping node: each entry represents one row in the spreadsheet. Within each row, the fields A, B, C... hold string values for each cell.

  2. The folder_id field under createSheetsRequest is optional. Supply a Google Drive folder ID in the transformation to place the spreadsheet in a specific folder. If omitted, the spreadsheet is created in the folder shared with the connection's service account.

  3. Click Finished.

Part 3: Map the transformation

  1. On the design canvas, add a Transformation step between the source activity and the Create Sheets activity.

  2. Open the transformation. The source schema appears on the left and the Create Sheets target schema appears on the right.

  3. Locate the row node under Entity > Sheets > Sheet > data. Studio automatically treats this node as a loop node, so each source record produces one row in the spreadsheet.

  4. Map source fields to the column letters (A, B, C...) under the row node. The columns correspond to the spreadsheet columns from left to right.

  5. (Optional) Map the SheetTitle field under Sheet to set the name of the sheet tab within the spreadsheet. If not mapped, a default tab title is used.

  6. (Optional) Map startRow and startColumn values under Sheet to control where data begins writing. Both fields are zero-based integers, so a value of 0 starts at the first row or column.

For a full walkthrough of transformation mapping, see Transformation mapping overview.

Verify the integration

Deploy and run the operation. Check the operation logs to confirm that the Create Sheets activity completed successfully. Open Google Drive to verify that the spreadsheet was created with the expected name and that the rows and columns match the source data.

If data is missing or columns are cut off, verify that the Number of Columns value in the activity configuration is at least equal to the number of mapped columns in the transformation.

To run this operation on a recurring schedule, see Schedule an operation to run automatically.