Learn how to Export WooCommerce Clients to Google Sheets

Discover ways to simply export your WooCommerce clients e mail, identify and tackle to Google Sheets utilizing Google Apps Script. The script will create a brand new tab in your Google Sheet and replica the info from the WooCommerce clients desk.

In case you are working a web based retailer working on WordPress, likelihood is you might be utilizing WooCommerce to handle your clients and orders. The vacation season in close to and chances are you’ll need to ship your current clients a particular low cost code for his or her subsequent buy. Or chances are you’ll need to analyze your retailer’s information to see how what you are promoting is performing in varied areas.

You may the built-in export characteristic of WooCommerce to export your clients information to a CSV file after which import the CSV file into Google Sheets. Go to your WooCommerce dashboard, navigate to the Clients part, and also you’ll discover an choice to obtain the purchasers listing as a CSV file.

In case you are nonetheless searching for a extra environment friendly solution to export your WooCommerce clients to Google Sheets, you need to use Google Apps Script to create a customized script that may export the purchasers to a Google Sheet.

Step 1: Create an API Key in WooCommerce

To get began, you’ll create an API key in WooCommerce. Go to your WooCommerce dashboard, navigate to the Settings part, after which click on on the “Superior” tab. Go to the “Relaxation API” part and click on on the “Create API Key” button.

On the following display screen, you’ll be requested to enter a reputation for the API key. You need to use a reputation like “Import Clients to Google Sheets” or one thing comparable. You may prohibit the API key permissions to learn solely, which is all we’d like since we’re solely going to be studying buyer information and never modifying any information.

WooCommerce will generate the patron key and shopper secret for you. You’ll want to avoid wasting the key key someplace, as you received’t have the ability to entry it later from the WooCommerce dashboard.

Step 2: Create a Google Sheet

Now that you’ve your WooCommerce credentials, let’s create a Google Sheet to retailer the shopper information. Sort sheets.new in your browser’s tackle bar to create a brand new spreadsheet. Go to Extensions > Apps Script to open the Google Apps Script editor related along with your spreadsheet.

Paste the next code into the Apps Script editor. Bear in mind to switch the WooCommerce shopper key, shopper secret and WordPress area with your personal values. Don’t add a slash on the finish of the WordPress area.

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 = (choices) => {
    return Object.keys(choices)
      .map((key) => `${key}=${choices[key]}`)
      .be part of('&');
  };

  const getApiUrl = (pageNum) => {
    const choices = {
      context: 'view',
      web page: pageNum,
      per_page: MAX_PER_PAGE,
      order: 'desc',
      orderby: 'id',
      position: 'buyer',
    };
    return `${WORDPRESS_DOMAIN}/wp-json/wc/v3/clients?${getQueryString(choices)}`;
  };

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

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

  let web page = 1;
  let allCustomers = [];
  let hasMore = true;

  do {
    const clients = fetchPage(web page);
    allCustomers = allCustomers.concat(clients);
    web page += 1;
    hasMore = clients.size === MAX_PER_PAGE;
  } whereas (hasMore === true);

  return allCustomers;
};

The above script will fetch all the purchasers out of your WooCommerce retailer. Subsequent, we’ll add a perform to flatten the shopper information and retailer it in a Google Sheet.

Step 3: Flatten the Buyer Knowledge

To flatten the shopper information, we’ll add the next perform to the script.

const parseCustomer = (buyer) => {
  const { id, first_name, last_name, e mail, billing = {} } = buyer;
  return {
    customer_id: id,
    first_name,
    last_name,
    customer_email: e mail,
    billing_first_name: billing.first_name,
    billing_last_name: billing.last_name,
    billing_email: billing.e mail,
    billing_phone: billing.cellphone,
    billing_address_1: billing.address_1,
    billing_address_2: billing.address_2,
    billing_city: billing.metropolis,
    billing_state: billing.state,
    billing_postcode: billing.postcode,
    billing_country: billing.nation,
  };
};

Step 4: Retailer the Buyer Knowledge

To retailer the shopper information in a Google Sheet, we’ll add the next perform to the script.

const exportCustomersToGoogleSheet = () =>  ''));
  const information = [headers, ...rows];
  const sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
  sheet.getRange(1, 1, information.size, information[0].size).setValues(information);
  const message = rows.size + ' clients exported to sheet ' + sheet.getName();
  SpreadsheetApp.getUi().alert(message);
;

Step 5: Run the Export Perform

Contained in the Apps Script editor, click on on the “exportCustomersToGoogleSheet” perform after which click on on the “Run” button. Authorize the script and watch as your clients information from WooCommerce magically seems in your Google Sheet.

You may then use Gmail Mail Merge to ship customized emails to your clients proper contained in the Google Sheet.