Introduction
Running a WooCommerce store generates a wealth of customer information—names, email addresses, billing and shipping details—that can be invaluable for marketing, analytics, and customer support. However, manually extracting this data from the WooCommerce dashboard quickly becomes impractical as your store grows. By linking WooCommerce with Google Sheets through Google Apps Script, you can automate the export of customer records, keeping a live spreadsheet that updates with each new order or registration. This article walks you through the entire process: configuring the WooCommerce REST API, preparing a Google Sheet, writing a lightweight Apps Script that pulls the relevant fields, and finally scheduling the script to run automatically. Follow each step to create a reliable, real‑time connection between your e‑commerce platform and Google’s collaborative spreadsheet environment.
Setting Up the WooCommerce REST API
Before any data can be accessed, WooCommerce must expose it via its REST API. Navigate to WooCommerce → Settings → Advanced → REST API and click “Add key”. Generate a new consumer key and secret, assigning the Read permission to the “Customer” resource. Keep these credentials safe; they will be used by the Apps Script to authenticate each request. It’s also wise to enable HTTPS on your site to protect the credentials during transmission. Once the keys are created, test the endpoint with a tool like Postman or cURL to ensure you receive a JSON payload containing customer objects.
Creating the Google Sheet and Apps Script Project
Open a new Google Sheet where the customer data will reside. In the first row, label the columns you wish to capture—e.g., Name, Email, Billing Address, Shipping Address. Next, go to Extensions → Apps Script to launch the script editor. Delete any starter code and give the project a meaningful name such as “WooCommerce Customer Export”. This environment provides a sandboxed JavaScript runtime that can call external APIs, parse JSON, and write directly to the sheet.
Writing the Apps Script to Pull Customer Data
The core of the solution is a function that fetches customers from WooCommerce and populates the spreadsheet. Below is a concise outline of the logic (the actual code should be placed in the script editor):
- Define constants: store URL, consumer key, secret, and the target sheet.
- Build the authentication header using Base64 encoding of
key:secret. - Request the customers endpoint with pagination (WooCommerce returns 100 records per page by default).
- Iterate through the JSON response, extracting first_name, last_name, email, and address fields.
- Write rows to the sheet in one batch operation to minimize API calls.
By handling pagination inside a while loop, the script will continue fetching until no further pages exist, ensuring every customer—no matter how many—gets recorded. Remember to use Utilities.sleep(200) if you approach the API rate limit.
Automating and Testing the Export Process
After confirming the script runs correctly in the editor (use the Run button and check the sheet for populated rows), set up a trigger to automate future exports. In the Apps Script UI, choose Triggers → Add Trigger, select the export function, and schedule it to run daily, hourly, or after each form submission, depending on your needs. Test the trigger by creating a new test customer in WooCommerce; within the next scheduled run, the new entry should appear in the Google Sheet without manual intervention. If you encounter authentication errors, double‑check that the consumer key and secret are still valid and that your site’s SSL certificate is trusted.
Conclusion
Exporting WooCommerce customers to Google Sheets via Apps Script transforms a static list of buyers into a dynamic, shareable resource that can power email campaigns, segment analysis, and real‑time reporting. By first securing the REST API, then preparing a clean spreadsheet and a focused script, you establish a robust pipeline that automatically captures every customer’s name, email, and address. Scheduling the script ensures the data stays fresh, while pagination and batch writes keep the process efficient and within API limits. Implementing these steps not only saves countless hours of manual copying but also opens the door to deeper integrations—such as linking the sheet to CRM tools or visualizing trends with Google Data Studio—empowering your e‑commerce business to make data‑driven decisions with ease.
