Googlesheets Translation Bundle Laravel Package

Getting Started

• composer require philipp-riddle/symfony-google-sheets-translation-bundle, then check config/bundles.php / vendor/philipp-riddle/.../Resources/config for the service wiring and exact config keys — the README is the fastest first read, followed by the command class itself (that's where the real behaviour lives, especially after 5.3.0).
• Create a Google Cloud service account, enable the Google Sheets API, download the JSON key, and share the spreadsheet with the service account's client_email. Point the bundle at the key via env (GOOGLE_APPLICATION_CREDENTIALS or the bundle's credential option) — never commit it.
• Configure the spreadsheet ID and the sheet/tab → locale mapping (one tab per locale is the common layout, with column A holding the translation key). Then run the bundle's sync command once and diff the resulting translations/messages.<locale>.yaml files in git.
• First real use case: a translator edits a Google Sheet, you run one command, and the YAML/XLIFF files under version control are regenerated. Wire that command into a cron job or CI step before you start thinking about anything fancier.
• Note the package targets Symfony; in a Laravel app you'll usually consume it as a standalone CLI step (or lift the sheet-fetching + file-writing classes) rather than as a native bundle. The generated lang/<locale>/*.php|json convention differs, so plan on a thin mapping layer.

Implementation Patterns

• Sheet as source of truth, repo as artefact. Treat the generated translation files as build output that you do commit (so deploys don't need Google credentials) but never hand-edit. Add a CI check that re-runs the sync and fails on a non-empty git diff — that catches "someone edited YAML directly" and "translator updated the sheet but nobody synced".
• Scheduled sync, not per-request. Cron every 15–60 minutes, or a scheduled job in app/Console/Kernel.php / routes/console.php, followed by a cache clear (php artisan cache:clear / php bin/console cache:clear) so the new catalogue is picked up.
• Separate spreadsheets per environment. Dev/staging pointing at a scratch sheet lets you experiment with column layouts without spamming translators, and keeps CI from hammering the production sheet (and its quotas).
• PR-driven review loop. Let the sync open a PR with the generated diffs; reviewers see exactly which strings changed and for which locales. This is where the bundle pays for itself — translation review becomes a normal code review.
• Lock and order. Wrap the command in flock (or Laravel's Cache::lock) if cron can overlap; concurrent writers to the same files are the one way to get corrupted output.
• Post-5.3.0, the write is cheap. Since 5.3.0 writes files directly instead of shelling out to translation:update, syncing thousands of strings takes well under a second. That means you can stop batching — run per-locale, run in every CI pipeline, run on a webhook — without the old "this takes minutes" tax. Just remember the Google API call is now the only meaningful latency.

Gotchas and Tips

• 5.3.0 changed the writer. Output is no longer produced by Symfony's translation:update, so formatting, key ordering, header comments, and how existing keys are merged or pruned may differ from files generated by 5.2.x. Do one upgrade sync on a branch and eyeball the diff before letting CI commit it — an ordering change alone can produce a 5,000-line noise PR.
• Flags may have moved or vanished. Anything you passed through to translation:update (--force, --dump-messages, --prefix, sort behaviour) either no longer applies or is now the bundle's own option. Re-read --help after upgrading rather than assuming your CI invocation still does what it did.
• Sheets silently coerce types. 1.0 becomes 1, TRUE becomes a boolean, 2024-01-01 may arrive as a serial number, and leading zeros on codes disappear. Quote cells in the sheet (prefix with ') or format columns as plain text for anything that isn't prose.
• Empty/whitespace cells are ambiguous. Decide up front whether a blank cell means "delete this key", "leave the existing value", or "write an empty string" — the bundle has to pick one, and it's rarely the one you want. Verify against a test sheet with a deliberately blanked row.
• Keys are literal. Nested YAML (foo.bar.baz) usually requires either dotted keys in the sheet or explicit nesting support; don't assume foo.bar in column A produces a nested array. Check a small sample first, then standardise the key column.
• Multi-line text and ICU/pluralisation are the usual casualties of a spreadsheet round-trip: newlines get normalised, | plural separators and {count} placeholders survive only if the writer preserves them. Test with a pluralised string and a string containing a colon before trusting the pipeline.
• API quotas and auth failures. 429s on large sheets, 403 when the spreadsheet isn't shared with the service account, 400 for a mismatched range or a tab name containing spaces/apostrophes. Log the raw API error once during setup; it saves an hour of guessing.
• File permissions and atomicity. The command now writes translation files itself rather than delegating, so the PHP user needs write access to translations/ (or your Laravel lang/) and to the directory itself if files are created. On failure mid-write, verify the writer is atomic (temp file + rename) — if not, keep generated files in git so recovery is a git checkout away.
• Cache staleness. Symfony caches compiled catalogues and Laravel caches JSON translations; a successful sync that isn't followed by a cache clear looks exactly like a broken sync. Make the clear part of the same command chain, not a separate manual step.
• Extension points to know: the sheet-fetching service and the file writer are the two seams worth reading in vendor/. If you need XLIFF, PHP arrays, or a different directory layout, subclassing/replacing the writer (rather than post-processing with sed) is the supported path — and post-5.3.0 that writer is now a first-class, self-contained component, which makes it a much friendlier thing to swap.