Admins / Analytics / Users

How to Sync Data from Salesforce to Google Sheets

By Matt Hammell

The built-in reporting capabilities inside Salesforce are powerful. So powerful that the interface can be too much for some Salesforce users; many would prefer to do their most detailed analyses in a tool they’re already familiar with, like Google Sheets, which also has the bonus of easy collaboration. 

There’s a problem, though: getting data from Salesforce to Google Sheets can be a colossal pain. There are a few ways to do it, each with varying degrees of difficulty and different pros and cons. Let’s take a closer look at these.

1. Salesforce Data Loader

Data Loader is a Salesforce Developer tool that facilitates the bulk import and export of data. It can insert, update, delete, and export Salesforce data. 

Pros of Salesforce Data Loader

  • Customizable and flexible: You get support for all objects, drag-and-drop field mapping, including custom objects, detailed success, and error log .csv files. 
  • Supports large data sets: Files with up to 5 million records are fair game for Data Loader.

Cons of Salesforce Data Loader

  • Data exports are manual: There’s no automatic sync scheduling option.
  • Doesn’t support extremely large files: While 5 million records per sync is plenty for some organizations, others will run into problems with this limit and may need to explore partner solutions that support larger table sizes. 
  • It’s a developer tool: While Data Loader can technically solve our problem of syncing data between Salesforce and Google Sheets, it wasn’t built with business end-users in mind. 

This data syncing method won’t be ideal for most people who want to analyze Salesforce data in Sheets, so let’s look at some more automated options that will save you tons of time.

2. Google Cloud Connector for Salesforce

The Salesforce Data Connector from Google Cloud is a straightforward Google Sheets extension that can:

  • Refresh Salesforce data in Sheets, manually and at scheduled intervals.
  • Import data based on a custom query or Salesforce Object Query Language (SOQL) statement that you write.
  • Add, update, and delete Salesforce data based on changes made to Sheets.
  • Pull in Salesforce reports.

Once you install the connector, you can find it in the Extensions menu with the rest of your Sheets add-ons. For a detailed walkthrough that explains exactly how to set up and use this add-on, check out this guide from Layer: How To Use The Google Sheets Salesforce Connector.

Pros of Salesforce Data Connector

  • Easy to use: If you’re familiar with Google Sheets add-ons, using the Salesforce Connector will be super easy and intuitive. 
  • No additional cost: Many of the methods on this list require additional SaaS tools and/or subscriptions. This one is free if you have an Enterprise, Performance, Unlimited, or Developer subscription. If you have the Professional tier, you can request that Salesforce enable API access on your account (there will be an additional cost however).

Cons of Salesforce Data Connector

  • Limited sync scheduling options: You can schedule recurring syncs, but with limited scheduling choices: 4, 8, 12, or 24-hour intervals are your only options. Additionally, you can only create one schedule for all reports within a Sheet.
  • Limited customization and flexibility: Ease of use is the biggest benefit, but the simplicity does come at the cost of customization and flexibility. 
  • Data size limitations: If you’re working with large datasets that contain 2000+ rows, you’ll probably encounter errors.

3. Point-to-Point Data to Destination Tools

Point-to-point data to docs solutions, such as Coefficient for Google Sheets, help you import data from third-party SaaS tools (i.e., Salesforce) into Google Sheets. 

While some of the other methods here are compatible with a variety of destinations, tools like Coefficient only work with one data destination: Sheets. They even have an impressive selection of free Salesforce reports and dashboards that you can use directly in Sheets.

To use Coefficient, install their extension from Google Marketplace.

Once you’ve installed the extension, you can access it from the Extensions menu in Sheets.

Pros of Point-to-Point Data to Docs Solutions

  • Incredibly easy to use: Point-to-point tools are intuitive and accessible for pretty much anyone, regardless of technical skill. 
  • Scheduled syncs: If you want fresh data to sync to Sheets on a daily or weekly basis, tools like Coefficient are a great choice.
  • Ability to blend data: While Coefficient only supports one destination, it does support different data sources. This comes in handy when you use a suite of SaaS tools, such as Salesforce, HubSpot, Stripe, and others. If you want to create a blended report that showcases a more complete picture, this is a helpful feature.
READ MORE: Pardot Scoring vs Grading: Pinpoint Hot Prospects

Cons of Point-to-Point Data to Docs Solutions

  • Reduced ability to transform data: Coefficient and similar tools don’t give you many data transformation options, which could be an issue depending on the questions you’re trying to answer from your Salesforce data.
  • Limited data destinations: If you only need to get data from Salesforce to Sheets, something like Coefficient will do the trick. However, it’s always smart to consider long-term usability and scalability. For example, your teams may want to move Salesforce data to Notion or Coda in addition to Sheets. To avoid budget bloat, look for multi-purpose solutions that integrate with your company’s current and future data sources and destinations.

Point-to-point data to destination tools are a solid option if you’re looking for a straightforward means of getting data from Salesforce to Sheets. The pre-built templates that Coefficient offers are a nice touch, too. 

4. Tools That Power Automation

Salesforce and Google Sheets can’t “talk” to each other, which is why it can be so difficult to move data from one tool to another. To open up the lines of communication, you can use an iPaaS (integration platform as a service) tool, which powers the automation of repetitive tasks between disconnected apps, no need to ask developers to build the integration.

Zapier is one of the most popular iPaaS tools around – use it to configure automated workflows (aka Zaps) that run based on specific triggers and actions you define. You can send data from Salesforce to Sheets and from Sheets to Salesforce (and that’s just the very beginning of what’s possible).

You can choose triggers and actions based on the available workflows. Here are some examples from Zapier:

To create a Zap to move data from Salesforce to Google Sheets, you need to:

  • Authenticate your Salesforce and Google Sheets accounts.
  • Select Salesforce as a trigger from Zapier’s list of connected apps.
  • Select the app that will receive data: Google Sheets.
  • Choose which data you want to send from Salesforce to Sheets.

Once you have everything set up, I always recommend testing each Zap to double-check that things run properly.

Pros of iPaaS Tools

  • Extremely easy to use: iPaaS tools like Zapier don’t require technical knowledge or coding skills. You can set up an automation to Sheets in a few minutes.
  • Affordable and versatile: Zapier and other iPaaS tools are quite affordable. Subscription fees are reasonable, especially when considering all potential use cases you can automate.

Cons of iPaaS Tools

  • Limited customization options: You’re stuck with the pre-designed workflows in the app, which limits your ability to work with your Salesforce data. 
  • Coding skills are necessary for more complex operations: Salesforce has some seriously powerful APIs that unlock the ability to create more integrations with Google Sheets. If you’re technical, or have access to technical resources, this isn’t necessarily a problem.
READ MORE: Ultimate Introduction to Salesforce Integration
  • Creates a point-to-point data architecture: The chart below from Census (who we’ll talk more about shortly), demonstrates the difference between a point-to-point versus hub and spoke architecture:

When you add more automations, your web of integrations becomes messier and messier. This may not be a problem for smaller organizations or teams, but can quickly become problematic as your sales and marketing operations become more complicated.

5. AI-Assisted Data Tools to Automatically Sync Data

By this point, we’ve all heard about ChatGPT, but artificial intelligence (AI) has wide-ranging applications. I firmly believe that AI will help people unlock super powers at work and make them more effective than ever before. In fact, AI can help solve the “getting Salesforce data into Google Sheets problem”.

In many companies, data analysts and engineers are responsible for entire data pipelines, which includes getting data into places where people will consume it (often, the destination is a BI tool like Looker or Tableau). 

READ MORE: Tableau vs Tableau CRM: Which Should You Use?

AI-assisted data tools flip the script and allow anyone to dive deeper into data, not just data pros. AirOps is one example of a tool in this category – it’s an AI-assisted platform that helps users easily get data into live documents, including Google Sheets. 

Full disclosure: I’m a co-founder of AirOps. While I do think it’s a great tool for certain use cases, my main goal with this article is to help you find the best way to sync data from Salesforce to Google Sheets. If one of the other methods on this list is a better fit, go with that one!

Let’s look at how an AI-assisted data tool like AirOps helps you move Salesforce data to Sheets:

  • Follow the prompts to connect to an existing data stack or spin up a new one in less than five minutes.
  • Configure a recurring sync by choosing your destination (in this case, Google Sheets, but others are available).
  • Choose a table from your data warehouse, then filter, sort, and remix your data to build your perfect Salesforce data set. 

Note: If you want extra advice on building your data set, use the “Refine with AI” option for additional ideas.

  • Configure your sync by choosing a sync frequency and confirming the sync location. You can also configure notifications, choose whether to lock the Sheet, and more.
  • Now, a Sheet with your HubSpot data will automatically update at your chosen frequency.

Here’s an example of an AirOps-powered Google Sheet that includes data from Salesforce:

Pros of AI-Assisted Data Tools

  • Powerful, flexible, and customizable: Lots of flexibility, especially regarding data transformations. For example, if you want a view showing deal conversion rate, you can create the view on your data warehouse within AirOps and then send it to Sheets. 
  • Ask “plain English” questions using AI: Ask natural language questions like: “which rep closed the most deals in the least amount of time last quarter?”
  • Data team and end-user-friendly: If you have a mix of technical and non-technical people on your sales team, a tool like AirOps (or something with similar functionality) can meet the needs of both user groups.

Cons of AI-Assisted Data Tools

  • Best for genuinely data-driven organizations: You don’t need a modern data stack or even a data warehouse to use AirOps, but there should be an organization- or department-wide consensus on the value of data.
  • Most cost-effective when used with multiple data sources and destinations, or with a deep use case: AirOps supports 250+ data sources, including HubSpot, Salesforce, Zendesk, and other SaaS tools. 

Reverse ETL Tools Like Census and Hightouch

Reverse ETL refers to the process of replicating data from a storage system (like your data warehouse), to third-party tools and applications you use for everyday operations, like Salesforce.

Reverse ETL is a growing sector of the data industry; Census and Hightouch dominate the scene at the moment.

The process for setting up a reverse ETL tool to move data from Salesforce to Google Sheets looks like this:

  • Authenticate the necessary accounts (in this case, Salesforce and Sheets).
  • Select your data source and destination.
  • Define your model using a SQL query or select an existing model in another tool like dbt or Looker.
  • Define how the various fields in your model map to Salesforce data.
  • Set up and schedule the sync.

It’s simple in theory, though a lot of work goes on behind the scenes, including data extraction, ingestion, transformation, and storage.

Pros of Reverse ETL

  • Powerful and customizable: Reverse ETL is a powerful and flexible solution for moving data from Salesforce to Google Sheets. You can customize syncs at near real-time intervals, perform complex transformations to make sure your sales teams get the exact information they need, and more. 
  • Unlock even more value from your Salesforce data: Reverse ETL helps you extract even more value from your data by pushing it back into everyday analysis tools like Sheets.

Cons of Reverse ETL

  • Overkill for simple use cases: Reverse ETL tools are most worthwhile when you have a variety of data sources and destinations or a more complex Salesforce use case. 
  • Requires a data warehouse: If your organization doesn’t have a data warehouse or other centralized data repository, reverse ETL isn’t an option.
  • Setup requires technical support: Most companies that use reverse ETL solutions have an in-house data team or a technically savvy ‘data wrangler’ who knows how to work with data and data tools.
  • Requires properly modeled data: Similar to the above point, you’ll also need someone to model your Salesforce data if you want to use reverse ETL. 

While reverse ETL is powerful and extremely customizable, a lot of legwork goes into making it a viable solution for moving data from Salesforce to Google Sheets. 

Summary

So, which method should you choose to sync Salesforce data into Google Sheets? It depends, but here’s a quick rundown of the options:

  • Use Salesforce Data Loader for a developer-friendly, one-time, manual way to move data to Sheets.
  • Use the Data Connector for Salesforce Sheets extension for a user-friendly solution if the sync scheduling intervals suit your needs.
  • Try a point-to-point data to destination tool, like Coefficient, if you want a user-friendly tool, scheduled syncs, and an environment designed for Sheets.
  • Use Zapier or another iPaaS tool if the available automations suit your needs. 
  • Try an AI-assisted data tool, like AirOps if you need a powerful, customizable solution that can scale as your organization becomes more data-driven.
  • Try a reverse ETL tool like Census or Hightouch for more advanced use cases, especially if you have support from a data team or a technically savvy Salesforce Admin.

The Author

Matt Hammell

Matt Hammel is co-founder and COO at AirOps. He is passionate about helping teams and individuals be more data-driven.

Comments:

    Terry Cole
    April 15, 2023 7:40 pm
    Big fan of the report and SOQL query capability of Xappex G-Connector in this area. Been using them for years and they are part of a lot of our efficient workflows at this point. Scheduled updates from SF to Sheets that frequently drive our public charts (for when SF charting can't do something or when we don't want to use a public Experience Cloud). Upsert from Sheets to SF. Export/Copy/Past/Insert to extend annual data structures with ease. Data cleaning using some spreadsheets functions not easily implemented in flow or reports. And some data sanity checks that just require a lot of human though to clean but then simple update back to SF from the sheet.
    Miguel
    May 02, 2023 2:02 pm
    I normally use Salesforce Connector (fka: Data connector for Salesforce) in Sheets and I like it -and it's the only one I can use according to my company data policies, so I'd better do- but I hate one thing: when you build your query using the addon's import feature step-by-step it generates curious SOQL queries but they work... so far so good, but if you want to raise your game up a notch and write your own SOQL, then the addon applies a hidden 10000 row limit that you can't bypass even if you include a higher limit in you query.... this really bothers me and I've raised it to google a few times but it looks like nobody is really doing maintenance/bug fixing for that addon since google took over it a year ago or so

Leave a Reply