Google Sheets

var ECONFIRME_WEBHOOK_URL = "https://econfirme.com/api/webhooks/order/YOUR_WEBHOOK_ID";
var ECONFIRME_COLUMNS = ["orderId","name","phone","product","quantity","price","city","address","state","zipCode","country","email","productId","optionName","optionDescription","totalAmount","currency","paymentMethod","notes","sendToEconfirme","econfirmeStatus","econfirmeError","econfirmeLastSyncAt"];
var ECONFIRME_REQUIRED_COLUMNS = ["orderId", "name", "phone", "product", "quantity", "price"];
var ECONFIRME_STATUS_COLUMNS = ["econfirmeStatus", "econfirmeError", "econfirmeLastSyncAt"];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("eConfirme")
    .addItem("Setup Sheet", "setupSheet")
    .addItem("Send Marked Rows", "sendMarkedRows")
    .addItem("Resend Failed Rows", "resendFailedRows")
    .addToUi();
}

function init() {
  setupSheet();
  ensureEconfirmeTriggers_();
  notifyEconfirmeConnection_();
  SpreadsheetApp.getActive().toast("eConfirme sheet is ready.", "eConfirme", 4);
}

function setupSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ensureHeaders_(sheet);
  applySheetDesign_(sheet);
  seedManagedCells_(sheet);
}

function ensureEconfirmeTriggers_() {
  var spreadsheet = SpreadsheetApp.getActive();
  var triggers = ScriptApp.getProjectTriggers();
  var hasEditTrigger = triggers.some(function(trigger) {
    return trigger.getHandlerFunction() === "handleEconfirmeEdit";
  });

  if (!hasEditTrigger) {
    ScriptApp.newTrigger("handleEconfirmeEdit").forSpreadsheet(spreadsheet).onEdit().create();
  }
}

function handleEconfirmeEdit(e) {
  if (!e || !e.range) return;

  var sheet = e.range.getSheet();
  if (e.range.getRow() === 1) return;

  var headers = getHeaders_(sheet);
  if (!headers.length) return;

  var editedColumn = headers[e.range.getColumn() - 1];
  if (!editedColumn || ECONFIRME_STATUS_COLUMNS.indexOf(editedColumn) !== -1) return;

  var orderId = getCellDisplayValue_(sheet, e.range.getRow(), headers, "orderId");
  if (!orderId) return;

  var syncEligibleColumns = [
    "orderId",
    "name",
    "phone",
    "product",
    "quantity",
    "price",
    "city",
    "address",
    "state",
    "zipCode",
    "country",
    "email",
    "productId",
    "optionName",
    "optionDescription",
    "totalAmount",
    "currency",
    "paymentMethod",
    "notes",
    "sendToEconfirme"
  ];

  if (syncEligibleColumns.indexOf(editedColumn) === -1) return;

  syncOrderGroups_(sheet, [String(orderId).trim()], {
    includeFailed: false,
    autoSync: true
  });
}

function sendMarkedRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  syncOrderGroups_(sheet, null, { markedOnly: true, includeFailed: false, autoSync: false });
}

function resendFailedRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  syncOrderGroups_(sheet, null, { markedOnly: false, includeFailed: true, autoSync: false });
}

function syncOrderGroups_(sheet, explicitOrderIds, options) {
  options = options || {};
  var grouped = collectGroupedOrders_(sheet, explicitOrderIds, options);
  var groupedOrders = grouped.groupedOrders;

  if (!groupedOrders.length) {
    SpreadsheetApp.getActive().toast("No eligible orders found to sync.", "eConfirme", 4);
    return;
  }

  groupedOrders.forEach(function(entry) {
    writeOrderResult_(sheet, entry.rowNumbers, {
      status: "Pending",
      message: "",
      keepSendFlag: true
    });
  });

  var payload = groupedOrders.map(function(entry) {
    return entry.order;
  });

  var response = postOrdersToEconfirme_(payload);
  var resultMap = {};

  if (response && Array.isArray(response.results)) {
    response.results.forEach(function(result) {
      if (result && result.orderId) {
        resultMap[String(result.orderId).trim()] = result;
      }
    });
  }

  groupedOrders.forEach(function(entry) {
    var orderId = String(entry.order.orderId).trim();
    var result = resultMap[orderId];

    if (!result) {
      writeOrderResult_(sheet, entry.rowNumbers, {
        status: "Failed",
        message: "No response received from eConfirme for this order.",
        keepSendFlag: false
      });
      return;
    }

    writeOrderResult_(sheet, entry.rowNumbers, {
      status: result.status === "failed" ? "Failed" : (result.status === "updated" ? "Updated" : "Imported"),
      message: result.status === "failed" ? (result.message || "Order sync failed") : "",
      keepSendFlag: result.status !== "failed"
    });
  });

  var successCount = groupedOrders.filter(function(entry) {
    var result = resultMap[String(entry.order.orderId).trim()];
    return result && result.status !== "failed";
  }).length;

  SpreadsheetApp.getActive().toast(successCount + " order(s) synced.", "eConfirme", 4);
}

function collectGroupedOrders_(sheet, explicitOrderIds, options) {
  options = options || {};
  var data = sheet.getDataRange().getDisplayValues();
  if (!data.length) return { groupedOrders: [] };

  var headers = data[0];
  var headerMap = buildHeaderMap_(headers);
  ensureRequiredColumns_(headerMap);

  var explicitMap = {};
  if (explicitOrderIds && explicitOrderIds.length) {
    explicitOrderIds.forEach(function(id) {
      explicitMap[String(id).trim()] = true;
    });
  }

  var selectedOrderIds = {};

  for (var index = 1; index < data.length; index += 1) {
    var row = data[index];
    if (isBlankRow_(row)) continue;

    var orderId = getRowCell_(row, headerMap, "orderId");
    if (!orderId) continue;
    orderId = String(orderId).trim();

    if (explicitOrderIds && explicitOrderIds.length) {
      if (explicitMap[orderId]) selectedOrderIds[orderId] = true;
      continue;
    }

    var status = String(getRowCell_(row, headerMap, "econfirmeStatus") || "").trim().toLowerCase();
    var hasError = String(getRowCell_(row, headerMap, "econfirmeError") || "").trim() !== "";

    if (options.autoSync) {
      selectedOrderIds[orderId] = true;
    }

    if (options.markedOnly && isTruthyValue_(getRowCell_(row, headerMap, "sendToEconfirme"))) {
      selectedOrderIds[orderId] = true;
    }

    if (options.includeFailed && (status === "failed" || hasError)) {
      selectedOrderIds[orderId] = true;
    }
  }

  var groupedMap = {};

  for (var rowIndex = 1; rowIndex < data.length; rowIndex += 1) {
    var currentRow = data[rowIndex];
    if (isBlankRow_(currentRow)) continue;

    var groupedOrderId = String(getRowCell_(currentRow, headerMap, "orderId") || "").trim();
    if (!groupedOrderId || !selectedOrderIds[groupedOrderId]) continue;

    if (!groupedMap[groupedOrderId]) {
      groupedMap[groupedOrderId] = {
        order: {
          orderId: groupedOrderId,
          externalOrderId: groupedOrderId,
          customer: {
            name: String(getRowCell_(currentRow, headerMap, "name") || "").trim(),
            phone: String(getRowCell_(currentRow, headerMap, "phone") || "").trim(),
            email: String(getRowCell_(currentRow, headerMap, "email") || "").trim(),
            address: {
              street: String(getRowCell_(currentRow, headerMap, "address") || "").trim(),
              city: String(getRowCell_(currentRow, headerMap, "city") || "").trim(),
              state: String(getRowCell_(currentRow, headerMap, "state") || "").trim(),
              zipCode: String(getRowCell_(currentRow, headerMap, "zipCode") || "").trim(),
              country: String(getRowCell_(currentRow, headerMap, "country") || "").trim() || "Morocco"
            }
          },
          shippingAddress: {
            street: String(getRowCell_(currentRow, headerMap, "address") || "").trim(),
            city: String(getRowCell_(currentRow, headerMap, "city") || "").trim(),
            state: String(getRowCell_(currentRow, headerMap, "state") || "").trim(),
            zipCode: String(getRowCell_(currentRow, headerMap, "zipCode") || "").trim(),
            country: String(getRowCell_(currentRow, headerMap, "country") || "").trim() || "Morocco"
          },
          items: [],
          totalAmount: null,
          currency: String(getRowCell_(currentRow, headerMap, "currency") || "").trim(),
          payment: {
            method: String(getRowCell_(currentRow, headerMap, "paymentMethod") || "").trim()
          },
          note: String(getRowCell_(currentRow, headerMap, "notes") || "").trim()
        },
        rowNumbers: [],
        validationErrors: []
      };
    }

    groupedMap[groupedOrderId].rowNumbers.push(rowIndex + 1);

    var quantity = toNumber_(getRowCell_(currentRow, headerMap, "quantity"));
    var price = toNumber_(getRowCell_(currentRow, headerMap, "price"));
    var productName = String(getRowCell_(currentRow, headerMap, "product") || "").trim();

    var optionName = String(getRowCell_(currentRow, headerMap, "optionName") || "").trim();
    var optionDescription = String(getRowCell_(currentRow, headerMap, "optionDescription") || "").trim();

    groupedMap[groupedOrderId].order.items.push({
      productId: String(getRowCell_(currentRow, headerMap, "productId") || "").trim() || (groupedOrderId + "-" + (groupedMap[groupedOrderId].order.items.length + 1)),
      name: productName,
      optionName: optionName || undefined,
      optionDescription: optionDescription || undefined,
      quantity: quantity,
      price: price
    });

    var explicitTotal = String(getRowCell_(currentRow, headerMap, "totalAmount") || "").trim();
    if (!groupedMap[groupedOrderId].order.totalAmount && explicitTotal) {
      groupedMap[groupedOrderId].order.totalAmount = toNumber_(explicitTotal);
    }

    if (!explicitTotal) {
      groupedMap[groupedOrderId].order.totalAmount = (groupedMap[groupedOrderId].order.totalAmount || 0) + (quantity || 0) * (price || 0);
    }
  }

  var groupedOrders = [];
  Object.keys(groupedMap).forEach(function(orderId) {
    var entry = groupedMap[orderId];
    var errors = validateGroupedOrder_(entry.order);

    if (errors.length) {
      writeOrderResult_(sheet, entry.rowNumbers, {
        status: "Failed",
        message: errors.join(", "),
        keepSendFlag: false
      });
      return;
    }

    groupedOrders.push(entry);
  });

  return { groupedOrders: groupedOrders };
}

function validateGroupedOrder_(order) {
  var errors = [];

  if (!String(order.orderId || "").trim()) errors.push("orderId is required");
  if (!String(order.customer && order.customer.name || "").trim()) errors.push("name is required");
  if (!String(order.customer && order.customer.phone || "").trim()) errors.push("phone is required");
  if (!Array.isArray(order.items) || !order.items.length) errors.push("at least one item is required");

  (order.items || []).forEach(function(item, index) {
    if (!String(item.name || "").trim()) errors.push("product is required on item " + (index + 1));
    if (!Number.isFinite(item.quantity) || item.quantity <= 0) errors.push("quantity is invalid on item " + (index + 1));
    if (!Number.isFinite(item.price) || item.price < 0) errors.push("price is invalid on item " + (index + 1));
  });

  return errors;
}

function postOrdersToEconfirme_(orders) {
  try {
    var response = UrlFetchApp.fetch(ECONFIRME_WEBHOOK_URL, {
      method: "post",
      contentType: "application/json",
      muteHttpExceptions: true,
      payload: JSON.stringify({
        mode: "sync",
        orders: orders,
        connection: getConnectionMetadata_()
      })
    });

    var body = response.getContentText() || "{}";
    try {
      return JSON.parse(body);
    } catch (error) {
      return {
        success: false,
        results: orders.map(function(order) {
          return {
            orderId: order.orderId,
            status: "failed",
            message: body || "Unable to parse eConfirme response."
          };
        })
      };
    }
  } catch (error) {
    return {
      success: false,
      results: orders.map(function(order) {
        return {
          orderId: order.orderId,
          status: "failed",
          message: error.message || "Unable to reach eConfirme."
        };
      })
    };
  }
}

function notifyEconfirmeConnection_() {
  try {
    UrlFetchApp.fetch(ECONFIRME_WEBHOOK_URL, {
      method: "post",
      contentType: "application/json",
      muteHttpExceptions: true,
      payload: JSON.stringify({
        mode: "handshake",
        connection: getConnectionMetadata_()
      })
    });
  } catch (error) {
    SpreadsheetApp.getActive().toast("Sheet prepared. Verify after the first successful sync.", "eConfirme", 5);
  }
}

function ensureHeaders_(sheet) {
  var lastColumn = Math.max(sheet.getLastColumn(), 1);
  var headers = sheet.getRange(1, 1, 1, lastColumn).getDisplayValues()[0];
  var normalizedHeaders = headers.slice();

  ECONFIRME_COLUMNS.forEach(function(columnName) {
    if (normalizedHeaders.indexOf(columnName) === -1) {
      normalizedHeaders.push(columnName);
    }
  });

  sheet.getRange(1, 1, 1, normalizedHeaders.length).setValues([normalizedHeaders]);
}

function applySheetDesign_(sheet) {
  var headers = getHeaders_(sheet);
  if (!headers.length) return;

  var maxRows = Math.max(sheet.getMaxRows(), 2);
  var maxColumns = headers.length;

  sheet.getRange(1, 1, maxRows, maxColumns)
    .setBackground("#ffffff")
    .setFontColor("#0f172a")
    .setFontFamily("Arial")
    .setFontSize(10)
    .setVerticalAlignment("middle")
    .setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);

  sheet.setFrozenRows(1);
  sheet.setFrozenColumns(1);
  sheet.setHiddenGridlines(true);
  sheet.setRowHeights(2, Math.max(maxRows - 1, 1), 28);
  sheet.getRange(1, 1, 1, headers.length)
    .setFontWeight("bold")
    .setFontSize(10)
    .setHorizontalAlignment("center")
    .setBackground("#e6f6f4")
    .setFontColor("#0f766e")
    .setBorder(true, true, true, true, true, true, "#99d8d0", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  sheet.setRowHeight(1, 32);
  setColumnWidths_(sheet, headers);
  applyColumnFormatting_(sheet, headers);
  applyBodyBorders_(sheet, headers);

  var sendColumn = headers.indexOf("sendToEconfirme") + 1;
  if (sendColumn > 0) {
    var rule = SpreadsheetApp.newDataValidation()
      .requireValueInList(["TRUE", "FALSE"], true)
      .setAllowInvalid(false)
      .build();
    sheet.getRange(2, sendColumn, Math.max(sheet.getMaxRows() - 1, 1), 1).setDataValidation(rule);
  }

  var paymentColumn = headers.indexOf("paymentMethod") + 1;
  if (paymentColumn > 0) {
    var paymentRule = SpreadsheetApp.newDataValidation()
      .requireValueInList(["cod", "credit_card", "bank_transfer", "paypal"], true)
      .setAllowInvalid(true)
      .build();
    sheet.getRange(2, paymentColumn, Math.max(sheet.getMaxRows() - 1, 1), 1).setDataValidation(paymentRule);
  }

  var statusColumn = headers.indexOf("econfirmeStatus") + 1;
  if (statusColumn > 0 && sheet.getMaxRows() > 1) {
    sheet.getRange(2, statusColumn, sheet.getMaxRows() - 1, 1)
      .setBackground("#eefaf8")
      .setHorizontalAlignment("center");
  }

  var errorColumn = headers.indexOf("econfirmeError") + 1;
  if (errorColumn > 0 && sheet.getMaxRows() > 1) {
    sheet.getRange(2, errorColumn, sheet.getMaxRows() - 1, 1)
      .setBackground("#fff8f1")
      .setHorizontalAlignment("left");
  }
}

function seedManagedCells_(sheet) {
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  var headers = getHeaders_(sheet);
  var sendColumn = headers.indexOf("sendToEconfirme") + 1;
  if (sendColumn < 1) return;

  var range = sheet.getRange(2, sendColumn, lastRow - 1, 1);
  var values = range.getDisplayValues();
  for (var index = 0; index < values.length; index += 1) {
    if (!String(values[index][0] || "").trim()) {
      values[index][0] = "FALSE";
    }
  }
  range.setValues(values);
}

function setColumnWidths_(sheet, headers) {
  var widths = {
    orderId: 115,
    name: 150,
    phone: 120,
    product: 170,
    quantity: 78,
    price: 88,
    city: 110,
    address: 190,
    state: 90,
    zipCode: 85,
    country: 110,
    email: 190,
    productId: 120,
    totalAmount: 96,
    currency: 80,
    paymentMethod: 118,
    notes: 165,
    sendToEconfirme: 112,
    econfirmeStatus: 120,
    econfirmeError: 220,
    econfirmeLastSyncAt: 140
  };

  headers.forEach(function(header, index) {
    sheet.setColumnWidth(index + 1, widths[header] || 120);
  });
}

function applyColumnFormatting_(sheet, headers) {
  var centeredHeaders = {
    orderId: true,
    phone: true,
    quantity: true,
    price: true,
    city: true,
    state: true,
    zipCode: true,
    country: true,
    totalAmount: true,
    currency: true,
    paymentMethod: true,
    sendToEconfirme: true,
    econfirmeStatus: true,
    econfirmeLastSyncAt: true
  };

  headers.forEach(function(header, index) {
    var columnRange = sheet.getRange(2, index + 1, Math.max(sheet.getMaxRows() - 1, 1), 1);
    columnRange.setHorizontalAlignment(centeredHeaders[header] ? "center" : "left");
  });
}

function applyBodyBorders_(sheet, headers) {
  if (sheet.getMaxRows() <= 1) return;

  sheet.getRange(2, 1, sheet.getMaxRows() - 1, headers.length)
    .setBorder(true, true, true, true, true, true, "#dbe7e5", SpreadsheetApp.BorderStyle.SOLID);
}

function getConnectionMetadata_() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();

  return {
    spreadsheetName: spreadsheet.getName(),
    spreadsheetId: spreadsheet.getId(),
    sheetName: sheet.getName(),
    sheetId: String(sheet.getSheetId())
  };
}

function writeOrderResult_(sheet, rowNumbers, result) {
  var headers = getHeaders_(sheet);
  var statusColumn = headers.indexOf("econfirmeStatus") + 1;
  var errorColumn = headers.indexOf("econfirmeError") + 1;
  var timestampColumn = headers.indexOf("econfirmeLastSyncAt") + 1;
  var sendColumn = headers.indexOf("sendToEconfirme") + 1;
  var timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");

  rowNumbers.forEach(function(rowNumber) {
    if (statusColumn > 0) sheet.getRange(rowNumber, statusColumn).setValue(result.status || "");
    if (errorColumn > 0) sheet.getRange(rowNumber, errorColumn).setValue(result.message || "");
    if (timestampColumn > 0) sheet.getRange(rowNumber, timestampColumn).setValue(timestamp);
    if (sendColumn > 0 && result.keepSendFlag === false) {
      sheet.getRange(rowNumber, sendColumn).setValue("FALSE");
    } else if (sendColumn > 0 && result.keepSendFlag === true) {
      sheet.getRange(rowNumber, sendColumn).setValue("TRUE");
    }
  });
}

function getHeaders_(sheet) {
  var lastColumn = sheet.getLastColumn();
  if (!lastColumn) return [];
  return sheet.getRange(1, 1, 1, lastColumn).getDisplayValues()[0].map(function(header) {
    return String(header || "").trim();
  });
}

function buildHeaderMap_(headers) {
  var map = {};
  headers.forEach(function(header, index) {
    if (header) map[header] = index;
  });
  return map;
}

function ensureRequiredColumns_(headerMap) {
  var missing = ECONFIRME_REQUIRED_COLUMNS.filter(function(column) {
    return headerMap[column] === undefined;
  });
  if (missing.length) {
    throw new Error("Missing required columns: " + missing.join(", "));
  }
}

function getRowCell_(row, headerMap, key) {
  var index = headerMap[key];
  return index === undefined ? "" : row[index];
}

function getCellDisplayValue_(sheet, rowNumber, headers, key) {
  var column = headers.indexOf(key) + 1;
  if (column < 1) return "";
  return sheet.getRange(rowNumber, column).getDisplayValue();
}

function isBlankRow_(row) {
  return !row.some(function(cell) {
    return String(cell || "").trim() !== "";
  });
}

function isTruthyValue_(value) {
  return String(value || "").trim().toUpperCase() === "TRUE";
}

function toNumber_(value) {
  var normalized = String(value || "").replace(/,/g, "").trim();
  var parsed = Number(normalized);
  return Number.isFinite(parsed) ? parsed : NaN;
}