Over Party Lab Chatbot

A LINE messaging bot for Over Party Lab, built on Google Apps Script and TypeScript, with its cocktail data in a Google Sheet. Send it a cocktail name and it answers with the description and the recipe link; send it an ingredient and it offers the cocktails made with it.

• 🔍 Bilingual lookup: matches a cocktail by its Chinese or English name, ignoring case, surrounding whitespace and numerically formatted cells
• 🎯 Ingredient fallback: when nothing matches by name, looks the message up in an ingredient table and offers the cocktails it maps to
• 🎨 Buttons template: up to 4 tappable options, clamped to every Messaging API payload limit so a long name or a long message can never make LINE reject the reply
• 📊 Search log: every answered message is appended to a Google Sheet, once per event even if LINE delivers it twice
• 💸 Free to answer: replies go through the Reply API, which does not count against the LINE Official Account's monthly message quota
• 🔒 Authenticated webhook: Apps Script cannot verify x-line-signature, so requests are authenticated by a shared secret in the URL plus a destination check

• TypeScript bundled to a single Apps Script file with esbuild, type-checked in strict mode
• 126 tests that run the built bundle in a sandbox with the Apps Script APIs stubbed
• CI on every pull request and on pushes to master: npm ci → typecheck → test → build
• Secrets in script properties, never in source

The bot follows a serverless, event-driven architecture:

┌─────────────────┐
│   LINE User     │
└────────┬────────┘
         │ message
         ▼
┌──────────────────────────────────────────────────┐
│              LINE Messaging API                  │
└────────┬─────────────────────────────▲───────────┘
         │ POST …/exec?token=SECRET    │ reply (free, single-use token)
         ▼                             │
┌──────────────────────────────────────┴───────────┐
│           Google Apps Script web app             │
│                                                  │
│  doPost(e)                                       │
│   1. token       vs WEBHOOK_TOKEN  ── reject ──▶ 200
│   2. destination vs BOT_USER_ID    ── reject ──▶ 200
│   3. for each event: text messages only          │
│        buildReply()  ─ lookup, then fallback     │
│        lineMessage   ─ clamp to API limits       │
│        lineService   ─ POST /message/reply       │
│        sheetService  ─ append the search         │
│   4. always 200 {"status":"ok"}                  │
└────────┬─────────────────────────────────────────┘
         │ one read per tab per delivery; one append per event
         ▼
┌──────────────────────────────────────────────────┐
│                 Google Sheets                    │
│  DRINK_LIST     ELEMENT_MAPPING     USER_ACTION  │
│  (cocktails)    (ingredient → idx)  (search log) │
└──────────────────────────────────────────────────┘

Secrets (channel token, spreadsheet id, webhook token, bot user id)
live in Apps Script script properties, never in the source.

Before you begin, ensure you have the following:

• Node.js: v20.0.0 or later (Download) — required by clasp 3.x
• Package Manager: npm. The repository ships a package-lock.json and CI runs npm ci, so installing with yarn would desynchronise the lockfile
• Google Account: For Google Apps Script and Sheets access
• LINE Developer Account: Register here
• LINE Messaging API Channel: Create a channel

# Clone the repository
git clone https://github.com/sean1093/over-party-lab-chatbot.git
cd over-party-lab-chatbot

# Install dependencies (clasp is a devDependency, no global install needed)
npm install

# Login to Google Account
npx clasp login

# Create the Apps Script project with the build output as its root.
# `--type` is not needed: what makes this a web app is the `webapp` block in
# appsscript.json, which is already committed. (clasp 3.3.0 rejected
# `--type webapp` outright with "Invalid container file type"; 3.4.1 accepts it.)
npx clasp create-script --title "Over Party Lab Chatbot" --rootDir dist

# Bundle TypeScript -> dist/Code.js and upload
npm run push
npx clasp deploy

# Finally, set the secrets as script properties (see step 3 below)

# Install project dependencies (includes clasp and esbuild)
npm install

# Login to Google Account
npx clasp login

# Create a new Apps Script project, with dist/ as the directory clasp uploads.
# `--type` is deliberately omitted: this is a web app because appsscript.json
# carries the `webapp` block, not because of a flag. (clasp 3.3.0 also rejected
# `--type webapp` with "Invalid container file type"; 3.4.1 accepts it.)
npx clasp create-script --title "Over Party Lab Chatbot" --rootDir dist

# Or adopt an existing project
npx clasp clone-script <SCRIPT_ID> --rootDir dist

Non-secret settings (column mapping, sheet tab names, Instagram link) live in config.ts and are committed. Secrets are not stored in source — they are read from Apps Script script properties at runtime, so they never end up in the code that clasp push uploads.

Open the Apps Script project (npx clasp open-script) and go to Project Settings → Script properties → Add script property:

If a property is missing, the execution fails with ConfigurationError: Missing script property "<KEY>" — the webhook returns an error and LINE's Verify button fails, so a misconfigured deployment is obvious instead of silently answering "not found" to every user. See properties.ts.

1. Create a new Google Sheet
2. Create three tabs, each with a header row, laid out as follows. The bot always treats row 1 as a header and reads from row 2 onwards.

Stores cocktail recipes and information.

Maps an ingredient to the cocktails made with it.

The recommendation cell holds indices, not names. 0 is the first data row of DRINK_LIST (spreadsheet row 2), 1 the second, and so on. Non-numeric or out-of-range entries are skipped; if none survives, the user gets the not-found reply. Note that inserting or deleting a DRINK_LIST row shifts every index after it.

The column positions above are fixed by COLUMN_KEY_MAPPING in config.ts, which applies to every tab — which is why recommendation is column E here even though C and D are unused.

Needs only its header row; the bot fills in every data row itself. The header is required — the index is derived from the row position, so without it every index is off by one.

3. Copy the Google Sheet ID from the URL into the SPREADSHEET_ID script property (see Configure Environment)

clasp 3.x does not transpile TypeScript, so the sources are bundled locally into a single dist/Code.js (plus a copy of appsscript.json) before being uploaded. .clasp.json must contain "rootDir": "dist"; npm run build refuses to run if it points anywhere else.

# Bundle TypeScript and push the bundle
npm run push

# Deploy as web app
npx clasp deploy

1. Deploy, and note the deployment ID — clasp 3.x prints Deployed <deploymentId> @HEAD and no URL:

   npx clasp create-deployment
   # or, to list the existing ones: npx clasp list-deployments

2. Append the shared secret to it. Apps Script web apps cannot read request headers, so LINE's x-line-signature cannot be verified here; the secret in the URL is what authenticates the caller instead:

   https://script.google.com/macros/s/<DEPLOYMENT_ID>/exec?token=<WEBHOOK_TOKEN>
   

3. Configure LINE Messaging API:

   • Go to LINE Developers Console
   • Select your Messaging API channel
   • Navigate to Messaging API tab
   • Set Webhook URL to the URL from step 2, including ?token=
   • Enable Use webhook
   • Disable Auto-reply messages (optional, recommended)

4. Verify webhook:

   • Click Verify button in LINE Console
   • Should return success message. A request without the token, or a delivery whose destination is not BOT_USER_ID, is answered 200 and logged as rejected without touching the spreadsheet or the Messaging API

# Bundle TypeScript into dist/Code.js
npm run build

# Type-check without emitting
npm run typecheck

# Build, then push the bundle to Google Apps Script
npm run push

# Build, push and deploy a new version
npm run deploy

# Rebuild on change (run `npx clasp push --watch` alongside to auto-upload)
npm run watch

1. Make local changes to TypeScript files
2. Verify locally: npm run typecheck && npm test
3. Push to Google Apps Script: npm run push
4. Test in LINE: Send messages to your bot
5. View logs: Check Google Apps Script editor > Executions

npm test          # vitest, no credentials or network needed
npm run typecheck # strict tsc over sources and tests

The suite in tests/ runs the real build output: npm test bundles the sources, then evaluates dist/Code.js inside a node:vm context with SpreadsheetApp, UrlFetchApp, PropertiesService and Logger stubbed (tests/gasHarness.ts). Entry points are invoked by global function name, exactly as Apps Script resolves them, so the bundling step is covered too — a build that Apps Script could not execute fails the suite.

What is asserted: the packaging contract (no import/export/require in the bundle, entry points present as top-level functions, a build target of ES2019 with the bundle grepped for newer syntax), the reply flow (exact match, case- and whitespace-insensitive English match, ingredient recommendations, not-found fallback), the USER_ACTION append, malformed webhook payloads being ignored without sending anything, and fail-loud behaviour when a script property is unset.

debug.ts provides test_post() and test_send() for manual checks from the Apps Script editor: select the function and click Run. Both need the DEBUG_USER_ID script property and both hit the live Messaging API, which is why they are not part of npm test. test_send() really does push a message to you; test_post()'s reply fails with Invalid reply token — real tokens only come from real deliveries — but it still writes a row to USER_ACTION.

• Before pushing: npm run typecheck && npm test — the same commands CI runs
• No formatter is configured; match the surrounding style rather than reformatting a file
• Watch mode: npm run watch rebuilds on change; run npx clasp push --watch alongside it to upload automatically

over-party-lab-chatbot/
│
├── 📄 Core Application Files
│   ├── main.ts                # Bundle entry point; exposes the Apps Script globals
│   ├── app.ts                 # Main webhook handler and message processing logic
│   ├── config.ts              # Non-secret configuration (committed)
│   ├── properties.ts          # Secret accessors backed by script properties
│   └── appsscript.json        # Google Apps Script manifest
│
├── 🔧 Service Layer
│   ├── lineService.ts         # LINE Messaging API client (reply / push)
│   ├── lineMessage.ts         # Message objects and the API's payload limits
│   ├── sheetService.ts        # Google Sheets reads and the analytics append
│   ├── logService.ts          # Execution logging
│   └── timeService.ts         # Timestamp formatting
│
├── 📝 Resources
│   ├── wording.ts             # User-facing strings
│   └── debug.ts               # Manual entry points for the Apps Script editor
│
├── 🧪 Tests
│   ├── tests/gasHarness.ts    # Runs dist/Code.js with the Apps Script APIs stubbed
│   ├── tests/bundle.test.ts   # End-to-end behaviour of the built bundle
│   ├── tests/lineMessage.test.ts   # Payload limits
│   ├── tests/buildConfig.test.ts   # Build contract and the clasp rootDir guard
│   └── tests/globalSetup.ts   # Builds the bundle before the suite runs
│
├── ⚙️ Configuration
│   ├── package.json           # Dependencies and scripts
│   ├── tsconfig.json          # Strict compiler options for the sources
│   ├── tsconfig.test.json     # Same, plus node types, for the tests
│   ├── vitest.config.mts      # Test runner configuration
│   ├── scripts/build.mjs      # esbuild bundler: sources -> dist/Code.js
│   ├── scripts/buildConfig.mjs   # Build contract: target, entry points, rootDir guard
│   ├── .github/workflows/ci.yml  # npm ci -> typecheck -> test -> build
│   └── .gitignore             # Git ignore rules
│
└── 📁 Other
    ├── CLAUDE.md              # Workflow and platform notes for coding agents
    ├── dist/                  # Build output uploaded by clasp (git-ignored)
    └── image/                 # Project assets (logo, screenshots)

1. User sends a message (e.g. "Margarita")
   ↓
2. LINE Platform POSTs the delivery to the webhook URL, secret included
   ↓
3. doPost(e) checks the token, then the delivery's `destination`
   ↓
4. For each event: keep text messages only, require a reply token, trim the text
   ↓
5. Look the text up in DRINK_LIST (name or nameen, case- and space-insensitive)
   ↓
6a. ✅ Row found                       6b. ❌ No row
    → echo, detail and link as text        → look the text up in ELEMENT_MAPPING
                                           → resolve its indices to cocktail names
                                           → up to 4 buttons, or the not-found reply
   ↓
7. Reply through the Reply API (free; the token is single-use)
   ↓
8. Append the search to USER_ACTION — after the reply, so it can never delay it
   ↓
9. Return 200 {"status":"ok"}, whatever happened

The bot implements a two-tier search strategy:

1. Exact Match Search (Primary):

   • Searches both name (Chinese) and nameen (English) columns
   • Case-insensitive, whitespace-insensitive, and tolerant of numerically formatted cells
   • Returns the cocktail's description and recipe link

2. Ingredient-Based Recommendations (Fallback):

   • Looks the message up in ELEMENT_MAPPING (exact match, same rules)
   • The recommendation cell holds comma-separated 0-based indices into the DRINK_LIST name column; stale or non-numeric entries are dropped
   • At most 4 are offered, the LINE maximum for a buttons template
   • Falls back to the not-found reply when nothing can be offered

Webhook handler. Authenticates the request first — e.parameter.token against the WEBHOOK_TOKEN property, then the body's destination against BOT_USER_ID — and only then parses e.postData.contents, answers each text message event through the Reply API, and appends a row to USER_ACTION.

Always returns {"status":"ok"} as JSON with HTTP 200, including for a rejected or unparseable request: LINE redelivers on a non-2xx and may suspend a webhook that keeps failing. The single exception is a missing script property, which surfaces as a failed execution so that a misconfigured deployment cannot look healthy.

Answers a webhook event. Reply messages are free of charge and work for group and room events, where source.userId may be absent. Reply tokens are single-use and expire about a minute after delivery.

Sends an unsolicited message. Counts against the monthly quota, so it is only used by debug.ts, which has no reply token.

interface SendResult {
  ok: boolean;
  status: number;
  body: string;
}

Builds text messages, skipping blank content (LINE rejects text: '').

Builds the buttons template, clamped to the Messaging API limits (4 actions, 40-character title, 20-character labels, 400-character altText). Returns null when there is nothing to offer, because a zero-action template is rejected with 400.

First row of from where any where column matches, limited to the select columns. null when nothing matches.

Every value of one column, excluding the header row.

Appends [index, search, user, timestamp] to USER_ACTION.

type SheetRow = Partial<Record<ColumnKey, string>>;

interface SaveData {
  search: string;       // User's search query
  user: string;         // LINE User ID
}

Configuration for Google Apps Script deployment:

{
  "timeZone": "Asia/Taipei",
  "runtimeVersion": "V8",
  "webapp": {
    "access": "ANYONE_ANONYMOUS",
    "executeAs": "USER_DEPLOYING"
  },
  "exceptionLogging": "STACKDRIVER"
}

Key Settings:

• timeZone: affects the USER_ACTION timestamps, which are formatted from the script's local time
• runtimeVersion: must stay V8. The bundle targets ES2019, which the legacy Rhino runtime cannot parse; tests/bundle.test.ts asserts this key on the built manifest
• access: must be ANYONE_ANONYMOUS, because LINE posts anonymously. Requests are authenticated by the ?token= secret and the destination check instead
• executeAs: USER_DEPLOYING, so the script reaches the deployer's spreadsheet

• ✅ Verify webhook URL is correct in LINE Console
• ✅ Ensure web app is deployed (not just saved)
• ✅ Check access is set to ANYONE_ANONYMOUS in appsscript.json
• ✅ Test webhook using LINE Console's verification tool

• ✅ Set all five properties — LINE_CHANNEL_ACCESS_TOKEN, SPREADSHEET_ID, WEBHOOK_TOKEN, BOT_USER_ID and DEBUG_USER_ID — in Apps Script → Project Settings → Script properties (see Configure Environment)
• ✅ Property names are case-sensitive

• ✅ You pushed the raw TypeScript sources. Run npm run push (which builds first) instead of clasp push
• ✅ Verify .clasp.json contains "rootDir": "dist"

Check the webhook token first. A rejected request is answered 200, exactly like a successful one, so the console's Verify button reports success even when the token is missing or wrong — it only reports the HTTP status. Send the bot a real message and look at the execution log:

• [doPost] rejected: webhook token mismatch → the registered webhook URL is missing ?token=, or its value no longer matches the WEBHOOK_TOKEN property. Rotating the secret means updating both, together.
• [doPost] rejected: destination is not this bot → the BOT_USER_ID property is not this channel's Your user ID.
• No [doPost] line at all → the deployment is not receiving the webhook; re-check the URL.

• ✅ Check Google Apps Script execution logs for errors
• ✅ Verify LINE Channel Access Token is valid
• ✅ Confirm the SPREADSHEET_ID script property is correct
• ✅ Ensure sheet tab names match exactly (case-sensitive)

# Check for errors before pushing
npm run typecheck

# Common fix: Reinstall dependencies
rm -rf node_modules package-lock.json
npm install

1. View Execution Logs:

   • Open Google Apps Script editor
   • Click View > Executions
   • Check recent execution logs for errors

2. Run the debug entry points from the editor:

   • test_post() drives the whole flow, including the real spreadsheet; its reply fails with Invalid reply token, so check the log for the payload it built
   • test_send() pushes a real message to DEBUG_USER_ID

3. Enable Verbose Logging:

   • Add console.log() statements in your code
   • View output in Apps Script Executions panel

Contributions are welcome! Here's how you can help:

1. Fork the repository

   git clone https://github.com/YOUR_USERNAME/over-party-lab-chatbot.git

2. Create a feature branch

   git checkout -b feature/amazing-feature

3. Make your changes

   • Follow existing code style
   • Add comments for complex logic
   • Update documentation if needed

4. Verify your changes

   npm run typecheck && npm test

   Both must pass, and CI runs them on every pull request. debug.ts is for manual checks against the live channel, not a substitute.

5. Commit with clear messages

   git commit -m "feat: add amazing feature"

   Follow Conventional Commits

6. Push and create Pull Request

   git push origin feature/amazing-feature

• Write clean, readable code
• Maintain type safety: no any, no unchecked casts for reading external input
• Add JSDoc comments for public functions, and say why rather than what
• Keep dependencies minimal
• Every behavioural change needs a test that fails before it and passes after it
• Respect the platform constraints in CLAUDE.md — several are not obvious and have each caused a real bug here

• 🌐 More language support
• 🎨 Better message templates and UI, e.g. quick replies instead of a 4-button template
• 📊 Richer analytics
• ⏱️ Cross-execution caching of the sheet data, if a bigger sheet makes the per-delivery read hurt
• 📝 Documentation
• 🐛 Bug fixes and performance improvements

This project is licensed under the MIT License - see the LICENSE file for details.

Yes! The architecture is generic. Simply modify:

• Google Sheets structure for your data
• Search logic in app.ts
• Message templates in wording.ts

Nothing, for a small bot. Google Apps Script's free quotas cover it, and — because the bot answers with the Reply API — replies do not count against the LINE Official Account's monthly message allowance. Only test_send() in debug.ts sends a push message, which does count.

Yes. Add the message object to lineMessage.ts, with its limits, and return it from buildReply. See LINE Message Types.

Per webhook delivery the cost is one Sheets read per tab, however many events the delivery carries, because each tab is read once per execution and cached in memory. The reply and the USER_ACTION append are per text-message event. Before reaching for a rewrite, check the real limits:

• LINE records a request_timeout error if the bot server does not respond within 2 seconds, so the response budget is the first thing to run out.
• Apps Script allows 6 minutes per execution and a limited number of simultaneous executions.
• Sheets and UrlFetch have daily quotas.

If a bigger sheet or heavier traffic does become the bottleneck, cache the tabs across executions with CacheService, or move the data to a real database.

Yes. Use a separate Apps Script deployment per bot, each with its own script properties (channel token, spreadsheet, WEBHOOK_TOKEN, BOT_USER_ID) and its own LINE channel. Do not share a WEBHOOK_TOKEN between deployments.

• 📝 How to create a LINE chatbot using Google Apps Script (Chinese)
• 📝 Using clasp and TypeScript to develop Google Apps Script (Chinese)

• 📚 LINE Messaging API Documentation
• 📚 Google Apps Script Documentation
• 📚 clasp - Command Line Apps Script Projects
• 📚 TypeScript Handbook

• LINE Bot SDK - Node.js SDK for LINE
• Google Apps Script Samples

Sean Chou

• GitHub: @sean1093
• Medium: @sean1093

• 🍸 Over Party Lab - Cocktail community and inspiration
• 💚 LINE Corporation - LINE Messaging API
• ☁️ Google - Apps Script platform and infrastructure
• 💙 TypeScript Team - Type-safe development tools
• 🙏 All contributors and users of this project