ให้ LINE ถ่ายและเก็บ ข้อมูลสลิปโอนเงิน หรือเอกสาร ด้วย ChatGPT API ใน Google Sheets

				
					// Configuration
const LINE_CHANNEL_ACCESS_TOKEN = '.....';
const OPENAI_API_KEY = '......';
const SPREADSHEET_ID = '.....';
const DRIVE_FOLDER_ID = '.... ';

// Webhook entry point
function doPost(e) {
  const event = JSON.parse(e.postData.contents).events[0];
  
  if (event.type === 'message') {
    if (event.message.type === 'image') {
      handleImageMessage(event);
    } else if (event.message.type === 'text' && event.message.text === 'สรุป') {
      handleSummaryRequest(event);
    }
  }
  
  return ContentService.createTextOutput(JSON.stringify({ status: 'ok' }))
    .setMimeType(ContentService.MimeType.JSON);
}

// Handle incoming image message
function handleImageMessage(event) {
  try {
    const imageBytes = getImageFromLine(event.message.id);
    const imageFile = saveImageToDrive(imageBytes, event.message.id);
    const apiResult = getImageTextFromOpenAI(imageFile.getDownloadUrl());
    const paymentDetails = extractPaymentDetails(apiResult.text);
    
    // Save to spreadsheet
    saveToSpreadsheet({
      ...paymentDetails,
      userId: event.source.userId,
      tokens: apiResult.tokens.total_tokens
    });
    
    // Send Flex Message response
    replyToUser(event.replyToken, {
      referenceNo: paymentDetails.referenceNo,
      amount: paymentDetails.amount,
      date: paymentDetails.date,
      userId: event.source.userId,
      tokens: apiResult.tokens.total_tokens
    });
    
  } catch (error) {
    replyToUser(event.replyToken, 'ขออภัย เกิดข้อผิดพลาดในการประมวลผล: ' + error.toString());
    console.error(error);
  }
}

// Handle summary request (ส่ง Flex Message พร้อมปุ่มแทนการส่งลิงก์ตรง)
function handleSummaryRequest(event) {
  try {
    const userId = event.source.userId;
    const summaryUrl = createSecureViewUrl(userId); 
    // แทนที่จะส่งข้อความ + URL ตรง ๆ ให้ส่ง Flex Message ที่มีปุ่มเปิด URL 
    const flexMessage = {
      type: "flex",
      altText: "สรุปรายการ",
      contents: {
        type: "bubble",
        body: {
          type: "box",
          layout: "vertical",
          contents: [
            {
              type: "text",
              text: "สรุปรายการ",
              weight: "bold",
              color: "#037bfc",
              size: "md"
            },
            {
              type: "text",
              text: "กดปุ่มด้านล่างเพื่อดูรายการทั้งหมด",
              margin: "md",
              wrap: true
            }
          ]
        },
        footer: {
          type: "box",
          layout: "vertical",
          spacing: "sm",
          contents: [
            {
              type: "button",
              style: "primary",
              height: "sm",
              action: {
                type: "uri",
                label: "สรุปรายการ",
                uri: summaryUrl
              },
              color: "#037bfc"
            }
          ],
          flex: 0
        }
      }
    };
    
    replyToUser(event.replyToken, flexMessage);
    
  } catch (error) {
    replyToUser(event.replyToken, 'ขออภัย เกิดข้อผิดพลาดในการสร้างสรุป: ' + error.toString());
    console.error(error);
  }
}

// Create secure web app URL
function createSecureViewUrl(userId) {
  const webAppUrl = ScriptApp.getService().getUrl();
  const hash = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256, 
    userId + new Date().toDateString(), 
    Utilities.Charset.UTF_8
  );
  const token = Utilities.base64EncodeWebSafe(hash);
  return `${webAppUrl}?userId=${userId}&token=${token}`;
}

// Web app entry point (for summary view)
function doGet(e) {
  const userId = e.parameter.userId;
  const token = e.parameter.token;

  // Verify token
  const hash = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256, 
    userId + new Date().toDateString(), 
    Utilities.Charset.UTF_8
  );
  const expectedToken = Utilities.base64EncodeWebSafe(hash);
  
  if (!userId || !token || token !== expectedToken) {
    return HtmlService.createHtmlOutput('Access Denied');
  }
  
  // สร้างหน้า HTML โดยส่งพารามิเตอร์ทั้งหมดไป
  const html = createSummaryHtml(userId);
  return HtmlService.createHtmlOutput(html)
    .setTitle('สรุปรายการ')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

// Create HTML summary view
function createSummaryHtml(userId) {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = ss.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  
  // Filter data for this user
  let userData = data.filter(row => row[3] === userId); // Assuming userId is in column D
  
  // Calculate totals
  const totals = userData.reduce((acc, row) => {
    return {
      amount: acc.amount + Number(row[1] || 0),
      tokens: acc.tokens + Number(row[4] || 0)
    };
  }, { amount: 0, tokens: 0 });
  
  // สร้าง Form สำหรับ Filter
  let html = `
    <!DOCTYPE html>
    <html>
    <head>
      <base target="_top">
      <meta charset="UTF-8">
      <link data-minify="1" rel="stylesheet" type="text/css" href="//insights.nconnect.asia/wp-content/cache/min/1/1.11.3/css/jquery.dataTables.css?ver=1738170223">
      <script data-minify="1" type="text/javascript" charset="utf8" src="//insights.nconnect.asia/wp-content/cache/min/1/jquery-3.5.1.js?ver=1738170223"></script>
      <script data-minify="1" type="text/javascript" charset="utf8" src="//insights.nconnect.asia/wp-content/cache/min/1/1.11.3/js/jquery.dataTables.js?ver=1738170223"></script>
      <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        table { border-collapse: collapse; width: 100%; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f5f5f5; }
        .total-row { font-weight: bold; background-color: #f8f9fa; }
        .amount { text-align: right; }
      </style>
      <script>
        $(document).ready(function() {
          $('#summaryTable').DataTable({
            "pageLength": 10 // จำนวนรายการต่อหน้า
          });
        });
      </script>
    </head>
    <body>
      <h2>สรุปรายการ</h2>
      
      <table id="summaryTable">
        <thead>
          <tr>
            <th>วันที่</th>
            <th>เลขอ้างอิง</th>
            <th>จำนวนเงิน</th>
            <th>Token ที่ใช้</th>
          </tr>
        </thead>
        <tbody>`;
  
  // Add data rows
  userData.forEach(row => {
    const amountNum = Number(row[1]) || 0;
    const amountText = amountNum.toLocaleString('th-TH', {
      minimumFractionDigits: 2, 
      maximumFractionDigits: 2
    });
    html += `
          <tr>
            <td>${row[2]}</td>
            <td>${row[0]}</td>
            <td class="amount">${amountText}</td>
            <td class="amount">${row[4] || 0}</td>
          </tr>`;
  });
  
  // Add total row
  html += `
          <tr class="total-row">
            <td colspan="2">รวมทั้งหมด</td>
            <td class="amount">${totals.amount.toLocaleString('th-TH', {
              minimumFractionDigits: 2, 
              maximumFractionDigits: 2
            })}</td>
            <td class="amount">${totals.tokens.toLocaleString('th-TH')}</td>
          </tr>
        </tbody>
      </table>
      <h3>ยอดรวมทั้งหมด: ${totals.amount.toLocaleString('th-TH', {
        minimumFractionDigits: 2, 
        maximumFractionDigits: 2
      })} บาท</h3>
      <h3>Token ที่ใช้ทั้งหมด: ${totals.tokens.toLocaleString('th-TH')}</h3>
    </body>
    </html>`;
  
  return html;
}

/**
 * ฟังก์ชันช่วย parse datetime (ถ้าทำได้)
 * คืนเป็น Date object หรือ null
 */
function tryParseDateTime(dateStr) {
  try {
    // ถ้าข้างในมีทั้งวันและเวลา เช่น "2024-01-29 14:30:25" หรือ "29/01/2024 14:30:25"
    // ลองแยกกัน และแก้ปีไทย (ถ้ามี 25xx) 
    let maybeStr = dateStr.trim();
    // Replace dash เป็น slash บ้าง ถ้าจะให้ new Date() parse ได้ง่ายขึ้น
    maybeStr = maybeStr.replace(/-/g, '/');
    
    // เช็คปีไทย
    const yearMatch = maybeStr.match(/(\d{4})/);
    if (yearMatch) {
      const y = parseInt(yearMatch[1], 10);
      if (y > 2400) {
        maybeStr = maybeStr.replace(y, (y - 543).toString());
      }
    }
    
    const dt = new Date(maybeStr);
    if (isNaN(dt.getTime())) return null;
    return dt;
  } catch (err) {
    return null;
  }
}

// คืน token ที่ใช้ในวันนี้สำหรับ user เดิม (เอาไว้ใส่ hidden form กัน query ผิด)
function getTodayToken(userId) {
  const hash = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256, 
    userId + new Date().toDateString(), 
    Utilities.Charset.UTF_8
  );
  return Utilities.base64EncodeWebSafe(hash);
}

// Get image content from LINE
function getImageFromLine(messageId) {
  const url = `https://api-data.line.me/v2/bot/message/${messageId}/content`;
  const response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' + LINE_CHANNEL_ACCESS_TOKEN
    }
  });
  return response.getBlob();
}

// Save image to Google Drive
function saveImageToDrive(imageBlob, messageId) {
  const folder = DriveApp.getFolderById(DRIVE_FOLDER_ID);
  const filename = `slip_${messageId}_${new Date().getTime()}.jpg`;
  return folder.createFile(imageBlob.setName(filename));
}

// Get image text using OpenAI Vision API
function getImageTextFromOpenAI(imageUrl) {
  const url = 'https://api.openai.com/v1/chat/completions';
  const payload = {
    model: "gpt-4o-mini",
    messages: [
      {
        role: "user",
        content: [
          {
            type: "text",
            text: "Please extract the following information from this Thai bank transfer slip:\n1. Reference number (look for 'รหัสอ้างอิง' or 'เลขที่รายการ')\n2. Amount (in Thai Baht)\n3. Transaction date and time (look for 'วันที่', 'เวลา', 'วันที่-เวลา' - include both date and time if available)\n\nReturn ONLY a JSON object with keys: referenceNo, amount, date, time. Example: {\"referenceNo\":\"REF123456\",\"amount\":\"1000.00\",\"date\":\"2024-01-29\",\"time\":\"14:30:25\"}"
          },
          {
            type: "image_url",
            image_url: {
              url: imageUrl
            }
          }
        ]
      }
    ],
    max_tokens: 300
  };
  
  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    headers: {
      'Authorization': 'Bearer ' + OPENAI_API_KEY,
      'Content-Type': 'application/json'
    },
    payload: JSON.stringify(payload)
  });
  
  const result = JSON.parse(response.getContentText());
  return {
    text: result.choices[0].message.content,
    tokens: result.usage
  };
}

// Extract payment details from OCR text
function extractPaymentDetails(apiResponse) {
  try {
    let content = apiResponse;
    if (content.includes('```json')) {
      content = content.replace(/```json\n?|```/g, '');
    }
    const parsedContent = JSON.parse(content);
    
    // Format date and time
    let formattedDateTime = parsedContent.date || '';
    if (parsedContent.time) {
      // Convert Thai Buddhist year to CE if needed
      if (formattedDateTime.includes('/')) {
        const parts = formattedDateTime.split('/');
        if (parts[2] && parseInt(parts[2]) > 2500) {
          parts[2] = (parseInt(parts[2]) - 543).toString();
          formattedDateTime = parts.join('/');
        }
      }
      
      // Add time if available
      formattedDateTime += ' ' + parsedContent.time;
      
      // Try to parse and format the date-time
      try {
        const dt = new Date(formattedDateTime);
        if (!isNaN(dt.getTime())) {
          formattedDateTime = dt.toLocaleString('th-TH', {
            year: 'numeric',
            month: '2-digit',
            day: '2-digit',
            hour: '2-digit',
            minute: '2-digit',
            second: '2-digit',
            hour12: false
          });
        }
      } catch (e) {
        console.error('Error formatting date-time:', e);
      }
    }
    
    return {
      referenceNo: parsedContent.referenceNo || '',
      amount: parsedContent.amount || '',
      date: formattedDateTime
    };
  } catch (error) {
    console.error('Error parsing response:', error);
    console.log('Raw API Response:', apiResponse);
    throw new Error('ไม่สามารถอ่านข้อมูลจากรูปภาพได้ กรุณาลองใหม่อีกครั้ง');
  }
}

// Save data to spreadsheet
function saveToSpreadsheet(details) {
  const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getActiveSheet();
  sheet.appendRow([
    details.referenceNo,
    details.amount,
    details.date,
    details.userId,
    details.tokens
  ]);
}

// Reply to user on LINE with Flex Message or Text
function replyToUser(replyToken, messageData) {
  const url = 'https://api.line.me/v2/bot/message/reply';
  
  // ถ้าเป็น string ให้ส่งข้อความตัวหนังสือ
  if (typeof messageData === 'string') {
    const payload = {
      replyToken: replyToken,
      messages: [{
        type: 'text',
        text: messageData
      }]
    };
    
    UrlFetchApp.fetch(url, {
      method: 'post',
      headers: {
        'Authorization': 'Bearer ' + LINE_CHANNEL_ACCESS_TOKEN,
        'Content-Type': 'application/json'
      },
      payload: JSON.stringify(payload)
    });
    return;
  }
  
  // ถ้าเป็น object ให้ส่ง Flex Message
  // กรณีที่เป็นการตอบกลับหลังบันทึกภาพ
  if (messageData.referenceNo) {
    const summaryUrl = createSecureViewUrl(messageData.userId);
    const flexMessage = {
      type: "flex",
      altText: "บันทึกข้อมูลสำเร็จ",
      contents: {
        type: "bubble",
        body: {
          type: "box",
          layout: "vertical",
          contents: [
            {
              type: "text",
              text: "บันทึกข้อมูลสำเร็จ:",
              weight: "bold",
              color: "#037bfc",
              size: "md"
            },
            {
              type: "box",
              layout: "vertical",
              margin: "lg",
              spacing: "sm",
              contents: [
                {
                  type: "box",
                  layout: "horizontal",
                  contents: [
                    {
                      type: "text",
                      text: "เลขอ้างอิง:",
                      size: "sm",
                      color: "#666666",
                      flex: 3
                    },
                    {
                      type: "text",
                      text: messageData.referenceNo,
                      size: "sm",
                      color: "#333333",
                      flex: 7,
                      wrap: true
                    }
                  ]
                },
                {
                  type: "box",
                  layout: "horizontal",
                  contents: [
                    {
                      type: "text",
                      text: "จำนวนเงิน:",
                      size: "sm",
                      color: "#666666",
                      flex: 3
                    },
                    {
                      type: "text",
                      text: messageData.amount + " บาท",
                      size: "sm",
                      color: "#333333",
                      flex: 7,
                      wrap: true
                    }
                  ]
                },
                {
                  type: "box",
                  layout: "horizontal",
                  contents: [
                    {
                      type: "text",
                      text: "วันที่:",
                      size: "sm",
                      color: "#666666",
                      flex: 3
                    },
                    {
                      type: "text",
                      text: messageData.date,
                      size: "sm",
                      color: "#333333",
                      flex: 7,
                      wrap: true
                    }
                  ]
                },
                {
                  type: "box",
                  layout: "horizontal",
                  contents: [
                    {
                      type: "text",
                      text: "Token ที่ใช้:",
                      size: "sm",
                      color: "#666666",
                      flex: 3
                    },
                    {
                      type: "text",
                      text: messageData.tokens.toString(),
                      size: "sm",
                      color: "#333333",
                      flex: 7,
                      wrap: true
                    }
                  ]
                }
              ]
            }
          ]
        },
        footer: {
          type: "box",
          layout: "vertical",
          spacing: "sm",
          contents: [
            {
              type: "button",
              style: "primary",
              height: "sm",
              action: {
                type: "uri",
                label: "ดูรายการเดินบัญชี",
                uri: summaryUrl
              },
              color: "#037bfc"
            }
          ],
          flex: 0
        }
      }
    };

    const payload = {
      replyToken: replyToken,
      messages: [flexMessage]
    };

    UrlFetchApp.fetch(url, {
      method: 'post',
      headers: {
        'Authorization': 'Bearer ' + LINE_CHANNEL_ACCESS_TOKEN,
        'Content-Type': 'application/json'
      },
      payload: JSON.stringify(payload)
    });
  }
  // ถ้าเป็น Flex สำหรับสรุปรายการ
  else {
    const payload = {
      replyToken: replyToken,
      messages: [messageData] // messageData เป็น flex message อยู่แล้ว
    };
    
    UrlFetchApp.fetch(url, {
      method: 'post',
      headers: {
        'Authorization': 'Bearer ' + LINE_CHANNEL_ACCESS_TOKEN,
        'Content-Type': 'application/json'
      },
      payload: JSON.stringify(payload)
    });
  }
}