Connecting Salesforce to Google Sheets: A Step-by-Step Guide

Every company needs to build meaningful connections with their customer base to keep them satisfied and improve brand loyalty. Salesforce is a platform that allows you to manage customer relationships across departments such as marketing, sales, and service. 

Furthermore, you’ll perform CRM analytics to gain insights into your team’s efforts, learn more about your customers, and determine how best to serve them. But native Salesforce analytics capabilities may be inadequate, so it’s best to use an external application like Google Sheets.

However, exporting large amounts of Salesforce data manually can be tricky and inefficient. In this article, we’ll show how to connect Salesforce to Google Sheets to automate your reporting and analytics.

How to connect Salesforce to Google Sheets – methods available

There’s a native functionality known as Data Export and a web application called Dataloader.io that you can use to export data from Salesforce. But we will also explore third-party ways to help automate the integration process. Here are some efficient methods to help connect Salesforce to Google Sheets. We’ll provide step-by-step instructions for each of them in a later section. 

• Coupler.io web application and connector add-on

Coupler.io is a data and analytics automation platform that you can use to import data from Salesforce into spreadsheets or data visualization software. It allows you to set up custom schedules for your integration e.g., every 15 minutes, every 30 minutes, hourly, or weekly. 

• Salesforce connector from Google

This Google Sheets add-on allows you to connect Salesforce to your Google Sheets account. You can import, update, and set up automatic refreshes for your reports. But you’d need an Enterprise, Performance, Unlimited, or Developer Salesforce Edition.

• CSV export with Data Export and Dataloader.io

You can download Salesforce tables and reports as CSV with the native Salesforce functionality known as Data Export and a web-based tool called Dataloader.io. Although you can pull or schedule Salesforce exports, you still have to upload the CSV files to Google Sheets manually. 

• Salesforce REST API integration

You can use Google Apps Script to load data via APIs for the Salesforce to Google Sheets integration. It can be quite complex and requires you to write some amount of code for the setup.

How to auto pull Salesforce data to Google Sheets?

Coupler.io is the most convenient way to automate data from Salesforce to Google Sheets. You can easily set up the integration once, specify a custom schedule, and easily access your data in the destination. It’s best for frequent analysis or reporting of your customer relationship data. There are two main ways to connect Salesforce to Google Sheets using Coupler.io: the web app and the connector add-on. We’ll show the workflows for setting up both of them below.

Coupler.io web application

If you don’t already have a Coupler.io account, you can sign up for a free trial – no credit card required. Otherwise, log in and proceed to set up a new importer for the integration. Then, proceed with the next steps for using the Coupler.io web app to perform the integration:

• Click Add New Importer, choose a source app and destination app, and Proceed. For a Salesforce to Google Sheets integration, you’ll select both apps to connect.

• To configure the source, add your Salesforce account and Continue.

• Next, you’ll choose the Data type. In this case, we’ll select Data Entity. You can also choose Custom SOQL if you want to make a custom request by writing SOQL.

Data Entity helps you select a predefined data type such as Contacts, Opportunity, Account, etc. 

• For the basic settings, pick a data category to export. We’re using Lead here.

There are some advanced settings to help filter your data, but these are totally optional.

• Next, you have the option to transform your data.

You can manage columns and perform actions such as filtering, sorting, or adding columns. 

• To configure the destination, add your Google Sheets account and Continue.

You may also add a cell address/range to specify the cell where data starts from. You can set the import mode as Replace or Append to either overwrite all existing data in the sheet or simply begin at the end of the last import.

• Now, you can Save and Run the importer. On the importer page, you can specify a custom schedule for auto refresh. You can set the interval, days of the week, time preference, and schedule the time zone.

When you check the chosen workbook and sheet, you should find your data already in it. 

Coupler.io connector add-on

Coupler.io also provides an add-on for Google Sheets to connect to Salesforce. This method can help you integrate directly with the Google Sheets app. However, you need to set up a Coupler.io account before making the connection. Below are the steps for automating Salesforce to Google Sheets using the Coupler.io connector add-on. 

• First, open a new spreadsheet in Google Sheets. Then, click Extensions, select Add-ons, and choose Get Add-ons.

• You’ll see the Google Workspace Marketplace. Type Coupler.io in the search bar and select it from the list of search results.

• On the Coupler.io add-on page, click install. Next, you’ll see some mini windows open up one by one. First, give permission to install Coupler.io, then choose the Google Account to work with. Next, click Allow for any permissions required by the add-on. Once successful, you should be able to find Coupler.io among add-ons.

• Go to Extensions, select Coupler.io, and click Open Dashboard. 

• A sidebar will open up on the right side of the spreadsheet. 

• Now, click Add Importer. You’ll see a new window where you can set up the importer.

You’ll connect a source account, transform your data, and specify the spreadsheet and sheet for the import. You can also set up a schedule to update data imports from Salesforce to Google Sheets. For detailed guidance, you can refer to the steps discussed in the previous section. 

Note: The Coupler.io web application is the most convenient way to connect Salesforce to Google Sheets. It helps you integrate SF with other destinations, such as Microsoft Excel and BigQuery. Moreover, you can connect Salesforce to Looker Studio. But the Coupler.io add-on can only allow you to use Google Sheets as a destination.

How to import Salesforce tables into Google Sheets?

Since we’re covering add-ons, it would be a great idea to discuss how to import Salesforce tables into Google Sheets with the data connector for Salesforce from Google. It’s a free option that allows you to import data from reports, update your existing data, and set up automatic imports. Here’s how to import Salesforce data to Google Sheets using the add-on from Google:

• Go to Extensions, click Add-ons, then Get Add-ons. In the Google Workspace Marketplace, search for Salesforce Connector. You should see a couple of results.

• Select Salesforce Connector from Google. You’ll be taken to the installation page. After you click Install, you’ll click Continue to give permission to install the add-on. Next, you’ll choose the Google Account you’d like to use. Then, click Allow to give necessary permissions. Once successful, the Salesforce Connector will be added to the Add-ons section.
• Click Extensions, select Salesforce Connector, then click Open.

• Now, you should see a sidebar on the right-hand side of the spreadsheet. 

• Click Authorize then a popup will appear. If you aren’t logged into your Salesforce account, you’ll be asked to input your Salesforce username and password as well as click on login. Once you’ve logged in to your Salesforce account, you’ll be asked to Allow access. 
• Next, the sidebar will refresh with a couple of operations to enable the integration. You should see options, such as Reports, Import, Update, Delete, and Refresh. Click on Reports to import data from a Salesforce Report to Google Sheets here.

• Now, you’ll enter a keyword and search for the exact report you’re looking for. 

When you click on Get Data, a dialog box will inform you that all data from the active spreadsheet will be replaced. Click on Replace to continue. You may also choose to cancel. Go back and select “Import to new sheet” if you want your Salesforce data in a brand new sheet. You should see your chosen sheet already populated with the new records. 

That’s all for how to import reports using the Salesforce Connector from Google. Remember that there are other actions, such as Import, Update, Refresh, and Delete to help with the integration. There are some limitations, such as the ability to only pull reports via the no-code option and auto refresh being done every 4 hours, 8 hours, and 24 hours. So you might have to seek out a more flexible alternative, such as Coupler.io, to cover all your Salesforce integration needs.

CSV export with Data Export and Data Loader

Now, let’s explore manual methods that you can use to export Salesforce data easily. You’d have to export your data as CSV files and import these files into Google Sheets manually. There are two major methods to help you achieve this – Data Export and Dataloader.io.

Data Export

Salesforce has a native functionality for exporting data known as Data Export. You can access it using any edition of Salesforce. We have already covered the integration process for Data Export in our Salesforce export methods, but here’s a summary of the steps involved. 

• On the UI of your Salesforce dashboard, you’ll navigate to Setup on the top-right menu. 

• You’ll see a page with a sidebar such as below. Go to Administration, click the Data dropdown, select Data Export, and you’ll access the Monthly Export Service function.

When you click Export Now, you’ll proceed to select specific data or include all data, and click Start Export. It allows you to manually pull data from Salesforce once per month. Once your file is ready for download, you’ll receive an email notification. 

• The email contains a link to the Data Export page where you can download the file.

• You can also use Schedule Export to automatically export data on a monthly schedule.

Now, you’ll select the preferred settings for your automatic Salesforce exports. After selecting the frequency and data type, you’ll click Save. This will export the file periodically based on the schedule as well as send email notifications so you can return to the page for the download.

Dataloader.io

Dataloader.io is a web application that you can use to import and export data from Salesforce. You can export data as CSV or pull it into a directory in an FTP server, Dropbox, Box, or a database. However, the free plan only allows you to export 10,000 records per month from Salesforce. A complete guide on Dataloader.io has been published already, but here’s a quick summary. 

• Navigate to dataloader.io and click Login with Salesforce. 

• At the top-left corner, click New Task and select Export from the dropdown options. 

• Next, choose the connection and object (data entity) to export, and click Next.

• Select the fields you need, add filters, and specify how the data should be ordered. 

This will generate a SOQL query to help pull your data. You can also write your own SOQL, but it won’t let you make changes using the query generator anymore. Click Next to proceed. 

• Next, you’ll set the schedule, include specific settings, and connect destinations.

Dataloader.io allows you to run the task immediately or use Schedule Task to automate on an hourly, daily, weekly, or monthly schedule. The Advanced section lets you specify Date format, Timeout, and others. The Add Destination Folder section allows you to connect to any of the available destinations in dataloader.io. Lastly, you’ll click Save or Save & Run to start the task.

Salesforce REST API integration to Google Sheets

Salesforce offers REST API for only a few editions – Enterprise, Unlimited, Developer, and Performance. This means that you can only use the API method to connect Salesforce to Google Sheets with these editions. We’ll cover some of the connection steps here, but please refer to the Salesforce API guide for complete guidance. 

• Login to Salesforce, switch to Setup, type App Manager in the search bar, and click New Connected App.

• Add the configuration settings for your new connected app.

You’ll fill out important fields like Connected App Name, API Name, and Contact Email. Make sure to mark the checkboxes for both Enable OAuth Settings and Enable for Device Flow. Then, select Manage User via API from the list of Available OAuth Scopes, and click Add to include it in the Selected OAuth Scopes box. Scroll down the page to click Save and then Continue.

• Next, you need to get an access token and instance URL.

There are a few ways of getting these details, but here’s a faster process. On the Connected App’s settings page, you’ll scroll to the Initial Access Token for Dynamic Client Registration section and generate an initial access token. For the instance URL, simply add /services/data to your login URL like this – https://yourname-dev-ed.my.salesforce.com/services/data

You’ll need to add a custom SOQL query to the JSON URL in order to choose a specific data type for export. Next, you need to write a script for the authentication and integration of the API. It will typically involve some coding but can help to load bulk data from Salesforce into Google Sheets. We recommend a platform such as Apps Script for the remaining integration process.

Note: The API integration method can be quite complex, so you might consider any other data connector for Salesforce. But it’s easier to use an automation platform, such as Coupler.io for wider capabilities and other destinations such as Microsoft Excel, BigQuery, and Looker Studio. 

Benefits of automating Salesforce data to Google Sheets

Rather than export data using manual and tedious methods, you should streamline Salesforce integration to Google Sheets. Below are some benefits of automating the process.

• Data backup

It makes sense to keep your data in a central location where you can easily access your data and refer back to it anytime. It also saves time since your data will be automatically exported.

• Real-time analytics

With your Salesforce data being readily available in Google Sheets, you can perform analytics as frequently as you’d like. It helps to monitor and discover areas of improvement in your CRM.

• Efficient Reporting

It becomes more efficient to create reports when the data integration process is shorter or completely automated. This way, you’ll focus on creating and structuring insightful reports.

Summary: Salesforce integration with Google Sheets

The workflows above can help to connect Salesforce to Google Sheets. But if you’re keen on using a quick and seamless process for your integration, you should consider a data automation and integration platform such as Coupler.io. There are 70+ data sources supported as well as major destinations to pull data into. If you also need custom integrations or help with business analytics, please feel free to reach out to the Coupler.io data consultancy team.

Back to Blog