Automatically saving Gmail messages to Google Drive using Google Apps Script

import * as cheerio from 'cheerio'
import fs from 'fs/promises'
import { simpleParser } from 'mailparser'

async function readEmailFile(emlPath) {
  const emlContent = await fs.readFile(emlPath, 'utf8')
  const parsed = await simpleParser(emlContent)
  return parsed
}

function parseGrabReceiptHtml(htmlContent) {
  const $ = cheerio.load(htmlContent)

  const result = {
    service: null,
    orderNumber: null,
    dateTime: null,
    merchant: null,
    pickupLocation: null,
    dropoffLocation: null,
    paymentMethod: null,
    items: [],
    subtotal: null,
    deliveryFee: null,
    discounts: [],
    total: null,
  }

  const allText = $('body').text()

  // Service type
  if (allText.includes('GrabFood')) {
    result.service = 'GrabFood'
  }

  // Extract metadata from table cells
  $('td').each((_i, elem) => {
    const text = $(elem).text().trim()

    if (text.includes('รหัสการจอง')) {
      const spans = $(elem).find('span')
      spans.each((_j, span) => {
        const spanText = $(span).text().trim()
        if (
          spanText &&
          !spanText.includes('รหัสการจอง') &&
          spanText.length > 5
        ) {
          result.orderNumber = spanText
        }
      })
    }

    if (text.includes('สถานที่เริ่มต้นการเดินทาง')) {
      const spans = $(elem).find('span')
      spans.each((_j, span) => {
        const spanText = $(span).text().trim()
        if (
          spanText &&
          !spanText.includes('สถานที่เริ่มต้นการเดินทาง') &&
          spanText.length > 3
        ) {
          result.pickupLocation = spanText
          // Extract merchant name (text before dash)
          const dashIndex = spanText.indexOf(' -')
          if (dashIndex > 0) {
            result.merchant = spanText.substring(0, dashIndex)
          } else {
            result.merchant = spanText
          }
        }
      })
    }

    if (text.includes('สถานที่ปลายทาง')) {
      const spans = $(elem).find('span')
      spans.each((_j, span) => {
        const spanText = $(span).text().trim()
        if (
          spanText &&
          !spanText.includes('สถานที่ปลายทาง') &&
          spanText.length > 2
        ) {
          result.dropoffLocation = spanText
        }
      })
    }

    if (text.includes('รูปแบบการชำระเงิน')) {
      const spans = $(elem).find('span')
      spans.each((_j, span) => {
        const spanText = $(span).text().trim()
        if (
          spanText &&
          !spanText.includes('รูปแบบการชำระเงิน') &&
          spanText.length > 3
        ) {
          result.paymentMethod = spanText
        }
      })
    }
  })

  // Extract date/time
  const dateTimeMatch = allText.match(
    /(\d{2}\s+\w+\s+\d{2}\s+\d{2}:\d{2}[^\n]*)/
  )
  if (dateTimeMatch) {
    result.dateTime = dateTimeMatch[1].trim()
  }

  // Parse items and pricing from the receipt table
  // Strategy: iterate through all <tr> rows and parse based on column structure
  const allRows = $('tr').toArray()

  for (let i = 0; i < allRows.length; i++) {
    const $row = $(allRows[i])
    const $tds = $row.find('td')

    // Skip rows that don't have the expected 4-column structure
    if ($tds.length !== 4) continue

    const col1Text = $($tds[1]).text().trim()
    const col2Text = $($tds[2]).text().trim()

    // Check for item row: "1x Item Name" in col1, "฿ XXX" in col2
    const itemMatch = col1Text.match(/^(\d+)x\s/)
    if (itemMatch && col2Text.includes('฿') && !col2Text.startsWith('-')) {
      const quantity = parseInt(itemMatch[1])

      // Extract item name from bold span
      const boldSpan = $($tds[1]).find("span[style*='font-weight:bold']")
      const name = boldSpan.text().trim()

      // Extract price
      const priceMatch = col2Text.match(/฿\s*([\d,]+)/)
      const price = priceMatch ? parseFloat(priceMatch[1].replace(/,/g, '')) : 0

      const options = []

      // Look ahead for option rows
      let j = i + 1
      while (j < allRows.length) {
        const $nextRow = $(allRows[j])
        const $nextTds = $nextRow.find('td')

        if ($nextTds.length !== 4) break

        const nextCol1Text = $($nextTds[1]).text().trim()
        const nextCol2Text = $($nextTds[2]).text().trim()
        const nextCol1Html = $($nextTds[1]).html()

        // Check if this is an option row (indented, no price)
        if (
          nextCol1Html &&
          nextCol1Html.includes('&nbsp;&nbsp;&nbsp;&nbsp;') &&
          !nextCol2Text.includes('฿')
        ) {
          // Extract option text (after the nbsp characters)
          const optionSpans = $($nextTds[1]).find('span')
          optionSpans.each((_k, span) => {
            const spanText = $(span).text().trim()
            if (spanText.length > 0 && spanText !== '') {
              options.push(spanText)
            }
          })
          j++
        } else {
          break
        }
      }

      result.items.push({
        quantity,
        name,
        options,
        price,
      })
    }
    // Check for subtotal row
    else if (col1Text === 'ค่าอาหาร' && col2Text.includes('฿')) {
      const subtotalMatch = col2Text.match(/฿\s*([\d,]+)/)
      if (subtotalMatch) {
        result.subtotal = parseFloat(subtotalMatch[1].replace(/,/g, ''))
      }
    }
    // Check for delivery fee row
    else if (col1Text === 'ค่าจัดส่ง' && col2Text.includes('฿')) {
      const feeMatch = col2Text.match(/฿\s*([\d,]+)/)
      if (feeMatch) {
        result.deliveryFee = parseFloat(feeMatch[1].replace(/,/g, ''))
      }
    }
    // Check for discount rows (any row with negative amount)
    else if (col2Text.includes('-') && col2Text.includes('฿')) {
      const discountMatch = col2Text.match(/-\s*฿\s*([\d,]+)/)
      if (discountMatch && col1Text.length > 0) {
        result.discounts.push({
          description: col1Text,
          amount: parseFloat(discountMatch[1].replace(/,/g, '')),
        })
      }
    }
    // Check for total row
    else if (col1Text.includes('รวม') && col2Text.includes('฿')) {
      const totalMatch = col2Text.match(/฿\s*([\d,]+)/)
      if (totalMatch) {
        result.total = parseFloat(totalMatch[1].replace(/,/g, ''))
      }
    }
  }

  return result
}

async function parseGrabEmail(emlPath) {
  const parsed = await readEmailFile(emlPath)

  // Debug mode: print raw HTML
  if (process.env.DEBUG_HTML) {
    console.log('=== RAW HTML ===')
    console.log(parsed.html)
    console.log('\n=== END HTML ===\n')
  }

  // Debug mode: print all text content
  if (process.env.DEBUG_TEXT && parsed.html) {
    const $ = cheerio.load(parsed.html)
    console.log('=== ALL TEXT ===')
    console.log($('body').text())
    console.log('\n=== END TEXT ===\n')
  }

  const result = {
    from: parsed.from?.text,
    to: parsed.to?.text,
    subject: parsed.subject,
    date: parsed.date,
  }

  if (parsed.html) {
    const receiptData = parseGrabReceiptHtml(parsed.html)
    Object.assign(result, receiptData)
  }

  return result
}

const result = await parseGrabEmail(process.argv[2])
console.log(JSON.stringify(result, null, 2))