Mirroring a production database into Google Sheets with no SDK

The request sounded simple: “we want our data in a Google Sheet, live.”

The context made it interesting. The client runs a gold-trading and savings platform on a legacy PHP app — vendored libraries committed to the repo, no Composer, hosted on cPanel. The normal answer, composer require google/apiclient, did not exist as an option. And the app handles money, so the one non-negotiable was: whatever this sync is, it must never, ever take the app down.

What shipped: a mirror built on the raw Sheets REST API with hand-rolled service-account auth, and — more importantly — a two-layer sync design that I’d now reuse anywhere.

Part one: Google auth from scratch

Strip away the SDK and Google’s service-account authentication is a well-documented sequence you can implement in plain PHP:

1. Create a service account in Google Cloud, download its JSON key, and share the target spreadsheet with the service account’s email address — the step everyone forgets; the account can only see sheets it’s been invited to.

2. Build a JWT by hand. Two base64url-encoded JSON parts — a header declaring RS256, and a claim set with the service-account email as issuer, the OAuth token endpoint as audience, the Sheets scope, and short issued-at/expiry timestamps.

3. Sign it with RS256 using the private key from the JSON key file — in PHP this is openssl_sign() with SHA-256, no library needed.

4. Exchange the JWT for an access token with a single POST to Google’s OAuth token endpoint, then send that token as an Authorization: Bearer header on Sheets API calls. Tokens live about an hour; cache and refresh on expiry.

5. Write with the values endpoints — the spreadsheet values REST endpoints for updating and appending ranges.

That’s the whole “SDK”: maybe a couple hundred lines of PHP, vendored like everything else in the codebase, with zero new dependencies. The point isn’t that SDKs are bad — it’s that they’re not load-bearing. The API is HTTP; when the environment can’t have the SDK, the environment doesn’t have to go without the integration.

Part two: the design that actually matters

Auth is the searchable part of this problem. The important part is the failure model. A naive sync calls the Sheets API inside the write path, and now every transaction depends on Google being reachable from a cPanel box. That’s how a reporting feature becomes an outage.

The design that shipped has two layers with sharply different jobs:

Layer one: push-on-write, strictly best-effort. When the app writes a transaction, it also pushes the update toward the sheet — but the push is wrapped so that any failure (timeout, quota, auth hiccup, Google outage) is caught and logged, never propagated. The transaction succeeds regardless. This layer’s job is freshness, and it is allowed to fail silently, because—

Layer two: reconciliation cron, the accuracy guarantee. On a schedule, a job re-derives what the sheet should contain from the database and repairs any drift — rows the push layer dropped, edits, anything. This layer’s job is correctness, and because it exists, layer one never has to be reliable.

That separation is the reusable idea: split freshness from correctness. Real-time pushes are allowed to be lossy because a boring cron guarantees convergence. Neither layer alone is good enough; together each one’s weakness is covered by the other. I’ve since applied the same shape to integrations that have nothing to do with Sheets.

The domain rule that outranks both layers

One modeling decision in this system deserves its own section: the gold balance is never stored — it is always computed from the sum of transactions.

There is no balance column that gets incremented and decremented. There are transactions, which are immutable facts, and a balance, which is a query over them. A stored balance can drift from its own history through a missed update, a race, a bad migration — and when it drifts, you can’t tell which side is lying. A computed balance cannot disagree with the transactions, because it has no independent existence.

For a savings platform, that property is worth more than the query cost. It also made the Sheets mirror honest for free: the sheet mirrors transactions, and anyone can verify the totals from the rows in front of them.

Legacy landmines, noted for the next visitor

Working in a codebase of this age means documenting what you find even when it’s outside the task. The audit notes recorded, among other things, dead code paths writing invalid status values and query patterns in need of hardening — logged for the client as follow-up work rather than silently absorbed into a sync ticket.

That’s part of the job in legacy systems: leave a map, not just a feature.

Takeaways

• No package manager doesn’t mean no integration — Google’s APIs are reachable with openssl_sign() and an HTTP client.
• Put third-party syncs beside the write path, never in it: best-effort push for freshness, reconciliation cron for correctness.
• Store financial facts, compute financial state.

The client sees live data in a spreadsheet. The app never learned Google exists. Both of those sentences are the success criteria.