Make your own Japanese dictionary and quiz app on Google Sheets

A step by step tutorial. Ganbatte!

Photo by @felipepelaquim on Unsplash

When learning a new language, have you ever come across a new vocab that you just can’t seem to memorize despite looking it up in the dictionary multiple times? I know I have. Especially when learning Japanese kanji. Here is a simple app that you can make on Google Sheets that might help.

In this tutorial, we are going to build a simple Japanese dictionary app on a spreadsheet that:

• Automatically gets the Japanese reading and English meaning of a word
• Stores a list of your searched words
• Allows you to quiz yourself on the searched words (tutorial coming soon!)

Step 1: Set up your Google Sheet

Sign in to your Google account and create a new Google Sheet. Put a motivating name for your document and open up Script Editor where the magic will happen.

Open the Script Editor under the Tools tab.

You should see an empty project like this.

Step 2: Get the English definition & Japanese reading

To proceed, we need a dictionary. My favorite Japanese online dictionary out there is, no doubt, jisho.org. Not only the interface is easy to use, but the search options are also quite powerful. More importantly, they have a free API that allows anyone to tap into their dictionary, which is what we’re trying to do here.

If you access the API address directly from the browser, you can see something like this. There are tons of information here but the most important ones for us are the Japanese reading and English definitions, which we are going to tap into using following codes.

function getEnglishDef(word) {
// pass the word into the API link
  var url = "http://jisho.org/api/v1/search/words?keyword=" + word;
// get the response from API
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
// parse the response into JSON
  var json = JSON.parse(response.getContentText());
// tap into the English definition
  englishDef = json.data[0].senses[0].english_definitions;
return englishDef;
}

Similarly you can follow the same steps to get the Japanese reading.

function getJapReading(word) {
  var url = "http://jisho.org/api/v1/search/words?keyword=" + word;
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var json = JSON.parse(response.getContentText());
  japReading = json.data[0].japanese[0].reading;
  return japReading;
}

Step 3: Use the functions in Google Sheets

Now that we have the functions ready, let’s head back over to Google Sheets to test them out. Simply call the functions from any cell and you should get the Japanese reading and English definition as return.

=getJapReading(your word or the cell that contains the word)
=getEnglishDef(your word or the cell that contains the word)

Make sure you type the function exactly as how you defined them in Script Editor in Step 2.

You might realize when there are multiple English definitions, they are written into the next line, which is not really cool. We can try to join those definitions into a list separated by comma like def 1, def 2, etc. To do that, simply change the last line of getEnglishDef to the code below.

return englishDef.join(", ");

Here you go. The definitions are now contained in one cell separated by a comma.

Step 4: Apply it to the whole sheet

Once you have the functions working in Google Sheet, it’s time to apply it to the whole sheet so that whenever you type a word in column A you can get the outputs in columns B and C. To do that you can simply drag and fill the formula down to the end of the sheet but the sheet is going to be full of #ERROR as the function is expecting an input in column A. To avoid that, slightly modify the formula to return nothing when the cell in column A is empty.

=if(A2="","",getJapReading(A2))
=if(A2="","",getEnglishDef(A2))

Step 5: Make a quiz function

Let’s say we already have a list of words in your Google Sheet with the kanji or the word we want to know the meaning of on column A. Now here comes the good part. Let’s head back to the Script Editor and write some codes.

function showQuiz(){
  
  // get a random question from the list
  var s = SpreadsheetApp.getActiveSheet();
  var ALastRow=s.getRange("A1:A").getValues().filter(String).length;
  var rand = Math.floor(Math.random()*(ALastRow-2)+2);
  var ques = s.getRange(rand, 1).getValue();
  var correctAns = s.getRange(rand, 2).getValue();
// display the question and prompt for user input 
  var ui = SpreadsheetApp.getUi();
  var input = ui.prompt(ques, ui.ButtonSet.OK_CANCEL);
  var answer = input.getResponseText();
// if OK button is pressed, check whether the answer is correct
  if (input.getSelectedButton() == ui.Button.OK) {
    if (answer === correctAns) {
      var cont = ui.alert("Correct! \n Do you want to continue?", ui.ButtonSet.YES_NO);
    } else { 
      var cont = ui.alert(`Wrong! \n正解は【${correctAns}】\n Do you want to continue?`, ui.ButtonSet.YES_NO);
    }
    // check if user wants to continue or not
    if (cont === ui.Button.YES) {showQuiz();} 
    else {return;}
  }
}

Step 6: Quiz time

Now that we have created a quiz function, how do we actually display the quiz? There are two ways to do it:

1. Assign the function to a button

Add a button using the drawing editor via the Insert > Drawing menu.

Click “Save and close” and the button will be added to our sheet. To assign a script, click the three little dots in the top right of the drawing and select “Assign a script”.

Then type in the name of our quiz function exactly as how we wrote it in Step 5, i.e. showQuiz. Now, when we click the button it will run the function and get our quiz displayed.

Type in the Japanese reading of the word and click OK to submit. If you got it correct you will get the following message.

2. Add the quiz function to a custom menu

I prefer this method over the button because the custom menu is always on the menu bar and you don’t have to add another button every time you have a new function to call.

Here’s how you do it. Add the following code into our Script Editor so whenever our Sheet is open, this function will get triggered and the quiz function will be added to our custom menu.

function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu('Custom Menu')  // create a new custom menu
  .addItem('Quiz', 'showQuiz') // add showQuiz function to the menu
  .addToUi();
}

Conclusion

That’s it! Now you have your own Japanese dictionary app that stores a history of words that you have searched and lets you quiz yourself on those words. Things do not stop here. Here are some steps we can take to bring this app further:

• Add a score to each word/question; once a certain score has been reached, delete or archive the word
• Create a web app with a better interface or add multiple-choice questions to the quiz

Leave a comment and stay tuned for more tutorials like this! Get the full code here:

Also, check out my other articles here:

Build your own technical analysis stock screener using Python

Resources that Helped Me Land a Developer Job Without a CS Degree