Print

Import Salesforce Data into Google Sheets

In this guide, we’ll walk through how to import Salesforce data directly into Google Sheets, using the API Connector add-on for Sheets.

We'll set up a custom OAuth connection and connect to their API. Note: you must have a Salesforce plan that enables API access.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get your Salesforce OAuth Credentials

  1. Log in to Salesforce and click Settings > Setup
    salesforce-setup
  2. Now scroll down the left sidebar and click Apps > App Manager
    salesforce-appsmanager
  3. On this page click New Connected App
    salesforce-newconnectedapp
  4. This will open up an app configuration form. Under Basic Information, fill in the app name and a contact email address.
  5. Under API (Enable OAuth Settings), tick the box to Enable OAuth settings, enter a callback URL, and select the scopes you'd like to have access to. Make sure to include "Perform requests at any time" to avoid expiring tokens immediately.
    salesforce-oauthsettings
  6. Scroll to the bottom and hit Save.
  7. One more step: click Apps > App Manager > Manage > Edit Policies and change the IP Relaxation setting to Relax IP restrictions.
    salesforce-relaxip
  8. Under Refresh Token Policy, change the refresh token policy to something other than "Immediately expire refresh token".
  9. Click Save again.

Part 2: Connect Salesforce to API Connector

  1. Open up Google Sheets and click Extensions > API Connector > Manage Connections
  2. Scroll to the bottom of the sidebar and click Add Custom OAuth
  3. Fill in the Custom OAuth modal as follows:
    • OAuth Grant Type: Authorization Code
    • Name: Custom Salesforce
    • Authorization Base URL: https://login.salesforce.com/services/oauth2/authorize
    • Token URL: https://login.salesforce.com/services/oauth2/token
    • Client IDprovided by Salesforce at App Manager > click app name > Manage Consumer Details
    • Client Secretprovided by Salesforce at App Manager > click app name > Manage Consumer Details
  4. Click Save
    salesforce-customoauth
  5. Click Manage Connections to return to the list of connections
  6. Scroll down until you find your new custom connection. Click Connect
    salesforce-customoauthconnect
  7. You'll be prompted to give access to the new app you created above. Click Allow
    salesforce-allow
  8. You'll be returned to your sheet and can verify that the new connection is now active.

Part 3: Get Salesforce Data in Sheets

To create a request, include your full request URL in the request URL field, and select your custom Salesforce connection from the dropdown OAuth menu.

You can see the full list of available endpoint and parameters in the API documentation. For example, to get a list of leads, run a request like this:

  • Application: Custom
  • Method: GET
  • Request URL: https://your_domain.my.salesforce.com/services/data/v57.0/query/?q=SELECT Id,FirstName,LastName FROM Lead
  • OAuth: Custom Salesforce
salesforce-customresponse
  • To get standard fields or all fields, edit the query so it reads /query/?q=SELECT FIELDS(STANDARD) FROM Lead LIMIT 200 or /query/?q=SELECT FIELDS(ALL) FROM Lead LIMIT 200 or
  • Where it says Lead, substitute in any of the tables listed here, e.g. Account, Opportunity, Task, and so on.

Part 4: API Documentation

Official API documentation: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_list.htm

SOQL Query documentation: https://developer.salesforce.com/docs/atlas.en-us.242.0.api_rest.meta/api_rest/dome_query.htm

2 thoughts on “Import Salesforce Data into Google Sheets”

  1. I am not able to use my Salesforce connection after some time of usage. It is asking me for upgrade. How long is the free trial version of OAuth2 connections ? I am a student, and I am finishing my project.

    Reply
    • When you first install API Connector, you get a free 2 week trial that activates all paid features including OAuth. If you'd like that trial extended, please contact support.
      Update: You can now access OAuth through the free plan as well.

      Reply

Leave a Comment

Jump To