Collect Ather Logs in Google Sheets via Telegram Bot

I have been using the Ather 450X for over 8 months now. I prefer driving this vehicle more compared to my previous ones.

The Ather app provides statistics such as the last 20 rides, monthly ride logs, charging statistics, etc. However, they recently stopped sending monthly logs. To continue analyzing the data, I decided to collect the logs myself.

Purpose #

My aim was to track trends in my monthly driving distance, monitor expected range variations, and evaluate whether these metrics are increasing or decreasing over time. The monthly ride logs were helpful, but since Ather discontinued them, there’s no single place to view all the monthly data. I used to rely on my previous emails to access them.

I wanted to take charge of collecting the logs, incorporate additional metrics not provided by Ather, such as Battery Usage for each ride, Daily and Monthly Battery Usage, and Battery Consumption per Kilometer. By managing my data, I can access it at my convenience.

Thought Process #

Initially, I considered manually collecting statistics using a webpage. However, I realized this could become cumbersome and might discourage me from maintaining the logs over time. I opted for the option in the Ather app to share ride statistics as images for their marketing purposes.

I planned to run Optical Character Recognition (OCR) on these images to extract relevant text values and then organize them systematically. This way, I wouldn’t lose interest in maintaining the logs, as it require less work.

Tools #

I needed tools for the following steps:

1. Collect input from the Ather app.
2. Process OCR and convert to data points.
3. Store organized data.
4. Set up reports for trend analysis.
5. Automate the process.
6. Keep costs low or free.

After consideration, I settled on the following tools:

1. Collect input from Ather app: Telegram / Telegram bot.
2. Process OCR and convert to data points: Google Docs.
3. Store organized data: Google Sheets.
4. Set up trend analysis reports: Google Sheets.
5. Automate the process: Google Scripts.
6. Keep costs low or free: All tools above are free within certain limits.

How It Works #

1. Open the Ather app and access ride statistics, which includes details like Date, Distance Traveled, Time Spent, Efficiency, Projected Range, and Top Speed for the last 20 rides.
2. Share the ride statistics image with my Telegram bot.
3. The bot sends the image to Google Drive.
4. A script retrieves the image, converts it to text and data points.
5. The script organizes the data and places it in the correct columns.
6. Google Sheets summarizes the data and generates update charts.
7. The Telegram bot sends a summary of the extracted image information.
8. The Telegram bot provides options to view ride statistics as graphs.

Setup #

Requirements #

To set up a similar system for personal use, you’ll need:

• A Google account.
• A Telegram account.
• A computer (setting up on a mobile device is challenging).
• Some time and patience.

Setting up Telegram Bot #

1. Search for BotFather on Telegram or use this link: https://t.me/BotFather.
2. Start a chat and choose /newbot from the menu.
3. Provide basic information such as Bot’s name and username (naming rules apply).

Setting up Google Sheets #

Make a copy of this Google Sheet: Google Sheets Template.

It will ask you to name the file and also highlight that the scripts also will be saved.

You can rename the sheet the way you want and click ‘Make a copy’. Once the file is saved, you will be able to make edits to the sheet.

Setting up Google Drive #

Visit https://drive.google.com/ and create a new folder.

Choose a name, such as ‘Ride Logs’, and securely save the content from the address bar, as highlighted below. This information will be utilized in the subsequent step.

Setting up Script #

Access Google Apps Script via the “Extensions” menu in Google Sheets.

Rename the project to match your sheet’s name (optional). Deploy the script, authorizing necessary permissions.

You will get a pop-up like below. Make sure you select the highlighted poritions. If you make mistakes here, your bot will not work.

Then you need to follow the steps as highlighted in the screenshots. These steps are required to access your google drive, google sheets, google docs and slides, to process further.

Choose your gmail ID, if you don’t want to use your primary gmail you can create a new one.

You will get a warning that the app is unverified. Click advanced

Click go to ‘your google sheet file name’

Click Allow

You will get a deployment URL. Keep it safe and do not share it with anyone.

Now click the gear icon on the left pane, which will open up the project settings.

Scroll to the bottom where you can see the Script Properties, then click on ‘Add Script Property’ button

Update Script Properties #

Create the following Script Properties. To avoid spell error, copy and paste.

ADMIN
ALLOWED_USER_IDS
SSID
DRIVE_FOLDER_ID
DOC_ID
SLIDE_ID
WEBHOOK_URL
bot_token
GROUP_CHECK
GROUP_ID
DRIVE_ID_USER_DATA
LOG
RUN_DATE_UPDATED
RUN_TIME_COUNT
RUN_TIME_MSEC

The suggested values to be set on the Script Properties.

Variable / Property	Value	Remarks
ADMIN	Your Telegram ID	A numeric Value. Explained in later part how to get this.
ALLOWED_USER_IDS	Your Telegram ID, If you want someone else to access your bot	A numeric Value. Explained in later part how to get this.
SSID	ID of the sample google sheet after making a copy	Get it from Address bar. Only the ID not the URL
DRIVE_FOLDER_ID	Create a folder in Google Drive and update the ID here	Get it from Address bar. Only the ID not the URL
DOC_ID	Create a Doc file in Google Drive and update the ID here	Get it from Address bar. Only the ID not the URL
SLIDE_ID	Create a slide from Google Drive and update the ID here	Get it from Address bar. Only the ID not the URL
WEBHOOK_URL	Read the instructions from blog post	Keep it safe, do not share it with anyone.
bot_token	Your telegram bot token form BotFather	Keep it safe, don’t share it withanyone.
GROUP_CHECK	false	Not required if you are not mainting a group
GROUP_ID	NA	Not required if you are not mainting a group
DRIVE_ID_USER_DATA	NA	Not required if you are not mainting a group
LOG	false	It will fill up the sheets, used only for debugging purpose
RUN_DATE_UPDATED	0	This will get updated automatically
RUN_TIME_COUNT	0	This will get updated automatically
RUN_TIME_MSEC	0	This will get updated automatically

It should look like the below picture.

Then click ‘Save Script Properties’ Button.

Deploy it Again #

The changes that we made in the script properties should be deployed again. But this time, we need to deploy a new version.

Click the ‘Deploy’ Button, then click ‘Manage Deployments’.

Click on the pencil icon,on the version drop down choose ‘New Version’, then deploy.

Setup Webhook and Get Telegram User Info #

Now we need to set up the ADMIN and ALLOWED_USER_IDS, so that the bot can be accessible only to the intendented users. For that, go to Script editor from the left pane.

Then go to bot.gs file. You will see many functions like getMe(), setWebhook(), etc.,

from the top select getMe then click Run. You will get a success message in the execution log on the bottom.

Now do the same for setWebhook.

Once it is done Deploy It Again.

Once the webhook is setup the telegram bot that we created above will start working.

Open the telegram app and open the bot that you have created. If you don’t know the bot, the link would be available in BotFather.

Hit ‘Start’ button. You need to get a message like

Note the chatID and this is going to be our ADMIN and ALLOWED_USER_IDS.

Follow the steps explained in the Update Script Properties and fill in ADMIN and ALLOWED_USER_IDS.

So your script proprties should have the telegram numeric IDs something like this.

Now go back to telegram and send a message /start. You should be getting a welcome message.

Usage #

1. You can clear the contents from ‘Data’ sheet from row no.2 onwards.
2. Do not delete the titles.
3. Do not make any changes on the other sheets, unless you know what you are doing.
4. Share the ride log image with the Telegram bot.
5. The script extracts and populates data in the ‘Data’ sheet.

   

Telegram bot menu #

We don’t get menus by default on the bot. The bottom text input box would look like this at this stage.

To enable menu and commands, touch the name of the bot.

Touch the pencil icon.

Touch ‘Edit commands’ option

The Botfather window would open and ask for commands. Copy and paste the following.

start - Initiate the bot
daily_charts - Day level stats
monthly_charts - Month level stats

Once it is pasted the menu will be activated for the bot you have creaged and it will look like this.

Choosing menu and relevant buttons will bring charts for analysis.

Sample Charts #

Here are some sample charts:

• Distance vs. Range:

  

• Distance vs. Efficiency:

  

• Distance vs. Battery % per Km:

  

• Daily battery usage in %:

  

Important Notes #

1. The script will process data extracted from the ride log image; if the source is incorrect the result also will be incorrect.
2. Ather’s average speed calculation differs from the script due to how stop time is handled. I feel the calculation on Ather’s side also incorrect.
3. This data is intended for educational use only.
4. The script’s security vulnerabilities were checked, but use at your own risk.
5. This data shouldn’t be used for legal or official purposes.
6. The code can be modified, enhanced, redistributed, or sold.
7. The project is provided to support the community, without expectations of monetary gain.

For questions, contact me on this Telegram channel. I’ll respond when available, but immediate support isn’t guaranteed.