Connect Salesforce to Google Sheets Without an Export

Introduction

To connect Salesforce to Google Sheets, most teams still run the same loop: build a report, export it as CSV, import the file, fix the columns and repeat next week. The export is stale the moment it downloads, and every import adds another copy that someone will eventually confuse with the real one.

A Sheets add-on can call the Salesforce API directly, inside the document, with the same Google account that owns the sheet. No file lands on a laptop and no one has to remember the export.

This guide covers that route end to end: the Connected App, the OAuth handshake, the object and field picker, a SOQL query that does not waste API calls, and what to do with the records once they arrive.

What the CSV Route Costs

A CSV export is not free just because the tool is. The cost moves to the person repeating it.

The deeper problem is drift. A downloaded export is a photograph of the CRM, and the sheet built on top of it keeps being trusted after the CRM has moved on. Rows get added to the sheet, a colleague edits a phone number, and now the two systems disagree with no record of which value is correct.

Pulling the data again resets that argument. The staging sheet is disposable by design, which is why the connector clears it on every run instead of appending.

What the Connector Does

The Salesforce connector lives under Extensions > Flookup Data Wrangler > Data Connectors. It does four things:

1. Authenticates with OAuth against production or a sandbox, and stores the tokens in your own Apps Script user properties.
2. Lists the objects your user can query and the fields each object exposes, so the query is built from what Salesforce reports rather than from memory.
3. Assembles a SOQL statement, shows it, and lets you edit it before anything runs.
4. Pages the results and writes them into a hidden staging sheet, where the cleaning tools can reach them.

It is a pull tool, not a sync engine. That distinction matters: there is no background daemon, no webhook listener and no conflict resolution. What you get is a repeatable way to bring a defined slice of Salesforce into a sheet you control.

Before You Start

• A pass that includes connectors: the 1-Year Pass or the Team Pass. The 1-Month Pass and the free tier do not open the sidebar.
• API access in the org: a Developer Edition is fine for testing. Some editions ship without API access and will refuse the call.
• Permission to create a Connected App: usually an admin task. If you cannot create one, ask the admin to create it and hand you the consumer key and secret.
• A sheet to receive the data: any spreadsheet with edit access. The connector creates the staging sheet itself.

Create the Connected App

The Connected App is the contract that lets Flookup ask Salesforce for a token on your behalf. Create it once per org.

1. In Salesforce, open Setup, then App Manager, and click New Connected App.
2. Name it something recognisable, such as Flookup Sheets, and add a contact email.
3. Tick Enable OAuth Settings.
4. Paste the callback URL shown in the Salesforce card: https://script.google.com/macros/d/<SCRIPT ID>/usercallback
5. Add the scopes api and refresh_token (shown as offline access).
6. Save, wait a few minutes for the app to become active, then copy the consumer key and secret from Manage Consumer Details.

The callback URL is tied to the script that runs the add-on. If the add-on is reinstalled into a different Apps Script project, the id changes and the Connected App has to be edited. That single detail causes most of the connection failures in this setup.

The full walkthrough, including the exact screens and the sandbox variant, sits in the Salesforce connector documentation .

Connect and Authorise

With the key and secret in hand, the sidebar takes over.

1. Paste the consumer key and secret into the Salesforce card.
2. Choose Production or Sandbox. A sandbox authorises against test.salesforce.com, so a production key will not work there.
3. Click Save app, then Connect.
4. Authorise Flookup in the tab that opens, then return and click Refresh status.

The access token is refreshed after roughly 100 minutes and again if Salesforce rejects it, so a long session does not need reauthorising. A password change, a revoked app or an admin policy that blocks refresh tokens will end the connection, and the symptom is a 401 rather than a wrong password prompt.

Pick the Objects and Fields

Click Load objects to see what the signed-in user can query. Only queryable objects appear; setup objects that SOQL cannot read are filtered out.

Choosing an object loads its fields from the describe call. Three practical notes:

• Include Id when the sheet will be matched against another export or fed into a dedupe pass. It is the only stable key across pulls.
• Relationships are allowed in the field list, so Owner.Name arrives as the owner's name rather than a user id.
• Address and location fields are hidden because SOQL cannot select them directly. Pull the component parts, such as BillingCity and BillingCountry, when you need them.

Selecting fewer fields is not just tidier. It reduces payload size per page, which means fewer API calls for the same number of records.

Write SOQL That Saves API Calls

The sidebar builds the statement from your choices:

SELECT Id, Name, Email, Owner.Name
FROM Lead
WHERE LastModifiedDate > 2026-01-01T00:00:00Z
ORDER BY LastModifiedDate DESC
LIMIT 5000

Four rules keep it cheap and correct:

• Filter on an indexed field. Id, Name, OwnerId, CreatedDate and LastModifiedDate are indexed. A filter on a long text field scans the object and can time out.
• Pull the delta, not the world. A LastModifiedDate filter turns a 40,000-record object into a few thousand rows that actually changed.
• Do not fight the cap. The LIMIT is clamped to 5,000, which is also the connector's ceiling. For a larger object, split the filter and pull in passes.
• Keep it a single statement. Only one SELECT runs. A semicolon or a comment marker in the filter or sort is refused rather than passed to Salesforce.

Salesforce counts every query against a rolling 24-hour API allowance, and the allowance does not reset at midnight. That is another reason to pull a delta: a daily full refresh of a large object can consume a day's allowance on its own. The current usage is visible in Salesforce Setup.

Pull to Staging

Click Pull to staging. The connector clears the staging sheet, writes a bold header row from the selected fields and then the records.

• Results page through nextRecordsUrl, so a query can return more than the first batch Salesforce sends.
• The internal attributes block is dropped, so every column is a field you chose.
• The pull stops at 5,000 records and reports that it was truncated instead of failing silently.
• The staging sheet is hidden from casual view but is a normal sheet: rename it and the connector will create a fresh one next time.

Clean Before It Becomes a Report

CRM exports are clean in the sense that the fields are typed, not in the sense that the values agree. The same company appears as Acme Ltd, ACME Limited and Acme, and three leads share one inbox.

That is the part the connector hands to the rest of Flookup. On the staging sheet you can run Standard Data Cleaning to find duplicate groups, standardise names, phone numbers and company suffixes, and profile the columns before anything is charted.

Two habits make the result trustworthy:

• Dedupe on a composite key. Email alone misses the lead who used a personal address; email plus company name catches it.
• Record the decision. Keep the Salesforce Id beside every cleaned row so a reviewer can check the source instead of trusting the sheet.

The Salesforce dedupe guide covers the matching strategy in detail, and CRM data cleaning covers the account-level normalisation that makes reports comparable month to month.

Keep the Sheet Current

A pull is a job, and Flookup can run jobs on a schedule. Save the pull as an automation job and the scheduler runs it on your cadence, writes to the staging sheet and resumes if a run hits the execution limit.

Three settings matter for a Salesforce job:

• Cadence: hourly is usually wasted on a CRM. Daily or weekly matches how the data changes.
• Filter: a LastModifiedDate window keeps each run inside the API allowance and makes the run idempotent.
• Alerts: an email when a run fails is the difference between a stale sheet and a known stale sheet.

Where the Connector Stops

If a requirement lands outside that box, the honest answer is usually a different tool, not a longer Apps Script. The connector is for the reporting slice, not for the integration bus.

Frequently Asked Questions