Managing and retrieving translated strings in a Google Sheet

In my previous article, I discussed how we at TextPixie implemented a straightforward solution for managing multilingual routes in our Express-based application. In this article, I will dive deeper into the second requirement of our i18n solution: managing and loading translated strings for our web pages.

By the end of this article, you'll have a clear understanding of how we've implemented a simple yet effective i18n solution that allows us to quickly translate our pages and content, making TextPixie accessible to users around the world.

I18N Workflow

At TextPixie, we use a straightforward process to make our website available in multiple languages. It's a three steps process.

1. String Management

We store all our text strings in a Google Sheet. This sheet is like a big table. The first column contains "keys" - unique identifiers for each piece of text. The other columns contain translations for each language we support. For example:

key	en	zh-tw
hello	Hello	你好
welcome	Welcome	歡迎
start_button	Start	開始

This setup makes it easy for our team to add or update translations without needing coding skills.

2. Database Storage

We use a simple script to copy all the strings from our Google Sheet into a SQLite database. This database acts as a quick-access storage for our app. Whenever we update the Google Sheet, we run this script to keep the database in sync with the latest translations.

3. Using Translations

When someone visits our website, we first check extract the language in the URL and store it to req.locale so that the matched route handler is aware of which language it should display. Then for each page, we fetch only the strings needed for that page in the correct language. We use these strings to build the page, ensuring it displays in the right language for the user.

Implementation

1. Database Structure

Our i18n solution uses a SQLite database to store translations. The database schema is defined as follows:

CREATE TABLE IF NOT EXISTS "i18n" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "locale" TEXT NOT NULL,
    "key" TEXT NOT NULL,
    "string" TEXT NOT NULL,
    "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "updated_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_i18n_locale_key on i18n("locale", "key");

This structure allows us to store translations for multiple languages efficiently. The locale and key columns together form a unique index, ensuring that we don't have duplicate entries for the same key in the same language.

2. I18N Class

We've created an I18N class to handle interactions with the database. Here are some key methods:

export class I18N {
    constructor(db) {
        this.db = db;
        this.createDb();
    }

    createDb() {
        try {
            return this.db.exec(schema);
        } catch (error) {
            console.error("Failed to create database schema:", error);
        }
    }

    insertRow(locale, key, string) {
        // ... implementation ...
    }

    getRows(locale, keyPrefixes) {
        // ... implementation ...
    }

    getStringByKey(locale, key) {
        // ... implementation ...
    }

    // ... other methods ...
}

The insertRow method is used to add or update translations in the database, while getRows and getStringByKey are used to retrieve translations.

3. Populating the Database

We use a script to populate our database from the Google Sheet. Here's a simplified version of how we insert data:

function saveGoogleSheet(sheetData) {
    let headers = sheetData.values[0];
    for (const row of sheetData.values.slice(1)) {
        let key = row[0];
        for (let i = 1; i < headers.length; i++) {
            let locale = headers[i];
            i18n.insertRow(locale, key, row[i]);
        }
    }
}

4. Using Translated Strings in Express route handler

In our Express routes, we use the I18N class to fetch translations according to req.locale set by the extract locale middleware in our previous article. Here's an example of how we might use it in a route handler:

app.get('/:locale/welcome', (req, res) => {
    const i18n = new I18N(db);

    const welcomeMessage = i18n.getStringByKey(req.locale, 'welcome_message');
    const pageTitle = i18n.getStringByKey(req.locale, 'page_title');

    res.render('welcome', { 
        welcomeMessage: welcomeMessage.string, 
        pageTitle: pageTitle.string 
    });
});

Conclusion

Implementing this i18n workflow has significantly improved our ability to manage and deliver multilingual content for TextPixie. By leveraging a Google Sheet for content management, a SQLite database for efficient storage and retrieval, and integrating seamlessly with our Express.js application, we've created a system that is both powerful and user-friendly.

By sharing our approach, we hope to provide insights and ideas for other developers facing similar internationalization challenges. This solution has worked well for TextPixie, but as with any implementation, it's important to consider your specific needs and constraints when designing an i18n system for your own projects.

---

To see this i18n solution in action, visit TextPixie AI Translator and try switching between languages.