Working with the Google Sheets connector

Introduction

Having installed the Google Sheets connector and added required instances (using credentials obtained via the Google Sheets APIs and Services portal), you can add this connector to process flows, syncing data to/from your spreadsheets.

This page provides guidance and techniques for using the Google Sheets connector in process flows.

Connector settings

Having added a connector shape to your process flow and selected a Google Sheets source instance and endpoint, you'll see that variables are available - for example:

Specific variables will vary according to which source endpoint is selected but essentially, this is where you define which spreadsheet to access and then additional details (for example, target cell range).

Finding your spreadsheet id

Whichever endpoint you select, you'll always need to enter a value for a spreadsheet id variable. Every Google Sheets spreadsheet has a unique id, which you can find by opening the spreadsheet and checking the URL. Here you'll find a long string of characters after base URL - for example:

This string of characters (everything between the two / characters) is your spreadsheet id.

More information is available in Google's documentation, here.

Example: Retrieving data

For this example, we have a simple product list in a spreadsheet:

In the steps below we'll walk through the process of sending data from columns E, F, G, H, and I into a destination database.

Prerequisites

Ensure you've completed the required setup in your Google Workspace to obtain OAuth 2.0 credentials.
Ensure that the Google Sheets connector is installed from the Patchworks marketplace.
Ensure that at least one instance has been created for this connector, using your OAuth 2.0 credentials for authentication.

The steps

Step 1 Create a new process flow and add a connector shape. Configure the

Field

Setting

Notes

Source instance

Select the Google Sheets connector instance that you want to use.

Source endpoint

Select GET Method

This endpoint retrieves data from a given spreadsheet.

Spreadsheet id

Enter the id associated with the Google Sheets spreadsheet that you want to access.

Range

Enter the range of data that you want to retrieve. If your spreadsheet includes multiple sheets, you should prefix the range with the sheet name.

For our example, we're targeting a specific sheet named fruit and we're interested in data from column A to column I, rows 2 to 13. This is expressed as: fruit!A2:I13.

If your spreadsheet is comprised of a single sheet, you don't need to specify the sheet name - any range given is taken from the first visible sheet.

For example:

Step 2 If we run the flow now and check payload information for the connector step, we can see data from the spreadsheet:

The first thing to note is that the majorDimension is set to ROWS:

majorDimension is a Google Sheets API parameter that determines how values are structured - it might be set to ROWS or COLUMNS (ROWS is the default if nothing is defined). In Patchworks, this value is set in the schema for each Google Sheets endpoint.

You can read more about this field in Google's documentation, here

Taking our example, we can see that each row in our sheet is output as an array containing items for each column. Let's look at this structure in a bit more detail.

In our spreadsheet, columns are organised from A to I:

But in our payloads, these column labels aren't shown:

To target columns or rows, we use numbers. For rows this is straightforward - by default, every row in a sheet has a unique number. But for columns we must translate existing alpha labels to numbers, starting at 0. So our columns A to I can be referenced as 0 to 8:

You'll see how this works in subsequent steps.

Step 3 Now we've connected to our Google Sheet and pulled a single payload that contains all rows. To send this data to our destination system, we need to break that payload into smaller batches, with one record (i.e. row) per payload. To achieve this, we add and configure a flow control shape:

Here, we're saying that we want to target records within the values array and create batches of 1 - i.e. create one payload for every row. We also toggle the Do not wrap single items in an array option ON, because our destination system requires this.

Now if we run the flow and check the output, we have multiple payloads:

...each containing data for a single row:

Step 4 Next, we add a connector for our destination system:

Step 5 Finally, we add a map shape and define field mappings to determine where our column values are placed in the destination system - this is where our numbering system for column labels is needed:

Step 6 At the end of the mappings page, we toggle the wrap input payload option to ON, so our data is in the required format for our destination system.

Step 7 Having saved field mappings we can run the flow and check resulting payloads for the map step - here we see that our spreadsheet columns are successfully mapped to destination fields.

Step 8 If we check our destination database, all spreadsheet rows have been added: