How to Use Google Forms to Visualize Children’s Efforts – An Example of English Vocabulary Learning

1. Use the Google Forms test not just once, but repeatedly

Depending on the settings, tests created with Google Forms can be taken repeatedly, and all responses and grading results are kept. By utilizing this feature of Google Forms(also referred to as Forms), it’s possible to visualize how middle school students, who initially could answer only a few questions correctly on the English word test, gradually improve with each attempt at the test.

This feature is incredibly useful for repetitive learning exercises like memorizing English words or answering social studies Q&A one by one, and it can visualize the “efforts of children”.

In Japanese junior high school English textbooks, there are approximately 40 new English words in a single learning unit. If these words are divided into small quizzes of about 5 questions each, to make it easier for junior high school students who are not very good at English to engage, you will end up with roughly 8 to 10 groups of small quizzes per learning unit. Junior high school students can attempt these quizzes as many times as they like at their own pace.　　

If you organize and compile the efforts towards these tests from two perspectives: ‘number of attempts’ and ‘the highest score during that period’ for each student, it becomes immediately clear how far the student has progressed in their efforts, how many times they have attempted, and as a result, how many words they are able to answer correctly.

Of course, depending on a student’s proficiency in English, there are those who can achieve perfect scores with less effort and fewer attempts, and those who have to repeat their efforts many times to remember five words.I believe that in many cases, the performance of students who are proficient in English can be observed through their scores in regular classes and periodic tests. In addition to this, by summarizing the students’ efforts towards small quizzes using Forms into a list, we can visualize the “persistent and hardworking” efforts of students that were less noticeable with traditional methods. This can be utilized for appropriate instruction and evaluation by teachers.

“Cultivating children who strive” is extremely important in education. From this perspective, the efforts this time not only improve learners’ English vocabulary skills, but also encourage personal growth and foster a positive learning culture for the entire class. I believe that these efforts will bring positive ripple effects to the students and the class in various aspects.

2 How data is collected and processed to evaluate student efforts

In English learning at Japanese middle schools, students are required to memorize a large number of English words, following on from their elementary school English. Additionally, with the introduction of writing, the difficulty level increases.In a previous article on this blog, I introduced a spreadsheet that creates an auto-scoring descriptive test that can be used for English word learning.　
Using this automatic scoring test, we initially created a 20-question English word test. However, I felt that the number of questions, being 20, proved challenging for students who struggled with English, and I perceived a significant disparity in engagement between those students and those who excelled in English.

As an improvement measure, when I divided the 40 new English words to be learned in that unit into smaller quizzes of 5 questions each, I observed a different response compared to when I had conducted 20-question tests previously. I could sense the efforts made by the students who had difficulty with English.

Tests with a large number of questions tend to be hard to tackle and often avoided by students who are not proficient in English. However, the approach of stacking several compact tests with about five questions each has lowered the hurdle for these students, encouraged more students to engage, and has proven to be more effective than when we used a 20-question test.
As I experimented with English word tests automatically generated from spreadsheets, I found that it was more effective to study in learning units by stacking multiple forms with about five narrowed down questions. So, I decided to change to that format.

3 Repeatedly Use Google Forms Tests

In the initiative of learning English words through a group of small tests consisting of five questions, I aimed to prioritize the students practicing repeatedly and learning the words, rather than how many points they got on the test.
Each time a student attempts the Forms, a new record of the attempt is added to the spreadsheet associated with that Forms. By aggregating and organizing these records based on each student’s email address, I was able to determine how far the student had worked on this group of English word Forms, how many times they had attempted it, and how many points they were able to score.

However, with a small number of questions, five at a time, it is necessary to string together 8 to 10 small tests to cover the entire learning unit. Manually picking up the results of each Forms from the spreadsheet and organizing them in the order of the students’ names is a big task for the teachers in charge.
So, I decided to create a script that gathers the question groups for the word tests into a single folder in the shared drive, and using the folderID, pulls out the test results all at once. With this script, teachers can easily access the students’ performance on the Forms whenever they need it.

4 Two Important Data Processing Steps When Creating a List That Shows Traces of Effort

In the early stages of this initiative, I was performing the aggregation work by combining simple processes built with manual work and Excel macros. However, as the operation became more established, it was necessary to further automate the manual parts. Therefore, by utilizing Google for Education, which our school has adopted, and operating a process that integrates not only Forms but also Google Drive and spreadsheets, I found that the results of the Forms efforts could be aggregated much more easily than before.

In fact, the following two processes are key when automating this operation.

• Since multiple small test Forms subdivided into five questions each are created, it is necessary to extract the state of students’ efforts from each Forms in order to understand the entire learning unit.
• To get an overview of the entire unit, it is necessary to collect the individual student’s efforts for each extracted Google Forms, and summarize how many times that student has attempted each Forms and how well they have been able to answer the questions correctly.

In general, if you can create a macro that performs these two tasks, the usability will improve dramatically. And by using a mechanism called GAS built into Google’s spreadsheets, I was able to incorporate a script that realizes these two processes with the help of ChatGPT.

5 Procedure of Actual Operation

So, the system for “visualizing students’ efforts” in English word learning has been completed. When using this initiative in a school setting, follow the steps below.

• The English teacher creates the words to be learned in the new learning unit as a group of descriptive Forms questions, five questions each.
• At this time, in order to arrange the results in the order of the group of Forms tests created according to the appearance order of the learning unit, the Forms names are named in an order that can be understood, such as Unit5_01, Unit5_02, etc. By doing this, even when collecting results using the script, you can organize the results of the efforts in the order intended by the teacher.
• Collect all the groups of Forms questions and place them in a folder created on the shared drive. The students will be notified of the short URL that links to the Forms as documents or tasks in the Google Classroom. This way, students can access these Forms from the Google Classroom.
• In order to extract the results of the efforts in the Forms, you need to create a spreadsheet to output the Forms results. With Google for Education, each time a student works on a Forms, the results are automatically added to the created spreadsheet. By using this mechanism, you can obtain the efforts for each Forms by accessing this spreadsheet.
• When studying words, students use the link to the Forms in the Google Classroom to work on the group of word test questions. This Forms is set to be able to work on as many times as you want, so I tell them to work hard enough to remember by repeating.
• When you want to understand students’ efforts, run a script using GAS on the folder on the shared drive where the tests are saved, and salvage (pick up) students’ efforts from each spreadsheet associated with the Forms.
• The efforts of the students on the Forms that were gathered from the folder are now organized in the order of the students’ names, and the results are compiled into a table so as to understand things like, ‘Student A attempted the first test five times and got full marks, and after three attempts on the second test can now get about 80% correct…’.
• Depending on the size of the school or the number of students, a single Forms test may have hundreds of attempts recorded. However, this process of collecting efforts that are dispersed across about ten Forms is automated using GAS, so all you need to do is pass the collected efforts to the script that collates them by name as each student’s efforts, and you will be able to create a list.
• English teachers check the list of efforts and, depending on the need, use it in daily instruction such as giving guidance to the whole class, giving words of encouragement to hard-working students, or helping with individual instruction for students with fewer efforts.

Since we actually started conducting vocabulary tests in this format, we have been able to clearly see the students’ efforts. We can see who is currently studying which words, who is not making many efforts, and who is trying hard over and over again…etc. Especially, the teacher’s words of encouragement to the students who are repeatedly making a good effort have a tremendous effect. We have seen many times how these students use that as a springboard to try even harder.

6. Conclusion

In the current curriculum guidelines in Japan, as English education transitions from elementary school to middle school, there are many students struggling due to the increase in the number of new words to learn and the addition of writing tasks.Therefore, the importance of firmly establishing English vocabulary has increased.

In addition, the introduction of the new curriculum guidelines coincided with the spread of the novel coronavirus, leading to the early adoption of one-tablet-per-student programs in Japanese schools, a few years ahead of the original schedule. Consequently, school education has drastically changed due to these changes in the educational environment.

The introduction of one-tablet-per-student programs is visibly transforming classroom instruction. As one such change, the author believes that providing detailed feedback to learners frequently through small tests using Forms will likely become even more prevalent in the future.The use of individual tablets enables a variety of measures and supplementary strategies to be implemented in ways that are more suitable for each junior high school student, helping them to master what they have learned. This is likely to be a significant advantage of education utilizing ICT.

Through the initiative of visualizing students’ efforts using Google Forms, as discussed in this paper, we are now able to firmly grasp the daily efforts of students in learning English vocabulary, which we were previously unable to fully comprehend.Based on this, we have once again realized that we were able to cultivate not only the results of “can do, can’t do” learning but also the attitudes towards learning and continuous practices and grab the opportunity to give appropriate advice.

Automatic scoring with Google Forms tends to be used to reduce teachers’ “grading” tasks. However, as demonstrated in this paper, it can also be utilized to highlight the persistent efforts students put into their tasks and to cultivate an attitude of steadily building up their studies step by step. I believe I have been able to show this.I hope that further innovative efforts by each school using one-tablet-per-student programs will continue to be shared and that everyday school practices will become more sophisticated.

Former Principal of Hakodate Kita Junior High School, Toshiyuki Okuzaki

Postscript: Translated from Japanese to English with the help of ChatGPT4.0.

Appendix: The GAS code created in this article can be downloaded. Please use it after confirming the disclaimer.