Connect Salesforce to Google Sheets: Apex or Connector

Finance wants a spreadsheet of renewals. Engineering wants that spreadsheet to change the moment an Opportunity moves stage. Those are two different requirements, and the usual answer to the first — a Google Sheets add-on — is a bad answer to the second, because the add-on runs on the spreadsheet's schedule and knows nothing about what happens in your org. This guide covers both paths and is specific about where each one stops.

Where a Sheets add-on stops

G-Connector, the Xappex add-on this post originally covered, does the spreadsheet-side job well: install it from Extensions, sign into production or sandbox, pull records in from a report or a SOQL query, edit cells, push the edits back. Two-way, scheduled refreshes, bulk insert and upsert and delete. If your requirement is "an ops analyst needs to see and correct CRM data in a familiar grid", that is the right tool and I would not talk anyone out of it.

Four things it cannot do, and they are the four a developer usually needs:

• It is not triggerable from Salesforce. A record-triggered flow, a platform event subscriber or an Apex trigger has no way to make the add-on run. The clock lives in the sheet.
• It is not deployable. There is no metadata in your repository, no diff in a pull request, no sandbox-to-production promotion. The integration is configuration inside one Google user's spreadsheet.
• It is not testable. You cannot assert the payload in an Apex test.
• The identity is a person. Access follows whoever authorised the add-on, with whatever record access their Salesforce user happens to have.

The other family of tools — Apipheny, Mixed Analytics' API Connector — has the same shape from the other side. Apipheny's own Salesforce tutorial has you create a connected app, mint an access token with a curl command, and paste it into the sheet as a Bearer header; the data then flows Salesforce → Sheets only. That is a reporting pull, not a write path.

If a row has to appear in the sheet as a consequence of something happening in Salesforce, you need an HTTP callout, which means Apex.

The auth chain, built once

There are five pieces and the order matters, because step 3 needs a URL that only exists after step 2.

1. Google Cloud project. Enable the Google Sheets API, configure the OAuth consent screen, then create an OAuth client ID with application type Web application.
2. External credential in Salesforce. Authentication protocol OAuth 2.0, authentication flow type Browser Flow — the authorization code grant. Paste in the Google client ID and secret. Salesforce generates a callback URL on save.
3. Back in Google Cloud, add that callback URL to the client's Authorized redirect URIs. A character off and you get redirect_uri_mismatch at consent time, not at callout time.
4. Named credential with URL https://sheets.googleapis.com, pointing at the external credential, with Generate Authorization Header enabled. Give it a name you can live with in code — GoogleSheetApi below.
5. Permission set granting the running user access to the external credential's principal. Skip this and the callouts fail for everyone except the admin who built them.

Use these endpoint values. Plenty of walkthroughs still carry Google's older pair, and one of them no longer appears in Google's documentation at all:

Setting	Value
Authorize endpoint	https://accounts.google.com/o/oauth2/v2/auth
Token endpoint	https://oauth2.googleapis.com/token
Scope	https://www.googleapis.com/auth/spreadsheets
Extra auth parameters	access_type=offline, prompt=consent

Google's web-server OAuth guide documents https://accounts.google.com/o/oauth2/v2/auth and https://oauth2.googleapis.com/token, and says access_type=offline is what earns you a refresh token. It documents prompt; it never mentions approval_prompt, which is the parameter older posts pass as approval_prompt=force.

Two decisions worth making deliberately.

Named principal, not per-user. For a scheduled or trigger-driven export, authorise once as a service identity — a Google account that is not a person who might leave. I have seen a nightly export die three weeks after an offboarding revoked the refresh token sitting behind it: the callouts came back 401, the Queueable swallowed the exception, and nobody noticed until a quarterly number was wrong.

Take the narrowest scope. The Sheets values.append method accepts drive, drive.file or spreadsheets. Take spreadsheets. Google describes the drive scope as "See, edit, create, and delete all of your Google Drive files" — every file in that account, not the one sheet you care about.

Build this on the current external credential model rather than the "New Legacy" named credential button. Legacy still works, but the current model splits authentication (external credential, with principals and permission-set access) from the endpoint (named credential), and that split is what makes the access grant auditable. You also do not need a Google API key: once the request carries the Authorization header the named credential generates, appending ?key= to the endpoint adds nothing.

The callout

One method, one batch of rows, one request. This appends renewal Opportunities to a tab called Renewals.

public with sharing class SheetsLedgerSync {

    private static final String SHEET_ID =
        SheetsConfig__mdt.getInstance('Renewals').Spreadsheet_Id__c;
    private static final String TARGET_RANGE = 'Renewals!A1';

    public class ValueRange {
        public String range;
        public String majorDimension = 'ROWS';
        public List<List<Object>> values = new List<List<Object>>();
    }

    public static void appendRenewals(Set<Id> opportunityIds) {
        ValueRange payload = new ValueRange();
        payload.range = TARGET_RANGE;

        for (Opportunity opp : [
            SELECT Name, Account.Name, Amount, CloseDate, StageName
            FROM Opportunity
            WHERE Id IN :opportunityIds
            ORDER BY CloseDate
        ]) {
            payload.values.add(new List<Object>{
                opp.Name,
                opp.Account.Name,
                opp.Amount,
                String.valueOf(opp.CloseDate),
                opp.StageName
            });
        }
        if (payload.values.isEmpty()) {
            return;
        }

        HttpRequest req = new HttpRequest();
        req.setEndpoint(
            'callout:GoogleSheetApi/v4/spreadsheets/' + SHEET_ID +
            '/values/' + EncodingUtil.urlEncode(TARGET_RANGE, 'UTF-8') +
            ':append?valueInputOption=USER_ENTERED&insertDataOption=INSERT_ROWS'
        );
        req.setMethod('POST');
        req.setHeader('Content-Type', 'application/json');
        req.setTimeout(60000);
        req.setBody(JSON.serialize(payload));

        HttpResponse res = new Http().send(req);
        if (res.getStatusCode() != 200) {
            throw new CalloutException(
                'Sheets append failed (' + res.getStatusCode() + '): ' + res.getBody()
            );
        }
    }
}

What goes across the wire is a ValueRange:

{
  "range": "Renewals!A1",
  "majorDimension": "ROWS",
  "values": [
    ["Northwind Renewal FY27", "Northwind Traders", 48000, "2026-11-30", "Negotiation"]
  ]
}

The two query parameters carry more meaning than they look like they do. valueInputOption is required: USER_ENTERED makes Sheets parse each cell the way it would if a person had typed it, so 2026-11-30 lands as a date and 48000 as a number, while RAW stores exactly the string you sent. insertDataOption is either OVERWRITE or INSERT_ROWS, and INSERT_ROWS pushes existing content down instead of writing over whatever sits below your table.

Reading back is the same endpoint without the :append suffix, with GET:

HttpRequest req = new HttpRequest();
req.setEndpoint('callout:GoogleSheetApi/v4/spreadsheets/' + SHEET_ID +
    '/values/' + EncodingUtil.urlEncode('Renewals!A2:E', 'UTF-8'));
req.setMethod('GET');
HttpResponse res = new Http().send(req);

Batch by the limits, not by the record

Two sets of limits meet here, and they push in the same direction.

Limit	Value
Callouts per Apex transaction	100
Cumulative callout timeout per transaction	120 seconds
Default timeout per callout	10 seconds
Apex heap, synchronous / asynchronous	6 MB / 12 MB
Sheets read or write requests, per minute per project	300
Sheets read or write requests, per minute per user per project	60

Sixty write requests per minute per user is the one that bites. A trigger firing one callout per record hits it during any bulk load, and Google answers 429. Assemble the rows into a single ValueRange and send one request per batch — the append body has no published row limit, so a normal export runs out of the 12 MB asynchronous heap long before Google objects.

For the 429 itself, Google recommends truncated exponential backoff: wait min(((2^n) + random_number_milliseconds), maximum_backoff), incrementing n on each retry, with a typical maximum backoff of 32 to 64 seconds. Apex gives you no retry primitive, so implement that as a Queueable that re-enqueues itself with an attempt counter rather than as a loop inside one transaction — a loop spends your 120 seconds of cumulative callout time waiting.

Which path to pick

	Apex + named credential	G-Connector	Apipheny / API Connector
Direction	Both, under your control	Two-way	Salesforce → Sheets
Started by	Salesforce events, flows, schedules	A spreadsheet user or add-on schedule	A spreadsheet user or add-on schedule
In version control	Yes	No	No
Unit-testable	Yes, via HttpCalloutMock	No	No
Who can change it	Developer with deploy access	Whoever owns the sheet	Whoever owns the sheet
Setup effort	Hours, once	Minutes	Minutes
Ongoing cost	Platform only	Add-on licence	Add-on; a free tier is advertised

My rule is the trigger. If a person is the trigger, use the add-on. If the system is the trigger, write the Apex. The Apex path costs an afternoon of OAuth plumbing that the add-on does not, and it buys the three things an add-on can never give you: a deployable artefact, a test, and a service identity that outlives an employee.

What to watch for

• Consent screen review. Google's scope reference says sensitive scopes "require review by Google and have a sensitive indicator on the Google Cloud Console's OAuth consent screen configuration page". Check for that indicator against the spreadsheets scope before you promise anyone a go-live date.
• Table detection on append. Google defines the range on values.append as "the A1 notation of a range to search for a logical table of data". A blank row inside your data ends the table, and the next append lands in the gap rather than at the bottom.
• URL-encode the range. Renewals!A1 contains a !. Put it through EncodingUtil.urlEncode as in the code above, or spend an afternoon reading a 400.
• Do not hard-code spreadsheet IDs. They differ per sandbox. Keep them in custom metadata so a deployment does not carry production's sheet into a scratch org.
• Raise the timeout. The default is 10 seconds per callout. A few thousand rows through USER_ENTERED parsing can exceed that, and setTimeout accepts up to the 120-second transaction ceiling.
• Fail loudly on 401. A revoked or expired refresh token dies quietly in an asynchronous context. Write it to a custom object or emit a platform event — something with a report behind it.