I developed a Google Sheets translation script using GPT-4o-Mini

In my previous newsletter, I told you how I developed my own Emoji Search & Pick Chrome extension, Easymoji, using ChatGPT.

In this edition, I’m going to share with you the full .gs and .html code of a simple script which will enable you to translate any text selection in Google Sheets.

Another Personal Tool Prompted by Necessity

I’m frequently posting statistics about the evolution of the Odoo ecosystem, as part of my content marketing for doo.finance.

I developed custom Python BeautifulSoup scraper to quickly gather those stats from Odoo’s website.

In the output, I get 200+ country names in French but I always write my Linkedin posts in English.

I needed a quick way to translate the country names from French into English, straight in my Google Sheet.

So I turned to my dear friend ChatGPT and gave it the following prompt:

Can you give me a Google script for Google Sheets to call the OpenAI API to translate a column's content into another language. I need a simple UI to select the source and the target language. Use GPT4o-Mini for the model. 

The first suggested UI asked me to type in the source and target languages into traditional input fields and to type in the range of the selection in another input field.

I wanted a faster UX, much more intuitive, so I refined my brief.

Instead of input fields, can we have 2 dropdowns for the source and target language? 
Feat. the 15 main languages. Thanks.  

Then I also needed to tell ChatGPT that I wanted the script to automatically grab the text selection as the text to be translated.

Now, instead of asking for the column range, just pick the user's selection as the content to be translated, it will be more user-friendly

In the first iterations of the code, the translation replaced the source text.

I wanted to improve the UX, inserting the translation in the next adjacent cell.

Let's change the script.
Instead of having the translation override / replace the selection,
let's insert the translation in the next empty adjacent cell.
Also, while we're waiting for the API to return the translation, let's display "Translating..." in the cell.

After a few iterations, I ended up with the perfect script!

I simply have to select the text, click on the EASY TRANSLATOR button added to the Google Sheets menu, pick the source & target language and hit TRANSLATE.
The translation is immediately added to the next adjacent cell.

I can translate multiple cells at once.

The script is using GPT4o-Mini, which is fast and much cheaper than GPT4o.

Below you’ll find the full .gs code and .html code for the Apps Script.

To activate the script at your end, you simply have to open Apps Script via Extensions in the Google Sheets menu.

Then create a .gs file, paste the .gs code, replace “Your OpenAI API Key” by an API Key created on https://platform.openai.com/api-keys, create a TranslateDialog.html file and paste the HTML code (To create a new file, simply press on the + icon next to Files).

This should be the final structure of your project.

Head back to your Google Sheet, select the text you want to translate and press on the EASY TRANSLATOR button added to the main menu.

Don’t hesitate to share this code with friends and colleagues if they also want to add an easy translator to their Google Sheets configuration.

Here’s the full code, in 2 files.

Code.gs

// Global Variables
const OPENAI_API_KEY = 'YOUR OPENAI API KEY';
// replace with your own API Key

// Create a custom menu in Google Sheets
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Easy Translator')
    .addItem('Translate Content', 'showTranslateDialog')
    .addToUi();
}

// Show the language selection dialog
function showTranslateDialog() {
  const html = HtmlService.createHtmlOutputFromFile('TranslateDialog')
    .setWidth(400)
    .setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(html, 'Translate Content');
}

// Function to trigger initial placeholder and then call the translation function
function translateSelectedRange(sourceLang, targetLang) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getActiveRange();
  const values = range.getValues();

  // Iterate through each cell in the selection
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      const text = values[i][j];

      // If there is text to translate
      if (text) {
        // Determine the cell where the translation will be inserted
        const targetCell = sheet.getRange(range.getRow() + i, range.getColumn() + j + 1);

        // Insert "Translating..." into the target cell as a placeholder
        targetCell.setValue("Translating...");
      }
    }
  }

  // Call the function to perform the actual translation with a slight delay
  Utilities.sleep(500); // Sleep for half a second to ensure "Translating..." is visible
  performTranslation(sourceLang, targetLang, range);
}

// Function to perform the actual translation
function performTranslation(sourceLang, targetLang, range) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = range.getValues();

  // Iterate through each cell in the selection again for translation
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      const text = values[i][j];

      // If there is text to translate
      if (text) {
        // Determine the cell where the translation will be inserted
        const targetCell = sheet.getRange(range.getRow() + i, range.getColumn() + j + 1);

        try {
          // Translate the text and update the cell with the result
          const translatedText = callOpenAIAPI(text, sourceLang, targetLang);
          targetCell.setValue(translatedText);
        } catch (error) {
          targetCell.setValue("Error: Translation failed");
          Logger.log('Translation error: ' + error.message);
        }
      }
    }
  }
}

// Call OpenAI API to translate text
function callOpenAIAPI(text, sourceLang, targetLang) {
  const apiUrl = 'https://api.openai.com/v1/chat/completions';
  const payload = {
    model: "gpt-4o-mini",
    messages: [
      { 
        "role": "system", 
        "content": `You are a translator. Translate the following text from ${sourceLang} to ${targetLang}. Respond with only the translated text, no explanations, notes, or other content.` 
      },
      { 
        "role": "user", 
        "content": text 
      }
    ]
  };
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': `Bearer ${OPENAI_API_KEY}`
    },
    payload: JSON.stringify(payload)
  };

  try {
    const response = UrlFetchApp.fetch(apiUrl, options);
    const json = JSON.parse(response.getContentText());
    return json.choices[0].message.content.trim();
  } catch (error) {
    Logger.log('API call error: ' + error.message);
    throw new Error('Translation failed for text: ' + text);
  }
}

TranslateDialog.html

<!DOCTYPE html>
<html lang="en">
<head>
  <base target="_top">
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Translate Content</title>
  <!-- Tailwind CSS via CDN -->
  <script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="bg-gray-50 p-4">
  <div class="max-w-md mx-auto bg-white p-6 rounded-lg shadow-md">
    <p class="mb-4 text-xs text-gray-600">The translation will be inserted in the next adjacent cell.</p>
    <label for="sourceLang" class="block text-sm font-medium text-gray-700 mb-1">Source Language:</label>
    <select id="sourceLang" class="w-full px-3 py-2 mb-4 border rounded-md focus:outline-none focus:ring-2 focus:ring-blue-500">
      <option value="">Select Source Language</option>
      <option value="en">English</option>
      <option value="es">Spanish</option>
      <option value="fr">French</option>
      <option value="de">German</option>
      <option value="it">Italian</option>
      <option value="pt">Portuguese</option>
      <option value="zh">Chinese</option>
      <option value="ja">Japanese</option>
      <option value="ko">Korean</option>
      <option value="ru">Russian</option>
      <option value="ar">Arabic</option>
      <option value="hi">Hindi</option>
      <option value="bn">Bengali</option>
      <option value="pa">Punjabi</option>
      <option value="ur">Urdu</option>
    </select>
    
    <label for="targetLang" class="block text-sm font-medium text-gray-700 mb-1">Target Language:</label>
    <select id="targetLang" class="w-full px-3 py-2 mb-4 border rounded-md focus:outline-none focus:ring-2 focus:ring-blue-500">
      <option value="">Select Target Language</option>
      <option value="en">English</option>
      <option value="es">Spanish</option>
      <option value="fr">French</option>
      <option value="de">German</option>
      <option value="it">Italian</option>
      <option value="pt">Portuguese</option>
      <option value="zh">Chinese</option>
      <option value="ja">Japanese</option>
      <option value="ko">Korean</option>
      <option value="ru">Russian</option>
      <option value="ar">Arabic</option>
      <option value="hi">Hindi</option>
      <option value="bn">Bengali</option>
      <option value="pa">Punjabi</option>
      <option value="ur">Urdu</option>
    </select>
    
    <button id="translateButton" class="w-full bg-blue-600 text-white py-2 px-4 rounded-md hover:bg-blue-700 focus:outline-none focus:ring-2 focus:ring-blue-500">
      Translate
    </button>
  </div>

  <script>
    document.getElementById('translateButton').addEventListener('click', function () {
      const sourceLang = document.getElementById('sourceLang').value;
      const targetLang = document.getElementById('targetLang').value;

      if (!sourceLang || !targetLang) {
        alert('Please select both source and target languages.');
        return;
      }

      // Close the language selection modal
      google.script.host.close();

      // Call the Google Apps Script function to start the translation
      google.script.run
        .withFailureHandler((error) => {
          console.error('Error:', error.message);
          alert('Failed to translate. Check the console for more details.');
        })
        .translateSelectedRange(sourceLang, targetLang);
    });
  </script>
</body>
</html>

I hope you found this information helpful.

If you have any further questions, feel free to reply to this email—I’m here to help.

Stay creative!

Frédérick