Tag: wordpress optimization

Step-by-step automation — from WooCommerce to monthly GST report, no manual exports.

What This Solves

If you manage a WooCommerce store on WordPress, this guide will help you:

• Extract completed order data from your WordPress site
• Sync it into Google Sheets via Apps Script
• Auto-generate a GST-compliant monthly report
• Email it — as a downloadable Google Sheet — every month

No plugins. No third-party tools. Full control.

Step 1: Create export_orders.php in Your WooCommerce Site

What It Does

This script connects directly to your WordPress backend using core WooCommerce functions to fetch order data in CSV format.

Where to Place It

• Upload this to your theme root or a protected folder like:

/wp-content/export/export_orders.php

Access via:

https://yourdomain.com/wp-content/export-secure/export_orders.php?token=your_secure_token_here

Ensure it’s not publicly browsable without restrictions (add basic auth or token check in production).

export_orders.php

<?php
// CONFIGURE YOUR SECRET TOKEN BELOW
define('EXPORT_TOKEN', 'your_secure_token_here');

// Block unauthorized access
if (!isset($_GET['token']) || $_GET['token'] !== EXPORT_TOKEN) {
    http_response_code(403);
    exit('Unauthorized access');
}

// Load WordPress core
require_once($_SERVER['DOCUMENT_ROOT'] . '/wp-load.php');

// Ensure WooCommerce is active
if (!class_exists('WooCommerce')) {
    exit('WooCommerce is not active');
}

// Fetch all orders
$args = array(
    'post_type'      => 'shop_order',
    'post_status'    => array_keys(wc_get_order_statuses()),
    'posts_per_page' => -1,
    'orderby'        => 'date',
    'order'          => 'DESC',
);

$orders = get_posts($args);
$csv_data = [];

// Process each order
foreach ($orders as $post) {
    $order = wc_get_order($post->ID);
    $data = $order->get_data();

    // Basic order info
    $row = [
        'Order_ID'         => $order->get_id(),
        'Order_Status'     => $order->get_status(),
        'Payment_Method'   => $data['payment_method_title'] ?? 'NA',
        'Total'            => $data['total'] ?? 0,
        'Tax_Total'        => $data['total_tax'] ?? 0,
        'Shipping_Total'   => $data['shipping_total'] ?? 0,
        'COD_Charges'      => get_post_meta($order->get_id(), '_cod_charges', true) ?: 0,
        'Invoice_Number'   => get_post_meta($order->get_id(), '_wcpdf_invoice_number', true) ?: 'NA',
        'Invoice_Date'     => get_post_meta($order->get_id(), '_wcpdf_invoice_date_formatted', true) ?: 'NA',
        'Date_Created'     => $data['date_created']->date('Y-m-d H:i:s'),
        'Date_Modified'    => $data['date_modified']->date('Y-m-d H:i:s'),
    ];

    // Flatten billing info
    foreach ($data['billing'] as $key => $val) {
        $row['Billing_' . ucfirst($key)] = $val;
    }

    // Flatten shipping info
    foreach ($data['shipping'] as $key => $val) {
        $row['Shipping_' . ucfirst($key)] = $val;
    }

    // Line items
    foreach ($order->get_items() as $item) {
        if (!is_a($item, 'WC_Order_Item_Product')) continue;

        $product = $item->get_product();
        if (!$product) continue;

        $product_row = $row;
        $product_row['Product_Name'] = $product->get_name();
        $product_row['Quantity']     = $item->get_quantity();
        $product_row['Size']         = $product->get_attribute('pa_size');
        $product_row['Color']        = $product->get_attribute('pa_color');
        $product_row['Rate']         = $product->get_price();

        // Sanitize all data
        foreach ($product_row as $k => $v) {
            $product_row[$k] = is_scalar($v) ? $v : json_encode($v);
        }

        $csv_data[] = $product_row;
    }
}

// Serve CSV in memory
if (!empty($csv_data)) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="exported_orders.csv"');

    $output = fopen('php://output', 'w');
    fputcsv($output, array_keys($csv_data[0]));
    foreach ($csv_data as $row) {
        fputcsv($output, $row);
    }
    fclose($output);
    exit;
} else {
    echo "No orders to export.";
}

Add authentication if this is accessible from the web (e.g., API key via URL parameter or Basic Auth header).

Step 2: Connect Google Apps Script

1. Open a Google Sheet
2. Go to Extensions → Apps Script
3. Paste the updateData() function to pull this CSV and store it in a sheet called Data
4. Follow with generateSalesReport() and generateGSTReport() functions (see earlier messages)

Update the url in updateData() like this:

var url = "https://yourdomain.com/wp-content/export-secure/export_orders.php?token=your_secure_token_here"

1. Import Orders from Your Endpoint

Use this function to:

• Fetch live order data via UrlFetchApp
• Parse CSV
• Filter for only "completed" orders
• Overwrite your "Data" sheet with filtered results

function updateData() {
  const url = "https://yourdomain.com/wp-content/export-secure/export_orders.php?token=your_secure_token_here";
  const response = UrlFetchApp.fetch(url);
  const csvData = Utilities.parseCsv(response.getContentText());

  const completed = csvData.filter((row, i) => i === 0 || row[2] === 'completed');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data") || SpreadsheetApp.getActiveSpreadsheet().insertSheet("Data");

  sheet.clear();
  sheet.getRange(1, 1, completed.length, completed[0].length).setValues(completed);
}

2. Create a “Sales Report” Sheet

This formats your filtered data, assigns serial numbers (Sno), and builds a structured report with invoice numbers like:

function generateSalesReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("Data");
  const salesSheet = ss.getSheetByName("Sales Report") || ss.insertSheet("Sales Report");

  salesSheet.clear();
  const data = dataSheet.getDataRange().getValues();

  // Sort and structure the report
  data.sort((a, b) => (a[0] - b[0]) || new Date(b[62]) - new Date(a[62]));

  const header = data[0].concat(["Sno", "Invoice Date", "Invoice Number"]);
  const salesData = [];
  const orderMap = {};

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const id = row[0];
    const date = new Date(row[62]);
    const sno = orderMap[id] || Object.keys(orderMap).length + 1;
    orderMap[id] = sno;

    const invoiceNo = Utilities.formatDate(date, Session.getScriptTimeZone(), "yy-MM") + "-" + id + "-" + ("000000" + sno).slice(-7);
    salesData.push(row.concat([sno, date, invoiceNo]));
  }

  salesSheet.getRange(1, 1, 1, header.length).setValues([header]);
  salesSheet.getRange(2, 1, salesData.length, header.length).setValues(salesData);
}

3. Generate the GST Report for Previous Month

This function:

• Filters last month’s sales
• Groups by order ID
• Computes invoice-level GST
• Creates a new sheet file in Drive
• Emails it as an attachment

function generateGSTReport() {
  updateData();
  generateSalesReport();

  const salesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales Report");
  const data = salesSheet.getDataRange().getValues();

  const today = new Date();
  const prevMonthStart = new Date(today.getFullYear(), today.getMonth() - 1, 1);
  const prevMonthEnd = new Date(today.getFullYear(), today.getMonth(), 0, 23, 59, 59, 999);

  const filtered = data.filter(row => new Date(row[69]) >= prevMonthStart && new Date(row[69]) <= prevMonthEnd);
  const orders = {};

  filtered.forEach(row => {
    const id = row[0];
    const total = parseFloat(row[11]);
    const tax = parseFloat(row[12]);
    const name = row[40] + ' ' + row[41];
    const state = row[57];

    if (!orders[id]) {
      orders[id] = {
        orderId: id,
        invoiceDate: row[69],
        invoiceNumber: row[70],
        customerName: name,
        totalSum: 0,
        totalTaxSum: 0,
        totalCount: 0,
        shippingState: state,
      };
    }

    orders[id].totalSum += total;
    orders[id].totalTaxSum += tax;
    orders[id].totalCount++;
  });

  const rows = [["Order ID", "Invoice Date", "Invoice Number", "Customer Name", "Total Amount", "GST Amount", "Shipping State", "Source State", "GST Rate", "GSTN"]];
  Object.values(orders).forEach(o => {
    rows.push([
      o.orderId,
      o.invoiceDate,
      o.invoiceNumber,
      o.customerName,
      (o.totalSum / o.totalCount).toFixed(2),
      (o.totalTaxSum / o.totalCount).toFixed(2),
      o.shippingState,
      "MH",
      "12%",
      "27ABICS6682G1ZU"
    ]);
  });

  const label = Utilities.formatDate(prevMonthStart, Session.getScriptTimeZone(), "MMM-yy");
  const newSheet = SpreadsheetApp.create("GST Report_" + label);
  newSheet.getSheets()[0].getRange(1, 1, rows.length, rows[0].length).setValues(rows);

  const folder = DriveApp.getFolderById("your-folder-id");
  const file = DriveApp.getFileById(newSheet.getId());
  folder.addFile(file);
  DriveApp.getRootFolder().removeFile(file);

  const total = Object.values(orders).reduce((a, o) => a + parseFloat(o.totalSum / o.totalCount), 0).toFixed(2);
  const gst = Object.values(orders).reduce((a, o) => a + parseFloat(o.totalTaxSum / o.totalCount), 0).toFixed(2);

  GmailApp.sendEmail("you@example.com", `GST Report - ${label}`, `Please find attached the GST report for { enter your GST#}.\nTotal Orders: ${rows.length - 1}\nSales: ₹${total}\nGST: ₹${gst}\n${newSheet.getUrl()}`, {
    attachments: [file]
  });
}

Folder Setup Summary

Example Output (Final Email)

Subject: GST Report – Apr 2025
Body:

Please find attached the GST report for GSTN:xxxxxxxxxxxx.

Total Orders: 42  
Total Sales: ₹3,24,870.00  
GST Collected: ₹38,090.00  
Report Link: [view on Google Sheets]

Warning: We’ve prepared this script for our internal reporting and hence, you may have adjust references to columns.

Feel free to share your queries in comment.