How to Set Up Vocabulary Sync with Your Google Spreadsheet

Check out how this function works.

P.S.: Random Word will not be recorded as checked words or synced to your Google Spreadsheet.

Purpose:

This function is designed to sync vocabulary checked by user with the user’s personal Google Spreadsheet. It allows users to keep track of the vocabulary words they’ve reviewed, along with additional information like phonetics, explanations, and examples.

Why This Feature Is Useful:

Personalized Vocabulary Tracking: Users can maintain their own vocabulary lists in a Google Sheet, which can be reviewed later or used for learning purposes.
Seamless Sync: Each time a word is checked, it is automatically logged in the spreadsheet.
Custom User Data: The Google Sheet can act as a personal study tracker, with all relevant word data stored neatly for the user’s reference.

💡

Setup is tricky but Totally Manageable! Let's tackle it step by step.

1: Create a New Google Sheet and Prepare It

Go to Google Sheets: Open Google Sheets.
Create a new sheet: Click on the Blank button to create a new spreadsheet. You can also click HERE to copy my sample structure.

Rename the Spreadsheet to Daily Words (Optional)

How to change the name and columns in your spreadsheet.

Rename the First Tab: At the bottom, click the default tab name ("Sheet1"), and rename to Vocabulary (This step is required and is case-sensitive, so ensure it matches exactly.)

This is how you rename your tab to `Vocabulary`

Copy the Google Sheet URL: Send this URL to the bot directly.

Copy the full-length link from the address bar of your browser.

Send the full-length url to the bot, and you'll get a message like this:

2: Enable Google Sheets API and Create Service Account Credentials

Go to the Google Cloud Console and search for Spreadsheet, or click HERE to get to Spreadsheet API page directly.

If you search by yourself... Click `Google Sheet API`

If you haven't create any project, then just create one for free.

If you do not have an existing project, then you need to create one:

Click `NEW PROJECT` if you don't have one.

Keep the default name or change to a new project name you prefer.

Once you have a project, then you can select the project.

You see on the top, I chose `My Preject 60232`, which I just created.

Then you can hit ENABLE button.

Navigate to `CREDENTIALS` and find `Manage service accounts`, then click `Manage service accounts`

Input any name and description you prefer. Then click `CREATE AND CONTINUE`

This step is optional, skip, just click `DONE`

Then, you got an `Service Email Address` in your project table, copy this email address. Keep this window open, we need to come back.

It's handy to click the `copy` icon attached to the email address.

Go back to your spreadsheet and click `File` >> `Share` >> `Share with others`. This is crucial.

Paste your `Service Email Address` to the box.

Then you'll see this small pop up notification shows `Access updated`

Generate a Key for the Service Account (crucial step):

Go back to this window, and click the `Service Email Address`, get to next page.

Find `KEYS` tab and click, get to this page, click `ADD KEY` >> `Create new key`

Choose default `JSON` format and click `CREATE`

Once the `JSON` file is created, you'll get this notice, and the file should be in your default `download folder` already.

Find the .json file from your download folder and rename to google_sheet_credentials.json, then send the file directly to the bot. This is crucial.

If you don't know your download folder, then you can find the file from browser download history button.

Once the `.json` file is received, you will get a confirmation.

Now it should be fine, let's try it out. Check some vocabularies first.

Troubleshooting

Error: Invalid Credentials
- Ensure the google_sheet_credentials.json file is correctly renamed and uploaded.
- Make sure the Service Account email has Editor access to the Google Sheet and your spreadsheet has shared to this Service Email Address
- Don't forget to send your spreadsheet full-length url to the bot and the first tab is renamed to Vocabulary.
- Verify that the Google Sheets API is enabled in Google Cloud.

Summary

Although the setup process requires several steps, it ensures a smooth and secure connection between the bot and your Google Spreadsheet. By following this guide, you’ll be able to:

Automatically log vocabulary words to your Google Sheet.
Track your progress efficiently using Telegram and Google Sheets.
Enjoy seamless synchronization without manually entering data.

Once set up, this feature will enhance your learning experience by keeping all vocabulary data organized and accessible.