Simplified i18n in Next.js: Sync Translations with Google Sheets

1) Introduction

In this tutorial, we'll simplify the process of managing translations in Next.js by syncing with Google Sheets. First, we'll set up the API, then connect it to our project, and finally implement a local SQLite database to store the translations persistently. By the end, you'll have an easy way of translating content in your application.

Hosting Considerations

If you're deploying to Vercel , note that SQLite is not supported natively. However, there are several alternatives:

• SQLite via Turso

  Recommended

  A remote SQLite solution with a generous free tier, making it an excellent choice for edge deployments.

• Rely on caching

  Optional

  This is an option, but not ideal for development workflows. Since syncing translations depends on cache invalidation, this method could lead to delays in updates, especially as your application grows.

• Third-party database providers

  Any managed database service (e.g., MySQL, MongoDB) that fits your needs.

2) Prerequisites

To follow this tutorial, you'll need a few packages installed in your Next.js project:

• SQLite package — For managing local data storage.
• Google API client package — To connect to the Google Sheets API and fetch translation data.
• Server-only package (recommended) — This helps ensure that sensitive "parts" remain strictly on the server, avoiding unintended exposure to the client.

3) Google Sheet Overview

Below is an example sheet with basic translations for a login screen. The structure is designed to be simple and intuitive:

• "key" contains keys that we'll reference in our application.
• "en", "de"... represent localized strings for each supported language, dynamically rendered based on the user's selected locale.

4) Database Setup

Depending on your project setup, you might need to tweak this step slightly. However, if you're working on a new app that doesn't already use SQLite, this step should be straightforward.

• Create a directory named database inside your application's src/ directory. If you're not using src/, place the database directory in your project's root.
• Inside this directory, create two files:
  • migration.mjs
  • schema.sql

Below are the contents of both files for reference:

import Database from "better-sqlite3";
import { readFileSync } from "fs";

let DB = new Database("src/database/application.db");

// Enable WAL mode for better performance.
DB.pragma("journal_mode = WAL");

// Run migrations.
let migration = readFileSync("src/database/schema.sql", "utf-8");
DB.exec(migration);

Database Schema

Schema is designed to be simple and flexible, enabling efficient storage and retrieval of translations for multiple locales. This table will later be populated dynamically with data retrieved from the Google Sheet.

DROP TABLE IF EXISTS translations;

CREATE TABLE translations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key VARCHAR(200) NOT NULL,
    category VARCHAR(200) NOT NULL,
    locale VARCHAR(2) NOT NULL,
    value TEXT
);

CREATE INDEX idx_multi ON translations(key, category, locale);

Next, we need to ensure that any SQLite files generated by migration.mjs are excluded from git. Simply add this at the end of your .gitignore and you are good to go.

# sqlite db
src/database/application*

Once this is set up, run the migration.mjs file to create your database and initialize it with the schema. Use the following command:

5) Connecting to Google

To access data from Google Sheets, you'll need to authenticate your application using the googleapis package. This requires creating a Google API project, enabling the Google Sheets API, and downloading the private key for authentication.

• Go to the Google Cloud Console:
  

  Navigate to https://console.cloud.google.com/ and log in with your Google account.

• Create a New Project:
  • Click the project dropdown at the top and select "New Project."
  • Provide a name for your project and click "Create."
• Enable the Google Sheets API:
  • Within your project, go to APIs & Services > Library.
  • Search for "Google Sheets API" and click Enable.
• Set Up a Service Account:
  • Go to APIs & Services > Credentials, and click Create Credentials.
  • Select Service Account and follow the prompts to name your account.
  • After creation, go to the service account settings and click Manage Keys.
  • Add a new key in JSON format and download it. This file contains your private key and other authentication details.
• Share Your Sheet:
  • Open the Google Sheet you want to connect.
  • Share it with the service account email (e.g. your-service-account@your-project-id.iam.gserviceaccount.com).
  • Ensure the account has "Viewer" or "Editor" access

Retrieving the Google Sheet ID

To access a specific Google Sheet programmatically, you'll need the sheet's unique ID. This ID is part of the Google Sheets URL and can be easily extracted.

With the credentials obtained, we'll store them securely in the .env.local file. Since Next.js supports multiline values, it's perfectly safe to include the private key directly in this file.

GOOGLE_PRIVATE_KEY_ID="..."
GOOGLE_PROJECT_ID="..."
GOOGLE_CLIENT_EMAIL="..."
GOOGLE_TRANSLATIONS_SHEET_ID="..."
GOOGLE_PRIVATE_KEY="..."

Creating a Google Auth Client

To interact with the Google Sheets API, we need to create an authentication client using the credentials we've stored in the .env file. This client will allow our application to securely access the API and retrieve the necessary data.

import "server-only";

import { google, sheets_v4 } from "googleapis";

export async function googleClient(): Promise<sheets_v4.Sheets> {
  let glAuth = await google.auth.getClient({
    projectId: process.env.GOOGLE_PROJECT_ID,
    credentials: {
      type: "service_account",
      project_id: process.env.GOOGLE_PROJECT_ID,
      private_key_id: process.env.GOOGLE_PRIVATE_KEY_ID,
      private_key: process.env.GOOGLE_PRIVATE_KEY,
      client_email: process.env.GOOGLE_CLIENT_EMAIL,
      universe_domain: "googleapis.com",
    },
    scopes: ["https://www.googleapis.com/auth/spreadsheets"],
  });

  return google.sheets({ version: "v4", auth: glAuth });
}

6) Setting Up The i18n Directory

To manage our application's translations, we need a dedicated i18n (internationalization) directory that organizes all related files. This setup will allow us to maintain clean and reusable code while centralizing translation-related logic.

We'll begin by creating the i18n directory in the src/ and three files in it:

• config.ts — To define configuration, constants, and types.
• core.ts — To house core translation functions
• helpers.ts — To include helper functions for working with translations

Defining the i18n Config

The config.ts file defines key settings and types for our i18n system. Here's the complete code:

export const LOCALES = {
  en: "English",
  de: "German",
  it: "Italian",
  fr: "French",
} as const;

export type Locale = keyof typeof LOCALES;

export type Dictionary = { [key: string]: string };
export type Translator = (key: string, replace?: Dictionary) => string;

export const LOCALE_COOKIE_NAME = "locale";
export const DEFAULT_LOCALE: Locale = "en";

• LOCALES Constant:
  • This object defines the supported locales in your application (e.g., English, German, Italian, French).
  • The as const assertion ensures that the values are immutable and TypeScript treats them as literal types.
• Locale Type:
  • A derived type that represents the keys of the LOCALES object. For example: "en" | "de" | "it" | "fr"
• Dictionary Type:
  • A utility type used to define a collection of key-value pairs, where each key corresponds to a translation string and its associated value is the translated text.
• Translator Type:
  • Represents a function that takes a translation key and an optional Dictionary of replacement values, returning the translated string.
• Constants
  • LOCALE_COOKIE_NAME: The name of the cookie that stores the user's selected locale.
  • DEFAULT_LOCALE: Specifies the fallback locale when no cookie or selection is set, defaulting to English ("en").

Building the Core Translation Functionality

In the core.ts file, we'll implement the core logic for fetching translations from the database, caching them for improved performance, and tagging the cache for easy invalidation during synchronization.

Here's the complete implementation:

import "server-only";

import {
  DEFAULT_LOCALE,
  Dictionary,
  Locale,
  LOCALE_COOKIE_NAME,
  Translator
} from "./config";
import { _t } from "./helpers";
import Database from "better-sqlite3";
import { unstable_cache } from "next/cache";
import { cookies } from "next/headers";

let DB = new Database("src/database/application.db");

// Auto-completion for getTranslations function.
const categories = [
  "login_page",
  "side_nav",
  "account_details",
  "users_list",
] as const;

type Category = (typeof categories)[number];

async function _getTranslations(category: Category, locale: Locale): Promise<Dictionary> {
  // prettier-ignore
  let stmt = DB.prepare("SELECT key, value FROM translations WHERE category = ? AND locale = ?");
  let result = stmt.all(category, locale) as {key: string; value: string}[];

  let dictionary: Dictionary = {};
  result.map((v) => (dictionary[v.key] = v.value));

  return dictionary;
}

/** Retrieve translations for the given category. */
export async function getTranslations(category: Category): Promise<[Translator, Dictionary]> {
  let locale = (cookies().get(LOCALE_COOKIE_NAME)?.value ?? DEFAULT_LOCALE) as Locale;

  let cache = unstable_cache(
     _getTranslations,
    [`translations-${category}-${locale}`],
    {tags: ["translations"], revalidate: Infinity },
  );

  let messages = await cache(category, locale);

  return [
    (key: string, replace?: Dictionary) => _t(key, messages, replace),
     messages,
  ];
}

Breaking It Down

• Database Connection
  • The better-sqlite3 package establishes a connection to our SQLite database located at src/database/application.db
  • This is where all the translations are stored, categorized by category and locale.
• Translation Categories
  • The categories constant lists all available categories (e.g., "login_page", "side_nav").
  • It ensures type safety and auto-completion when retrieving translations.
• Fetching Translations
  • The _getTranslations function queries the database for translations based on the given category and locale.
  • The results are mapped into a Dictionary, a simple object where keys are translation keys, and values are the localized strings.
• Caching with unstable_cache
  • The unstable_cache function is used to cache the results of _getTranslations indefinitely.
  • Cache entries are tagged as "translations" for easy invalidation later (e.g., during sync operations).
  • The cache key-part is dynamically generated using category and locale, ensuring unique entries for each combination.
• Locale Detection
  • The getTranslations function detects the user's locale using a cookie (LOCALE_COOKIE_NAME) or defaults to DEFAULT_LOCALE.
• Returning the Translator
  • getTranslations returns a Translator function along with the dictionary.
  • The Translator function uses _t (from helpers.ts) to fetch translation strings and replace placeholders if needed.

This concludes the core translation functionality. Next, we'll explore the helpers.ts file, which includes the _t function for rendering translation strings with dynamic replacements.

Adding Helper Functions

The helpers.ts file provides utility functions that simplify working with translations. These helpers enhance the readability and reusability of our i18n logic, allowing us to focus on functionality rather than repetitive tasks.

Here's the primary function we'll include:

import type { Dictionary } from "./config";

/** Extract value by key from the dictionary. */
export function _t(key: string, dict: Dictionary, replace?: Dictionary): string {
  if (dict.hasOwnProperty(key)) {
    let result = dict[key];

    if (! replace) {
      return result;
    }

    if (replace) {
      let matches = result.match(/:\w+/g);

      if ( matches === null) {
        console.warn(`Could not find any matching patterns in: ${result}`);
        return result;
      }

      for (const [key, value] of Object.entries(replace)) {
        const regex = new RegExp(`:${key}:`, "g");
         result = result.replace( regex, value.trim());
      }

      return result;
    }
  }

  console.warn(`Translation key missing for: ${key}`);

  return key;
}

The _t function is the core utility for retrieving and processing translations from a dictionary. It looks up the provided key and returns the corresponding translation string. If placeholders (e.g., :name:) are present in the string, the function replaces them dynamically using values from the optional replace object. This makes it easy to inject context-specific data, such as user names or dynamic values, into translation strings.

If the key is missing or no placeholders are matched, _t logs a warning for better debugging and returns the untranslated key or the original string. While this implementation handles basic needs, it's a flexible starting point, allowing you to extend it for more advanced use cases like specialized formatting.

7) Sync Translations (Server Action)

With our database and Google client ready, it's time to bring everything together by syncing translations from the Google Sheet to our local database. This involves fetching the sheet's data, organizing it by categories (corresponding to sheet tabs), and storing translations in the database. To keep things efficient, the database is updated in a transaction, ensuring consistency.

"use server";

import Database from "better-sqlite3";
import { revalidateTag } from "next/cache";
import { googleClient } from "../google-client";

let DB = new Database("src/database/application.db");

type SyncValue = {
  locale: string;
  key: string;
  value: string;
  category: string;
};

export async function syncTranslations(): Promise<void> {
  // Create Google Client
  let client = await googleClient();

  // First we fetch all categories (tabs).
  let sheets = await client.spreadsheets.get({
    spreadsheetId: process.env.GOOGLE_TRANSLATIONS_SHEET_ID,
  });

  // We will use sheet names as categories.
  let categories =
    sheets.data.sheets?.map((v) => String(v?.properties?.title)) ?? [];

  try {
    for (const category of categories) {
      const data = await client.spreadsheets.values.get({
        spreadsheetId: process.env.GOOGLE_TRANSLATIONS_SHEET_ID,
        range: category,
      });

      syncDatabase(data.data.values, category);
    }
  } catch (error) {
    console.log(error);
  }

  // Clear cache;
  revalidateTag("translations");
}

function syncDatabase(data: string[][] | undefined | null, category: string) {
  if (!data) return;

  // We will use the header as our locale config;
  // e.g. ["key", "en", "sr", "de"...];
  const locales = data[0];

  // We are skipping the 0,0 col since it's named "key".
  locales.shift();

  // We are skipping the first row (header).
  data.shift();

  const values: SyncValue[] = [];

  locales.forEach((locale, position) => {
    for (const entry of data) {
      // If we encounter an empty row, we skip it.
      if (entry.length === 0) continue;

      values.push({
        locale,
        key: entry[0],
        value: entry[position + 1],
        category,
      });
    }
  });

  let transaction = DB.transaction((values: SyncValue[]) => {
    // Truncate the existing
    DB.prepare("DELETE FROM translations WHERE category = ?").run(category);

    for (const value of values) {
      DB.prepare("INSERT INTO translations (key,value,category,locale) VALUES (?,?,?,?)")
        .run(
          value.key,
          value.value,
          value.category,
          value.locale
      );
    }
  });

  transaction(values);
}

8) Using Translations: Server Side

To use the translations on the server side, mark your page or API route as an async function. Using the getTranslations function we created earlier, you can load translations for a specific category. This function returns a tuple containing a translator function (t) and the raw dictionary of translations.

import { getTranslations } from "@/i18n/core";

export default async function Page() {
  let [t, translations] = await getTranslations("side_nav");

  return <div>
    <label htmlFor="email" className="...">{t('email_label')}</label>
    <input id="email" type="email" className="..."/>
  </div>;
}

In this example, the t function is used to retrieve the localized string for a specific key, such as email_label. By dynamically fetching translations this way, you can ensure your server-rendered content is localized appropriately before being sent to the client.

9) Using Translations: Client Side

To make translations available in client components, we need two key elements: a TranslationsProvider and a custom hook useTranslations. The provider takes the dictionary and makes it accessible throughout the component tree, while the hook retrieves the translator function (t) for localized strings.

"use client";

import { _t } from "@/i18n/helpers";
import { Dictionary, Translator } from "@/i18n/config";
import { createContext, ReactNode, useContext } from "react";

type TranslationsContextType = {
  readonly values: Dictionary;
};

let TranslationsContext = createContext<TranslationsContextType>({
  values: {},
});

export default function TranslationsProvider({
  values,
  children
}: {
  values: Dictionary;
  children: ReactNode;
}) {
  return <TranslationsContext.Provider value={{ values }}>
    {children}
  </TranslationsContext.Provider>
}

export const useTranslations = (): Translator => {
  let translations = useContext(TranslationsContext).values ;

  return (key: string, replace?: Dictionary) =>
    _t(key, translations, replace);
}

This approach ensures translations are fetched on the server and passed to client components. The TranslationsProvider handles context management, while useTranslations makes it simple to retrieve localized strings wherever needed.

import { getTranslations } from "@/i18n/core";
import TranslationsProvider from "@/providers/translations-provider";

export default async function Page() {
  let [_, messages] = await getTranslations('side_nav');

  return <TranslationsProvider values={messages}>
    <ClientComponent />
  </TranslationsProvider>;
}

// Somewhere in your ClientComponent...
"use client"

import { useTranslations } from "@/providers/translations-provider";

export default function ClientComponent() {
  let t = useTranslations();

  return <div>
    <label htmlFor="email" className="...">{t('email_label')}</label>
    <input id="email" type="email" className="..."/>
  </div>
}

10) Updating User's Locale

Finally, we need a server action to handle locale change. This function will update the user's locale by updating/setting a cookie. Once the cookie is updated/set, the new translations will automatically reflect across the application.

"use server";

import { cookies } from "next/headers";
import { LOCALES, LOCALE_COOKIE_NAME } from "@/i18n/config";

export async function changeLocale(locale: string): Promise<void> {
  if (!locales.hasOwnProperty(locale)) {
    return;
  }

  cookies().set(LOCALE_COOKIE_NAME, locale, {
    // Due to Chrome cookie policy, cookies must expire within 400 days.
    expires: new Date(Date.now() + 400 * 24 * 60 * 60 * 1000),
    httpOnly: true,
    sameSite: "lax",
  });
}