Duolingo is the most popular way to learn languages in the world. Best of all, it's 100% free!

https://www.duolingo.com/wchargin

Useful organization tool, especially for reverse courses: Airtable

wchargin
  • 25
  • 24
  • 19
  • 1597

Hello fellow language learners! I've been playing around with a tool that I've found really helpful for learning languages effectively on Duolingo.

<h1>Motivation</h1>

You know how sometimes you get a question wrong, but you're not sure if it's just because Duolingo doesn't have your answer in the system or if you were actually, fundamentally incorrect? Or you'd like to delve into the discussions section, but you're in timed practice mode, and the dialog will spontaneously close while you're in the middle of writing a comment when your time runs out? Or you know you've seen a similar construction before but can't quite remember what it is?

What we need is a system to effectively record our observations about what we learn on Duolingo. (This is especially true for reverse tree learners because we don't really have any "Tips and notes" sections, and the course just isn't made for us. It's certainly true for the forward tree as well, though.)

Physical notebooks are great and reliable, but they're not always the easiest to navigate or search through (especially if your handwriting is less than excellent!). A Google Doc (or Word document) is a bit better because you can search it, but it can grow large and unstructured quite quickly.

A spreadsheet is a step in the right direction: it lets you describe the structure of the data, and you can even ask some interesting questions with filters, like "show me all the verbs that end in -emos."

But there's something really important that spreadsheets lack: the concept of connections.

To be specific, there are two kinds of fundamental connections, or relationships, that I want to be able to express:

  • words that are different forms of each other; and
  • example sentences that contain a given word.

For example, I want to be able to say something like "show me examples of sentences that use the word comemos," or "show me all the forms of comer."

It turns out there's a free web application, superficially much like Google Sheets, that does just this. It's called Airtable.

(Disclaimer: I am not affiliated with Airtable, although I do think it's pretty nifty.)

<h1>A look at Airtable</h1>

If you open up Airtable, you'll see that indeed it looks a lot like a spreadsheet. Here's part of my database of Greek words that I've been creating (control-click any image to see a larger view in a new tab):

Snapshot of an Airtable base, with the columns as described below

The columns—in case you, like me, do not yet speak Greek—are: Greek word, English word, Examples, Lesson, Pronunciation (we'll get back to this later!), Notes, Form of…, and Part of speech.

Looks pretty straightforward. But do you notice something interesting about the Examples column (the third from the left)? It doesn't look like normal text; its contents are surrounded in blue boxes. That's because each of the examples is actually a link to a different record representing an example sentence, which can have information of its own!

So now, for example, all the example sentences using προηγούμενο (previous) are readily available:

Example sentences using *προηγούμενο* in a lightweight overlay

But there's more! Because those are links, I can click any sentence to access a more detailed view:

Detailed view of a sentence

"Πρότασεις table" means "Sentences table," and the fields here say: Greek sentence, English sentence, Words, Pronunciation, and Notes.

So consider the following scenario: I want to write a sentence like "They haven't paid me since last week," but I can't remember whether week should appear in the accusative or genitive case. So I look up since and find that it translates to από, then I find this example and immediately know to use the accusative! Boom.

But wait—there's one more big thing, and that's the pronunciation column! It's the fifth one from the left in the top image, the one that just looks like "http://t…" on every row because the column is so narrow. This is an auto-generated link to make Google Translate pronounce the word!

To be specific, the column is a Formula column that automatically takes the Greek word and appends it to the URL http://translate.google.com/... (see end for actual URL) to form the URL that Google Translate expects. Then I can just double-click the cell to play the audio (once to select the cell, again to click the link).

(Note: for technical reasons, I've only gotten this to work for single words, so most things are fine, but a phrase like buenas días would be pronounced just buenas. If you have an idea, let me know! But, in any case, single words are the vast majority of the content on Duolingo.)

That's all I've got! Hope it's useful.

<h1>Summary of pros and cons</h1>

Pros

  • Really easy to enter data.
  • Really easy to find translations and related information (like examples).
  • Expressive: can store lots of additional information, like parts of speech, conjugation tables, whatever you want.
  • Powerful: has spreadsheet-like features for filtering data ("show me just the verbs") and automatically constructing new data from existing data ("count the number of times this word appears in examples from the Adjectives lesson").
  • Free!

Cons

  • When learning new lessons on Duolingo, or when seeing a new sentence for the first time, you have to add it to your Airtable base.
  • Only free up to 1,200 records (rows) per database. This might affect you if you're trying to build the entire skill tree. One solution would be to split the tree up into multiple databases, but that loses a lot of the benefits (cross-linked examples, searchability) that make it so desirable in the first place. Price for 5,000 records per database is USD 12.00/month. (Thanks, pont!)
<h1>Resources</h1>

The Airtable website is airtable.com.

To create the text-to-speech column:

  1. Add a new column of type Formula.
  2. Find the URL for the speech engine you want to use.
    • For Google translate, this is http://translate.google.com/translate_tts?tl=XX, followed by the ampersand character, then q= (doesn't show up properly in text), replacing XX with the two-letter language code you want to translate into. For example, Spanish is es, German is de, etc. If you don't know the language code, you can find it, e.g., here.
    • For SpanishDict (English and Spanish only), this is http://audio1.spanishdict.com/audio?lang=es, followed by the ampersand character, then text= (doesn't show up properly in text).
  3. Find the name of the column containing the word you want to be spoken. In my example, this is Ελληνική λέξη.
  4. Set the formula to "base_url" & {source_column_name}, keeping the quotes and braces just like that but replacing base_url and source_column_name with their respective values. For example, my formula is "http://translate.google.com/...q=" & {Ελληνική λέξη}.
  5. Click Save and double-click a URL to make sure that it works.
  6. Shrink the column so it doesn't take up so much space.

For more technical reading about relational databases, you might be interested in Khan Academy's SQL course or Wikipedia's introductory article. (Disclaimer: I do work for Khan Academy.)

Exercise: This post contains one Spanish error and one Greek error. Can you find them? :)

2 years ago

19 Comments


https://www.duolingo.com/Jack.Elliot
Jack.Elliot
  • 25
  • 25
  • 25
  • 25
  • 21
  • 13
  • 10
  • 10
  • 9
  • 9
  • 8
  • 5
  • 3
  • 2
  • 2

,

I normally use paper and pencil

However perhaps I shall try it

Thank you for the link

and the concise explanations

2 years ago

https://www.duolingo.com/wchargin
wchargin
  • 25
  • 24
  • 19
  • 1597

Hope you enjoy it! Do whatever works best for you.

2 years ago

https://www.duolingo.com/Jack.Elliot
Jack.Elliot
  • 25
  • 25
  • 25
  • 25
  • 21
  • 13
  • 10
  • 10
  • 9
  • 9
  • 8
  • 5
  • 3
  • 2
  • 2

thanks

2 years ago

https://www.duolingo.com/pont
pont
  • 25
  • 23
  • 18
  • 16
  • 14
  • 14
  • 11
  • 10
  • 7
  • 5
  • 396

Interesting, hadn't come across this service before.

Under "Cons", you might want to add this:

Only free up to 1200 records per database. For 1201-5000 records it's 12 USD per month, and for more than 5000 records it's 24 USD per month.

dqxxmvyvoedn

2 years ago

https://www.duolingo.com/wchargin
wchargin
  • 25
  • 24
  • 19
  • 1597

Oh, rats, hadn't noticed that. Thanks!

2 years ago

https://www.duolingo.com/NukuchAjau

Wow, interesting post! Thanks!

2 years ago

https://www.duolingo.com/UneJamKuqEZi

Thank you for this, I will definitely use it!

Spanish Error: It's not Buenas días, it's buenos días. :)
I don't know the Greek error since I don't speak Greek. :P

EDIT: If I want to use IVONA (link here) how would I make it so it plays a specific voice?

2 years ago

https://www.duolingo.com/wchargin
wchargin
  • 25
  • 24
  • 19
  • 1597

Good job! Buenos días is correct.

I've looked into IVONA, and it's a bit trickier. They offer their service for a fee, unlike Google Translate and SpanishDict. Because of this, they don't let you visit a single URL to play a sound. So I don't see a way to make it work :(

If you already have a subscription to their service, you could probably build a webapp that forwards requests with your API key added (presumably with a secret query parameter so that only you can use it).

Technical details: When you use the demo widget, you POST to /let-it-speak/?setLang=XX (for an ISO-639-1 language code XX) with a form body containing the text to speak. This endpoint returns a URL that serves (with GET) an MP3 for the given text. If you try to change the returned URL to use different text, it returns "Authentication failed." So a single URL probably won't be able to do it (especially a single GET request). You might be able to build a small webapp to simulate the demo widget, but that might or might not work (CORS), it would certainly violate their Terms of Service, and it might be illegal. So I'd advise against it.

2 years ago

https://www.duolingo.com/UneJamKuqEZi

That's all right, I've decided to go with Google Translate. Thank you!

For the multiple word speech, I've noticed that spaces are written as %20 in the link, so maybe you can find a way, so then wherever there are spaces in the link, it would be replaced with %20.

2 years ago

https://www.duolingo.com/wchargin
wchargin
  • 25
  • 24
  • 19
  • 1597

Right again! The %20 is called an escape sequence, and the resulting URL is said to be URL-encoded. Airtable's formula system doesn't have a URL-encoding function (neither does Excel's or Google Sheets's, although I wrote my own for the last one). I emailed their support team about it with a feature request, though, so I'm hopeful! :)

2 years ago

https://www.duolingo.com/UneJamKuqEZi

One last question, on the picture below, how did you make it so when you click the example sentence, more sentences come up?

EDIT: I'm gonna try for the Greek error, is it Πρότασεις, does it not mean sentences?

2 years ago

https://www.duolingo.com/wchargin
wchargin
  • 25
  • 24
  • 19
  • 1597

Nice! It should be Προτάσεις. (It does mean Sentences.)

2 years ago

https://www.duolingo.com/wchargin
wchargin
  • 25
  • 24
  • 19
  • 1597

To show linked records: click the cell containing the examples, then press Shift-Space or click the little "expand" button.

2 years ago

https://www.duolingo.com/mizinamo
mizinamo
  • 20
  • 17
  • 16
  • 14
  • 14
  • 14
  • 13
  • 11
  • 10
  • 10
  • 9
  • 8
  • 8
  • 7
  • 6
  • 6
  • 4
  • 4
  • 4
  • 3
  • 2

But doesn't it have a "replace in strings" function? As in "=replace('foo', 'o', 'a')" which would give "faa"? Then you could use "=replace(url, ' ', '%20')"

2 years ago

https://www.duolingo.com/wchargin
wchargin
  • 25
  • 24
  • 19
  • 1597

Nope, it doesn't. Any of the following would work: URLENCODE, REPLACE, SPLIT (because they have ARRAY_JOIN), or an option to let text formulas be formatted as other column types (the "URL" column type already displays a clickable link even if there are spaces).

2 years ago

https://www.duolingo.com/TheQueenZerelda

Nice.

2 years ago

https://www.duolingo.com/EricaHarley

The Greek word από also means from. My great-uncle Arnold speaks Greek.

2 years ago

https://www.duolingo.com/wchargin
wchargin
  • 25
  • 24
  • 19
  • 1597

Yep, it does! My strategy is to keep a separate entry for each (major) use of the word. So I have a row for "από/from" and a row for "από/since." The advantage of this is that I can then see examples using just one sense of the word, while at the same time it's easy to see them all together.

(The Greek error is really quite subtle…)

2 years ago

https://www.duolingo.com/EricaHarley

Ah, I see. :)

2 years ago