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.

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

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.

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:

Useful information

• Google Sheets API overview

Description

Google Sheets is whatever you want it to be! Retrieve and update data from your sheets as needed.

API documentation

API documentation for Google Sheets (used to build this connector) can be found via the link below:

Authentication

When adding connector instances for Google Sheets, the following authentication methods are supported:

• OAuth 2 (client credentials)

OAuth 2 (client credentials)

When a user chooses to add a connector instance using OAuth 2 (client credentials) authentication, the credentials below are required:

Prerequisites

• You must have a Google Workspace account that's part of an organisation.

The steps

• Stage 1: Create a Google APIs and services project

• Stage 2: Configure a consent screen

• Stage 3: Create OAuth 2.0 credentials

• Stage 4: Apply OAuth 2.0 credentials to your OAuth playground

• Stage 5: Obtain a refresh token

Stage 1: Create a Google APIs and services project

Step 1 Access the Google APIs console - APIs and Services.

Step 2 If you haven't got an account already, go ahead and create one (use the Google account associated with the sheets you want to access).

Step 3 Click the create project button.

Step 4 Enter an appropriate name for this project (leave other settings as they default) and click the create button:

Stage 2: Configure consent screen

Step 1 Select OAuth consent screen from the left-hand navigation menu:

Step 2 Select the internal radio button and then click the create button:

Step 3 Complete app information:

The only mandatory fields are:

Step 4 Click save and continue.

Step 5 Click the add or remove scopes button:

Step 6 Add the following in the manually add scopes section:

https://www.googleapis.com/auth/spreadsheets

...then select add to table:

Step 7 Click the update button at the end of the page:

Step 8 Check that the new scope has been added under sensitive scopes:

Step 9 Click save and continue at the end of the page:

Stage 3: Create OAuth credentials

Step 1 Select credentials from the left-hand navigation menu:

Step 2 Click the create credentials button:

Step 3 Select OAuth client ID:

Step 4 Select web application:

Step 5 Enter a name for this application - we recommend something clearly identifiable as a Patchworks integration, for example Patchworks Web Client:

Step 6 Move down to the authorised redirect URIs section and click the add URI button:

Step 7 Enter the following URI:

https://developers.google.com/oauthplayground

Step 8 Click the create button:

Step 9 Your client ID and client secret are displayed - copy these and add them to your password manager so they're available in the next stage (and for later use in Patchworks):

Step 10 Click OK to close this window.

Stage 4: Apply credentials in the OAuth playground

Step 1 In a new browser tab/window, access the following URL to access the OAuth playground:

https://developers.google.com/oauthplayground

Step 2 In the top-right corner, click the 'cog' icon to access OAuth 2.0 configuration:

Step 3 At the end of the configuration page, select the use your own OAuth credentials checkbox:

Step 4 Add your client ID and client secret (created in the previous stage) into given fields:

Step 5 Close this window.

Stage 5: Obtain a refresh token

Step 1 Still in the OAuth playground, move to the end of the left panel and enter the following URL in the input your own scopes field:

https://www.googleapis.com/auth/spreadsheets

...then click the authorise APIs button:

Step 2 When prompted, log in with the same Google account that you started with in Stage 1:

...you should see confirmation that your app (in this case named Patchworks) has access to Google spreadsheets associated with this account:

Step 3 Click allow to continue to the OAuth 2.0 playground.

Step 4 Click the exchange authorization code for tokens button:

Step 5 Copy the refresh token and add it to your password manager.

Stage 6: Enable the Google Sheets API

Step 1 Switch back to your Google APIs console - APIs and Services tab/window and select enabled APIs and services from the left-hand navigation menu:

Step 2 Select enable APIs and Services at the top of the page:

Step 3 Search for Google sheets API:

Step 4 Click the Google sheets API result tile:

Step 5 Click the enable button:

Step 6 Google Sheets setup is now complete. If you haven't done so already, you can install the Google Sheets connector from the Patchworks marketplace and then use your client ID, client secret, and refresh token to add required connector instances.