function doGet() {
  var template = HtmlService.createTemplateFromFile('pg');
    var pageData= template.evaluate()
    .setTitle('Order form')
    .setFaviconUrl('https://www.gstatic.com/images/icons/material/product/2x/apps_script_64dp.png')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .addMetaTag('viewport', 'width=device-width, initial-scale=1')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
  return pageData;
}

function getDropdownData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('dd');
  const data = sheet.getRange(1,1,sheet.getLastRow() ,7).getValues();

  const dropdownData = {
    concernedPerson: [...new Set(data.slice(1).map(row => row[0]).filter(Boolean))],
    category: [...new Set(data.slice(1).map(row => row[1]).filter(Boolean))],
    outletName: [...new Set(data.slice(1).map(row => row[2]).filter(Boolean))],
    occasionType: [...new Set(data.slice(1).map(row => row[3]).filter(Boolean))],
    boxType: [...new Set(data.slice(1).map(row => row[4]).filter(Boolean))],
    referBy: [...new Set(data.slice(1).map(row => row[5]).filter(Boolean))],
    itNames: [...new Set(data.slice(1).map(row => row[6]).filter(Boolean))],
  };

  return dropdownData;
}

function saveOrder(data) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const itemsSheet = ss.getSheetByName('order request');

  const date = new Date()

  const orderInfo = data.orderInfo;
  const orderRow = [
    orderInfo.orderNumber,
    orderInfo.outletName,
    orderInfo.customerName,
    orderInfo.phoneNumber,
    orderInfo.occasionType,
    orderInfo.occasionDate,
    orderInfo.boxType,
    orderInfo.coupleName,
    orderInfo.referBy,
    orderInfo.concernedPerson
  ];

  const categories = data.categories;
  categories.forEach(category => {
    category.items.forEach(item => {
      const itemRow = [
        date,
        ...orderRow,
        category.category,
        category.boxNumber,
        category.mrp,
        category.fixedPrice,
        category.boxCode,
        category.categoryRemarks,
        item.itemName,
        item.quantity,
        item.unit,
        item.itemRemarks
      ];
      itemsSheet.appendRow(itemRow);
    });
  });

  return "Order saved successfully!";
}