Tutorial: Bulk Translation with the DeepL API and Google Sheets

Translating text in bulk, such as product descriptions for an e-shop, can eat a lot of time, especially when you are working with hundreds or thousands of records or need to keep the HTML formatting of the text intact. Luckily there is a way to automate the process using the DeepL API and Google Apps Script. This article walks you through creating a script that automatically translates text from Czech into Slovak, German and English, and shows how you can add further languages as needed.

What is DeepL?

DeepL is an advanced translation tool built on deep neural networks, which allow it to produce very accurate and natural translations. Unlike traditional translation systems, which often translate word by word, DeepL uses deep learning to understand the context and nuance of the original text, and that leads to smoother, more accurate translations.

The advantages of using DeepL for translation

DeepL is known for its high accuracy and its ability to capture fine nuances in a text. Thanks to advanced deep-learning algorithms the translations are produced accurately and fast. Translations made with DeepL often sound more natural than those from other translation tools, which matters especially for professional and formal documents.

What you will need

• A Google account
• Access to Google Sheets
• An API key from DeepL

Step 1: Getting an API key from DeepL

1. Visit the DeepL website.
2. Register or log in to your account. Note that using the API requires a special API account, which is different from an ordinary translation account.
3. Go to the API section and generate a new API key.
4. Store the key somewhere safe; you will need it when setting the script up.

Step 2: Preparing Google Sheets

1. Open Google Sheets and create a new spreadsheet.
2. Put the texts you want translated into the first column (column A).

Step 3: Installing the script

1. In Google Sheets click Extensions > Apps Script.
2. In the script editor delete all the existing code and paste in the following:

/* Change the line below */
const authKey = "YOUR-API-KEY"; // Replace this with your own DeepL API authentication key

/* Change the line below to disable all translations. */
const disableTranslations = false; // Set to true to stop translating.

/* Change the line below to enable automatic re-translation detection. */
const activateAutoDetect = false; // Set to true to enable automatic re-translation detection.

// Add a custom menu to the spreadsheet
function onOpen() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('DeepL Translate Kubicek.ai')
        .addItem('Translate Czech to Slovak (column A -> B)', 'translateCzechToSlovak')
        .addItem('Translate Czech to German (column A -> C)', 'translateCzechToGerman')
        .addItem('Translate Czech to English (column A -> D)', 'translateCzechToEnglish')
        .addToUi();
}

/**
 * Translates texts from Czech into Slovak with the DeepL API and writes the results into column B.
 */
function translateCzechToSlovak() {
    translateText('cs', 'sk', 2); // Sloupec B
}

/**
 * Translates texts from Czech into German with the DeepL API and writes the results into column C.
 */
function translateCzechToGerman() {
    translateText('cs', 'de', 3); // Sloupec C
}

/**
 * Translates texts from Czech into English with the DeepL API and writes the results into column D.
 */
function translateCzechToEnglish() {
    translateText('cs', 'en', 4); // Sloupec D
}

/**
 * Generic function that translates texts from the source language into the target language and writes the results into the specified column.
 */
function translateText(sourceLang, targetLang, targetColumn) {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const range = sheet.getActiveRange();
    const numRows = range.getNumRows();
    const colAValues = sheet.getRange(range.getRow(), 1, numRows, 1).getValues();
    
    const translations = [];
    for (let i = 0; i < colAValues.length; i++) {
        let input = colAValues[i][0];
        if (input === undefined) {
            translations.push([""]);
            continue;
        } else if (typeof input === "number") {
            input = input.toString();
        } else if (typeof input !== "string") {
            translations.push(["Invalid input"]);
            continue;
        }

        // Check the current cell to detect repeated translation
        const cell = sheet.getRange(range.getRow() + i, targetColumn, 1, 1);
        
        if (disableTranslations) {
            Logger.log("Translations are disabled, skipping the translation request");
            translations.push([cell.getDisplayValue()]);
            continue;
        }

        if (activateAutoDetect && cell.getDisplayValue() !== "" && cell.getDisplayValue() !== "Loading...") {
            Logger.log("Repeated translation detected, skipping the translation request");
            translations.push([cell.getDisplayValue()]);
            continue;
        }

        let formData = {
            'source_lang': sourceLang,
            'target_lang': targetLang,
            'text': input
        };

        const response = httpRequestWithRetries_('post', '/v2/translate', formData, input.length);
        checkResponse_(response);
        const responseObject = JSON.parse(response.getContentText());
        translations.push([responseObject.translations[0].text]);
    }

    // Write the translations into the specified column
    const outputRange = sheet.getRange(range.getRow(), targetColumn, numRows, 1);
    outputRange.setValues(translations);
}

/**
 * Helper function that checks the API response.
 */
function checkResponse_(response) {
    const responseCode = response.getResponseCode();
    if (200 <= responseCode && responseCode < 400) return;

    const content = response.getContentText();
    let message = '';
    try {
        const jsonObj = JSON.parse(content);
        if (jsonObj.message !== undefined) {
            message += `, message: ${jsonObj.message}`;
        }
        if (jsonObj.detail !== undefined) {
            message += `, detail: ${jsonObj.detail}`;
        }
    } catch (error) {
        message = ', ' + content;
    }

    switch (responseCode) {
        case 403:
            throw new Error(`Autorizace selhala, zkontrolujte authKey${message}`);
        case 456:
            throw new Error(`The quota for this billing period has been used up${message}`);
        case 400:
            throw new Error(`Bad request${message}`);
        case 429:
            throw new Error(`Too many requests, DeepL servers are under heavy load${message}`);
        default:
            throw new Error(`Unexpected status code: ${responseCode} ${message}, content: ${content}`);
    }
}

/**
 * Helper function that sends HTTP requests and retries failed ones.
 */
function httpRequestWithRetries_(method, relativeUrl, formData = null, charCount = 0) {
    const baseUrl = authKey.endsWith(':fx') ? 'https://api-free.deepl.com' : 'https://api.deepl.com';
    const url = baseUrl + relativeUrl;
    const params = {
        method: method,
        muteHttpExceptions: true,
        headers: {
            'Authorization': 'DeepL-Auth-Key ' + authKey,
        },
    };
    if (formData) params.payload = formData;
    let response = null;
    for (let numRetries = 0; numRetries < 5; numRetries++) {
        const lastRequestTime = Date.now();
        try {
            Logger.log(`Sending an HTTP request to ${url} with ${charCount} characters`);
            response = UrlFetchApp.fetch(url, params);
            const responseCode = response.getResponseCode();
            if (responseCode !== 429 && responseCode < 500) {
                return response;
            }
        } catch (e) {
            throw e;
        }
        Logger.log(`Retrying after ${numRetries} failed requests.`);
        sleepForBackoff(numRetries, lastRequestTime);
    }
    return response;
}

/**
 * Helper function that sleeps after failed requests.
 */
function sleepForBackoff(numRetries, lastRequestTime) {
    const backoff = Math.min(1000 * (1.6 ** numRetries), 60000);
    const jitter = 1 + 0.23 * (2 * Math.random() - 1); // Random value in the range [0.77 1.23]
    const sleepTime = Date.now() - lastRequestTime + backoff * jitter;
    Utilities.sleep(sleepTime);
}

3. Edit the line const authKey = “YOUR-API-KEY”; at the top of the script and paste in the API key you obtained from DeepL.
4. Save the script and close the script editor.
5. Reload Google Sheets.

Step 4: Using the script

1. After you reload Google Sheets, a new DeepL Translate menu appears in the top bar.
2. Select the range of cells in column A that you want translated.
3. Click DeepL Translate and then choose the language you want the text translated into:
   • Translate Czech to Slovak translates the text into Slovak and writes the results into column B.
   • Translate Czech to German translates the text into German and writes the results into column C.
   • Translate Czech to English translates the text into English and writes the results into column D.

How to add more languages to the script

If you want to add further languages, here is how to go about it (and if your source texts are not in Czech, simply change the source language code in the script as well):

1. Open the script editor again.
2. Add new functions for translating into the additional languages. For example, if you want to add French:

/**
 * Translates texts from Czech into French with the DeepL API and writes the results into column E.
 */
function translateCzechToFrench() {
    translateText('cs', 'fr', 5); // Sloupec E
}

3. Add this new function to the menu in the onOpen section:

ui.createMenu('DeepL Translate')
    .addItem('Translate Czech to Slovak', 'translateCzechToSlovak')
    .addItem('Translate Czech to German', 'translateCzechToGerman')
    .addItem('Translate Czech to English', 'translateCzechToEnglish')
    .addItem('Translate Czech to French', 'translateCzechToFrench')
    .addToUi();

4. Save the script and close the editor.
5. Reload Google Sheets; the new Translate Czech to French option should now be available in the menu.

In this way you can add as many languages as DeepL supports and assign each of them its own column in the spreadsheet. The approach lets you translate large amounts of text efficiently, straight inside Google Sheets, which makes your work considerably easier and faster.