How to Set Up Vocabulary Sync with Your Google Spreadsheet

This guide helps you set up and sync vocabulary between the bot and your Google Spreadsheet. It may seem a bit complex at first, but by following the steps carefully, you’ll be ready in no time.

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.

Make sure you've Selected a project

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

Click CREATE SERVICE ACCOUNT

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

Select Basic >> Editor role.

Then hit 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 click Send

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.

It's working as expected...

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.