My students are amazing! They always bring me joy in a variety of ways. Today, one of my amazing students Jason Choi made my jaw drop with one of his modifications to my Google Sheets. Google Sheets was already my favorite tool to use for my live sessions with my beginner to intermediate students, but now it is just perfection!

 

I am going to share with you how I use Google Sheets effectively, and I hope you will find this tool helpful as well. Oh, and it is free! :) Who is this tutorial for? I am writing this tutorial from a teacher’s perspective, so my target readers are teachers, tutors, and anyone else who wants to use their language exchange time more effectively.

 

This tool is perfect for beginner and intermediate level students focusing on conversational fluency. What does this tool do? Why is it great? By typing words and phrases into a spreadsheet, it makes reviewing and studying a lot easier for both the teacher and the student.

 

The three key features:

 

  1. Keeps session notes organized for both teachers and students.
  2. Auto generates pinyin on the spot for maximum tutoring value.
  3. Auto generates translation on the spot for maximum target language exposure.

 

Let’s take a closer look at the key features.

 

Feature #1: Keeps Session Notes Organized  

 

By using Google Sheets, it is easy for teachers to update and review what has been covered. Teachers can use this to prepare the next lesson and analyze any incorrect sentence patterns that students tend to make. Students can easily review new terms and sentences covered.

 

Feature #2: Auto Generates Pinyin on the Spot

 

No more manually typing pinyin or using a separate Google Translator page! I don’t know about you, but I am definitely a lot faster in typing Chinese characters than Pinyin with numbers. While it does take a second or two to show the Pinyin value, it definitely beats manual typing. By utilizing this tool, you maximize speaking and listening exposure for the student.

 

Feature #3: Auto Generates Translation on the Spot

 

While it might not give you a perfect translation, intermediate students can generally get the gist of what is being said. You can also quickly update it with manual translation if you prefer. Doing so, you avoid speaking English and maximize the Chinese exposure.

 

So, let's dive in! But first, bring me a life vest. I don't know how to swim. I am dividing this section into three parts. The first part shows you how it works. The second part shows you how to set it up. The third part gives troubleshooting for any problems that you might encounter.

 

Requirements

 

  • Google Account (for the teacher only).
  • Access to Google Sheets.
  • Install the HanyuPinyinTools add-on (if needed).

 

Part One: Demonstration

 

To start, have a look at this YouTube video, which shows you the tool.

 

 

As you can see, the first column is for Chinese characters. The second column is for pinyin. The third column is for Google translation. All you need to do is type in the Chinese phrases. And viola! Your pinyin and translation will be automatically generated within seconds! 15-Second Video Demonstration.

 

 

Part Two: How You Can Start Using It

 

Here is the link to my Google Sheets Tutoring Template. All you have to do is copy it to your Google drive and you will be able to start using it right away!

 

How to Copy the File:

 

  1. Click on “File”.
  2. Click on “Make a Copy”.
  3. Enter a new document name (optional).
  4. Click on “Ok”.

 

Part Three: Troubleshooting

 

Question #1: Pinyin is not being generated.

 

If for some reason HanyuPinyinTools does not appear in your copy, then you will need to install the add-on. Below is the tutorial for how to install the add-on.

 

How to Install HanyuPinyinTools:

 

  1. Click on “Add-ons”.
  2. Click on “Get add-ons…”.
  3. In the search bar, type in “Hanyu” (that should be sufficient).
  4. Click on “+ Free” button to install the add-on.

 

Question #2: I want to use numbers instead of accent marks.

 

Install the add-on and you will be able to customize the code.

 

The code for pinyin with accent marks is =HANYUPINYIN_TONEMARKS(A2), and (A2) is your cell position.

 

The code for pinyin with numbers is =HANYUPINYIN(A2), and (A2) is your cell position.

 

Question #3: I see “ERROR!” instead of pinyin.

 

This happens every now and then. Sometimes it can be fixed if you just refresh the page. If not, type the characters in a different cell and you should see the pinyin in the new cell.

 

My Tutoring Workflow

 

The basic concept of this tutoring tool is pretty straight forward. Instead of using a Skype chat window, I type all the words and phrases in Google Sheets. After the session, I revise the content based on the student’s level and learning style.

 

For beginners: I fix any typos and remove any incorrect words or sentences. I update the Google translation so that it is readable and beginner friendly.

 

For intermediate students: After ten to fifteen minutes of conversation during the session, if I have noticed any mistakes, we will go back to that line and fix them. After the session, I fix any typos.

 

For most of my students: I keep the incorrect sentences and provide the corrected version in the fourth column for comparison and self-study. I may or may not update Google translation depending on the student’s level.

 

This is the end of my tutorial! My students love this tool and I hope your students will too! I hope this will increase your teaching efficiency and maximize the live session value for your students. Happy teaching and thank you for being here!

 

Our students need you! Reference Jason Choi, the amazing student who added HanyuPinyinTools to our session notes! Arthur Worsley is another amazing student who has helped to explain the functions!

 

Image Sources