How to Automate Gmail Lead Capture using the API

1#!/usr/bin/env python3
2"""Gmail Lead Capture — polls inbox, parses leads, writes to Google Sheets."""
3import os
4import base64
5import json
6import re
7import time
8import logging
9from datetime import datetime
10from pathlib import Path
11from googleapiclient.discovery import build
12from google.oauth2.credentials import Credentials
13from google_auth_oauthlib.flow import InstalledAppFlow
14from google.auth.transport.requests import Request
15
16logging.basicConfig(level=logging.INFO, format='%(asctime)s %(message)s')
17log = logging.getLogger(__name__)
18
19SCOPES = ['https://www.googleapis.com/auth/gmail.readonly', 'https://www.googleapis.com/auth/spreadsheets']
20LEAD_QUERY = os.environ.get('LEAD_QUERY', 'subject:"New contact form" in:inbox')
21SPREADSHEET_ID = os.environ.get('SPREADSHEET_ID', 'your-spreadsheet-id')
22SHEET_NAME = 'Leads'
23CHECKPOINT_FILE = 'checkpoint.json'
24
25def get_creds():
26    creds = None
27    if os.path.exists('token.json'):
28        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
29    if not creds or not creds.valid:
30        if creds and creds.expired and creds.refresh_token:
31            creds.refresh(Request())
32        else:
33            flow = InstalledAppFlow.from_client_secrets_file('credentials.json', SCOPES)
34            creds = flow.run_local_server(port=0)
35        with open('token.json', 'w') as f: f.write(creds.to_json())
36    return creds
37
38def get_body(payload):
39    if payload.get('mimeType') == 'text/plain' and payload.get('body', {}).get('data'):
40        return base64.urlsafe_b64decode(payload['body']['data'] + '==').decode('utf-8', errors='ignore')
41    for p in payload.get('parts', []):
42        r = get_body(p)
43        if r: return r
44    return ''
45
46def parse_lead(body, headers):
47    lead = {'received_at': headers.get('Date', '')}
48    for field, pattern in [
49        ('name', r'(?:Name|Full Name):\s*(.+)'),
50        ('email', r'(?:Email):\s*([\w.+-]+@[\w-]+\.[\w.]+)'),
51        ('company', r'(?:Company|Org):\s*(.+)'),
52        ('message', r'(?:Message|How can we help|Notes):\s*(.+?)(?=\n[A-Z]|$)')
53    ]:
54        m = re.search(pattern, body, re.IGNORECASE | re.DOTALL)
55        if m: lead[field] = m.group(1).strip()[:500]  # Limit length
56    # Fallback: extract email from From header if not in body
57    if not lead.get('email'):
58        m = re.search(r'[\w.+-]+@[\w-]+\.[\w.]+', headers.get('Reply-To', '') + headers.get('From', ''))
59        if m: lead['email'] = m.group(0)
60    return lead
61
62def main():
63    creds = get_creds()
64    gmail = build('gmail', 'v1', credentials=creds)
65    sheets = build('sheets', 'v4', credentials=creds)
66
67    cp = json.loads(Path(CHECKPOINT_FILE).read_text()) if Path(CHECKPOINT_FILE).exists() else {}
68    last_ts = cp.get('last_ts')
69    processed = set(cp.get('processed', []))
70    run_ts = int(time.time())
71
72    query = LEAD_QUERY + (f' after:{last_ts}' if last_ts else '')
73    msg_ids, page_token = [], None
74    while True:
75        params = {'userId': 'me', 'q': query, 'maxResults': 500}
76        if page_token: params['pageToken'] = page_token
77        r = gmail.users().messages().list(**params).execute()
78        msg_ids.extend([m['id'] for m in r.get('messages', [])])
79        page_token = r.get('nextPageToken')
80        if not page_token: break
81
82    new_ids = [i for i in msg_ids if i not in processed]
83    log.info(f'Found {len(new_ids)} new messages')
84
85    existing_emails = set()
86    ex = sheets.spreadsheets().values().get(
87        spreadsheetId=SPREADSHEET_ID, range=f'{SHEET_NAME}!B:B'
88    ).execute()
89    for row in ex.get('values', []):
90        if row: existing_emails.add(row[0].lower())
91
92    rows = []
93    for msg_id in new_ids:
94        msg = gmail.users().messages().get(userId='me', id=msg_id, format='full').execute()
95        hdrs = {h['name']: h['value'] for h in msg['payload']['headers']}
96        lead = parse_lead(get_body(msg['payload']), hdrs)
97        email = lead.get('email', '').lower()
98        if email and email not in existing_emails:
99            rows.append([lead.get('name',''), lead.get('email',''), lead.get('company',''),
100                        lead.get('message',''), datetime.now().strftime('%Y-%m-%d %H:%M')])
101            existing_emails.add(email)
102        processed.add(msg_id)
103        time.sleep(0.1)
104
105    if rows:
106        sheets.spreadsheets().values().append(
107            spreadsheetId=SPREADSHEET_ID, range=f'{SHEET_NAME}!A:E',
108            valueInputOption='USER_ENTERED', body={'values': rows}
109        ).execute()
110        log.info(f'Written {len(rows)} new leads')
111
112    Path(CHECKPOINT_FILE).write_text(json.dumps({'last_ts': run_ts, 'processed': list(processed)[-5000:]}))
113
114if __name__ == '__main__':
115    main()