How to Achieve Accurate and Natural Translations in Google Sheets Using the DeepL API

2024年12月27日

In a previous article, we introduced a method for bulk translation using Google Translate. While it is easy to use, it can sometimes result in unnatural translations that don’t match the context, requiring extra time for corrections.

For simple translations, Google Translate is sufficient. However, if you want to translate subtle nuances into English, DeepL is a more convenient option.

DeepL offers high translation accuracy and is particularly useful in situations where natural expressions are required, such as for business purposes.

By utilizing the DeepL API, you can enable DeepL translation directly within your spreadsheet.

In this article, we will introduce a method to achieve high-accuracy translations within a spreadsheet using the DeepL API and Google Apps Script.

Differences Between Google Translate and DeepL

Google Translate

Advantages
Easy to use, free, and highly versatile
Disadvantages
In some languages or contexts, the translations may be literal and awkward.

DeepL Translation

Advantages
Context-aware and natural translations, highly regarded for specialized texts and business documents.
Disadvantages
The free plan for API usage has limitations (500,000 characters per month).

Example Output

Able to translate large volumes of text in bulk, saving time.
DeepL’s high-accuracy translations reduce the need for manual reviews.

This script is designed for translating Japanese into English.

Steps

Obtain the DeepL API Key

To execute the GAS, a DeepL API key is required.

For instructions on how to obtain a DeepL API key, please refer to this article.

Preparing the Spreadsheet

Enter the text you want to translate in column A.
Use the first row as a header row.

Open the spreadsheet and enter the text you want to translate in column A.

Please note that the script targets the currently active sheet.

If you want to specify a different sheet name, you will need to adjust the script accordingly.

Executing the Script

STEP

Open the Script Editor

In your Google Spreadsheet, go to Extensions > Apps Script to access the script editor.

STEP

Paste the Script

Since function myFunction() { } is already included by default, delete it and paste the following script instead.

function translateText() {
  // Retrieve the API key from the script properties
  const properties = PropertiesService.getScriptProperties();
  const DEEPL_API_KEY = properties.getProperty('DEEPL_API_KEY');
  
  if (!DEEPL_API_KEY) {
    throw new Error('API key is not set. Please configure DEEPL_API_KEY in the script properties.');
  }
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1); // Data range in column A
  const texts = range.getValues();
  
  const results = texts.map(row => {
    const response = UrlFetchApp.fetch('https://api-free.deepl.com/v2/translate', {
      method: 'post',
      payload: {
        auth_key: DEEPL_API_KEY,
        text: row[0],
        target_lang: 'EN' // Target language code (e.g., 'EN' = English)
      }
    });
    const json = JSON.parse(response.getContentText());
    return [json.translations[0].text];
  });
  
  const outputRange = sheet.getRange(2, 2, results.length, 1); // Output results in column B
  outputRange.setValues(results);
}

Script Explanation

Obtaining the API Key

const properties = PropertiesService.getScriptProperties();
  const DEEPL_API_KEY = properties.getProperty('DEEPL_API_KEY');

Use Google Apps Script’s PropertiesService to retrieve the DEEPL_API_KEY stored in the script properties.
If the key is not set in the script properties, the following if statement will throw an error.

Error Handling for Unset Script Properties

  if (!DEEPL_API_KEY) {
    throw new Error('API key is not set. Please configure DEEPL_API_KEY in the script properties.');
  }

If the DEEPL_API_KEY cannot be retrieved, an error is triggered, and execution is halted.
A message is displayed to clearly indicate that the API key has not been set.

Retrieving the Data Range from the Spreadsheet

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1); // Data range in column A
  const texts = range.getValues();

Retrieve the active spreadsheet and read the data in column A (from the 2nd row to the last row).
Use getValues() to obtain the cell values as an array.
The first row in column A is assumed to be a header row, and rows from the 2nd onward are considered the translation targets.

Requesting the DeepL API

  const results = texts.map(row => {
    const response = UrlFetchApp.fetch('https://api-free.deepl.com/v2/translate', {
      method: 'post',
      payload: {
        auth_key: DEEPL_API_KEY,
        text: row[0],
        target_lang: 'EN' // Target language code (e.g., 'EN' = English)
      }
    });
    const json = JSON.parse(response.getContentText());
    return [json.translations[0].text];
  });

Send the text from each row in column A to the DeepL API and retrieve the translation results.
Use the DeepL API endpoint: https://api-free.deepl.com/v2/translate.
Send a POST request with the required parameters specified in the payload:
auth_key: The API key retrieved from the script properties.
text: The text to be translated.
target_lang: The target language code (English in this case).

Outputting Translation Results to the Spreadsheet

  const results = texts.map(row => {
    const response = UrlFetchApp.fetch('https://api-free.deepl.com/v2/translate', {
      method: 'post',
      payload: {
        auth_key: DEEPL_API_KEY,
        text: row[0],
        target_lang: 'EN' // Target language code (e.g., 'EN' = English)
      }
    });
    const json = JSON.parse(response.getContentText());
    return [json.translations[0].text];
  });

The translation results are output to column B (starting from the 2nd row).
results.length specifies the number of rows for the translation output, and 1 specifies the number of columns.
The translated text is displayed in the cells of column B corresponding to the rows in column A.
Even if there are many texts to translate, the results can be output in bulk.

STEP

Save the Script