Use Google Sheets as a Translation Database for Your Web App (Apps Script + Next.js)

查看原文 / View Original 编辑 / Edit

Use Google Sheets as a Translation Database for Your Web App (Apps Script + Next.js)

使用 Google Sheets 作为 Web 应用的翻译数据库（Apps Script + Next.js）

Every i18n setup I’ve seen has the same three-way standoff. Developers want type-safe JSON in the repo. Translators want a familiar tool, not a pull request. Product wants to fix a typo without a deploy. So you either pay $50–$500/month for a localization SaaS, or you copy-paste strings between a translator’s spreadsheet and your JSON files until something silently breaks. 我所见过的每一个国际化（i18n）方案都面临着同样的“三方僵局”：开发人员希望仓库里有类型安全的 JSON 文件；译员希望使用熟悉的工具，而不是提交 Pull Request；产品经理希望无需重新部署就能修正错别字。因此，你要么每月支付 50 到 500 美元购买本地化 SaaS 服务，要么就在译员的电子表格和你的 JSON 文件之间反复复制粘贴，直到某处悄无声息地出错。

For projects under ~1,000 keys, there’s a better middle: the spreadsheet is the database. Translators edit a Google Sheet; an Apps Script endpoint serves it as clean locale JSON; your app pulls that at build time. Here’s the whole pattern, with the code. 对于键值（keys）在 1,000 个以内的项目，有一个更好的折中方案：直接将电子表格作为数据库。译员编辑 Google Sheet，通过 Apps Script 端点将其作为整洁的本地化 JSON 提供服务，而你的应用在构建时拉取这些数据。以下是完整的模式及代码实现。

Why a sheet beats a translation service for small projects

为什么对于小型项目，电子表格优于翻译服务

A localization SaaS earns its price at scale — dozens of translators, thousands of keys, screenshots and review workflows. A 300-key marketing site doesn’t have that problem; it has a coordination problem. A Sheet solves coordination for free: translators already know it, it has revision history and suggested edits built in, and product can change a string in ten seconds. You only add the two things a raw sheet lacks — a clean JSON API and a fallback for missing translations. 本地化 SaaS 服务在规模化时才体现其价值——比如数十名译员、数千个键值、截图和审核工作流。一个拥有 300 个键值的营销网站没有这些问题，它面临的是协作问题。Google Sheet 可以免费解决协作问题：译员已经很熟悉它，它内置了修订历史和建议编辑功能，产品经理十秒钟就能修改一个字符串。你只需要补充原始表格所缺失的两点：一个整洁的 JSON API 和针对缺失翻译的兜底机制。

The schema: one tab, one row per key

数据结构：一个标签页，每个键值占一行

A strings tab, with the key in column A and one column per locale: 创建一个 strings 标签页，A 列存放键值，后续每一列对应一种语言：

keyentresfr
hero.titleWelcomeHoş geldinizBienvenidoBienvenue
hero.ctaGet startedBaşlaEmpezarCommencer

Use dot-notation keys (hero.title) so the JSON nests naturally in your i18n library. Keep a tiny meta tab too: B1 = default locale (en), B3 = version (1.0.0). 使用点号命名法（如 hero.title），这样 JSON 就能自然地嵌套在你的 i18n 库中。同时保留一个微小的 meta 标签页：B1 单元格设为默认语言（en），B3 设为版本号（1.0.0）。

The Apps Script endpoint

Apps Script 端点

Deploy this as a Web App (same mechanics as any Apps Script webhook). doGet serves one locale — or all of them — as JSON, and the fallback lives right in the query: an empty cell resolves to the default locale, so a half-translated key never ships blank. 将其部署为 Web 应用（机制与任何 Apps Script webhook 相同）。doGet 函数以 JSON 格式提供单一或全部语言数据，兜底逻辑直接在查询中处理：空单元格会自动解析为默认语言，因此未翻译完全的键值永远不会显示为空白。

// Code.gs
const SHEET_ID = 'your-sheet-id';

function doGet(e) {
  const locale = (e.parameter.locale || 'all').toLowerCase();
  const result = buildLocaleData(locale);
  return ContentService
    .createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

function buildLocaleData(locale) {
  const ss = SpreadsheetApp.openById(SHEET_ID);
  const data = ss.getSheetByName('strings').getDataRange().getValues();
  const headers = data[0]; // ['key','en','tr','es','fr']
  const localeIdx = {};
  headers.forEach((h, i) => { if (i > 0) localeIdx[String(h).toLowerCase()] = i; });
  
  const meta = ss.getSheetByName('meta');
  const defaultLocale = meta.getRange('B1').getValue() || 'en';
  const version = meta.getRange('B3').getValue() || '1.0.0';

  if (locale !== 'all' && !localeIdx[locale]) {
    return { error: 'Unknown locale', available: Object.keys(localeIdx) };
  }

  const targets = locale === 'all' ? Object.keys(localeIdx) : [locale];
  const out = { locale, defaultLocale, version, strings: {} };
  
  if (locale === 'all') targets.forEach(l => out.strings[l] = {});

  for (let row = 1; row < data.length; row++) {
    const key = data[row][0];
    if (!key) continue;
    if (locale === 'all') {
      targets.forEach(l => {
        out.strings[l][key] = data[row][localeIdx[l]] || data[row][localeIdx[defaultLocale]];
      });
    } else {
      out.strings[key] = data[row][localeIdx[locale]] || data[row][localeIdx[defaultLocale]];
    }
  }
  return out;
}

I unit-tested buildLocaleData before shipping — the cases that matter are an empty cell falling back to the default locale, a blank-key row being skipped, and an unknown locale returning an error with the list of available ones instead of a 500. 我在发布前对 buildLocaleData 进行了单元测试——关键测试用例包括：空单元格回退到默认语言、跳过键值为空的行、以及未知语言返回错误列表而非 500 错误。

Frontend integration: sync at build time

前端集成：构建时同步

Don’t hit the endpoint at runtime — pull the JSON once at build and commit it to public/locales. Your app ships static files, and a Sheet outage can never take your site down. 不要在运行时请求该端点——在构建时拉取一次 JSON 并将其提交到 public/locales 目录。你的应用发布的是静态文件，因此即使 Google Sheet 宕机，也不会导致你的网站无法访问。

// scripts/sync-locales.ts
import fs from 'fs';
import path from 'path';

const URL = process.env.LOCALE_SOURCE_URL!;
const LOCALES = ['en', 'tr', 'es', 'fr'];

async function sync() {
  for (const locale of LOCALES) {
    const res = await fetch(`${URL}?locale=${locale}`);
    const data = await res.json();
    const out = path.join(process.cwd(), 'public', 'locales', `${locale}.json`);
    fs.writeFileSync(out, JSON.stringify(data.strings, null, 2));
    console.log(`✓ ${locale}: ${Object.keys(data.strings).length} strings`);
  }
}
sync();

Wire it into prebuild and feed the output to next-intl or i18next like any other JSON. Between deploys, a 5-minute edge cache in front of the endpoint is plenty if you also want a preview environment reading live. 将其接入 prebuild 脚本，并将输出像其他 JSON 一样提供给 next-intl 或 i18next。如果你还需要一个实时读取数据的预览环境，在端点前设置一个 5 分钟的边缘缓存就足够了。

Caching, versioning, and catching missing keys

缓存、版本控制与缺失键值捕获

The version field from the meta tab lets you cache-bust deliberately instead of guessing. And a daily trigger turns “translators forgot three strings” from a production surprise into an email. meta 标签页中的 version 字段让你能够主动进行缓存清理，而无需盲目猜测。此外，设置一个每日触发器，可以将“译员漏掉了三个字符串”这类生产环境的意外，转化为一封提醒邮件。

When to stop using Sheets and pay for Lokalise

何时该放弃 Sheets 并转向 Lokalise

Be honest about the ceiling. Sheets stays comfortable up to roughly 1,000 keys; Apps Script serves this at ~50–100 requests/second, which build-time sync never approaches. The real limit is people: past 5 or so active translators, you’ll want proper roles, screenshots, and review queues — that’s when you export the sheet to CSV and move to Lokalise or Crowdin. Below that, the tooling is the coordination cost, not the value. 要诚实地面对上限。Sheets 在 1,000 个键值以内表现良好；Apps Script 每秒可处理约 50-100 次请求，构建时同步远达不到这个负载。真正的限制在于人员：当活跃译员超过 5 人时，你需要完善的角色权限、截图和审核队列——那时就该将表格导出为 CSV 并迁移到 Lokalise 或 Crowdin 了。在此规模之下，工具本身带来的协作成本远高于其价值。

Pitfalls

避坑指南

  • Don’t fetch at runtime. Build-time sync means a Sheet outage can’t break your live site. Commit the JSON. 不要在运行时获取数据。 构建时同步意味着 Sheet 宕机不会导致你的线上网站崩溃。记得提交 JSON 文件。
  • Put the fallback in the query, not the client. An empty cell resolving to the default locale (as above) is simpler and safer than fallback logic scattered across components. 将兜底逻辑放在查询端，而不是客户端。 空单元格自动解析为默认语言（如上文所述）比在各个组件中分散编写回退逻辑更简单、更安全。
  • Skip blank-key rows. Translators leave empty rows. 跳过键值为空的行。 译员经常会留下空行。
← 返回新闻列表 / Back to news list