update

appsscript.json

{
  "timeZone": "Asia/Ho_Chi_Minh",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Gmail",
        "version": "v1",
        "serviceId": "gmail"
      },
      {
        "userSymbol": "Sheets",
        "version": "v4",
        "serviceId": "sheets"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

main.gs

/*
references:
- https://developers.google.com/gmail/api/quickstart/apps-script
- https://developers.google.com/apps-script/reference/gmail
*/

function listLabels() {
  /**
   * Lists all labels in the user's mailbox
   * @see https://developers.google.com/gmail/api/reference/rest/v1/users.labels/list
   */
  try {
    // Gmail.Users.Labels.list() API returns the list of all Labels in user's mailbox
    const response = Gmail.Users.Labels.list('me');
    if (!response || response.labels.length === 0) {
      // TODO (developer) - No labels are returned from the response
      console.log('No labels found.');
      return;
    }
    // Print the Labels that are available.
    console.log('Labels:');
    for (const label of response.labels ) {
      console.log('- %s', label.name);
    }
  } catch (err) {
    // TODO (developer) - Handle exception on Labels.list() API
    console.log('Labels.list() API failed with error %s', err.toString());
  }
}

function parse_currency(str) {
  try {
    return parseInt(/₫?([\d,]+)₫?/.exec(str)[1].replace(",", ""));
  }
  catch {
    console.log(str);
  }
}

function parse_message(message) {
  var msg_subject = message.getSubject();
  var msg_body = message.getPlainBody();
  console.log(msg_subject, message.getFrom(), message.getDate());
  var order_id_matcher = /^Đơn hàng #(\w+) đã giao hàng thành công$/g.exec(msg_subject);
  var product_info_list = [];
  var result = [];
  if (order_id_matcher) {
    var order_id = order_id_matcher[1];
    var lines = msg_body.split('\n').map(s => s.trim()).filter(s => s && !s.startsWith("<https://"));
    var idx_start = lines.findIndex(s => s == "THÔNG TIN ĐƠN HÀNG - DÀNH CHO NGƯỜI MUA");
    var idx_end = lines.findIndex(s => s == "BƯỚC TIẾP THEO");
    lines = lines.slice(idx_start + 1, idx_end);

    result = [null, message.getDate(), message.getFrom(), order_id, null, 0, 0, 0, 0, 0];

    msg_body = lines.join("\n");
    console.log(order_id);
    var temp = "";
    for (const line of lines) {
      if (line.startsWith("Mã đơn hàng: #") || line.startsWith("Người bán: ") || line.startsWith("Tin nhắn đến Người bán: ")) {
        continue;
      }
      else if (line.startsWith("Ngày đặt hàng: ")) {
        console.log(line);
        let m = /: (\d{2})\/(\d{2})\/(\d{4}) (\d{2}:\d{2}:\d{2})$/gi.exec(line);
        result[0] = new Date(`${m[3]}/${m[2]}/${m[1]} ${m[4]}`);
      }
      else if (line.startsWith("Số lượng: ")) {
        // console.log(temp);
        let num = parseInt(/^Số lượng: (\d+)$/.exec(line)[1]);
        // console.log(line);
        if (temp) {
          product_info_list.push([temp]);
        }
        product_info_list[product_info_list.length - 1].push(num);
        temp = "";
      }
      else if (line.startsWith("Giá: ")) {
        // console.log(line);
        let num = parse_currency(line);
        product_info_list[product_info_list.length - 1].push(num);
      }
      else if (line.startsWith("Tổng tiền: ")) {
        // console.log(line, parse_currency(line));
        result[5] = parse_currency(line);
      }
      else if (line.startsWith("Voucher từ Shopee: ")) {
        // console.log(line, parse_currency(line));
        result[7] = parse_currency(line);
      }
      else if (line.startsWith("Voucher ")) {
        // console.log(line, parse_currency(line));
        result[6] += parse_currency(line);
      }
      else if (line.startsWith("Phí vận chuyển: ")) {
        // console.log(line, parse_currency(line));
        result[8] = parse_currency(line);
      }
      else if (line.startsWith("Tổng thanh toán: ")) {
        // console.log(line, parse_currency(line));
        result[9] = parse_currency(line);
      } else {
        temp += line + " ";
      }
    }
    result[4] = product_info_list;
  }
  return result;
}

function main() {
  // B1. lấy các thông tin từ email đến
  // B2. cập nhật ngày giờ vào gg sheet
  var wb = SpreadsheetApp.open(DriveApp.getFileById(GOOGLE_SHEET_ID));
  var sheet_overall = wb.getSheetByName("orders_overall");
  var sheet_list = wb.getSheetByName("product_list");

  var threads = GmailApp.search('label:-e-market').sort();
  let row_id1 = 2, row_id2 = 2;
  for (const thread of threads) {
    var messages = thread.getMessages();
    for (const message of messages) {
      let parsed_info = parse_message(message);
      console.log(parsed_info);
      if (parsed_info.length) {
        sheet_overall.getRange(row_id1, 1, 1, parsed_info.length - 1).setValues([parsed_info.filter((v, j) => j != 4)]);
        row_id1++;
        sheet_list.getRange(row_id2, 1, parsed_info[4].length, 1 + parsed_info[4][0].length).setValues(parsed_info[4].map(u => [parsed_info[3], ...u]));
        row_id2 += parsed_info[4].length;
      }
    }
  }
}