With Celigo’s Google Sheets connector, your spreadsheets can dynamically read and write data across your business tech stack.

In a previous article, we demo how to automate lead syncing in Google Sheets.

Here, we demonstrate how Google Sheets can intelligently communicate with your CRM, streamlining record entries and updates. While we use a CRM as an example, the connector can integrate with various systems, including ERPs, HR applications, and more.

Let’s dive into a practical example.

Google Sheets video walkthrough

Imagine you’re a Business Development Representative at a customer conference. You and your team want to use a shared Google Sheet to easily view and update information on current customers and future prospects attending the conference.

Your mission is to meet each customer, capture new information, and have that data sync back to your CRM (Salesforce) automatically. This way, you avoid re-entering information or having your system administrator import updates after the conference.

To do this, you have enlisted the help of your Celigo administrator, who will connect a Google spreadsheet to your Salesforce instance and then build two flows.

• The first flow will export Salesforce’s existing customer conference attendee details into the spreadsheet.
• The second flow will use spreadsheet entries to add new attendees or update details for existing ones in Salesforce.

Celigo provides pre-built templates for Google Sheets from the Celigo Marketplace. These templates are partially pre-configured to make it easier to get started.

Let’s see how these templates are used in practice, beginning with the first flow, which will export information on conference attendees in Salesforce and import that information as rows in a Google Sheets Spreadsheet.

Flow 1: Conference attendees to Google Sheets

Below is the configuration of the complete flow. This is a two-step flow. In the first step of this flow, conference attendees are exported from Salesforce. In the second step, we import the information on these attendees to a Google Sheets spreadsheet.

To better understand the Google Sheets connector, we’ll take a step back. Let’s assume the Salesforce export step is already configured, and now we need to build the Sheets step from scratch.

We’ll begin from an empty step and click “Add destination/lookup” to configure this step with the Sheets connector.

You’ll be prompted to select the application you’d like to use. To narrow your options, begin typing “Google Sheets” in the application field.

Next, configure the “What would you like to do?” field. Remember that records are coming out of Salesforce (our source application), and we want to put those records into Google Sheets (our destination application).

We’ll select “Import Records into Destination Application.”

Before you click “Create Flow Step” to create a Google Sheets destination/lookup, scroll down to find preconfigured flow steps from the Celigo Marketplace’s collection of templates. The steps recommended vary depending on the application you’ve chosen, what you would like to do with this step, and the type of step you’re building (export, import, lookup).

Template flows are built to save you time and have best practices baked in. We’ll select the “Update Existing Rows and Append New Rows” step. This particularly useful template flow makes adds and updates to your spreadsheet possible by including a pre-written hook.

Click ‘Next’ to move on, using this template flow step as a starting point.

The Google Sheets import form (pictured below) is the next thing you’ll see. If you use a template, some of this information will be filled out in advance.

We’ll look at the ‘General’ tab first.

• Name your import: Here, we name our import so that it describes our flow in detail.
• Description: To distinguish this flow step, more descriptive details can be added here.
• Connection: A Connection is a reusable object you can create to connect your various application environments to integrator.io. We’ll need to create a Google Sheets connection to access/affect our Google Sheets environment.
  One to many: Suppose the records traveling through the flow are parent records to the child records you mean to target. One record in the flow will be referenced to create many records. The help bubble for this field will explain this use more deeply. However, this isn’t needed for this flow.

After giving this step an appropriate name, creating a connection is perhaps the most important step we can take first.

We’ll look at how to create a Google Sheets Connection next.

Creating a Google Sheets connection

In the “Connection” field, we can either select or create a connection. Let’s assume this is our first time creating a Sheets connection. We’ll click the blue “create connection” text in the field to reach the following “Create Connection” screen.

Give your Connection a name. We’ll be able to select and reuse the same Connection for the next flow.

Next, click ‘Scopes’. You can think of scopes as the permissions for your Connection; and what it can and can’t do in your environment. For example, we’ll have to write values into the spreadsheet in this flow. We’ll keep that in mind when selecting scopes.

Notice we have two scopes, one is marked “readonly”.

The other scope will enable both read and write access. Since we need to write values into our Google Sheets environment, we’ll select that scope. Press the ‘>’ button to move the scope to the right side, then save this configuration.

Now, after clicking “Save & Authorize,” we’ll be prompted to select the Google account we wish to use and allow integrator.io the permissions it needs to connect to our environment.

Once you’ve chosen your account and acknowledged these permissions, your connection is complete.

Configuring the Google Sheets import

With a connection added, the general configuration is complete. However, under the ‘Settings’ tab, we have a few fields to check before we move on to mapping.

Below is the complete configuration of this step, as it fits into this flow:

Let’s briefly look at these fields one by one:

• Spreadsheet ID: You’ll need to supply the ID of the spreadsheet you’d like to use in your flow. Copy this segment out of the URL and paste it into this field. The help bubble for this field will inform you where to find this segment.
• Sheet name: By default, the first spreadsheet in a Sheets project will be named ‘Sheet1’. However, if you have multiple sheets or named sheets, make sure to enter the name of the specific spreadsheet you’d like to access in single quotes. Ex. ‘ConferenceSpreadsheet’, ‘Sheet2’, etc.
• “Does your sheet have column headers?” Checkbox: This checkbox is very important. If your spreadsheet begins with a row of column headers (like all good spreadsheets do!) click this box. The spreadsheet used in this case features headers to make information easily readable and well-organized.
• Headers: This box will be filled automatically after all options are configured.
• Column to find existing rows: To avoid double entries and find existing columns to update changed values, we need a column to match. Make sure this column in the spreadsheet is a single column that is always unique, something like an ID or (in this example) an email address. Simply type the column’s name in this field as it appears in your spreadsheet.
• Source record field to find existing rows: This refers to the field in records exported by earlier steps (like the Salesforce source export that began this flow in our case) that will be used to find matches. This field will match values in the spreadsheet column specified above to determine existing rows as the flow runs.We want to match the email of Salesforce records to the email of attendees in the spreadsheet. Where a match is found, an update will be considered. If no match is found, an attendee will be added.

Now, with all the configuration complete, we can map the information from Salesforce to our Google Sheets spreadsheet.

Import mapping with Google Sheets

Let’s grasp this entire flow broadly. In our scenario, we assume that Leads and Contacts have already been added as Campaign Members to a Salesforce campaign created to track the event. In the initial export from Salesforce, we include Campaign Member attendee details like name, role, company, email, and useful notes on the attendee. The attendee info is then sent to the Google Sheets step to be imported into the spreadsheet.

Below is the complete mapping for the Sheets import step of the flow, along with the empty spreadsheet with the column headers selected.

Note that where we map to Google Sheets, we use the column name. Intuitively, the field chosen from Salesforce (corresponding to the fields on the right side) will be added under the chosen column in Google Sheets (corresponding to the fields on the left side) on a record-per-row basis.

This flow is ready with this step fully configured and mapped. After running the flow, the spreadsheet is filled with information for all known conference attendees.

We’ll now focus on the second step.

With the second step, information manually entered into the spreadsheet during the conference will be imported into Salesforce as new records or updates to existing records.

Flow 2: Spreadsheet adds/updates to Salesforce

Let’s look at the structure and purpose of this flow (pictured below).

The first export step of this flow will read all the information from the spreadsheet, bringing all the known attendees into the mix. Next, a branch will sort records based on whether they are designated a “Prospect” or a “Customer”.

The “Prospect/Customer” column from the spreadsheet notes whether the attendee is a “Prospect” (a potential customer) or a “Customer” (someone from a business we are known to work with). The branch will respectively send them to a step to add/update either a Lead or a Contact based on their label.

In the final step, the new Leads and Contacts are added to the Conference Campaign (but only if they were not previously added).

Now that we understand how this flow achieves our goal, let’s look closely at the first Google Sheets Export step.

Configuring the Google Sheets Export

A copy of the “Read All Data from Spreadsheet” export from the marketplace was used to start this step. Below is the complete configuration of this step. The same Connection was used, further simplifying the setup.

You’ve already seen fields like the Spreadsheet ID and the “headers” checkbox (which simply needs to be checked or unchecked depending on your spreadsheet format), but there are a few extra features in a Sheets export you’ll need to configure that are not in a Sheets import.

• Resource: Here, you’ll select the general use case that fits your purpose.
  • Spreadsheets: This resource deals with entire sheets and their metadata.
  • Spreadsheets.developermetadata: This resource relates specifically to metadata, as developers have applied it.
  • Spreadsheets.values: This resource allows you to access the values inside the spreadsheet cells. In other words, you can retrieve the information from your spreadsheet. This is the most common resource and was used for this step.

• API endpoint: This is where you decide what you would like to do with the selected resource. The options open to you will change depending on the resource. For example, after selecting “Spreadsheets.values” for our resource, we’ll choose “Get” to retrieve all the values in the spreadsheet. More specific resources can also be used, like “Batchget”, which allows you to get data from multiple specific ranges inside the spreadsheet, and “Batchgetbydatafilter”, which allows you to target multiple ranges that match filters.

• Range: To get specific about what data you’d like to access, you can use A1 Notation. If you’d simply like to get all the data from a sheet (as we will in this flow), enter the name of the sheet you’re targeting (found in the bottom left of your spreadsheet) in single quotes (ex. ‘Sheet1’, ‘MySpreadsheet’, etc).

The step is complete after being configured to access values in the spreadsheet with the “Spreadsheets” Resource and pull those values into the flow with the “Get” resource.

Below is an example of how we’d enter information into the spreadsheet to use this flow. This flow step will read every row from the spreadsheet, match the email field for updates, and create new records in Salesforce for rows that lack IDs to match.

So, “Peter Smith” from Mohr Inc. will be added to Salesforce, and other rows will be checked for updated data that differs from their respective Salesforce record.

We can click the calendar icon in the top right to schedule this flow to run daily, synchronizing the information entered each day of the conference into our CRM.

Furthermore, to sync the IDs back from Salesforce every time this flow runs, we can select the ‘settings’ icon (right beside the calendar icon) and set our first flow to run immediately after this one. That way, the empty ID columns in this row will be filled in by the first flow immediately after this second flow runs.

Enhancing your workflow with Google Sheets integrations

Whether your use case involves writing to spreadsheets, reading information from them, or both, integrating with Google Sheets is simple and significantly enhances their capabilities. Using flow steps from the Celigo Marketplace makes the process even quicker and easier. Instead of viewing spreadsheets as just a data repository, you can use them as an intermediary to quickly enter data into other applications.

Additionally, you can automate the compilation of data from various applications into your spreadsheets, streamlining your workflows.

With Celigo’s Google Sheets connector, your spreadsheets can become dynamic tools, reading and writing data across your business tech stack. In a previous article, we demo how Google Sheets can intelligently communicate with your CRM, streamlining record entries and updates.

Here, we’ll demonstrate how a spreadsheet can go from a shared note-taking tool to a shortcut for synchronizing your leads into your CRM with a flow.

Imagine you’re a Business Development Representative at a conference, capturing leads in a Google Sheet. You want to quickly add these leads to your CRM without manual entry, allowing sales reps to follow up immediately.

A flow using the Google Sheets connector will allow you to achieve this outcome quickly. The latest enhancements to this connector allow you to format spreadsheet data with headers and use pre-set template flows for easy automation between your CRM and Google Sheets.

We will examine a flow that solves this issue to demonstrate the use of the Google Sheets connector in practice. For this example, we used Salesforce as our CRM and a Salesforce campaign called “Tech Conference – Houston.” We’ll explore how this flow works, focusing on getting a Google Sheets export up and running. Once we’ve established a proper connection and export configuration, we’ll examine mapping that data properly.

Before you start a flow, it’s always a good idea to leverage what you already have. Celigo’s Integration Marketplace offers preconfigured templates with flow steps and is a great way to get a headstart on building a flow with a common use case.

Google Sheets video walkthrough

Explore the features and new enhancements of our Google Sheets connector in this video walkthrough.

Using the Marketplace to Streamline Work

When you click ‘Create Source’ to start building a new flow and search for the app of your choice, you will receive suggestions for existing flow steps and flow steps from Celigo’s Marketplace based on your application.

One of the Marketplace steps available for download is called “Read all data from a spreadsheet.” This prebuilt step is perfect for our needs and saves time. If you plan to read data from a spreadsheet, start here. Next, we’ll explore the information required to configure the connector after using this Marketplace step.

Creating a Google Sheets connection

Starting with this cloned step will preconfigure the resource, API endpoint, and check the “First Record Contains Headers” box. This is crucial if your spreadsheet has column names. With the Google Sheets connector, each row becomes a record, and the column names will match the corresponding values.

Name this step and create a connection to your Google Sheets environment. Click ‘Create Connection,’ name your connection, and configure the scopes. This setup ensures your flow understands your spreadsheets without complex business logic.

Creating a flow that solves our problem only requires reading from a spreadsheet so we’ll check the ‘readonly’ scope, press the ‘>’ button to move the selected scope over to right side, then save this configuration. Now we’ll be prompted to select the Google account we wish to connect to and allow the permissions integrator.io needs to connect to our environment. Once you’ve selected your account and acknowledged these permissions your connection is complete.

Add your spreadsheet ID

The next field to fill out is the “Spreadsheet ID.” The help bubble beside this field will instruct us how to retrieve this information.

After navigating to the desired Google Sheet, copy and paste the ID into the specified field. Then, check the final box to complete the setup.

Enter the range

The Range box asks for the name of the spreadsheet you want to access. After entering the ID for your spreadsheet project, you still need to indicate which spreadsheet you want to use. For example, you may have renamed your spreadsheet from “Sheet1” to something more descriptive. You may have multiple sheets involved in the project named “Sheet1”, “Sheet2”, “Sheet3”, and so on. To clarify which spreadsheet you want to use, place the name of the sheet you’re targeting into this field. You may also target specific cells, rows, or columns using more in-depth Google Sheets’ A1 notation if you’d like.

If you’ve created your connection, entered the ID from your spreadsheet URL, and specified the spreadsheet name, you can hit preview and see each spreadsheet row come through like a record from any other business application.

That’s all you need to take data from your spreadsheets and into your flows!

Using exported spreadsheet data

In the second step, we configure an import to create Leads in Salesforce. This is all standard and has more to do with Salesforce than Google Sheets. However, there is an important distinction when it comes to mapping. 

Recall that when the “First record contains headers” checkbox is active, the column names in your spreadsheet will be applied to the corresponding fields of each record. You should be able to select your fields from a list of suggested fields once you begin mapping source fields. However, if one or more of your Google Sheets fields do not appear as suggestions, you can access them using handlebars and get the same results.

Below is an example. The rest of the fields are mapped normally, while the Notes field is mapped using handlebars. Either approach will get the same results.

Once this is complete, we use response mapping to capture the ID of any leads that were created or found to already exist. This is then used in the final step to create a Campaign Member Salesforce object between each Lead ID and the Salesforce campaign (whose ID is entered with hardcoded mapping).

We set this flow to run on a scheduled basis, but after a manual trial, it’s clear everything has worked! Rather than individually creating all of these leads after a long conference, this spreadsheet has become a dynamic notebook capable of updating our CRM automatically.