How to Export WooCommerce Customers to Google Sheets

by

Learn how to easily export your WooCommerce customers’ emails, names and addresses to Google Sheets using Google Apps Script. The script will create a new tab in your Google Sheets and copy the data from the WooCommerce Customers table.

If you are running an online store running on WordPress, you are using WooCommerce to manage your customers and orders. The holiday season is just around the corner and you can send your existing customers a special discount code for their next purchase. Or you can analyze your store’s data to see how your business is performing in different areas.

You can use WooCommerce’s built-in export feature to export your customer data to a CSV file and then import the CSV file into Google Sheets. Go to your WooCommerce dashboard, navigate to the Customers section, and you’ll find the option to download a list of customers as a CSV file.

If you’re looking for a more efficient way to export your WooCommerce customers to Google Sheets, you can use Google Apps Script to create a custom script that exports customers to Google Sheets.

Step 1: Create an API key in WooCommerce

To get started, you’ll create an API key in WooCommerce. Go to your WooCommerce dashboard, navigate to the Settings section, and then click the “Advanced” tab. Go to “Rest API” section and click on “Generate API Key” button.

On the next screen, you will be asked to enter a name for the API key. You can use something like “Import Customers into Google Sheets” or something similar. You can limit API key permissions to read only, which is what we need because we will only read client data and not modify any data.

WooCommerce will generate a User Key and User Secret for you. You need to save the secret key somewhere, as you won’t be able to access it later from the WooCommerce dashboard.

Step 2: Create a Google Sheet

Now that you have your WooCommerce credentials, let’s create a Google Sheet to store customer data. Type it sheets.new in your browser’s address bar to create a new spreadsheet. Go to Extensions > Apps Script to open the Google Apps Script editor associated with your spreadsheet.

Paste the following code into the Apps script editor. Remember to replace WooCommerce Customer Key, Customer Secret and WordPress Domain with your own values. Do not add a slash to the end of a WordPress domain.

const MAX_PER_PAGE = 100;
const CONSUMER_KEY = '<>';
const CONSUMER_SECRET = '<>';
const WORDPRESS_DOMAIN = '<>';

const fetchWooCommerceCustomers = () => {
  const bearerToken = Utilities.base64Encode(`${CONSUMER_KEY}:${CONSUMER_SECRET}`);

  const getQueryString = (options) => {
    return Object.keys(options)
      .map((key) => `${key}=${options(key)}`)
      .join('&');
  };

  const getApiUrl = (pageNum) => {
    const options = {
      context: 'view',
      page: pageNum,
      per_page: MAX_PER_PAGE,
      order: 'desc',
      orderby: 'id',
      role: 'customer',
    };
    return `${WORDPRESS_DOMAIN}/wp-json/wc/v3/customers?${getQueryString(options)}`;
  };

  
  const fetchPage = (pageNum) => {
    const url = getApiUrl(pageNum);
    const response = UrlFetchApp.fetch(url, {
      headers: {
        'Content-Type': 'application/json',
        Authorization: `Basic ${bearerToken}`,
      },
    });

    return JSON.parse(response.getContentText());
  };

  let page = 1;
  let allCustomers = ();
  let hasMore = true;

  do {
    const customers = fetchPage(page);
    allCustomers = allCustomers.concat(customers);
    page += 1;
    hasMore = customers.length === MAX_PER_PAGE;
  } while (hasMore === true);

  return allCustomers;
};

The above script will fetch all customers from your WooCommerce store. Next, we’ll add a function to flatten customer data and store it in a Google Sheet.

Step 3: Flatten customer data

To flatten the customer data, we will add the following function to the script.

const parseCustomer = (customer) => {
  const { id, first_name, last_name, email, billing = {} } = customer;
  return {
    customer_id: id,
    first_name,
    last_name,
    customer_email: email,
    billing_first_name: billing.first_name,
    billing_last_name: billing.last_name,
    billing_email: billing.email,
    billing_phone: billing.phone,
    billing_address_1: billing.address_1,
    billing_address_2: billing.address_2,
    billing_city: billing.city,
    billing_state: billing.state,
    billing_postcode: billing.postcode,
    billing_country: billing.country,
  };
};

Step 4: Store customer data

To store customer data in a Google Sheet, we will add the following function to the script.

const exportCustomersToGoogleSheet = () => {
  const wooData = fetchWooCommerceCustomers();
  const customers = wooData.map(parseCustomer);
  const headers = Object.keys(customers(0));
  const rows = customers.map((c) => headers.map((header) => c(header) || ''));
  const data = (headers, ...rows);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
  sheet.getRange(1, 1, data.length, data(0).length).setValues(data);
  const message = rows.length + ' customers exported to sheet ' + sheet.getName();
  SpreadsheetApp.getUi().alert(message);
};

Step 5: Run the export function

Within the Apps Script Editor, click the “exportCustomersToGoogleSheet” function and then click the “Run” button. Authorize the script and watch your customer data from WooCommerce magically appear on your Google Sheets.

You can then use Gmail Mail Merge to send personalized emails to your customers within Google Sheets.

Leave a Comment