OCR Read - Tiktok Shop

plain texthttps://docs.google.com/spreadsheets/d/SHEET_ID/edit

plain texthttps://drive.google.com/drive/folders/FOLDER_ID

javascriptvar folderId = 'FOLDER_ID_ของคุณ';
var sheetId = 'SHEET_ID_ของคุณ';

javascript// ==========================================
// 🎵  TIKTOK Shop
// ==========================================
function syncPdfToSheet_E_Tiktok() {
  var folderId = 'FOLDER_ID_ของคุณ';
  var sheetId = 'SHEET_ID_ของคุณ';

  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByType(MimeType.PDF);
  var spreadsheet = SpreadsheetApp.openById(sheetId);
  var sheet = spreadsheet.getSheetByName("E-TIKTOK");

  if (!sheet) { Logger.log("❌ ไม่พบชีตที่ชื่อ 'E-TIKTOK'"); return; }

  var sheetFileIds = new Map(); 
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    var pdfUrl = data[i][0];
    if (pdfUrl.includes("drive.google.com/file/d/")) {
      var fileId = pdfUrl.split("/d/")[1].split("/")[0]; 
      if (sheetFileIds.has(fileId)) { sheet.deleteRow(i + 1); i--; } 
      else { sheetFileIds.set(fileId, i + 1); }
    }
  }

  var newFiles = [];
  var existingFiles = new Set();
  while (files.hasNext()) {
    var file = files.next();
    var fileId = file.getId();
    var pdfUrl = "https://drive.google.com/file/d/" + fileId;
    existingFiles.add(fileId);
    if (!sheetFileIds.has(fileId)) { newFiles.push({ id: fileId, url: pdfUrl }); }
  }

  var rowsToDelete = [];
  for (let [fileId, rowIndex] of sheetFileIds) {
    if (!existingFiles.has(fileId)) { rowsToDelete.push(rowIndex); }
  }
  rowsToDelete.sort((a, b) => b - a).forEach(row => sheet.deleteRow(row));

  newFiles.forEach(file => {
    try {
      var docFile = Drive.Files.copy({ mimeType: MimeType.GOOGLE_DOCS }, file.id);
      Utilities.sleep(1000);
      var docId = docFile.id;
      var doc = DocumentApp.openById(docId);
      var text = doc.getBody().getText().trim();
      DriveApp.getFileById(docId).setTrashed(true);

      if (!text) { sheet.appendRow([file.url, "Conversion Failed: Empty Document"]); return; }

      var lines = text.split('\n').map(line => line.trim());
      if (text.includes("Platform Service Fee")) { processPlatformServiceFee(sheet, file.url, lines); } 
      else if (text.includes("Logistics fee")) { processLogisticsFee(sheet, file.url, lines); } 
      else if (text.includes("Creator commission")) { processCreatorCommission(sheet, file.url, lines); } 
      else { sheet.appendRow([file.url, "Unknown document type"]); }
    } catch (e) {
      sheet.appendRow([file.url, 'Conversion Failed: ' + e.message]);
    }
  });
  Logger.log("✅ Tiktok อัปเดตเสร็จสิ้น");
}

// ------------------------------------------------------------------
// 1. ฟังก์ชันจัดการ Platform Service Fee
// ------------------------------------------------------------------
function processPlatformServiceFee(sheet, pdfUrl, lines) {
  var documentNo = "Not Found";
  var date = "Not Found";
  var period = "Not Found";
  var subtotal = "Not Found";
  var vat = "Not Found";
  var total = "Not Found";
  var description = "Platform Service Fee";

  for (var i = 0; i < lines.length; i++) {
    var line = lines[i];
    
    if (line.includes("Invoice number") || line.includes("Invoice date")) {
      var match = line.match(/Invoice number[：:]\s*(.*?)\s*Invoice date[：:]\s*(.*?)\s*Period[：:]\s*(.*)/);
      if (match) {
        documentNo = match[1].trim();
        date = match[2].trim();
        period = match[3].trim();
      }
    }
    
    if (line.includes("Subtotal (excluding VAT)")) {
      var amountMatch = line.match(/[฿B](\d+(?:,\d+)*(?:\.\d+)?)/);
      if (amountMatch) subtotal = amountMatch[1].replace(/,/g, '');
    }
    
    if (line.includes("Total VAT")) {
      var vatMatch = line.match(/[฿B](\d+(?:,\d+)*(?:\.\d+)?)/);
      if (vatMatch) vat = vatMatch[1].replace(/,/g, '');
    }
    
    if (line.includes("Total amount") && line.includes("including VAT")) {
      var totalMatch = line.match(/[฿B](\d+(?:,\d+)*(?:\.\d+)?)/);
      if (totalMatch) total = totalMatch[1].replace(/,/g, '');
    }
  }
  
  // พิมพ์ลงชีตพร้อมแปลงวันที่
  sheet.appendRow([pdfUrl, documentNo, formatDateToDDMMYYYY(date), formatDateToDDMMYYYY(period), description, subtotal, vat, total]);
}

// ------------------------------------------------------------------
// 2. ฟังก์ชันจัดการ Logistics Fee
// ------------------------------------------------------------------
function processLogisticsFee(sheet, pdfUrl, lines) {
  var documentNo = "Not Found";
  var date = "Not Found";
  var period = "Not Found";
  var amount = "Not Found";
  var vat = "0"; 
  var total = "Not Found";
  var description = "Logistics fee";

  for (var i = 0; i < lines.length; i++) {
    var line = lines[i];
    
    if (line.includes("Receipt number") || line.includes("Receipt date")) {
      var match = line.match(/Receipt number[：:]\s*(.*?)\s*Receipt date[：:]\s*(.*?)\s*Period[：:]\s*(.*)/);
      if (match) {
        documentNo = match[1].trim();
        date = match[2].trim();
        period = match[3].trim();
      }
    }
    
    if (line.includes("Total Amount")) {
      var match = line.match(/Total Amount\s*[฿B](\d+(?:,\d+)*(?:\.\d+)?)/);
      if (match) {
        total = match[1].replace(/,/g, '');
        amount = total; 
      }
    }
  }
  
  // พิมพ์ลงชีตพร้อมแปลงวันที่
  sheet.appendRow([pdfUrl, documentNo, formatDateToDDMMYYYY(date), formatDateToDDMMYYYY(period), description, amount, vat, total]);
}

// ------------------------------------------------------------------
// 3. ฟังก์ชันจัดการ Creator Commission
// ------------------------------------------------------------------
function processCreatorCommission(sheet, pdfUrl, lines) {
  try {
    var receiptData = extractCreatorReceiptData(findLineContaining(lines, "Receipt Number :"));
    var periodLine = findLineContaining(lines, "Period :");
    var period = periodLine ? periodLine.split("Period :")[1].trim() : "Not Found";
    var commissionLine = findLineContaining(lines, "Creator commission");
    var commission = commissionLine ? extractNumber(commissionLine) : "Not Found";
    var totalAmountLine = findLineContaining(lines, "Total Amount");
    var totalAmount = totalAmountLine ? extractNumber(totalAmountLine) : "Not Found";
    
    // พิมพ์ลงชีตพร้อมแปลงวันที่
    sheet.appendRow([pdfUrl, receiptData.number || "Not Found", formatDateToDDMMYYYY(receiptData.date) || "Not Found", formatDateToDDMMYYYY(period) || "Not Found", commission || "Creator commission", totalAmount || "Not Found", 0, totalAmount || "Not Found"]);
  } catch (e) { sheet.appendRow([pdfUrl, 'Error processing Creator commission: ' + e.message]); }
}

// ------------------------------------------------------------------
// ฟังก์ชันตัวช่วยต่างๆ 
// ------------------------------------------------------------------

// ฟังก์ชันแปลงวันที่ เดือนภาษาอังกฤษ เป็น DD/MM/YYYY
function formatDateToDDMMYYYY(dateString) {
  if (!dateString || dateString === "Not Found") return dateString;
  
  var months = {
    "Jan": "01", "Feb": "02", "Mar": "03", "Apr": "04", "May": "05", "Jun": "06",
    "Jul": "07", "Aug": "08", "Sep": "09", "Oct": "10", "Nov": "11", "Dec": "12"
  };

  function parseSingleDate(str) {
    // หาเดือนแบบตัวอักษร วันที่ และปี คั่นด้วยเครื่องหมาย , หรือช่องว่าง
    var match = str.trim().match(/([a-zA-Z]+)\s+(\d+),?\s+(\d{4})/);
    if (match) {
      var month = months[match[1].substring(0, 3)] || "00";
      var day = ("0" + match[2]).slice(-2); // เติม 0 ข้างหน้าถ้าเป็นเลขหลักเดียว
      var year = match[3];
      return day + "/" + month + "/" + year;
    }
    return str.trim();
  }

  // แยกรอบบิล (Period) ที่คั่นด้วย - เพื่อแปลงทั้งสองฝั่ง
  if (dateString.includes("-")) {
    var parts = dateString.split("-");
    return parseSingleDate(parts[0]) + " - " + parseSingleDate(parts[1]);
  } else {
    return parseSingleDate(dateString);
  }
}

function extractCreatorReceiptData(receiptLine) {
  if (!receiptLine) return { number: null, date: null };
  var result = { number: null, date: null };
  try {
    var numberPart = receiptLine.split("Receipt Number :")[1] || "";
    result.number = numberPart.split("Receipt Date :")[0].trim();
    var datePart = receiptLine.split("Receipt Date :")[1] || "";
    result.date = datePart.trim();
    if (result.number.includes("Creator Username:")) {
      result.number = result.number.split("Creator Username:")[1].trim();
    }
  } catch (e) {}
  return result;
}

function findLineContaining(lines, searchText) {
  var foundLine = lines.find(line => line.includes(searchText));
  return foundLine ? foundLine.trim() : null;
}

function extractNumber(line) {
  if (!line) return null;
  try {
    var bahtIndex = line.indexOf("฿");
    if (bahtIndex !== -1) return parseFloat(line.substring(bahtIndex + 1).trim().replace(/,/g, ''));
    var matches = line.match(/(\d+\.?\d*)/);
    if (matches && matches[0]) return parseFloat(matches[0]);
  } catch (e) {}
  return null;
}