shopify

gistfile1.txt

/**
 * Shopify Data Export to Google Sheets - https://mikerhodes.circle.so/c/ai-questions/shopify-data-export-script
 * 
 * This script exports Shopify product sales data to a Google Spreadsheet
 * in the same format as DataSlayer exports.
 * 
 * IMPORTANT: This script is READ-ONLY and will NEVER make changes to Shopify.
 * It only exports data.
 * 
 * SETUP INSTRUCTIONS - GET YOUR CREDENTIALS:
 * 
 * OPTION 1: Private App (Recommended for scripts)
 * 1. Go to Shopify Admin → Settings → Apps and sales channels
 * 2. Click "Develop apps" → "Create an app"
 * 3. Name it (e.g., "Data Export")
 * 4. Click "Configure Admin API scopes"
 * 5. Enable: read_orders, read_products, read_customers
 * 6. Click "Save" → "Install app"
 * 7. Go to "API credentials" tab
 * 8. Copy "API Key" → put in CONFIG.API_KEY
 * 9. Copy "API Password" (NOT secret key!) → put in CONFIG.API_PASSWORD
 * 10. Leave ACCESS_TOKEN empty
 * 
 * OPTION 2: Custom App (OAuth) - YOUR CURRENT SETUP
 * 1. Go to Shopify Admin → Settings → Apps and sales channels
 * 2. Find your custom app (e.g., "Lead Market Partnership") → Click it
 * 3. Go to "API credentials" tab
 * 4. Look for "Admin API access token" section
 * 5. IMPORTANT: If the token is masked (shows dots), you need to regenerate it:
 *    - Click "Regenerate" or "Create access token" button
 *    - Copy the NEW token immediately (you can only see it once!)
 *    - Paste it into CONFIG.ACCESS_TOKEN
 * 6. Leave API_KEY and API_PASSWORD empty
 * 
 * NOTE: The "API key" and "API secret key" shown are for OAuth flow, NOT for direct API access.
 * You MUST use the "Admin API access token" for this script.
 * 
 * TEST YOUR CREDENTIALS:
 * Run testShopifyConnection() to verify your credentials work before running the full export.
 */

// ============================================
// CONFIGURATION - UPDATE THESE VALUES
// ============================================
const CONFIG = {
    SHOPIFY_STORE: 'XXXX.myshopify.com',
    // For Private Apps: Use API_KEY and API_PASSWORD (leave ACCESS_TOKEN empty)
    API_KEY: '', // Leave empty for Custom Apps
    API_PASSWORD: '', // Leave empty for Custom Apps
    // For Custom Apps (OAuth): Use ACCESS_TOKEN - THIS IS YOUR CURRENT SETUP
    // Go to: Apps → Lead Market Partnership → API credentials → Admin API access token
    // If token is masked, click "Regenerate" and copy the new token immediately!
    ACCESS_TOKEN: 'XXXX', // Admin API access token
    SPREADSHEET_ID: 'XXXXX',
    SHEET_NAME: 'XXXX', // Name of the sheet tab
    DAYS_BACK: 365, // Number of days to look back (12 months)
  };
  
  // ============================================
  // MAIN EXPORT FUNCTION
  // ============================================
  
  /**
   * Main function to export Shopify monthly revenue to Google Sheets
   */
  function exportShopifyData() {
    try {
      // Validate configuration
      const hasAccessToken = CONFIG.ACCESS_TOKEN && CONFIG.ACCESS_TOKEN.trim() !== '';
      const hasBasicAuth = CONFIG.API_KEY && CONFIG.API_KEY.trim() !== '' && 
                           CONFIG.API_PASSWORD && CONFIG.API_PASSWORD.trim() !== '';
      
      if (CONFIG.SHOPIFY_STORE.includes('YOUR_') || (!hasAccessToken && !hasBasicAuth)) {
        throw new Error('Please update CONFIG with your Shopify credentials. You need either ACCESS_TOKEN (for OAuth) or both API_KEY and API_PASSWORD (for Private App).');
      }
  
      // Get or create spreadsheet
      let spreadsheet;
      if (CONFIG.SPREADSHEET_ID) {
        spreadsheet = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
      } else {
        spreadsheet = SpreadsheetApp.create('Shopify Monthly Revenue');
        CONFIG.SPREADSHEET_ID = spreadsheet.getId();
      }
  
      // Get or create sheet using CONFIG.SHEET_NAME
      let sheet = spreadsheet.getSheetByName(CONFIG.SHEET_NAME);
      if (!sheet) {
        sheet = spreadsheet.insertSheet(CONFIG.SHEET_NAME);
        logInfo(`Created new sheet: ${CONFIG.SHEET_NAME}`);
      } else {
        // Clear ALL existing data and formatting (rewrite everything)
        sheet.clear();
        logInfo(`Cleared all existing data from sheet: ${CONFIG.SHEET_NAME}`);
      }
  
      // Write headers
      const headers = [
        'Year-Month',
        'Store Name',
        'Total items ordered',
        'Total Orders',
        'Customer Count',
        'Gross Sales',
        'Total Sales',
        'Net Sales',
        'Gross Profit',
        'Discounts',
        'Returns',
        'Shipping charge taxes',
        'Shipping charges',
        'Total Returns',
        'Taxes Returned'
      ];
      sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
      const headerRange = sheet.getRange(1, 1, 1, headers.length);
      headerRange.setFontWeight('bold');
      headerRange.setBackground('#FFFFE0'); // Light yellow background
  
      // Cache auth headers
      const authHeaders = getAuthHeaders();
      logInfo('Starting export - fetching and exporting page by page...');
      
      // Fetch orders page by page and export immediately
      const endDate = new Date();
      const startDate = new Date();
      startDate.setDate(startDate.getDate() - CONFIG.DAYS_BACK); // Get last 12 months of data
      
      // Initialize aggregation object (will be updated incrementally)
      const aggregated = {};
      let pageInfo = null;
      let hasNextPage = true;
      let page = 1;
      const storeName = CONFIG.SHOPIFY_STORE.replace('.myshopify.com', '');
  
      while (hasNextPage) {
        // Fetch one page
        const pageResult = fetchShopifyOrdersPage(startDate, endDate, pageInfo, authHeaders);
        const orders = pageResult.orders;
        pageInfo = pageResult.nextPageInfo;
        hasNextPage = pageResult.hasNextPage;
        
        logProgress(`Fetched page ${page}: ${orders.length} orders`);
        
        // Track new customer detection for debugging
        let newCustomerCount = 0;
        let returningCustomerCount = 0;
        let guestCheckoutCount = 0;
        let missingOrdersCountCount = 0;
        
        // Process this page's orders and aggregate incrementally
        orders.forEach(order => {
          const orderDate = new Date(order.created_at);
          const yearMonth = Utilities.formatDate(orderDate, Session.getScriptTimeZone(), 'yyyy-MM');
          
          // Calculate order metrics
          const totalItems = order.line_items?.reduce((sum, item) => sum + (parseInt(item.quantity) || 0), 0) || 0;
          const grossSales = parseFloat(order.subtotal_price) || 0;
          const discounts = parseFloat(order.total_discounts) || 0;
          const netSales = grossSales - discounts;
          const shippingPrice = parseFloat(order.total_shipping_price_set?.shop_money?.amount) || 0;
          const taxAmount = parseFloat(order.total_tax) || 0;
          const totalSales = parseFloat(order.total_price) || 0;
          
          // Calculate refund amounts from refunds array
          // Refunds structure: refunds[].refund_line_items[].subtotal, refunds[].refund_line_items[].total_tax
          let refundAmount = 0;
          let shippingReturned = 0;
          let taxesReturned = 0;
          let returnFees = 0;
          
          if (order.refunds && order.refunds.length > 0) {
            order.refunds.forEach(refund => {
              // Total refund amount (sum of all refund line items)
              if (refund.refund_line_items && refund.refund_line_items.length > 0) {
                refund.refund_line_items.forEach(item => {
                  refundAmount += parseFloat(item.subtotal || 0);
                  taxesReturned += parseFloat(item.total_tax || 0);
                });
              }
              
              // Check for shipping refunds in shipping_lines or order_adjustments
              // Shipping refunds are typically in order_adjustments with kind="shipping_refund"
              if (refund.order_adjustments && refund.order_adjustments.length > 0) {
                refund.order_adjustments.forEach(adj => {
                  if (adj.kind === 'shipping_refund') {
                    shippingReturned += parseFloat(adj.amount || 0);
                  }
                });
              }
              
              // Additional fees (return fees)
              if (refund.total_additional_fees_set && refund.total_additional_fees_set.shop_money) {
                returnFees += parseFloat(refund.total_additional_fees_set.shop_money.amount || 0);
              }
            });
          }
          
          // Determine if customer is new or returning
          // According to Supermetrics: order_is_returning_customer indicates if customer is returning
          // In Shopify REST API, we calculate this from customer.orders_count:
          // - orders_count === 1 means new customer (first order)
          // - orders_count > 1 means returning customer
          // - null customer means guest checkout (treated as new customer)
          let isNewCustomer = true; // Default to new customer
          
          if (order.customer && order.customer.orders_count !== undefined && order.customer.orders_count !== null) {
            // Customer exists with orders_count - most reliable method
            isNewCustomer = order.customer.orders_count === 1;
            if (isNewCustomer) {
              newCustomerCount++;
            } else {
              returningCustomerCount++;
            }
          } else if (order.customer) {
            // Customer exists but orders_count not available - check if we can infer from customer ID
            // If customer has an ID, they might be returning (but we can't be sure without orders_count)
            // Default to returning customer if customer object exists but orders_count missing
            isNewCustomer = false;
            missingOrdersCountCount++;
            returningCustomerCount++;
          } else {
            // Guest checkout (null customer) - treat as new customer per Supermetrics convention
            isNewCustomer = true;
            guestCheckoutCount++;
          }
          
          // Calculate shipping and tax per Supermetrics definitions:
          // Shipping: "The total shipping amount including returns" (shipping charges - shipping returned)
          // Tax: "The total amount of tax for the line. Takes returns into account" (tax - taxes returned)
          const netShipping = shippingPrice - shippingReturned;
          const netTax = taxAmount - taxesReturned;
          
          // Aggregate this order into the aggregated object
          const key = `${yearMonth}|${storeName}`;
          
          if (!aggregated[key]) {
            aggregated[key] = {
              yearMonth: yearMonth,
              storeName: storeName,
              totalItems: 0,
              totalOrders: 0,
              grossSales: 0,
              totalSales: 0,
              netSales: 0,
              grossProfit: 0,
              discounts: 0,
              returns: 0,
              shippingTaxes: 0,
              shippingCharges: 0,
              totalReturns: 0,
              taxesReturned: 0
            };
          }
          
          const agg = aggregated[key];
          agg.totalItems += totalItems;
          agg.totalOrders += 1;
          agg.grossSales += grossSales;
          agg.totalSales += totalSales;
          agg.netSales += netSales;
          agg.grossProfit += netSales;
          agg.discounts += discounts;
          agg.returns += refundAmount;
          agg.shippingTaxes += netTax;
          agg.shippingCharges += netShipping;
          agg.totalReturns += refundAmount;
          agg.taxesReturned += taxesReturned;
        });
        
        logProgress(`Page ${page}: Processed ${orders.length} orders`);
        logInfo(`  - New customers: ${newCustomerCount}, Returning: ${returningCustomerCount}, Guests: ${guestCheckoutCount}, Missing orders_count: ${missingOrdersCountCount}`);
        
        // Write aggregated data to sheet after each page (incremental rollout)
        writeAggregatedDataToSheet(sheet, aggregated, headers, false);
        
        page++;
        
        // Rate limiting - Shopify allows 2 requests per second
        if (hasNextPage) {
          Utilities.sleep(500);
        }
      }
  
      logSuccess(`Total orders processed. Final aggregated rows: ${Object.keys(aggregated).length}`);
      
      // Final write and formatting (data already written incrementally, but ensure final state is correct)
      writeAggregatedDataToSheet(sheet, aggregated, headers, true);
      
      // Log completion
      logComplete(`Export completed successfully! ${Object.keys(aggregated).length} aggregated rows exported.`);
      logInfo(`Spreadsheet URL: ${spreadsheet.getUrl()}`);
  
      return spreadsheet.getUrl();
    } catch (error) {
      logError(`Export failed: ${error.message}`);
      throw error;
    }
  }
  
  // ============================================
  // HELPER FUNCTIONS
  // ============================================
  
  /**
   * Logger functions with emojis for better visibility
   */
  function logSuccess(message) {
    Logger.log(`✅ ${message}`);
  }
  
  function logError(message) {
    Logger.log(`❌ ${message}`);
  }
  
  function logWarning(message) {
    Logger.log(`⚠️ ${message}`);
  }
  
  function logInfo(message) {
    Logger.log(`ℹ️ ${message}`);
  }
  
  function logProgress(message) {
    Logger.log(`🔄 ${message}`);
  }
  
  function logComplete(message) {
    Logger.log(`✨ ${message}`);
  }
  
  /**
   * Write aggregated data to sheet (called incrementally after each page)
   * @param {Sheet} sheet - The Google Sheet to write to
   * @param {Object} aggregated - The aggregated data object
   * @param {Array} headers - The header row array
   * @param {boolean} finalFormat - Whether this is the final write (apply formatting)
   */
  function writeAggregatedDataToSheet(sheet, aggregated, headers, finalFormat) {
    // Convert aggregated data to rows
    const aggregatedRows = Object.keys(aggregated).map(key => {
      const item = aggregated[key];
      return [
        item.yearMonth,
        item.storeName,
        item.totalItems,
        item.totalOrders,
        item.totalOrders, // Customer Count (using order count as proxy)
        item.grossSales,
        item.totalSales,
        item.netSales,
        item.grossProfit,
        item.discounts,
        item.returns,
        item.shippingTaxes,
        item.shippingCharges,
        item.totalReturns,
        item.taxesReturned
      ];
    });
    
    // Sort by Year-Month descending (newest first)
    const sortedRows = aggregatedRows.sort((a, b) => {
      return b[0].localeCompare(a[0]); // Year-Month is first column, descending
    });
    
    if (sortedRows.length > 0) {
      // Clear existing data rows (keep header)
      const lastRow = sheet.getLastRow();
      if (lastRow > 1) {
        sheet.getRange(2, 1, lastRow - 1, headers.length).clearContent();
      }
      
      // Write all aggregated and sorted rows
      sheet.getRange(2, 1, sortedRows.length, headers.length).setValues(sortedRows);
      
      // Apply formatting if this is the final write
      if (finalFormat) {
        sheet.setFrozenRows(1);
        sheet.autoResizeColumns(1, headers.length);
        
        // Format currency columns (updated indices after removing Returning Customers Sales)
        const currencyColumns = [6, 7, 8, 9, 10, 11, 12, 13, 14, 15]; // Column indices (1-based): Gross Sales, Total Sales, Net Sales, Gross Profit, Discounts, Returns, Shipping charge taxes, Shipping charges, Total Returns, Taxes Returned
        currencyColumns.forEach(colIndex => {
          sheet.getRange(2, colIndex, sortedRows.length, 1).setNumberFormat('$#,##0.00');
        });
        
        // Remove empty columns after writing
        removeEmptyColumns(sheet, headers.length);
      }
      
      logProgress(`Written ${sortedRows.length} aggregated rows to sheet`);
    }
  }
  
  /**
   * Remove empty columns from the sheet
   * Checks all columns and removes any that are completely empty
   * @param {Sheet} sheet - The Google Sheet
   * @param {number} expectedColumns - Expected number of columns (headers length)
   */
  function removeEmptyColumns(sheet, expectedColumns) {
    try {
      const lastRow = sheet.getLastRow();
      const lastCol = sheet.getLastColumn();
      
      if (lastRow < 1 || lastCol < 1) return; // No data to check
      
      // Check columns from right to left (so deletion doesn't affect indices)
      // Start from the last column and work backwards
      for (let col = lastCol; col >= 1; col--) {
        const columnRange = sheet.getRange(1, col, lastRow, 1);
        const values = columnRange.getValues();
        
        // Check if column is empty (all cells are empty, null, or whitespace)
        const isEmpty = values.every(row => {
          const cellValue = row[0];
          return cellValue === null || cellValue === '' || cellValue === undefined || 
                 (typeof cellValue === 'string' && cellValue.trim() === '');
        });
        
        if (isEmpty) {
          sheet.deleteColumn(col);
          logInfo(`Removed empty column ${col}`);
        }
      }
    } catch (error) {
      logWarning(`Error removing empty columns: ${error.message}`);
    }
  }
  
  // ============================================
  // SHOPIFY API FUNCTIONS
  // ============================================
  
  /**
   * Fetch a single page of orders from Shopify API
   * Returns: {orders: [], nextPageInfo: null, hasNextPage: false}
   */
  function fetchShopifyOrdersPage(startDate, endDate, pageInfo, authHeaders) {
    const baseUrl = `https://${CONFIG.SHOPIFY_STORE}/admin/api/2024-01`;
  
    // Format dates for Shopify API (ISO 8601)
    const createdAtMin = startDate.toISOString();
    const createdAtMax = endDate.toISOString();
  
    try {
      let url;
      // Request fields needed for calculations: customer, refunds (with all details), and financial fields
      // Note: order_is_returning_customer is a direct field on the order object (not nested in customer)
      // According to Supermetrics docs: order_is_returning_customer indicates if customer is returning
      const fields = 'id,created_at,subtotal_price,total_discounts,total_tax,total_price,total_shipping_price_set,financial_status,fulfillment_status,customer,line_items,shipping_address,billing_address,refunds,total_duties_set';
      if (pageInfo) {
        // When using page_info, we can only include page_info and limit
        // Cannot include status, created_at_min, or created_at_max
        url = `${baseUrl}/orders.json?limit=250&page_info=${pageInfo}&fields=${fields}`;
      } else {
        // First request: include all filters
        url = `${baseUrl}/orders.json?limit=250&status=any&created_at_min=${createdAtMin}&created_at_max=${createdAtMax}&fields=${fields}`;
      }
  
      // Merge headers
      const headers = {
        'Content-Type': 'application/json',
      };
      for (var key in authHeaders) {
        headers[key] = authHeaders[key];
      }
  
      const response = UrlFetchApp.fetch(url, {
        method: 'get',
        headers: headers,
        muteHttpExceptions: true,
      });
  
      if (response.getResponseCode() !== 200) {
        throw new Error(`Shopify API error: ${response.getResponseCode()} - ${response.getContentText()}`);
      }
  
      const data = JSON.parse(response.getContentText());
      const orders = data.orders || [];
  
      // Check for pagination
      let nextPageInfo = null;
      let hasNextPage = false;
      const linkHeader = response.getHeaders()['Link'];
      if (linkHeader && linkHeader.includes('rel="next"')) {
        const nextMatch = linkHeader.match(/<[^>]+page_info=([^&>]+)[^>]*>; rel="next"/);
        if (nextMatch) {
          nextPageInfo = nextMatch[1];
          hasNextPage = true;
        }
      }
  
      return {
        orders: orders,
        nextPageInfo: nextPageInfo,
        hasNextPage: hasNextPage
      };
    } catch (error) {
      logError(`Error fetching orders: ${error.message}`);
      throw error;
    }
  }
  
  /**
   * Get authentication headers based on available credentials
   * Tries OAuth token first, then falls back to Basic Auth
   * Only logs once when called for the first time
   */
  function getAuthHeaders() {
    if (CONFIG.ACCESS_TOKEN && CONFIG.ACCESS_TOKEN.trim() !== '') {
      // Use OAuth token authentication
      if (!getAuthHeaders._logged) {
        logInfo('Using OAuth token authentication');
        getAuthHeaders._logged = true;
      }
      return {
        'X-Shopify-Access-Token': CONFIG.ACCESS_TOKEN.trim(),
      };
    } else if (CONFIG.API_KEY && CONFIG.API_PASSWORD && CONFIG.API_PASSWORD.trim() !== '') {
      // Use Basic Auth
      // Trim any whitespace from credentials
      const apiKey = String(CONFIG.API_KEY).trim();
      const apiPassword = String(CONFIG.API_PASSWORD).trim();
      const authString = `${apiKey}:${apiPassword}`;
      const authHeader = Utilities.base64Encode(authString);
      if (!getAuthHeaders._logged) {
        logInfo('Using Basic Auth with API Key: ' + apiKey.substring(0, 10) + '...');
        getAuthHeaders._logged = true;
      }
      return {
        'Authorization': `Basic ${authHeader}`,
      };
    } else {
      throw new Error('No valid authentication credentials provided. Please check CONFIG section.');
    }
  }
  
  /**
   * Test function to verify Shopify API connection
   * Run this first to make sure your credentials are correct
   */
  function testShopifyConnection() {
    try {
      logInfo('Testing Shopify API connection...');
      logInfo('Store: ' + CONFIG.SHOPIFY_STORE);
      
      const baseUrl = `https://${CONFIG.SHOPIFY_STORE}/admin/api/2024-01`;
      const authHeaders = getAuthHeaders();
      
      // Try to fetch a single order to test connection
      const testUrl = `${baseUrl}/orders.json?limit=1`;
      
      const headers = {
        'Content-Type': 'application/json',
      };
      for (var key in authHeaders) {
        headers[key] = authHeaders[key];
      }
      
      const response = UrlFetchApp.fetch(testUrl, {
        method: 'get',
        headers: headers,
        muteHttpExceptions: true,
      });
      
      const statusCode = response.getResponseCode();
      const responseText = response.getContentText();
      
      if (statusCode === 200) {
        const data = JSON.parse(responseText);
        logSuccess('Connection verified!');
        logInfo('Found ' + (data.orders ? data.orders.length : 0) + ' test order(s)');
        logComplete('Your credentials are correct. You can now run exportShopifyData().');
        return true;
      } else {
        logError('Connection failed! Status code: ' + statusCode);
        logWarning('Response: ' + responseText);
        
        if (statusCode === 401) {
          logError('\n=== AUTHENTICATION ERROR ===');
          logWarning('Your credentials are incorrect. Please check:');
          logInfo('1. For Private Apps: Make sure you copied the "API Password" (not secret key)');
          logInfo('2. For Custom Apps: Make sure you copied the "Admin API access token"');
          logInfo('3. Verify the credentials in Shopify Admin → Apps → API credentials');
        }
        return false;
      }
    } catch (error) {
      logError('Connection test error: ' + error.message);
      return false;
    }
  }
  
  /**
   * Fetch product details for line items
   */
  function fetchProductDetails(productId, variantId, authHeaders) {
    const baseUrl = `https://${CONFIG.SHOPIFY_STORE}/admin/api/2024-01`;
    
    try {
      const productUrl = `${baseUrl}/products/${productId}.json`;
      
      // Merge headers
      const headers = {
        'Content-Type': 'application/json',
      };
      for (var key in authHeaders) {
        headers[key] = authHeaders[key];
      }
      
      const response = UrlFetchApp.fetch(productUrl, {
        method: 'get',
        headers: headers,
        muteHttpExceptions: true,
      });
  
      if (response.getResponseCode() === 200) {
        const data = JSON.parse(response.getContentText());
        return data.product || null;
      }
    } catch (error) {
      logWarning(`Error fetching product ${productId}: ${error.message}`);
    }
    
    return null;
  }
  
  // ============================================
  // DATA PROCESSING FUNCTIONS
  // ============================================
  
  /**
   * Process a page of Shopify orders into the DataSlayer format
   * Uses shared productCache to avoid re-fetching products across pages
   */
  function processShopifyDataPage(orders, startDate, endDate, productCache, authHeaders) {
    const rows = [];
  
    // Generate all dates in the range
    const dateMap = {};
    const currentDate = new Date(startDate);
    while (currentDate <= endDate) {
      const dateKey = Utilities.formatDate(currentDate, Session.getScriptTimeZone(), 'yyyy-MM-dd');
      dateMap[dateKey] = new Date(currentDate);
      currentDate.setDate(currentDate.getDate() + 1);
    }
  
    // Process each order
    orders.forEach(order => {
      const orderDate = new Date(order.created_at);
      const dateKey = Utilities.formatDate(orderDate, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  
      // Process each line item
      if (order.line_items && order.line_items.length > 0) {
        order.line_items.forEach(lineItem => {
          // Get product details (with caching)
          let product = null;
          const cacheKey = `${lineItem.product_id}_${lineItem.variant_id}`;
          
          if (productCache[cacheKey]) {
            product = productCache[cacheKey];
          } else if (lineItem.product_id) {
            product = fetchProductDetails(lineItem.product_id, lineItem.variant_id, authHeaders);
            if (product) {
              productCache[cacheKey] = product;
            }
            Utilities.sleep(200); // Rate limiting
          }
  
          // Determine if customer is new or returning
          const isNewCustomer = order.customer && order.customer.orders_count === 1;
  
          // Calculate metrics
          const quantity = parseInt(lineItem.quantity) || 0;
          const price = parseFloat(lineItem.price) || 0;
          const totalDiscount = parseFloat(order.total_discounts) || 0;
          const shippingPrice = parseFloat(order.total_shipping_price_set?.shop_money?.amount) || 0;
          const taxAmount = parseFloat(order.total_tax) || 0;
          const refundAmount = parseFloat(order.refunds?.reduce((sum, r) => sum + (parseFloat(r.amount) || 0), 0)) || 0;
  
          // Calculate per-item amounts (proportional)
          const lineItemTotal = quantity * price;
          const orderTotal = parseFloat(order.total_price) || 0;
          const proportion = orderTotal > 0 ? lineItemTotal / orderTotal : 0;
  
          const grossPrice = lineItemTotal;
          const discounts = totalDiscount * proportion;
          const shipping = shippingPrice * proportion;
          const taxes = taxAmount * proportion;
          const refunds = refundAmount * proportion;
          const netPrice = grossPrice - discounts;
          const finalPrice = netPrice + shipping + taxes;
  
          // Get product details
          const productTitle = product?.title || lineItem.title || '';
          const variantTitle = lineItem.variant_title || '';
          const sku = lineItem.sku || '';
          const storeName = CONFIG.SHOPIFY_STORE.replace('.myshopify.com', '');
  
          // Create row data with only metrics and dimensions
          const row = [
            dateKey, // Date (day)
            storeName, // Store Name (store_name)
            order.shipping_address?.country || '', // Order Shipping Country (order_shipping_country)
            sku, // SKU (product_sku)
            productTitle, // Product Title (product_title)
            variantTitle, // Variant Title (variant_title)
            quantity, // Total items ordered (items_ordered)
            1, // Total Orders (total_orders - 1 per line item row)
            0, // Customer Count (customer_count - not calculated per row)
            isNewCustomer ? 1 : 0, // New Customers Order (new_customer_order)
            isNewCustomer ? 0 : 1, // Returning Customers Order (returning_customer_order)
            grossPrice, // Gross Sales (gross_price)
            isNewCustomer ? grossPrice : 0, // New Customers Sales (new_customer_sales)
            isNewCustomer ? 0 : grossPrice, // Returning Customers Sales (returning_customer_sales)
            finalPrice, // Total Sales (final_price)
            netPrice, // Net Sales (net_price)
            netPrice, // Gross Profit (gross_profit - simplified)
            discounts, // Discounts
            refunds, // Returns (refunds)
            taxes, // Shipping charge taxes (shipping_taxes)
            shipping, // Shipping charges (gross_shipping)
            0, // Total Duties (total_duties)
            refunds, // Total Returns (total_returns)
            0, // Shipping Returned (shipping_returned)
            0, // Taxes Returned (taxes_returned)
            0, // Return Fees (fee_returned)
            shipping, // Total Shipping charges (shipping)
            price, // Product variant price (product_price)
            0, // Product variant quantity in stock (product_quantity_inventory - not available from orders API)
            0, // Inventory item cost (inventory_item_cost - not available from orders API)
            lineItemTotal, // Product price in stock (product_total_price)
          ];
  
          rows.push(row);
        });
      }
    });
  
    // Sort by date descending
    rows.sort((a, b) => {
      const dateA = new Date(a[getColumnIndex('day')]);
      const dateB = new Date(b[getColumnIndex('day')]);
      return dateB - dateA;
    });
  
    return rows;
  }
  
  // ============================================
  // HELPER FUNCTIONS
  // ============================================
  
  /**
   * Get headers matching DataSlayer format
   */
  function getHeaders() {
    return [
      'Date',
      'Store Name',
      'Order Shipping Country',
      'SKU',
      'Product Title',
      'Variant Title',
      'Total items ordered',
      'Total Orders',
      'Customer Count',
      'New Customers Order',
      'Returning Customers Order',
      'Gross Sales',
      'New Customers Sales',
      'Returning Customers Sales',
      'Total Sales',
      'Net Sales',
      'Gross Profit',
      'Discounts',
      'Returns',
      'Shipping charge taxes',
      'Shipping charges',
      'Total Duties',
      'Total Returns',
      'Shipping Returned',
      'Taxes Returned',
      'Return Fees',
      'Total Shipping charges',
      'Product variant price',
      'Product variant quantity in stock',
      'Inventory item cost',
      'Product price in stock',
    ];
  }
  
  /**
   * Get metrics array in DataSlayer format
   */
  function getMetricsArray() {
    return [
      {id: 'items_ordered', name: 'Total items ordered'},
      {id: 'total_orders', name: 'Total Orders'},
      {id: 'customer_count', name: 'Customer Count'},
      {id: 'new_customer_order', name: 'New Customers Order'},
      {id: 'returning_customer_order', name: 'Returning Customers Order'},
      {id: 'gross_price', name: 'Gross Sales'},
      {id: 'new_customer_sales', name: 'New Customers Sales'},
      {id: 'returning_customer_sales', name: 'Returning Customers Sales'},
      {id: 'final_price', name: 'Total Sales'},
      {id: 'net_price', name: 'Net Sales'},
      {id: 'gross_profit', name: 'Gross Profit'},
      {id: 'discounts', name: 'Discounts'},
      {id: 'refunds', name: 'Returns'},
      {id: 'shipping_taxes', name: 'Shipping charge taxes'},
      {id: 'gross_shipping', name: 'Shipping charges'},
      {id: 'total_duties', name: 'Total Duties'},
      {id: 'total_returns', name: 'Total Returns'},
      {id: 'shipping_returned', name: 'Shipping Returned'},
      {id: 'taxes_returned', name: 'Taxes Returned'},
      {id: 'fee_returned', name: 'Return Fees'},
      {id: 'shipping', name: 'Total Shipping charges'},
      {id: 'product_price', name: 'Product variant price'},
      {id: 'product_quantity_inventory', name: 'Product variant quantity in stock'},
      {id: 'inventory_item_cost', name: 'Inventory item cost'},
      {id: 'product_total_price', name: 'Product price in stock'},
    ];
  }
  
  /**
   * Get dimensions array in DataSlayer format
   */
  function getDimensionsArray() {
    return [
      {id: 'day', name: 'Date'},
      {id: 'store_name', name: 'Store Name'},
      {id: 'order_shipping_country', name: 'Order Shipping Country'},
      {id: 'product_sku', name: 'SKU'},
      {id: 'product_title', name: 'Product Title'},
      {id: 'variant_title', name: 'Variant Title'},
    ];
  }
  
  /**
   * Get column index by name (for sorting/filtering)
   */
  function getColumnIndex(columnName) {
    const headers = getHeaders();
    return headers.indexOf(columnName);
  }
  
  /**
   * Generate a UUID
   */
  function generateUUID() {
    return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
      const r = Math.random() * 16 | 0;
      const v = c === 'x' ? r : (r & 0x3 | 0x8);
      return v.toString(16);
    });
  }
  
  /**
   * Format the sheet with proper styling
   */
  function formatSheet(sheet, dataRows) {
    const totalRows = dataRows + 1; // +1 for header
    const totalCols = getHeaders().length;
  
    // Freeze header row
    sheet.setFrozenRows(1);
  
    // Auto-resize columns
    sheet.autoResizeColumns(1, totalCols);
  
    // Format header row
    const headerRange = sheet.getRange(1, 1, 1, totalCols);
    headerRange.setFontWeight('bold');
    headerRange.setBackground('#f0f0f0');
  
    // Format date column (Date is first column, index 0)
    if (totalRows > 1) {
      const dateRange = sheet.getRange(2, 1, totalRows - 1, 1);
      dateRange.setNumberFormat('yyyy-mm-dd');
    }
  
    // Format currency columns (using new header names)
    const currencyColumns = ['Gross Sales', 'New Customers Sales', 'Returning Customers Sales', 
                            'Total Sales', 'Net Sales', 'Gross Profit', 'Discounts', 'Returns',
                            'Shipping charge taxes', 'Shipping charges', 'Total Duties', 'Total Returns',
                            'Shipping Returned', 'Taxes Returned', 'Return Fees', 'Total Shipping charges',
                            'Product variant price', 'Product price in stock'];
    
    currencyColumns.forEach(colName => {
      const colIndex = getColumnIndex(colName) + 1;
      if (colIndex > 0 && totalRows > 1) {
        const currencyRange = sheet.getRange(2, colIndex, totalRows - 1, 1);
        currencyRange.setNumberFormat('$#,##0.00');
      }
    });
  }
  
  /**
   * Convert column number to letter (1 -> A, 27 -> AA, etc.)
   */
  function getColumnLetter(columnNumber) {
    let result = '';
    while (columnNumber > 0) {
      columnNumber--;
      result = String.fromCharCode(65 + (columnNumber % 26)) + result;
      columnNumber = Math.floor(columnNumber / 26);
    }
    return result;
  }