How to Translate to Spanish Automatically in Google Sheets
Learn how to automatically translate text from English to Spanish in Google Sheets using a combination of formulas and Apps Script. This tutorial covers both the formula method and a more advanced automation technique.
I can translate automatically from English to Spanish in Google Sheets just by typing a word, month, or year, or year. Right away they're translating to Spanish. How are we doing this? It's with one interesting formula combination. I'm going to walk you through this whole formula and even show you possibly one more way to automate this. So let's start with Google Translate. We're just going to use Google Translate. We're going to take the text of A2, comma, and the source language is going to be EN for English. Our target language is going to be in quotes ES for Spanish. Now, if we just did that right away, you would see we have some errors. We have some value errors down here. And if we delete these words we have more errors. value errors. So how do we hide this until we have some text in the A column?
We are going to wrap it with an if formula , and inside of the if formula we need three arguments . Our final argument will be this Google Translate, but the very first one is going to be is blank, and inside is blank. We just put a three there. Now, if it's blank, That means it's true. We want nothing. So we put two commas here. This middle argument is literally going to be nothing. Now, we can copy and paste this all the way down the entire column and start typing and immediately get this Spanish translation. This is just using a Google Sheets formula , but I want to show you with Apps Script how simple this is.
get a few things from this e. e. range . getRow is going to be our variable row. And if row is greater than one, meaning it's not the, it's not the header row , we want to do something here. But we want to make sure also that we're, we're editing a certain column. So we're going to need the column as well, variable col equals e dot range dot, can you guess it? Column, get column. So we need double ampersand to say and column equals one. We wanna make sure that we're editing in that a column and if we edit something there, we wanna add a formula . Say spreadsheet app dot, get active sheet , whichever sheet we're on. Get range , and our range is going to be in the row that we're editing. Column is going to be the second column.
And we're going to set the value, and that value is going to be this formula . In a single quote, if we do double quotes, the double quotes of en and es are going to be sort of messed up. So we're going to wrap that in single quotes. We're going to save this. Command S. Go back to our sheet. And we're going to delete all of those formulas. And now, we're going to type in month. We're going to type in dog here. And we see that this is copy and pasting the exact same formula . So it's actually the wrong formula because it doesn't change the row. How do we change the row? We're going to change our single quotes. to backticks. And instead of a 2, we're going to do a dollar sign and brackets, row. This is called interpolation and we're basically taking this string and then adding this row to it.
We're doing dollar sign , two curly brackets, and row. Now save it. And this interpolation is going to change the row number every time. So now, let's go and delete these, and put in dog, door again, well not door, let's do month, year, you see now we have no formula , but that formula is added once we put in Text in the a column. So I hope that was really fun for you to learn how to automate with a formula , but also with a pretty simple Google sheets app script . Thanks for watching. If you want to get more out of your Google sheets and you want to do cool stuff, make better sheets, subscribe to better sheets here on YouTube.