appscript

appscript.gs

const AMAZON_SELLER_NOTIFICATION_LABEL_NAME = "Amazon Seller Notification";
const NOTIFICATION_SUBJECT_PREFIX = "Sold, ship now:";
const ORDER_ID_REGEX = "Order ID: 113-3770207-6613845";
const MANAGE_ACCOUNT_SHEET_NAME = "MANAGE ACCOUNT";
const AMZ_MAILS_SHEET_NAME = "AMZ MAILS";
const IGNORED_ORDERS_SHEET_NAME = "Ignored Orders";
const _ = LodashGS.load();

const ORDER_DETAIL_MAPPINGS = {
  shipBy: /(?:Ship by: )(.+)\n/,
  design: /(?:Item: )((.|\n)+)(?:Condition)/,
  sku: /(?:SKU: )(.+)\n/,
  quantity: /(?:Quantity: )(.+)\n/,
  orderDate: /(?:Order date: )(.+)\n/,
  price: /(?:Price: \$)(.+)\n/,
  shipping: /(?:Shipping: \$)(.+)\n/,
  earnings: /(?:Your earnings: \$)(.+)/,
};
const BACKGROUND_COLORS = ['#ffe599', '#9fc5e8'];

function syncMessages() {
  const LABEL = GmailApp.getUserLabelByName("Amazon Seller Notification");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(AMZ_MAILS_SHEET_NAME);
  const values = sheet.getDataRange().getValues();
  const threads = LABEL.getThreads();
  let data = threads.map((notification) => {
    notification.getMessages().forEach(message => {
      if (values.find((item) => item[0] === message.getId()) === undefined) {
        Logger.log(message.getBody());
        values.push([message.getId(), message.getPlainBody(), message.getTo()]);
        notification.removeLabel(LABEL)
      }
    });
  });
  let range = sheet.getRange(1,1,values.length, values[0].length);
  range.setValues(values);
}

function aggregate() {
  // _reset();

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const manageAccountSheet = ss.getSheetByName(MANAGE_ACCOUNT_SHEET_NAME);
  let accounts = manageAccountSheet.getDataRange().getValues();
  accounts.shift();
  let accountMappings = _.keyBy(accounts, (item) => item[0]);
  console.log(accountMappings);
  const dataRange = sheet.getDataRange();
  const oldValues = dataRange.getValues();
  /** get index of column */
  const header = oldValues.shift();
  const orderDateIndex = header.findIndex(h => h === "Order date");
  const supporterIndex = header.findIndex(h => h === "Supporter");
  const sellerIndex = header.findIndex(h => h === "Amazon Seller Acc");
  const orderIdIndex = header.findIndex(h => h === "Order ID Amazon");
  const designIndex = header.findIndex(h => h === "Design");
  const skuIndex = header.findIndex(h => h === "SKU");
  const quantityIndex = header.findIndex(h => h === "Quantity");
  const priceShippingIndex = header.findIndex(h => h === "Price + Shipping");
  const priceAfterTax = header.findIndex(h => h === "Price after tax");
  const shipByIndex = header.findIndex(h => h === "Ship by");
  const messageIdIndex = header.findIndex(h => h === "Message Id");
  const baseCostIndex = header.findIndex(h => h === "Base cost");
  /** end */
  
  /** get ignored order ids */
  const ignoredOrderValues = ss.getSheetByName(IGNORED_ORDERS_SHEET_NAME).getDataRange().getValues();
  ignoredOrderValues.shift();
  ignoredOrderIds = ignoredOrderValues.map((ignoredOrderValue) => ignoredOrderValue[0]);
  /** end */

  /** restore value from merged */
  oldValues.forEach((row) => {
    const messageIdParts = row[messageIdIndex].split("--");
    const idx = parseInt(messageIdParts[1]);
    if (idx > 0) {
      const baseRow = oldValues.find(v => v[messageIdIndex] === `${messageIdParts[0]}--0`);
      row[orderDateIndex] = baseRow[orderDateIndex];
      row[sellerIndex] = baseRow[sellerIndex];
      row[orderIdIndex] = baseRow[orderIdIndex];
      row[baseCostIndex] = baseRow[baseCostIndex];
    };
  });
  /** end */

  /** aggregate data */
  const emails = ss.getSheetByName(AMZ_MAILS_SHEET_NAME).getDataRange().getValues();
  emails.shift();
  let data = emails.map((email) => {
    return _getOrdersByMessage(email, oldValues, accountMappings);
  });
  let values = _.flatten(data);

  if (oldValues.length > 1) {
    values = _.concat(values, oldValues);
  }
  values = _.sortBy(values, [(item) => item[0], (item) => item[3]]);

  /** filter out ignored order ids */
  values = values.filter(value => ignoredOrderIds.indexOf(value[orderIdIndex]) === -1);
  
  /** resset */
  _reset();
  /** set values */
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);

  /** set colors */
  let colorFlag = false;
  let prevDate = values[0][orderDateIndex];
  for (let i = 0; i < values.length; i++) {
    let range = sheet.getRange(i+2, 1, 1, header.length);
    if (new Date(values[i][0]).getTime() !== new Date(prevDate).getTime()) {
      colorFlag = !colorFlag;
    }
    range.setBackground(colorFlag ? BACKGROUND_COLORS[0] : BACKGROUND_COLORS[1]);
    prevDate = values[i][orderDateIndex];
  }

  /** merge */
  let r = 0;
  while(r < values.length-1) {
    if (values[r][orderIdIndex] === values[r+1][orderIdIndex]) {
      let j = r;
      let count = 0;
      while(j < values.length && values[j][orderIdIndex] == values[r][orderIdIndex]) {
        j++;
        count++;
      }
      // console.log(count);
      sheet.getRange(sheet.getLastRow()-(values.length-r)+1, orderIdIndex+1, count, 1).mergeVertically();
      sheet.getRange(sheet.getLastRow()-(values.length-r)+1, sellerIndex+1, count, 1).mergeVertically();
      sheet.getRange(sheet.getLastRow()-(values.length-r)+1, orderDateIndex+1, count, 1).mergeVertically();
      sheet.getRange(sheet.getLastRow()-(values.length-r)+1, baseCostIndex+1, count, 1).mergeVertically();
      r = j;
    } else {
      r++;
    }
  }
}

function _reset() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const range = sheet.getRange("A2:V");
  if (range.getNumRows() > 0) {
    range.getMergedRanges().length > 0 && range.setBackground(null).breakApart();
    range.clearContent();
  }
}

function _getOrdersByMessage(email, oldValues, accountMappings) {
  const body = email[1];
  const orderId = _getOrderId(body);
  const messageId = email[0];
  const orders = [];
  _getOrderDetails(body).forEach((orderDetail, index) => {
    const id = `${messageId}--${index}`;
    const oldValue = _.find(oldValues, (item) => item[item.length-1] === id);
    if (oldValue !== undefined) {
      //console.log(oldValue);
    } else {
      const orderDetailStr = orderDetail.join("\n");
      const order = {};
      Object.keys(ORDER_DETAIL_MAPPINGS).forEach((key) => {
        const match = orderDetailStr.match(ORDER_DETAIL_MAPPINGS[key]);
        if (match) {
          order[key] = match[1];
        } else {
          Logger.log(orderDetailStr);
          throw new Error("Can not find: " + key);
        }
      });
      order['messageId'] = `${messageId}--${index}`;
      orders.push(order);
    }
  });

  // console.log(orders);

  const data = orders.map((order) => {
    return [
      new Date(order.orderDate), // order date
      '', // supporter
      '', // category
      accountMappings[email[2]][1], // seller
      orderId, // order
      order.design.split("\n").join(""), // design
      order.sku, // sku
      '', // print
      '', // base cost
      order.quantity, // quantity
      '$'+(parseFloat(order.price) + parseFloat(order.shipping)), // price shipping
      '$'+order.earnings, // price after tax
      new Date(order.shipBy), // ship by
      '', // Tình trạng Ship
      '', // Order ID Fullfill
      '', // Tracking ID
      '', // Carrier
      '', // Empty
      '', // Ghi chú
      '', // RETURN LABEL
      '', // CUSTOMER SHIPPED
      order.messageId
    ]
  });

  return data;
}

function _getOrderId(messageBody) {
  const match = messageBody.match(/(?:Order ID: )((\d|\-)+)\n/)
  if (match) {
    return match[1];
  }

  return '';
}

function _getOrderDetails(body) {
  const lines = body.split("\n");
  let i = 0;
  const ret = [];
  while(i < lines.length) {
    if (lines[i].match(/^Ship by:/)) {
      const orderDetailLines = [];
      while(!lines[i].match(/^Your earnings:/)) {
        orderDetailLines.push(lines[i++]);
      }
      orderDetailLines.push(lines[i]);
      ret.push(orderDetailLines);
    } else {
      i++;
    }
  }

  return ret;
}