script.js

/**
 * Fetches WooCommerce product data and writes it to the active Google Sheet.
 */
function fetchWooCommerceProducts() {
    // WooCommerce API credentials
    const API_URL = 'https://example.com/wp-json/wc/v3'; // Replace with your WordPress site URL
    const CONSUMER_KEY = 'consumer key'; // Replace with your Consumer Key
    const CONSUMER_SECRET = 'consumer secret'; // Replace with your Consumer Secret
  
    // Google Sheet setup
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    sheet.clear(); // Clear existing data
    sheet.appendRow([
      'id',
      'name',
      'type',
      'parent_id',
      'short_description',
      'regular_price',
      'sale_price',
      'categories',
      'manage_stock',
      'stock_quantity'
    ]); // Add headers
  
    // Fetch products from WooCommerce API
    let page = 1;
    let hasMoreData = true;
  
    while (hasMoreData) {
      const endpoint = `${API_URL}/products?per_page=100&page=${page}`; // Fetch 100 products per page
      const options = {
        method: 'get',
        headers: {
          'Authorization': 'Basic ' + Utilities.base64Encode(`${CONSUMER_KEY}:${CONSUMER_SECRET}`)
        }
      };
  
      const response = UrlFetchApp.fetch(endpoint, options);
      const products = JSON.parse(response.getContentText());
  
      if (products.length === 0) {
        hasMoreData = false; // No more products to fetch
      } else {
        // Process each product
        products.forEach(product => {
          const categories = product.categories.map(cat => cat.name).join(', ');
          sheet.appendRow([
            product.id,
            product.name,
            product.type,
            product.parent_id,
            product.short_description,
            product.regular_price,
            product.sale_price,
            categories,
            product.manage_stock,
            product.stock_quantity
          ]);
        });
  
        page++; // Move to the next page
      }
    }
  
    Logger.log('Product data fetched and written to the sheet.');
  }