WhatsApp -> Excel Issue Tracker Bot

A Windows bot that reads messages from a WhatsApp group chat and logs equipment issues into an Excel workbook — automatically classifying each message into a tidy set of columns (date, work-week, type, equipment, issue, remarks, alarm text).

It attaches to the WhatsApp Desktop app (Microsoft Store version, which runs on Edge WebView2) through its built-in DevTools endpoint, so it uses the account you are already logged into — no separate browser window and no re-scanning QR codes every run.

Privacy · Security · MIT License

This is a template — the equipment tags, fault codes, and issue/alarm master-lists are generic examples. Replace them with your own before use (see Customizing).

---

Upload directly to GitHub

This folder is commit-ready. Create or open an empty GitHub repository, choose Add file → Upload files, then drag all files from this folder onto the upload page and commit them. Keep the dotfiles (.gitignore and .gitattributes) included—they prevent private workbooks, images, backups, environment files, and local editor settings from being committed accidentally.

Before uploading any later customization, review the staged files and confirm that they contain no real chat names, participant names, internal paths, production equipment mappings, workbooks, logs, or downloaded media.

---

How it works

1. Connects to the running WhatsApp Desktop app via its WebView2 DevTools port 9333. If needed, it temporarily stages the launch flag, restarts only WhatsApp, waits for the native app endpoint, then immediately removes the flag so GitHub Copilot, Teams, Outlook, and other WebView2 apps do not inherit it.
2. Reads the most recent date/time already stored in Excel and verifies that the configured group—not whichever conversation happens to be visible—is open.
3. When history is needed, opens Search messages → Jump to date, selects each required calendar day, and waits for WhatsApp's delayed message blocks. If WhatsApp offers "Click here to get older messages from your phone", the bot activates it and waits for the extra history to load.
4. Parses each message bubble: timestamp, visible author metadata, text, and images.
5. For text messages it:
   • fixes common equipment-name typos,
   • detects the equipment,
   • classifies the issue into an approved Generic Issue / Type / Alarm Text,
   • learns participant display names from the group header and message-author elements, then strips names/contact annotations from remarks,
   • strips greetings, sign-offs, timestamps and "issue resolved" chatter,
   • de-duplicates against what is already in the sheet.
6. Applies a strict image rule:
   • Image with caption/comment: records the cleaned comment only; it does not download the image and does not underline/hyperlink the Excel cell.
   • Image with no caption/comment: fetches the bytes while the temporary blob: URL is alive, saves them under the current Windows user's Downloads folder, and writes a clickable Excel hyperlink. Calendar/backfill image recovery begins at Excel's latest recorded timestamp, not at the start of all available chat history.
7. Appends new rows to the workbook and keeps rolling local backups.
8. Repeats every CHECK_EVERY minutes.

On each run it resumes from the most recent entry already in the sheet, so no messages are missed and none are duplicated. If the workbook can't be read (e.g. a transient network-share glitch) it recovers the resume point from the newest local backup instead of jumping to "now", so it never silently skips history.

---

Requirements

• Windows (uses the Windows registry and the WhatsApp Desktop app)
• Python 3.9+
• WhatsApp Desktop app (Microsoft Store version) installed and logged in
• Microsoft Edge / WebView2 runtime (ships with Windows 10/11)

Install the Python dependencies:

pip install -r requirements.txt

Selenium 4.6+ includes Selenium Manager, which downloads a matching msedgedriver automatically — no manual driver setup needed.

---

Setup

1. Install and sign in to the WhatsApp Desktop app from the Microsoft Store.

2. Open whatsapp_bot.py and edit the YOUR SETTINGS block:

   CHAT_NAME       = "My Team Chat"                 # exact chat/group name
   EXCEL_FILE      = r"C:\path\to\your\alerts.xlsx" # workbook to log into
   CHECK_EVERY     = 1                              # minutes between re-checks
   SENDER_NAME_TAG = "Company Tag"                  # suffix in colleagues' display names

   Optionally edit IGNORE_NAMES for display names that WhatsApp cannot learn automatically (for example, names found only inside old exported remarks).

3. On the first run (or whenever WhatsApp was started without debugging), the bot temporarily sets WEBVIEW2_ADDITIONAL_BROWSER_ARGUMENTS, launches the real packaged WhatsApp app on dedicated local port 9333, verifies WhatsApp's native osBuild/windowsBuild target, then restores/removes the environment value. If prompted, scan the QR code on your phone (WhatsApp → Linked devices → Link a device).

   The bot never calls driver.get() and never navigates an arbitrary WebView. If the genuine native WhatsApp target is unavailable, it fails safely instead of replacing another application's content.

---

Usage

# Normal run: resume from the sheet's latest entry and keep checking
python whatsapp_bot.py

# One-off: re-scan the last N days (manual backfill; default 7)
python whatsapp_bot.py --backfill 14

# Clean/normalize an existing workbook's remarks and drop non-issue rows
python whatsapp_bot.py --clean

# Remove a stale/global WebView2 debugging flag left by an older version
python whatsapp_bot.py --disable-debug

Press Ctrl+C to stop the scheduler.

---

Output columns

Column	Description
Date	Message date/time
LW	Fiscal work-week label (e.g. LW2601) — customizable
Type	HARDWARE / SOFTWARE / MISMATCH / OTHERS
Equipment	Detected equipment tag
Generic Issues	One of the approved GENERIC_ISSUES values
Actions / Remarks	Cleaned text; clickable only for image-only messages
Alarm Text	One of the approved ALARM_TEXTS values

Image-only messages are logged as Open WhatsApp image (or an album count) with blank issue columns. The text is an Excel hyperlink to a persistent image file; albums link to their message folder.

Mobile-robot issue types

The generic AGV/mobile-robot examples use SOFTWARE by default for navigation, task, traffic-control, localization, charging, safety-system, timeout, stuck, deadlock, and no-response faults. They remain HARDWARE only when the message explicitly describes a physical event such as a collision, hit, dropped or misplaced material, damage, a broken component, wheel, or bumper issue. Data mismatches remain MISMATCH.

This policy is centralized and also self-heals existing mobile-robot Type cells the next time the workbook is saved. Customize the physical keyword list if your site uses different terminology.

---

Persistent image storage

WhatsApp often exposes media as a blob: URL. That URL belongs to one WebView2 session and cannot be opened later from Excel. This bot fixes that at capture time:

1. It first checks for usable caption/comment text. If present, it records that text only and skips the image completely.

2. For image-only messages, it fetches the rendered image from inside the authenticated WhatsApp page and validates/decodes the bytes (maximum 20 MiB per image).

3. It writes files atomically under:

   %USERPROFILE%\Downloads\whatsapp_images\YYYY-MM-DD\HHMM_<message-id>\
   

4. It hyperlinks column F to the saved file (single image) or message folder (album), using Open WhatsApp image(s) as the visible text.

5. Ordinary text and image captions are never hyperlinks. During a save, the bot also removes stale caption/text hyperlinks left by older versions while preserving explicit image-only links.

The whatsapp_images/ folder is intentionally local and separate from the workbook to avoid filling a shared/network storage location or making the .xlsx extremely large. The Excel hyperlinks therefore work on the laptop that runs the bot unless you later move/share the image files and update the hyperlinks.

At each program start, the bot reads the latest datetime already recorded in Excel and uses it as an inclusive image cutoff. Calendar catch-up and manual backfill can still inspect older text, but image files and image-only rows are created only at/after that Excel timestamp. This recovers images missed since the last saved record without downloading the full historic archive. If the workbook has no usable timestamp, image capture starts at the current time.

Old expired blob: URLs cannot be converted after WhatsApp can no longer render the source media.

---

Privacy and people-name filtering

The template does not contain real company names, servers, network paths, equipment identifiers, contacts, or production alarm mappings.

At runtime, the bot learns display names from:

• the open group's participant subtitle,
• each message's [data-testid="author"] metadata,
• the optional IGNORE_NAMES list.

Names and attached contact/company annotations are removed from Actions / Remarks. Matching uses token boundaries, so filtering a short name does not alter a larger word containing the same letters (for example, a name matching Sam does not change SAMPLE). Author metadata and timestamps are also excluded from message-text extraction at the source.

The .gitignore excludes Excel workbooks, logs, local backups, virtual environments, and environment files. Always review staged files before pushing; never commit a real tracking workbook or a private configuration.

---

Customizing for your line

All of the domain data is grouped near the top of the script and marked with EXAMPLE comments. Replace it with your own:

• _EQUIP_PREFIXES / _EQUIP_ALIASES — numbered machine prefixes and typo fixes.
• detect_equipment() — how a message maps to an equipment tag.
• GENERIC_ISSUES — the exact allowed values for the Generic Issues column.
• ALARM_TEXTS — the exact allowed values for the Alarm Text column.
• ISSUE_RULES — keyword → (issue, type, alarm) classification rules.
• compute_lw() — swap in your own work-week / fiscal-calendar scheme.
• SYMPTOM_WORDS — words that mark a message as a real equipment problem.
• IGNORE_NAMES / SENDER_NAME_TAG — optional name-filter fallbacks.

---

Backups

Before each save the workbook is copied to a rolling daily backup under %LOCALAPPDATA%\whatsapp_bot\backups\ (the newest/fullest copy per day is kept, up to 14 days). If the target workbook ever goes missing, the newest backup is restored automatically instead of starting from an empty sheet.

---

Notes & limitations

• Windows-only by design (WhatsApp Desktop app + Edge WebView2 + registry).
• WhatsApp Web selectors change over time; the parsing uses several fallbacks but may need occasional updates.
• WhatsApp controls how much history is available. The bot requests older history from the linked phone when WhatsApp exposes that option, but unavailable history cannot be recovered by the bot.
• Images can only be persisted while WhatsApp still renders their media bytes. If saving fails, the row says WhatsApp image (could not save) rather than storing another broken blob: URL.
• Image hyperlinks point to this laptop's Downloads folder and are not portable to other computers unless the files are copied and links are updated.
• Historical catch-up intentionally waits for delayed date-search blocks and can take several minutes when the workbook is multiple days behind.
• WhatsApp uses dedicated local port 9333. The bot requires native WhatsApp's osBuild/windowsBuild URL marker and rejects plain WhatsApp Web pages exposed by another application.
• The launch flag is removed immediately after WhatsApp starts. If an older bot version left a global flag behind, run python whatsapp_bot.py --disable-debug and restart any already-open affected WebView2 application once.
• Do not expose port 9333 to other machines or untrusted networks.
• This project automates your own account reading a chat you are a member of. Review WhatsApp's Terms of Service before use.

Disclaimer

Provided as-is, for educational purposes. Not affiliated with or endorsed by WhatsApp / Meta.

License

Released under the MIT License.