“Game Changer”
***
Hallo ihr alle,
German learning Advent Calendar day number 16. And behind today’s door is a something that might be an absolute game changer, at least for those of you who make their own flashcards or vocabulary lists.
I definitely was like “Wooooaaaaaaah” when I heard about this – in a freaking TikTok video, no less.
Anyways, get ready for an epic
Google Sheets Hack for Translation
Google Sheets is pretty much Google’s version of Microsoft Excel and like Excel, it has lots of cool functions that 99.9% of people don’t know about.
And one of these functions is… drumroll….
Google Sheets can be set to pull in translation directly from Google translate.
And here’s how it works.
Open a new document, one column is for English and one for German.
Put some words or phrases into the side that you want to translate.
Now, go to the first cell in the column of the target language and put in the formula for Google Translate
The formula is:
=GOOGLETRANSLATE([field number of origin] ; “[code for origin languge]” ; “[code for target language]”)
In our example, the origin field is A2, so the second field in column A. Once you’ve entered the full formula, hit “enter” and the translation will appear.
Now you can grab the lower right corner of the field that has the formula and pull that down. That’ll add the same formula to all following fields and adjust the row number accordingly. So you could theoretically easily extend that to like 200 rows and translate 200 words and phrases in seconds.
As you can see above, you can totally set up the formula for new rows that don’t have an original word or phrase yet. As soon as you enter one, it’ll be translated.
If you’re learning two languages at once, or you want to compare some, you can just add a new column and repeat the process with new language codes and boom… you have a new list. 
And I know that some of you also translate back the Google translation just to double check, so you can do that, to, just to see.
And of course you can also modify the translations, if you want to. Remember… they come from Google translate, so there’s no context and it’s not always the best or most intuitive match. In the screenshots, “zu machen” should be just machen and maybe would maybe be better served with vielleicht (though “kann sein” is not wrong).
But the longer phrases and sentences for example were all pretty well translated.
So yeah… I found this a really neat feature and I think it’ll be a real help for some of you at least.
Let me know in the comments if you’ve tried it and if you think it’s helpful. And also if you have any other cool hacks like that for sheets or excel or Apple’s numbers. I’m sure there’s plenty out there that we’re not aware of :)
Have a great day, and I’ll see you tomorrow.
Audio Speed
Dark Mode
SUPER TOLL!!!
Sehr Toll!
It works!! THANKS !!
Wowaweewah, vielen Dank für dieser Tip!
Wowaweewah, vielen Dank für dieser Tip!
Hey Emmanuel and the community members! I feel blessed for having received a 1 year with the help of Emmanuel & Your Daily German community who donated extra so that I could get a free membership!
I love you all and can’t wait to get going on this learning experience!
Welcome :).
Oh and just to make sure… you have everything, right? So you can login and all. Your comment made me not sure if you’re actually still waiting for a password :)
Nah nah, I set my password and I’m all set!
Having so much fun with this!
Happy to hear that :)!
The Google Sheets function appears to be very helpful for of common phrases and dialogs.
I use the Google translator website often, see https://translate.google.com/ This works well for words to full text translations. It also has translate by voice and listen to translation.
Mainly I use a spreadsheet to keep new words I am learning on Seedlang along with genders and plurals.
Example:
das Brot die Brote bread
das Buch die Bücher (pl) book
das Datum die Daten (pl) date
das Ei die Eier egg
das Eigelb die Eigelbe (pl.) egg yolk
das Ende die Enden (pl) end
Frohe Weihnachten
I wonder if there’s a similar hack for Google sheets that’d get you the plural automatically.
OMG this is the coolest thing ever!!!!!!!!!! Thank you!!!
It did not work for me :(
shows that the formula is incorrect
=googletranslate(A2;”EN”;”DE”)
You’re using the wrong quotes. You need: ” ” , bit you’re using ” ”.
Difference doesn’t show on android chrome, but it does on a computer
Just to translate that into something that makes sense: you need “straight double quotes”, not the curly double quotes that the text input box here loves to insert. So don’t cut and paste from the formula in the text, but type it in by hand. ((Put me down as one of the philistines who actually loathes curly/”smart” quotes on the web. They cause so much more trouble than they’re worth, and their automatic implementation so frequently leads to a mess, as per both the subject and the rendering of this comment!))
Uff, awful!!
In the text that I wrote, the “html” view of it, the quotes do show up as straight quotes. I removed the “italic” marking now, in the hopes that this fixes it.
I really don’t know why there are several versions of quotes either. One option would be enough. Someone call the EU… please regulate.
USBC plug and Apple “jailbreak” was just the a warm up. Unified quotes are the endboss.
Test: “” “”
So, I copied the first pair right over from Unicode table, and the second one was put via keyboard and looked the same in the comment. But after posting, they’re both angled.
I’ll practice some love and acceptance now.
Mind Blown!! Danke sehr.
Danke Emmanuel, aber nicht danke! I‘m the pencil and paper generation, quite tech savvy, but this is way too fiddly! Much prefer hard-copy dictionaries and grammars or online ones like yours. Duden, Larousse En-De and Collins EN-DE apps for iPhone are great Andre Klein’s Learn out live books – all online and downloadable – are also excellent, and as a veteran of Duolingo, it also has its good points. I really enjoyed the 3 simple sentences into one complex – many thanks again. Ich wünsche dir Frohe Weihnachten und frohes neues Jahr, wenn es ankommt!
If you use this formula in the second column, you won’t even have to copy down, nor worry about how many rows you are using:
=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(GOOGLETRANSLATE(TEXTJOIN(“. “; 1; A1:A); “de”; “en”); “.”))))
You can simply go on adding English terms to the first column, and automatically the translated term will appear on the second. There won’t be any error marker for empty rows.
I know, I know, it starts to get complicated…
Wow, that’s even better :)!!
A couple of caveats, though:
First, as wrote in other comments, be sure to use straight quotes instead of curly ones:
Second, the formula relies on stringing together all the words separated by periods, so it will break if any of the cells in the first column include a phrase containing a period. The translation will still be completed, but the alignment will be broken.
Yes, a great Christmas present! Diese ist ausgezeichnet!!
Camille
This is really cool, I will definitely try this out.
Super-cool! And, related, Google Docs has a translate tool so you can translate entire documents. But the study-lists using Sheets is brilliant. Vielen Dank!
I think the best thing about this is actually that you can export it as csv and then import it into a flashcard system. Saves a lot of time, imo.
Thank you so much, Manuel. Best Christmas present ever. I look forward to your grammar book. Merry Christmas and Happy New Year!
Have you checked out Lexilize Flashcards? You can make your own categories. You have to make your own entries but I think that’s a useful exercise. There are also prepackaged categories. It has a decent interface for practicing words, or phrases if you added those. Looking forward to another interesting year of blog posts.
OK, das ist die Krönung der wunderbaren Adventskalendertürchen. Das ist so viel besser als irgendein süßer Glibber hinter dem Türchen!!!
Und noch zwei Dinge, die Sie nicht hervorgehoben haben, also, falls es für jemanden, der hier liest, hilfreich ist, hier sind sie:
DANKE, EMANUEL!
Auf dieser Seite kann man so viel mehr als nur Deutsch lernen!!! Aber es IST fantastisch, um Deutsch zu lernen.
Super helpful and easy to use! There is a thing for Excel that uses DeepL, but its a developer add-in and you need to register somewhere (with paid and free options) and I gave up trying to figure it out. So this looks so much easier.