Make your own Japanese dictionary and quiz app on Google Sheets

A step by step tutorial. Ganbatte!

Photo by @felipepelaquim on Unsplash
  • 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;
}
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.
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.

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.

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:

  • Create a web app with a better interface or add multiple-choice questions to the quiz