Creating a Free Translator with Google Sheets: A Step-by-Step Guide

If you've ever wanted a simple, free translator using Google Sheets, you're in the right place. In this post, I'll walk you through creating a multilingual translator using Google Sheets, Google Apps Script, and the Google Translate formula. Let's dive in!

What We’re Building

Today’s project involves setting up a translator in Google Sheets that supports multiple languages. Here's a brief overview of what we’ll accomplish:

• Fetch language codes and names from a free API.
• Utilize Google Apps Script to handle these codes.
• Use the Google Translate formula to translate content in your Google Sheets.

Getting Started

1. Setting Up Your Spreadsheet

1. Create a New Spreadsheet:

   • Open Google Sheets and create a new spreadsheet.
   • Rename the sheet to “Translator”.

2. Fetching Language Codes

1. Using the LibreTranslate API:

   • LibreTranslate offers a free API to get language codes and names. You’ll need this to get a list of all supported languages.
   • The API URL is available for free, and it provides standardized language codes compatible with Google Translate.

2. Implement Google Apps Script:

• Go to Extensions > Apps Script in your Google Sheet.
• You’ll need to write a script to fetch and handle the language data. 

3. Writing the Script

1. Fetching Data:

   • Create a variable for the API URL and fetch the data using HTTP requests.
   • Parse the JSON response to extract language codes and names.

2. Populating the Sheet:

• Insert the language names into the first row and the language codes into the second row of your sheet.
• Format the cells: make text bold and set a background color for better readability.

function myFunction() {

var url = "https://libretranslate.com/languages"

//Make the http get request.

var response = UrlFetchApp.fetch(url)

var languages = JSON.parse(response.getContentText())

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

let codeArray = languages.map(({code})=>code)

let nameArray = languages.map(({name})=>name)

sheet.getRange(1,1,1,nameArray.length).setValues([nameArray])

sheet.getRange(2,1,1,codeArray.length).setValues([codeArray])

}

4. Setting Up Translation

1. Using Google Translate Formula:

   • In your sheet, use the GOOGLETRANSLATE formula to translate text from English to other languages.

2. Automating with a Script:

• Write a script function called onEdit that triggers whenever a cell is edited.
• This function will check for edits in the English column and automatically apply the Google Translate formula to translate text into all supported languages.

function onEdit(e){

//This function calls when someone edits

var sheet = e.source.getActiveSheet();

var range = e.range;

if(range.getColumn()===1 && range.getRow() > 2){

var row = range.getRow();

var value = range.getValue();

if(value === ''){

sheet.getRange(row,1,1,45).setValues([Array(45).fill('')])

}else{

for(var col =2 ; col <=45; col++){

var columnLetter = getColumnLetter(col)

var formula = '=GOOGLETRANSLATE($A'+row+',"en",'+columnLetter+'2)'

sheet.getRange(row,col).setFormula(formula)

}

}

}

}

function getColumnLetter(col){

var letter = ""

while(col > 0) {

var mod = (col -1) % 26;

letter = String.fromCharCode(65+mod)+letter

col = Math.floor((col - mod) / 26);

}

return letter

}

5. Testing and Troubleshooting

1. Testing:

   • Enter text in the English column and see if it translates automatically.
   • Ensure that the translations update correctly and check for any issues with the script, such as incorrect column references or syntax errors.

2. Handling Errors:

   • If the script doesn’t work as expected, verify syntax and column references.
   • Ensure the formula and script are correctly set up to handle different languages and updates.

6. Extending the Functionality

1. Future Enhancements:

   • Consider creating a REST API for your Google Sheet in future projects. This would allow you to filter translations for specific languages and integrate with other applications.

2. Feedback and Community:

   • If you have suggestions or want to learn more, let us know in the comments! We’re here to help and might create a video on building a REST API for this sheet based on your feedback.

Conclusion

And that’s it! You've just created a free translator using Google Sheets. With this setup, you can translate text into multiple languages effortlessly. If you need a copy of the sheet or have any questions, feel free to ask in the comments.

Thank you for reading, and happy translating!

Don't forget to check out the detailed Apps Script tutorial linked below

https://youtu.be/3sWz7_6QKJA