How to Connect Woocommerce Products Inventory with Google Sheet Using API?

Managing your WooCommerce store’s inventory can be strenuous.

However, integrating WooCommerce with Google Sheets can streamline this process, ensuring your inventory is always up-to-date and easily accessible.

This post will walk you through the steps to connect WooCommerce products inventory with Google Sheets using the API.

Why Connect WooCommerce to Google Sheets?

Having your WooCommerce inventory linked to Google Sheets provides several benefits:

• Real-Time Updates: Automatically update your inventory in real-time.
• Accessibility: Access your inventory from anywhere.
• Collaboration: Easily share and collaborate with team members.
• Data Analysis: Use Google Sheets’ features to analyze your data.

Prerequisites

Before we start, ensure you have:

• A WooCommerce store.
• A Google account.
• Basic knowledge of APIs and Google Apps Script.

Step-by-Step Guide

Step 1: Set Up WooCommerce API

• Enable REST API in WooCommerce:
  • Go to your WordPress dashboard.
  • Navigate to WooCommerce > Settings > Advanced > REST API.

• Click on Add Key.

• Fill in the Description, select the user, and set Permissions to Read/Write.

• Click Generate API Key.

• Save Your API Keys:
  • You wSave Your Keys: You’ll receive a Consumer Key and Consumer Secret. Keep these safe, you’ll need them later!

Step 2: Create a Google Sheet

1. Create a New Google Sheet:
   • Open Google Sheets.
   • Create a new spreadsheet and name it (e.g., WooCommerce Inventory).
2. Set Up Columns:
   • Add the necessary columns such as Product ID, Name, SKU, Stock Quantity, Price, etc.

Step 3: Write Google Apps Script

• Open Script Editor:
  • In your Google Sheet, go to Extensions > Apps Script.
• Write the Script:
  • Replace the default code with the following script:

function fetchWooCommerceData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); // Replace 'Sheet1' with your sheet name
  var url = 'https://your-woocommerce-store.com/wp-json/wc/v3/products'; // Replace with your store URL
  var consumerKey = 'ck_your_consumer_key'; // Replace with your Consumer Key
  var consumerSecret = 'cs_your_consumer_secret'; // Replace with your Consumer Secret

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

  var response = UrlFetchApp.fetch(url, options);
  var products = JSON.parse(response.getContentText());

  sheet.clear(); // Clear existing data
  sheet.appendRow(['Product ID', 'Name', 'SKU', 'Stock Quantity', 'Price']); // Set headers

  products.forEach(function(product) {
    sheet.appendRow([product.id, product.name, product.sku, product.stock_quantity, product.price]);
  });
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('WooCommerce')
      .addItem('Fetch Data', 'fetchWooCommerceData')
      .addToUi();
}

• Run the Script:
  • Save the script and click the run button (►) to execute it.

• Authorize the Script:
  • You will be prompted to authorize the script to access your Google Sheet and make external requests.

Step 4: Automate Data Fetching

1. Set Up Triggers:
   • In the Apps Script editor, go to Triggers (clock icon) > + Add Trigger.
   • Choose fetchWooCommerceData for the function to run.
   • Select the event source as Time-driven and choose the frequency (e.g., daily).

FAQ

Q1: What if I get an authentication error?

Ensure your Consumer Key and Secret are correct and that you have sufficient permissions set in WooCommerce (Read/Write).

Q2: Can I update inventory quantities from Google Sheets back to WooCommerce?

Yes, but it requires additional scripting to send data back to WooCommerce using the API’s POST or PUT methods.

Q3: How do I handle large inventories?

For large inventories, consider paginating the requests by modifying the URL to include parameters like ?page=1&per_page=100.

Q4: Is there a limit to how often I can fetch data?

Google Apps Script has quotas and limitations. Refer to Google’s Quota documentation for more details.

Q5: Can I filter products fetched from WooCommerce?

Yes, you can add query parameters to the URL, such as ?status=publish or ?category=clothing.

Conclusion

Connecting your WooCommerce inventory with Google Sheets using API can simplify inventory management and improve accessibility and collaboration. By following this guide, you should now have a seamless link between your WooCommerce store and a Google Sheet. Happy managing!