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.
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).
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.
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.
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.
Step 1 Create a new process flow and add a connector shape. Configure the
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:
| Field | Setting | Notes |
|---|---|---|
Source instance
Select the Google Sheets connector instance that you want to use.
For information about obtaining OAuth 2.0 credentials required to create instances for the Google Sheets connector, see Google Sheets (prebuilt connector)
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.
This id forms part of the URL for the spreadsheet. For more information please see Finding your spreadsheet id.
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 detailed information about range notation see Google's documentation, here.
