How to Create an Anki Deck from a Google Sheets Spreadsheet

Anki logoI have used Anki for years. It is a very popular and powerful program with an active community. Although one of Anki’s most useful features is the ability to download study material created by others in the community, you will probably want to create your own study materials. You can do so by creating new entries directly in Anki, but if you are like me, you might prefer keeping a spreadsheet that you can then import into Anki.

In this article I will show you not just how to create an Anki deck from a spreadsheet but also offer some useful information and tips about Anki and show you a way to setup a useful spreadsheet format.

Understanding How Anki Works

Anki main screen

While Anki is a great program and is free (except for iOS, which costs $24.99), its biggest problem is usability, especially when trying to create your own study material. To be fair, the way Anki works is actually very logical. The problem is that logic is not intuitive. So, to get more comfortable creating your own content on Anki, it really helps to understand how the program functions.

The most important thing to know is that Anki uses Cards, Decks, and Note Types. A Card is like a flashcard, with the question on one side and the answer on the reverse side. A Deck is a collection of Cards. That is pretty straightforward. The confusing part is how these Cards and Decks are managed. The key to understanding that is the Note Type.

Anki - manage note types menu option

A Note Type is confusing because it is what you use for content management, design/presentation, and functionality. Developers often separate functionality from presentation (or back end from front end) but Anki sort of throws it all together with Note Types.

The first thing to know about a Note Type is that it is where all content is stored, and the core unit of content is a Card. I mentioned that Decks are collections of Cards, but Decks are NOT where Cards are actually stored. Yes, it is confusing, but read on and all will be clear.

One way to think about it is that a Note Type is a master collection of Cards which can be assigned to one or more Decks. The assignment is more like a link between a Deck and a Card than physically moving Cards around in the database. So, you can move Cards from one Deck to another. And, if you decide to delete a Deck, you are only deleting the assignment (links) not the actual Cards.

Why do things this way? Let’s explain with an example.

Currently, I am studying Thai. I can study my vocabulary in at least three ways: with an English word on the front card, with a Thai word written in Thai script on the front or with a transliterated Thai word on the front. But, if you think about it, all three of those options use the exact same content. One study item (a word to review) corresponds to one row (with multiple columns) in my spreadsheet of vocabulary.

Since we can—and probably will—want to reuse content in different formats, it is useful to separate out the underlying content and the way it is presented/studied. That’s where Cards and Fields enter the discussion.

Anki - manage note types screen - Fields and Cards options

Unfortunately, Anki uses the word Cards to refer to both individual items of study (e.g., flashcards) as well as the underlying design of those Cards.

When you create a Note Type you must also create at least one Card template, though you can have as many as you like. My Thai vocabulary Note Type has three for the each study method I just described.

I won’t go into the details of designing your Card template(s) except to say they use HTML and CSS. You can use basic HTML in the Front Template and Back Template and you can use CSS styles in the Styling section.

Anki - design cards screen

When you import items or add them manually, a new Card is automatically created for each Card template you have created. So, for each row in my spreadsheet, three different cards are created. I can then assign each of these different Cards to three different Decks.

So, if I want to review English words and recall their Thai equivalent, I will create and use an “English Front Card” Deck. Likewise for “Thai Front Card” and “Transliterated Front Card” Decks.

Revisiting my Thai study, after I get better with the Thai script I may want to stop using transliterations. Since all Cards belong to the Note Type, I can either delete my “Transliterated Front Card” Deck, which wouldn’t actually delete the individual Cards, or I could edit the Note Type and delete the Card template with the transliterated word on the front, which would delete all those Cards from the Note Type. Similarly, if I later wanted to add a new Card template, I can create one in the Manage Note Types area and it will automatically generate Cards for every existing (unique) item in the Note Type.

What if I also display the transliterated text on the back of the other two Card templates and I want to stop seeing those? No problem. I can just edit those templates and remove the references to the transliterated text and all the relevant individual Cards will be automatically updated.

Like I said, it’s all pretty logical, but not intuitive.

By the way, when you create or edit a Card template, you will use the other key aspect of a Note Type: Fields. These are how a Note Type organizes and maps your study material. If you are using a spreadsheet, these will be your columns.

Anki - note type Fields dialogue box

Considering my Thai study again, I have columns in my spreadsheet for the Thai word, the transliterated word, the English meaning, the type (word, phrase, etc.), the function (noun, verb, etc.), the source (textbook, class, etc.), any useful notes, whether I consider the word a priority or not, a record-keeping field and pronunciation. Note that Anki doesn’t limit you to text entries so you can have Fields for sound files or images. Also note that Anki does support HTML.

Note: You can create multiple Note Types for different types of material you want to study. So, if you are studying French and Spanish, you might like two different Note Types. Because it is essentially a database table, there is some flexibility involved. For example, you could have a Spanish Note Type and a French Note Type, but you could also combine the two in one larger Vocabulary Note Type by just having extra fields. For simplicity, I recommend keeping things separate.

Importing Study Materials from Your Spreadsheet

Now that you understand the basics of Note Types, Cards and Fields, you’re ready to import some study materials from your spreadsheet. This discussion illustrates how to use Google Sheets. If you are using Excel or something like LibreOffice, the process should be similar, but some differences may exist, especially if you are using UTF-8 encoding with Excel (LibreOffice and Google Sheets work fine with UTF-8).

The first step, as discussed above, is to make sure you have a Note Type with Fields that map to your spreadsheet columns and however many Card templates you want. Next, create a Deck to accommodate each Card template.

Now, on the Google Sheets “File” menu, chose “Download as” and then choose “Comma-separated values (.csv, current sheet).”

Anki - Google spreadsheet download to csv option menu

Next, in Anki, Choose the “Import” option in the “File” menu or use the “Import File” button on the bottom menu bar.

Anki - import menu option

Browse to and select the .csv file you created from your Google spreadsheet which will bring up the import options box.

Anki - import dialogue box

At the top you have to specify the Type (1). This is the Note Type we have been talking about and that you should have already created. You also must specify a Deck (2), which again you should have already created. If you have multiple Note Types or Decks, you can click either box to select the correct one (Anki remembers your last import setup).

You should also choose how the rows you import will be treated (3). The three choices are:

  1. Update existing notes when first field matches
  2. Ignore lines where first field matches existing note
  3. Import even if existing note has same first field

Anki - import dialogue box - choose import option

Generally I choose the first option, which is really helpful if you occasionally make corrections or changes in your spreadsheet. However, be careful about what first Field you use as you want it to always have unique content. So, for my Thai example, my Thai word (Thai Script) field should always be unique but my Type, Function, Source and Priority Fields will definitely have duplicate vales so should not be used as a first Field. For example, assume I used the Source Field as the first Field but I only have two sources, Textbook #1 and Textbook #2. In that case, I will end up importing just two Cards because every subsequent row that has either Textbook #1 or Textbook #2 will simply overwrite the last Card that was created with the same Source value.

As a final check, make sure all the fields in the “Field mapping” section (4) match what you expect based on your spreadsheet columns. It happens sometimes that you might make a change to your spreadsheet (adding, deleting or moving columns) and then forget to change the Note Type Fields settings.

Now just click the “Import” button and your Note Type will populate with all the rows from your spreadsheet. You will be shown a window with the results of the import process. If you see any error messages, try to fix those in your spreadsheet and re-import.

Populating Decks

When you import your .csv file, a Card representing each Card template for the Note Type is created for each row. So, if you have 100 spreadsheet rows and three templates, your Note Type should now consist of 300 Cards. Since you can only select one Deck in the import settings, all Cards will initially be assigned to that Deck. If you only have one Card template and you want it to include all Cards, you are done! If, like my Thai example, you have multiple Card templates, you have a bit more work to do.

You should have already created one Deck. If you haven’t done so already, you will also need to create Decks for every other Card template you created for your Note Type. Now you need to reassign the relevant Cards from your Note Type to those Decks. To do so, click “Browse” on the second menu bar.

Anki browse menu option

I find the browse screen to be the most confusing piece of the Anki UI and most in need of improvement. The confusion starts with the left sidebar, which lists lots of things with no explanation of what they represent. There are small icons to help, but no list of what those icons mean (even in the official manual).

Anki browse screen sidebar

In the screenshot above, the items in the red box (1) are not really relevant to what we are trying to do. Instead, focus on the icons in the blue box (2) and green box (3). Icons seen in the blue box are Decks and those in the green box (3) are Note Types.

You can edit or delete Cards in a Deck from the browse screen but keep in mind that those Cards really belong to the relevant Note Type. If you delete one from a Deck you will be deleting it from the Note Type as well.

Right now we really just want to reassign Cards to different Decks so find your relevant Note Type.

Anki - browse notes screen - change deck option

In the screen shot above you will see that I have already done my reassignments. If I hadn’t yet, the Deck column would be the same for each grouping of three rows.

Notice there is a Change Deck option at the top. You can manually select rows and click that option to reassign them to a different Deck, but that is a hassle if you have more than just a handful of items. The good news is there is a search box. The bad news is using it can be difficult once you have multiple Decks, especially if you have some Card templates with the same name. For that reason, I recommend always creating Card templates with unique names.

For now, we want to search for all of the Cards with the template name that we want to change to a different Deck. In my case, my Card templates are called “Priority Vocab – English Front,” “Priority Vocab – Transliteration Front,” and “Priority Vocab – Thai Script Front.”

Anki - browse screen - choose deck to change

If I had selected the “Priority Vocab – Thai Script Front Card” Deck when I imported the spreadsheet and I wanted to reassign all the English front card to the “Priority Vocab – English Front Card” Deck I created, I would use the following in the search box:

card:'Priority Vocab - English Front'

Note that you need the single quotation marks and the Card name must match exactly (partial searches don’t seem to work).

When you see the results, they should all show the same, original (unwanted) Deck (I took the screen shots after reassigning all my Cards so you don’t see that above). Simply select all rows, click the Change Deck option, choose the appropriate Deck and click the Move Cards button. Repeat the process for however many different Card templates and Decks you have in addition to the default ones.

Tips and Tricks

Anki has lot of features that most casual users probably don’t know about and many that I am sure I am also not familiar with. However, I think there are a few things that can really improve your study decks, depending on what kind of language you are studying.

Typing Answers

If you want to practice spelling, there is an easy way to add a text box on the front side of your Cards.

Anki - designing cards to type the answer

Simply use something like this:

{{type:Thai Script}}

and replace “Thai Script” with whatever Field you want to be able to type. When you type your answer and then show the back of the Card you will see what you typed and any mistakes you may have made will be highlighted.

Awesome TTS Audio Pronunciation

Anki - Awesome TTS menu option

When I discussed Fields earlier, I neglected to mention that you can add an extra Field that isn’t mapped to a column in your spreadsheet. One good example for doing this is to add pronunciation audio via the Awesome TTS plugin, which lets you generate the audio from various online sources (e.g., Google Translate) and either store MP3 files with your collection or allow the audio to be generated on-the-fly as you review a Deck.

To install Awesome TTS, under the “Tools” menu choose “Add-ons” and then the “Browse & Install” option. It will ask you to enter a code (each Anki plugin has its own unique code). Enter 301952613 for Awesome TTS.

Anki - Awesome TTS - broswe and install menu option

Anki - Awesome TTS - install Awesome TTS - enter code

In my Thai study case, I added an extra (final) Field called “Pronunciation.” Since I don’t have a pronunciation column in my spreadsheet, when I import it to Anki that field will initially be empty. Do note that this is just the way I chose to do it. You can also use Awesome TTS to append audio files to an existing Field if you prefer.

Simply click on the Awesome TTS menu item and you will have multiple options to configure, the most crucial being the audio service, the voice to use, and the source and destination fields. Again, note that my source and destination fields are different but you can have them be the same (but be sure to select the “Append [sound:xxx] Tag onto Destination Field” option). Click the Generate button and Awesome TTS will do the rest. Note that some services, like Google Translate, limit the number of audio files you can generate per period of time so it could take a while as Awesome TTS will automatically download in batches, waiting the proper number of seconds between each.

Anki - configure Awesome TTS

When you are done, you will see a file reference in your destination Field.

Anki - Awesome TTS example result

HTML and Maintaining Line Breaks

Anki can treat text imported from your spreadsheet as HTML (it is an option which is checked by default when importing). If you don’t use HTML it is probably better to disable this option since HTML special characters (like < and > might cause problems).

If you do wish to use HTML for formatting but also might be using special characters, replace them with their HTML equivalent. For example, < can be replaced with &lt; and > can be replaced with &gt;

One common inconvenience is importing non-HTML content that has line breaks. For example, if you have a Field with sample sentences and you have each on its own line, when you import your spreadsheet to Anki those carriage returns will be removed. The solution I use is to replace those line breaks with their HTML equivalent (<br/>). To do so, use the “Find and replace” option from the “Edit” menu.

Anki - Google spreadsheet - find and replace menu option

On the dialog box that will appear, check the “Search using regular expressions” option. For the “Find” field, use \n and for the “Replace with” field use <br/>\n.

Anki - Google spreadsheet - find and replace dialogue box

If you use my suggested Google Sheet methodology, you can skip this step by using formulas instead (see below).

Setting Up Your Google Spreadsheet

One idea I had recently was to keep a separate worksheet for the content I want to create Anki decks for. Why? Well, I simply have too many words to review and I want to concentrate on a subset of them that I have identified as most important to me.

I could just import my entire list and use tags and filtered Decks to achieve my goal but I thought I would try a different approach. I have all my vocabulary on one worksheet and I have added a column I use to separate words by their priority to me (you could separate your vocabulary by level, source, or whatever suits your needs).

I have a separate worksheet that I use for the subset of vocabulary I want to concentrate on at the moment. Rather than manually copying and pasting, which would be a real hassle, I create the content on this extra worksheet via formulas which check each row of my core vocabulary worksheet to see if it meets my priority criteria. If so, it gets copied, if not, it gets ignored. This has the added benefit that I can easily update the formulas if I want to change the criteria.

When I do create new vocabulary or change my criteria for what I feel like concentrating on, I simply have to export my created-on-the-fly worksheet to a .csv file, delete existing Cards in my Note Type and import the new .csv file. Here is what a typical formula in a cell looks like:

=IF(Vocab!$A3="Y",IF(Vocab!D3<>"",Vocab!D3,""),"")

“Vocab” is the name of my core vocabulary worksheet and column A in that worksheet is my priority field. So, basically, I am just seeing if I have marked a row as a priority and, if so, I copy the content in the corresponding cell. If not, I leave it blank. To avoid random blank rows I first sort the core vocabulary worksheet so all priority entries are on top.

Anki - Google spreadsheet example of conditional formatting

One added benefit of this approach is that I can replace the standard carriage returns with HTML equivalents (<br/>). I could do that in my core worksheet but for readability and other reasons, I prefer not to have that at the end of each line. In my case, I have two columns that could have line breaks in their cells: the English meaning (column E on my core worksheet) and Notes (column I on my core worksheet). For Notes, I use the following formula:

=IF(Vocab!$A2="Y",IF(Vocab!I2<>"",regexreplace(Vocab!I2,"(\n)","<br/>$1"),""),"")

I haven’t tested this with Excel or LibreOffice but it definitely works with Google Sheets.

Exporting a deck: Sharing it with others:

Now that you are becoming an Anki master, you might consider sharing your study materials with the wider Anki community or privately with friends and classmates if you prefer.

Conclusion

That’s it. Probably more complicated than it should ideally be, but not so terrible when you actually know what is going on with Anki. This is what has worked for me, but I don’t claim to be an Anki expert. I have read the manual and tried some things to get to this point but it’s possible there are better approaches I have missed. If you have any tips or insights please share in the comments.

Like this content? Why not share it?
Share on FacebookTweet about this on TwitterGoogle+Share on LinkedInBuffer this pagePin on PinterestShare on Redditshare on TumblrShare on StumbleUpon

2 Comments

  1. Wow, thanks. I have not studied out the details yet, but it was refreshing to have someone lay out all the details. I am a bit of a google spreadsheet nerd myself, so I’m really looking forward to harnessing the power of Anki through spreadsheets.

    An additional benefit here is that you can create study notebooks in Google Translate which export to Google Sheets. Or you can create lists at Readlang.com and export to Anki or as a tab delimited text file. So you can generate lists elsewhere, format in google, then import to Anki. This is the process I’m looking for.

    I’m hoping to dive into this later this week. If no one else thanks you, THANK YOU!!!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.