Sync WooCommerce Orders with Google Sheets Automatically (No Plugins Required)

Managing your WooCommerce orders can be time-consuming, especially as your business grows. Integrating your store with Google Sheets offers a powerful and flexible way to streamline order management.
This guide will walk you through the steps to achieve this integration without using any plugins.

Step-by-Step Guide to Sync WooCommerce Orders to Google Sheets

This method involves using the WooCommerce REST API, Google Apps Script, and a small PHP code snippet.

Prerequisites

Before we begin, ensure you have:

A working WooCommerce store
A Google account
Administrative access to your WordPress site

1. Enable WooCommerce REST API

Log in to your WordPress dashboard
Navigate to WooCommerce > Settings > Advanced > REST API
Click on Add Key
Fill in the description, select a user with appropriate permissions, and set permissions to Read/Write
Click Generate API Key

We will use the generated keys later.

2. Set up Google Apps Script

Create a new Google Spreadsheet.
Go to Extensions > Apps Script
Paste the provided script into the Apps Script editor

Show script

// Plugin Name: WooCommerce Google Sheets Integration
// Author: WPCookie
// Update: Maya1535 {at} gmail.com

// Global variables
var consumerKey = 'ck_0000000000000000';
var consumerSecret = 'cs_0000000000000000';
var siteUrl = 'https://00000000.com/';
var sheetName = 'Orders';


// Function to create the menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Orders')
    .addItem('Fetch orders', 'fetchOrders')
    .addToUi();

  // Ensure the "Orders" sheet exists and has the correct headers
  createOrdersSheet();
}

// Function to create the "Orders" sheet if it doesn't exist and add headers
function createOrdersSheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
    var headers = ['ID', 'Status', 'Name', 'Phone', 'Billing', 'Products', 'Total', 'Date'];
    sheet.appendRow(headers);

    // Set data validation for the "Status" column
    var statusRange = sheet.getRange(2, 2, sheet.getMaxRows());
    var statusRule = SpreadsheetApp.newDataValidation()
      .requireValueInList(['processing', 'on-hold', 'completed', 'cancelled', 'pending', 'refunded'], true)
      .build();
    statusRange.setDataValidation(statusRule);

    // Format the header
    var headerRange = sheet.getRange(1, 1, 1, headers.length);
    headerRange.setFontWeight('bold')
               .setHorizontalAlignment('center')
               .setBackground('#A52A2A') // Brown background
               .setFontColor('#FFFFFF'); // White text

    // Freeze the top row
    sheet.setFrozenRows(1);

    // Format the "Total" column as number with comma separation
    var totalRange = sheet.getRange(2, 7, sheet.getMaxRows());
    totalRange.setNumberFormat('#,##0.00');

    // Format the "Date" column as date
    var dateRange = sheet.getRange(2, 8, sheet.getMaxRows());
    dateRange.setNumberFormat('yyyy-MM-dd');

    // Left-align the "ID" column
    var idRange = sheet.getRange(2, 1, sheet.getMaxRows());
    idRange.setHorizontalAlignment('left');
  }
}

// Function to fetch orders from WooCommerce
function fetchOrders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  var page = Math.ceil(lastRow / 10); // Calculate the page number based on the number of rows

  var options = {
    'method': 'get',
    'muteHttpExceptions': true,
    'headers': {
      'Authorization': 'Basic ' + Utilities.base64Encode(consumerKey + ':' + consumerSecret)
    }
  };

  var url = siteUrl + 'wp-json/wc/v3/orders?per_page=10&page=' + page;
  var response = UrlFetchApp.fetch(url, options);
  var orders = JSON.parse(response.getContentText());

  if (orders.length > 0) {
    addOrdersToSheet(orders);
  } else {
    SpreadsheetApp.getUi().alert('No more orders to fetch.');
  }
}

// Function to add orders to the sheet
function addOrdersToSheet(orders) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = [];
  orders.forEach(function(order) {
    var status = order.status;
    var name = order.billing.first_name + ' ' + order.billing.last_name;
    var phone = order.billing.phone;
    var billing = [order.billing.address_1, order.billing.address_2, order.billing.city, order.billing.state, order.billing.postcode, order.billing.country, order.billing.email].filter(Boolean).join(', ');
    var products = order.line_items.map(function(item) { return item.name; }).join(', ');
    var total = order.total;
    var date = new Date(order.date_created).toISOString().split('T')[0];

    data.push([order.id, status, name, phone, billing, products, total, date]);
  });

  sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
}

// Function to update order status
function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var column = range.getColumn();
  var row = range.getRow();

  if (sheet.getName() === sheetName && column === 2) {
    var orderId = sheet.getRange(row, 1).getValue();
    var newStatus = range.getValue();
    updateOrderStatus(orderId, newStatus);
  }
}

// Function to update order status in WooCommerce
function updateOrderStatus(orderId, newStatus) {
  var options = {
    'method': 'put',
    'muteHttpExceptions': true,
    'headers': {
      'Authorization': 'Basic ' + Utilities.base64Encode(consumerKey + ':' + consumerSecret),
      'Content-Type': 'application/json'
    },
    'payload': JSON.stringify({
      'status': newStatus
    })
  };

  var url = siteUrl + 'wp-json/wc/v3/orders/' + orderId;
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response.getContentText());
}

// Function to handle incoming data from PHP script
function doPost(e) {
  try {
    // Parse the incoming JSON data
    var order = JSON.parse(e.postData.contents);

    // Get the active sheet for processing orders
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    var orderId = order.id;
    var orderRow = findOrderRow(orderId);

    if (orderRow) {
      // Update existing order
      updateOrderInSheet(orderRow, order);
    } else {
      // Add new order
      addNewOrderToSheet(order);
    }

    // Respond to the request
    return ContentService.createTextOutput('Order received').setMimeType(ContentService.MimeType.TEXT);

  } catch (error) {
    // Log any errors to the console
    Logger.log('Error in doPost: ' + error.message);

    // Return an error message
    return ContentService.createTextOutput('Error processing the order').setMimeType(ContentService.MimeType.TEXT);
  }
}


// Function to find the row of an existing order by ID
function findOrderRow(orderId) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] == orderId) {
      return i + 1; // Return the row number (1-based index)
    }
  }
  return null;
}

// Function to update an existing order in the sheet
function updateOrderInSheet(row, order) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var status = order.status;
  var name = order.name;
  var phone = order.phone;
  var billing = order.billing;
  var products = order.products;
  var total = order.total;
  var date = order.date;

  var data = [order.id, status, name, phone, billing, products, total, date];
  sheet.getRange(row, 1, 1, data.length).setValues([data]);
}

// Function to add a new order to the top of the sheet
function addNewOrderToSheet(order) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var status = order.status;
  var name = order.name;
  var phone = order.phone;
  var billing = order.billing;
  var products = order.products;
  var total = order.total;
  var date = order.date;

  var data = [[order.id, status, name, phone, billing, products, total, date]];

  // Insert the new order at the top of the sheet
  sheet.insertRowBefore(2);
  sheet.getRange(2, 1, 1, data[0].length).setValues(data);
}

Replace the following variables in the script:

consumerKey: Your WooCommerce API consumer key
consumerSecret: Your WooCommerce API consumer secret
siteUrl: Your WordPress site URL

3. Configure Trigger Settings

In Apps Script, click on “Triggers” in the left sidebar Click “Add Trigger” Configure trigger settings:

Choose function: OnEdit
Event type: On edit

Save the trigger configuration

4. Deploy the App

In the Apps Script editor, click “Deploy” > “New deployment”.
Select “Web app”.
Choose “Execute the app as:” “Me” and “Who has access to the app:” “Anyone with Google account” (or adjust permissions as needed).
Click “Deploy”.

Authorize the app to access your Google Drive.

Copy the web app URL.

5. Add PHP Code to Your WordPress Site:

Add the provided PHP code to your WordPress site (e.g., in your theme’s functions.php file or a custom plugin).

You must create a child theme before making any changes to functions.php file. Otherwise, the applied changes will be lost after each update.
Create child theme in WordPress step by step [without plugin]

As an alternative method, you can use the Code Snip pets plugin to insert your codes into WordPress.

Show code

/**
 * Plugin Name: WooCommerce Google Sheets Integration
 * Description: Sends order data to Google Sheets whenever an order is created or updated.
 * Version: 1.1
 * Author: WPCookie | Maya1535 {at} gmail.com
 */

add_action('woocommerce_checkout_order_processed', 'send_order_to_google_sheets', 10, 1);
add_action('woocommerce_order_status_changed', 'send_order_to_google_sheets', 10, 4);

function send_order_to_google_sheets($order_id, $old_status = '', $new_status = '', $order = null) {

    $url = 'https://script.google.com/macros/s/00000000000000000000000000000/exec';

    if (!$order) {
        $order = wc_get_order($order_id);
    }
    if (!$order) {
        error_log('Invalid order ID: ' . $order_id);
        return;
    }
    $order_data = array(
        'id'       => $order->get_id(),
        'status'   => $order->get_status(),
        'name'     => $order->get_billing_first_name() . ' ' . $order->get_billing_last_name(),
        'phone'    => $order->get_billing_phone(),
        'billing'  => implode(', ', array_filter(array(
            $order->get_billing_address_1(),
            $order->get_billing_address_2(),
            $order->get_billing_city(),
            $order->get_billing_state(),
            $order->get_billing_postcode(),
            $order->get_billing_country(),
            $order->get_billing_email()
        ))) ,
        'products' => implode(', ', array_map(function($item) {
            return $item->get_name();
        }, $order->get_items())),
        'total'    => $order->get_total(),
        'date'     => $order->get_date_created() ? $order->get_date_created()->date('Y-m-d') : ''
    );
    $data_json = wp_json_encode($order_data);
    $args = array(
        'body'    => $data_json,
        'headers' => array(
            'Content-Type' => 'application/json',
        ),
    );
    $response = wp_remote_post($url, $args);
    if (is_wp_error($response)) {
        error_log('Error sending order data to Google Sheets: ' . $response->get_error_message());
    } else {
        $response_code = wp_remote_retrieve_response_code($response);
        if ($response_code !== 200) {
            error_log('Unexpected response code: ' . $response_code . '. Response: ' . wp_remote_retrieve_body($response));
        }
    }
}

Replace the placeholder for the Google Apps Script URL ($url) with the URL you copied in the previous step and activate the code.

This PHP code acts as a bridge, sending order data to your Google Apps Script.

6. Fetch Orders

Refresh your Google Sheet. You should now see an “Orders” menu.
Click “Orders” > “Fetch Orders” to retrieve the last 10 orders.
Clicking again will fetch the next 10, and so on.

Google Sheet Order Columns:

The synced orders in your Google Sheet will have the following columns:

ID: The unique order ID.
Status: The current order status (e.g., Processing, Completed, Cancelled). You can update the status here, and it will sync back to WooCommerce.
Name: The customer’s full name.
Phone: The customer’s phone number.
Billing: The customer’s billing address and email.
Products: A comma-separated list of purchased products.
Total: The total order amount.
Date: The order date.

Two-Way Synchronization

This setup provides two-way synchronization. Updating the order status in Google Sheets will update the order status in WooCommerce, and new orders in WooCommerce will automatically appear at the top of your order list in Google Sheets.

Conclusion

This integration streamlines your order management process without requiring expensive plugins or complex setup procedures. It’s particularly valuable for small to medium-sized businesses looking to improve their order processing efficiency while maintaining data accuracy across platforms.

Need help troubleshooting or have questions about customizing the integration? Leave a comment below, and I’ll be happy to assist you.

Step-by-Step Guide to Sync WooCommerce Orders to Google Sheets

1. Enable WooCommerce REST API

2. Set up Google Apps Script

3. Configure Trigger Settings

4. Deploy the App

5. Add PHP Code to Your WordPress Site:

6. Fetch Orders

Maya

Related Posts

How to Migrate Shopify Products to WooCommerce for Free (No Plugins Needed)

Save Contact Form 7 Data to Google Sheets without plugin

Update WooCommerce Prices from Google Sheets for free: A Step-by-Step Guide

Leave a ReplyCancel Reply