# 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](https://doc.wearepatchworks.com/product-documentation/connectors-and-instances/patchworks-connectors/google-sheets-prebuilt-connector)), 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](#finding-your-spreadsheet-id) 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`. {% hint style="info" %} More information is available in Google's documentation, [here](https://developers.google.com/sheets/api/guides/concepts). {% endhint %} ## 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](https://doc.wearepatchworks.com/product-documentation/connectors-and-instances/patchworks-connectors/google-sheets-prebuilt-connector). * Ensure that the Google Sheets connector is [installed from the Patchworks marketplace](https://doc.wearepatchworks.com/product-documentation/connectors-and-instances/working-with-connectors/installing-a-connector). * Ensure that [at least one instance has been created](https://doc.wearepatchworks.com/product-documentation/process-flows/cross-reference-lookups/adding-a-cross-reference-lookup) 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.	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 i`d`	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.

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](https://doc.wearepatchworks.com/product-documentation/developer-hub/connector-builder/building-your-own-connector/4-endpoints/endpoint-options/schema-taxonomy) for each Google Sheets endpoint. {% hint style="info" %} You can read more about this field in Google's documentation, [here](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values) {% endhint %} 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](https://doc.wearepatchworks.com/product-documentation/process-flows/building-process-flows/process-flow-shapes/standard-shapes/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](https://doc.wearepatchworks.com/product-documentation/developer-hub/patchworks-core-api/core-api-spotlights/initialising-a-process-flow-and-sending-data-via-the-patchworks-api/the-steps/mapping-payload-data) 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](https://developers.google.com/sheets/api/guides/concepts)