Google Apps Script for automatically creating English word tests in Google Forms from a spreadsheet

Former Principal of Hakodate Kita Junior High School, Toshiyuki Okuzaki

The other day, I was talking to an English teacher who teaches first-year junior high school students, and I realized that there are so many new words to learn.

In addition to this, in elementary school, students have already learned 630 words through foreign language activities and English classes, so by the end of their first year of junior high school, they need to have learned about 1300 words.

Elementary school English was about “reading, speaking, and fun activities”, but when students get to junior high school, there is suddenly a lot more “writing”. Even words that were fine to understand the general meaning of when reading, when it comes to writing, I realized that they need to be memorized quite firmly, otherwise they won’t stick.

Since each student has been provided with a personal device, I thought it would be helpful to use it to assist with English word study, so I searched for solutions on the web.

I found that Mr. Yasuhiro Fukuda, a high school teacher in Yamaguchi Prefecture, had implemented a wonderful practice. He created a form that can be automatically graded using a macro-like code, Google App Script (GAS), from a list of words he made on a spreadsheet.

What’s brilliant is that it also automatically creates the wrong choices for multiple-choice questions, which greatly reduces the effort of creating word cheat choices.

Inspired by Mr. Fukuda’s practice, a person named GIGAch on their YouTube channel also explained in detail how to use it. While referring to these solutions, I modified the script to automatically create three types of tests from a spreadsheet: English to Japanese (multiple choice), Japanese to English (multiple choice), and Japanese to English (input from keyboard: some manual corrections are needed on the form).

When I showed it to the English teacher again, this time she mentioned that since the new words differ for each teaching unit, she would like a tool that allows students to study by unit.

Then, she created a ‘word collection for test materials’ consisting of 22 sheets from the textbook her studen’s use (Tokyo Shoseki edition) including unit 1, unit 2,…, Let’s Talk, Let’s Read, the entire range for first-year students, and all of elementary school English in Japan.

As expected of a specialized English teacher, their ideas were different from mine, so I further brushed up the GAS code to allow the creation of tests by selecting the desired unit from a dropdown list.

Thus, a spreadsheet was created that can automatically generate Form tests by specifying the unit or section of the textbook for the words that appear in the first-year junior high school textbook, and selecting English to Japanese or Japanese to English.

Having created a test that is automatically generated by GAS this time, I felt various possibilities. With the current GAS code, it is not possible to create a test where words are directly inputted via the keyboard. However, by cleverly operating it to display hints on the Form once, and having the teacher re-enter them by copy-pasting or manual input, it was found that it can be sufficiently used as an automatically graded test that requires keyboard input.

Using spreadsheets and GAS for word tests can create a very effective tool to support student’s learning. If you are interested in Mr. Fukuda’s site or this article, please try it at your school.