Managing a WooCommerce store with hundreds of products can be a daunting task. Manually adding, updating, and tracking each item is time-consuming, repetitive, and prone to errors. What if you could manage your entire product catalog from a simple Google Sheet and have it automatically sync with your online store?
In this comprehensive guide, we'll build a powerful automation script using Python that does exactly that. We will connect to the Google Sheets API to read product data and use the WooCommerce REST API to create or update products in your store. By the end, you'll have a fully functional tool that can save you hours of manual work. Let's get started!
Before we write any code, make sure you have the following:
First, we need to tell WooCommerce to allow our script to make changes. We do this by generating API keys.
Important: WooCommerce will show you a Consumer Key and a Consumer Secret. Copy and save these somewhere safe immediately. You will not be able to see the secret again!
This is the most involved setup process, so follow carefully.
.json file will be downloaded. Treat this file like a password! Save it in your project directory..json file you downloaded. Inside, you'll find a client_email address (e.g., ...gserviceaccount.com). Copy it.client_email into the sharing dialog and give it "Editor" permissions.Create a sheet with clear column headers. For this example, we'll use:
| sku | name | type | regular_price | description | image_url |
| TSHIRT-001 | Cool T-Shirt | simple | 19.99 | A very cool shirt. | https://.../image.jpg |
| HOODIE-002 | Warm Hoodie | simple | 39.99 | A very warm hoodie. | https://.../image2.jpg |
Експортиране в Таблици
The header names should match the fields in the WooCommerce API for simplicity. The SKU is crucial for checking if a product already exists.
Now for the fun part! Create a new folder for your project, place your Google API .json key file inside, and create a file named sync_products.py.
Step 1: Install Libraries Open your terminal and install the necessary Python packages:
Bash
pip install gspread oauth2client woocommerce
Step 2: Write the Code Paste the following code into sync_products.py. Be sure to fill in the placeholder variables at the top.
Python
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from woocommerce import API
# --- CONFIGURATION ---
# WooCommerce API credentials
WC_STORE_URL = "https://your-store.com"
WC_CONSUMER_KEY = "ck_xxxxxxxxxxxx"
WC_CONSUMER_SECRET = "cs_xxxxxxxxxxxx"
# Google Sheets API credentials
GOOGLE_SHEET_NAME = "Your Google Sheet Name" # The name of your spreadsheet
GOOGLE_API_JSON_FILE = "your-credentials-file.json" # The path to your downloaded JSON key
# --- INITIALIZE APIS ---
def connect_to_woocommerce():
"""Initializes and returns the WooCommerce API client."""
wcapi = API(
url=WC_STORE_URL,
consumer_key=WC_CONSUMER_KEY,
consumer_secret=WC_CONSUMER_SECRET,
version="wc/v3"
)
return wcapi
def connect_to_google_sheets():
"""Initializes and returns the Google Sheets client."""
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name(GOOGLE_API_JSON_FILE, scope)
client = gspread.authorize(creds)
sheet = client.open(GOOGLE_SHEET_NAME).sheet1 # Get the first sheet
return sheet.get_all_records() # Returns a list of dictionaries
# --- MAIN LOGIC ---
def sync_products():
"""Main function to sync products from Google Sheets to WooCommerce."""
print("Connecting to APIs...")
wcapi = connect_to_woocommerce()
products_from_sheet = connect_to_google_sheets()
print(f"Found {len(products_from_sheet)} products in the Google Sheet.")
for product in products_from_sheet:
sku = product.get('sku')
if not sku:
print(f"Skipping product with no SKU: {product.get('name')}")
continue
print(f"Processing product with SKU: {sku}...")
# Prepare product data for WooCommerce API
data = {
"name": product.get('name'),
"type": product.get('type', 'simple'),
"sku": sku,
"regular_price": str(product.get('regular_price', '0')),
"description": product.get('description', ''),
"images": [
{
"src": product.get('image_url')
}
] if product.get('image_url') else []
}
# Check if product with this SKU already exists
response = wcapi.get("products", params={"sku": sku})
existing_products = response.json()
if existing_products:
# Update existing product
product_id = existing_products[0]['id']
print(f"Product exists (ID: {product_id}). Updating...")
wcapi.put(f"products/{product_id}", data)
else:
# Create new product
print("Product does not exist. Creating...")
wcapi.post("products", data)
print("Synchronization complete!")
# --- RUN SCRIPT ---
if __name__ == "__main__":
sync_products()CONFIGURATION section of the script.python sync_products.pyYou should see output in your terminal as the script processes each row. After it's done, go to the Products section in your WooCommerce dashboard. Your new products should be there!
This script is a fantastic starting point. Here’s how you could make it even better:
try...except blocks to handle network issues or API errors gracefully.You have successfully bridged the gap between a simple spreadsheet and a powerful e-commerce platform. By leveraging the APIs of Google Sheets and WooCommerce, you've created a scalable and efficient workflow for managing your products. This automation not only saves time but also ensures data consistency across your platforms. Happy automating!
