Update WooCommerce Prices from Google Sheets for free: A Step-by-Step Guide

I will walk you through the steps to set up a system where you can update WooCommerce product prices directly from Google Sheets.

We start by enabling the WooCommerce REST API. Next, we export products to Google Sheets. Then, We integrate the provided Google Script code into your Google Sheet.

How to update WooCommerce Prices from Google Sheets

Prerequisites

A WooCommerce store
A Google account with access to Google Sheets

Step 1: Enable the WooCommerce REST API

Log in to your WordPress dashboard
Navigate to WooCommerce > Settings > Advanced > REST API
Click on Add Key
Fill in the description, select a user with appropriate permissions, and set permissions to Read/Write
Click Generate API Key
Copy the Consumer Key and Consumer Secret

Step 2: Export products to a CSV file

IN your WordPress dashboard navigate to Products
Click on the Export button
Configure the settings on the Export Products page according to the image below
Click the Generate CSV button and download the CSV file

Step 3: Import products to your Google Sheet

Open a new or existing Google Sheets document.
Go to File in the top menu and click Import.
In the Open a file window, choose the Upload tab.
Select the CSV file from your computer.
Click Import data.

Your Google sheet should have 7 columns, exactly like the image below.

Step 4: Add the Script

In your Google Sheet, click on Extensions > Apps Script.
Delete any existing code and paste the following script

Show code (for simple and variable products)

Want to customize this code or add new features? Click here!

function updateProductPricesCaller() {
  var consumerKey = '000000000000000000';
  var consumerSecret = '000000000000000000';
  var siteUrl = 'https://your_site_url/';
  updateProductPrices(consumerKey, consumerSecret, siteUrl);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('WordPress')
    .addItem('Synchronize Prices', 'updateProductPricesCaller')
    .addToUi();
  refreshUpdateColumn();
}

function refreshUpdateColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastColumn = sheet.getLastColumn();
  var headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  var updateColIndex = headers.indexOf("Update") + 1; // Get the index of the Update column (1-based index)

  // If the Update column exists, delete it
  if (updateColIndex > 0) {
    sheet.deleteColumn(updateColIndex);
  }

  // Add the new Update column with checkboxes
  lastColumn = sheet.getLastColumn(); // Update lastColumn after potential deletion
  sheet.insertColumnAfter(lastColumn);
  sheet.getRange(1, lastColumn + 1).setValue("Update");

  // Add checkboxes for each row in the new "Update" column
  var dataRange = sheet.getRange(2, lastColumn + 1, sheet.getLastRow() - 1, 1);
  dataRange.insertCheckboxes();
}

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;

  // Get the edited cell's row and column
  var row = range.getRow();
  var col = range.getColumn();

  // Get the last column and the position of the "Update" column
  var lastColumn = sheet.getLastColumn();
  var headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  var updateCol = headers.indexOf("Update") + 1; // Add 1 because headers is zero-indexed

  // Check if the edited cell is in the Sale Price or Regular Price column
  var priceColumns = [4, 5, 6, 7]; // Adjust based on the actual column positions
  if (priceColumns.indexOf(col) !== -1) {
    // Get the checkbox cell
    var checkboxCell = sheet.getRange(row, updateCol);

    // Check the checkbox
    checkboxCell.setValue(true);
  }
}

function updateProductPrices(consumerKey, consumerSecret, siteUrl) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();

  var idCol = 0;
  var typeCol = 1;
  var regularPriceCol = 5;
  var salePriceCol = 4;
  var variationRegularPriceCol = 5;
  var variationSalePriceCol = 4;
  var updateCol = data[0].indexOf("Update"); // Get the actual column index of "Update"

  var simpleBatch = [];
  var variationBatches = {};
  var batchSize = 100; // Maximum number of products to update in one batch

  for (var i = 1; i < data.length; i++) { // Start from 1 to skip the header row
    var productId = data[i][idCol];
    var productType = data[i][typeCol];
    var regularPrice = data[i][regularPriceCol];
    var salePrice = data[i][salePriceCol];
    var variationRegularPrice = data[i][variationRegularPriceCol];
    var variationSalePrice = data[i][variationSalePriceCol];
    var update = data[i][updateCol];

    if (update) {
      if (productType === 'simple') {
        simpleBatch.push({
          id: productId,
          regular_price: regularPrice ? regularPrice.toString() : "",
          sale_price: salePrice ? salePrice.toString() : ""
        });

        if (simpleBatch.length >= batchSize) {
          sendBatchRequest(simpleBatch, siteUrl + 'wp-json/wc/v3/products/batch', consumerKey, consumerSecret);
          simpleBatch = [];
        }
      } else if (productType === 'variation') {
        var variationId = productId;
        var parentId = findParentProductId(data, variationId, idCol, typeCol);

        if (!variationBatches[parentId]) {
          variationBatches[parentId] = [];
        }

        variationBatches[parentId].push({
          id: variationId,
          regular_price: variationRegularPrice ? variationRegularPrice.toString() : "",
          sale_price: variationSalePrice ? variationSalePrice.toString() : ""
        });

        if (variationBatches[parentId].length >= batchSize) {
          sendBatchRequest(variationBatches[parentId], siteUrl + 'wp-json/wc/v3/products/' + parentId + '/variations/batch', consumerKey, consumerSecret);
          variationBatches[parentId] = [];
        }
      }

      sheet.getRange(i + 1, updateCol + 1).setValue(false); // Uncheck the checkbox after updating
    }
  }

  // Send any remaining requests in the batches
  if (simpleBatch.length > 0) {
    sendBatchRequest(simpleBatch, siteUrl + 'wp-json/wc/v3/products/batch', consumerKey, consumerSecret);
  }

  for (var parentId in variationBatches) {
    if (variationBatches[parentId].length > 0) {
      sendBatchRequest(variationBatches[parentId], siteUrl + 'wp-json/wc/v3/products/' + parentId + '/variations/batch', consumerKey, consumerSecret);
    }
  }
}

function findParentProductId(data, variationId, idCol, typeCol) {
  var parentId = variationId - 1;

  while (parentId > 0) {
    for (var i = 1; i < data.length; i++) {
      if (data[i][idCol] == parentId && data[i][typeCol] == 'variable') {
        return parentId;
      }
    }
    parentId--;
  }

  return null; // or throw an error if the parent product ID is not found
}

function sendBatchRequest(batch, url, consumerKey, consumerSecret) {
  var payload = {
    update: batch
  };

  var options = {
    method: "POST",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    headers: {
      "Authorization": "Basic " + Utilities.base64Encode(consumerKey + ":" + consumerSecret)
    }
  };

  try {
    var response = UrlFetchApp.fetch(url, options);
    Logger.log("Batch update successful: " + response.getContentText());
  } catch (error) {
    Logger.log("Error in batch update: " + error.message);
  }
}

Explanation of the Script

onOpen(): Adds the custom menu and refreshes the update column whenever the Google Sheet is opened.

refreshUpdateColumn(): Deletes the existing “Update” column and creates a new one with checkboxes.

onEdit(e): Automatically checks the “Update” checkbox when you edit the “Regular Price” or “Sale Price” columns.

updateProductPrices(): Reads the data from the Google Sheet and prepares batches for updating the product prices.

sendBatchRequest(): Sends batch update requests to the WooCommerce REST API.

Replace your_consumer_key, your_consumer_secret, and https://your_site_url/ with your actual WooCommerce credentials and site URL.
Save the script.
At the top right, click Deploy > New deployment.
Next to Select type click Enable deployment types ⚙️.
Select Web app. Write New Description and click on Deploy button.

Next click the Authorize access button and complete the Authorize steps.

Head over to the Google Sheet containing your products and refresh it.

Update the prices of the products you wish to modify.

Notice that the checkbox in the “Update” column becomes active upon changing the product prices.

The prices of all products with a checked “Update” column will be updated in WooCommerce.

Now, navigate to the WordPress menu, click on the Synchronize Prices option, and wait for the script to execute, sending updated prices from the Google Sheet to your website.

And there you have it! Your WooCommerce product prices will be seamlessly synchronized with your Google Sheet,

Update: How to Update Stock and Stock Quantity from Google Sheets

In addition to updating prices, you can now manage stock status and stock quantity for your WooCommerce products directly from Google Sheets.

The process is simpler than before—there’s no need to export products to a CSV file. Our new script automatically fetches product data from your site. Here’s how to set it up:

Enable the WooCommerce REST API: Add your site’s consumerKey, consumerSecret, and URL to the code snippet provided below:

Show code (Free Version)

// @Update: WPCookie (Maya1535@gmail.com)

function constants() {
  var consumerKey = '0000000000000000000'; 
  var consumerSecret = '0000000000000000000';
  var siteUrl = 'https://0000000000000000000.com/';

  return [consumerKey,consumerSecret, siteUrl];
}


function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('WordPress')
    .addItem('Send Updated Prices', 'updateProductPrices')
    .addItem('Fetch Products', 'fetchProducts')
.addToUi();
  refreshUpdateColumn();
}

function fetchProducts() {
  var consumerKey = constants()[0];
  var consumerSecret = constants()[1];
  var siteUrl = constants()[2];
  siteUrl = siteUrl.replace(/\/$/, '');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tempFetchSheet = ss.getSheetByName("TempFetch");
  if (!tempFetchSheet) {
    tempFetchSheet = ss.insertSheet("TempFetch");
    var page = 1;
    tempFetchSheet.getRange("A1").setValue(page);
  } else {
	var pageValue = tempFetchSheet.getRange("A1").getValue();
	if (typeof pageValue === 'number') {
      var page = pageValue;
	} else {
	  var page = 1;
	}
  }
  var sheet = ss.getSheetByName('Products');
  var headers = ['ID', 'Type', 'SKU', 'Name', 'In stock?', 'Stock', 'Sale price', 'Regular price', 'Parent'];
  if ( page == 1 || !sheet )
  {
	    if (sheet) {
        ss.deleteSheet(sheet);
		}
		sheet = ss.insertSheet('Products');
	  if (sheet.getLastRow() === 0) {
		sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
		sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
	  }
	  page = 1;
  }
  var perPage = 10; // WooCommerce default max per page
  var currentRow = Math.max(sheet.getLastRow() + 1, 2); // Start after headers or last row
  while ( page < 6 ) {
    var products = fetchProductBatch(siteUrl, consumerKey, consumerSecret, page, perPage);
    var productData = [];
    if (!products || products.length === 0) {
      break;
    }
    products.forEach(function(product) {
		SpreadsheetApp.flush();
      productData.push([
        product.id,
        product.type,
        product.sku,
        product.name,
        product.stock_status === 'instock' ? 1 : 0,
        product.stock_quantity || "",
        product.type === 'variable' ? '' : (product.sale_price || ''),
        product.type === 'variable' ? '' : (product.regular_price || product.price || ''),
        ''
      ]);
      // For variable products, fetch and write variations
      if (product.type === 'variable') {
        var variations = fetchVariations(siteUrl, consumerKey, consumerSecret, product.id);
        if (variations) {
          variations.forEach(function(variation) {
            productData.push( [
              variation.id,
              'variation',
              variation.sku,
              product.name + ' - ' + getVariationAttributes(variation),
              variation.stock_status === 'instock' ? 1 : 0,
              variation.stock_quantity || "",
              variation.sale_price || '',
              variation.regular_price || variation.price || '',
              "id:" + product.id
            ]);
          });
        }
      }
      // Auto-resize columns periodically (every 10 products)
      if (currentRow % 10 === 0) {
        sheet.autoResizeColumns(1, headers.length);
      }
    });
    if (productData.length > 0) {
		productData.forEach(function(product) {
			sheet.getRange(currentRow, 1, 1, headers.length).setValues([product]);
			currentRow++;
		});
      }
    page++;
    tempFetchSheet.getRange("A1").setValue(page);
    Utilities.sleep(500);
  }
  // Final auto-resize and hide columns
  sheet.autoResizeColumns(1, headers.length);
  hideColumns(sheet, ['Type', 'SKU', 'Parent'], headers);
  
  if ( page == 6 ) {
	SpreadsheetApp.getActiveSpreadsheet().toast('You’ve reached the free script limit of 50 products! Please join WPCookie Plus+ to access the Plus version of this script.', 'Ops', 15);  
	} else {
		SpreadsheetApp.getActiveSpreadsheet().toast("Product fetching complete.", 'Success!',10);
		}
  if (tempFetchSheet) {
    ss.deleteSheet(tempFetchSheet);
	}
  // add update check box
  refreshUpdateColumn();
}

function fetchVariations(siteUrl, consumerKey, consumerSecret, productId) {
  var endpoint = '/wp-json/wc/v3/products/' + productId + '/variations';
  var url = siteUrl + endpoint;

  // Set up parameters
  var params = {
    'per_page': 100  // Maximum variations per request
  };

  try {
    var response = makeAuthenticatedRequest(url, params, consumerKey, consumerSecret);
    return JSON.parse(response.getContentText());
  } catch (error) {
    Logger.log('Error fetching variations for product ' + productId + ': ' + error);
    return null;
  }
}

function getVariationAttributes(variation) {
  if (!variation.attributes || variation.attributes.length === 0) {
    return '';
  }

  return variation.attributes
    .map(function(attr) {
      return attr.option;
    })
    .join(', ');
}

function fetchProductBatch(siteUrl, consumerKey, consumerSecret, page, perPage) {
  var endpoint = '/wp-json/wc/v3/products';
  var url = siteUrl + endpoint;

  // Set up parameters
  var params = {
    'per_page': perPage,
    'page': page
  };

  try {
    var response = makeAuthenticatedRequest(url, params, consumerKey, consumerSecret);
    return JSON.parse(response.getContentText());
  } catch (error) {
    Logger.log('Error fetching products: ' + error);
    return null;
  }
}

function makeAuthenticatedRequest(url, params, consumerKey, consumerSecret) {
  // Create OAuth signature
  var timestamp = Math.floor(Date.now() / 1000);
  var nonce = Utilities.getUuid();

  var signatureBaseString = 'GET&' +
    encodeURIComponent(url) + '&' +
    encodeURIComponent(Object.keys(params)
      .sort()
      .map(function(key) {
        return key + '=' + encodeURIComponent(params[key]);
      })
      .concat([
        'oauth_consumer_key=' + consumerKey,
        'oauth_nonce=' + nonce,
        'oauth_signature_method=HMAC-SHA1',
        'oauth_timestamp=' + timestamp,
        'oauth_version=1.0'
      ])
      .sort()
      .join('&'));

  var signature = Utilities.computeHmacSignature(
    Utilities.MacAlgorithm.HMAC_SHA_1,
    signatureBaseString,
    consumerSecret + '&',
    Utilities.Charset.UTF_8
  );

  // Build authorization header
  var authHeader = 'OAuth ' +
    'oauth_consumer_key="' + consumerKey + '", ' +
    'oauth_nonce="' + nonce + '", ' +
    'oauth_signature="' + encodeURIComponent(Utilities.base64Encode(signature)) + '", ' +
    'oauth_signature_method="HMAC-SHA1", ' +
    'oauth_timestamp="' + timestamp + '", ' +
    'oauth_version="1.0"';

  var options = {
    'method': 'GET',
    'headers': {
      'Authorization': authHeader
    },
    'muteHttpExceptions': true
  };

  // Add parameters to URL
  url += '?' + Object.keys(params)
    .map(function(key) {
      return key + '=' + encodeURIComponent(params[key]);
    })
    .join('&');

  return UrlFetchApp.fetch(url, options);
}

function hideColumns(sheet, columnsToHide, headers) {
  columnsToHide.forEach(function(columnName) {
    var columnIndex = headers.indexOf(columnName) + 1;
    if (columnIndex > 0) {
      sheet.hideColumns(columnIndex);
    }
  });
}
//**********************************************//
//************updat price section **************//
//**********************************************//
function refreshUpdateColumn() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Products');
  if (!sheet) {
        return
  }
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var updateColIndex = headers.indexOf("Update") + 1;

  if (updateColIndex > 0) {
    sheet.deleteColumn(updateColIndex);
  }
  var lastColumn = sheet.getLastColumn();
  sheet.insertColumnAfter(lastColumn);
  sheet.getRange(1, lastColumn + 1).setValue("Update");

  var dataRange = sheet.getRange(2, lastColumn + 1, sheet.getLastRow() - 1, 1);
  dataRange.insertCheckboxes();
}

function onEdit(e) {
  var activeSheetName = e.source.getActiveSheet().getName();
  if (activeSheetName !== "Products") {
    return;
  }
  var sheet = e.source.getActiveSheet();
  var range = e.range;

  // Get the edited cell's row and column
  var row = range.getRow();
  var col = range.getColumn();

  // Get the last column and the position of the "Update" column
  var lastColumn = sheet.getLastColumn();
  var headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  var updateCol = headers.indexOf("Update") + 1; // Add 1 because headers is zero-indexed

  // Check if the edited cell is in the Sale Price or Regular Price column
  var priceColumns = [4, 5, 6, 7,8]; // Adjust based on the actual column positions
  if (priceColumns.indexOf(col) !== -1) {
    // Get the checkbox cell
    var checkboxCell = sheet.getRange(row, updateCol);

    // Check the checkbox
    checkboxCell.setValue(true);
  }
}

function updateProductPrices() {
  var consumerKey = constants()[0];
  var consumerSecret = constants()[1];
  var siteUrl = constants()[2];
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();

  var idCol = 0, typeCol = 1, skuCol = 2, inStockCol = 4, stockCol = 5;
  var regularPriceCol = 7, salePriceCol = 6, parentCol = 8, updateCol = data[0].indexOf("Update");

  var simpleBatch = [];
  var variationBatches = {};
  var batchSize = 100;

  for (var i = 1; i < data.length; i++) {
    var productId = data[i][idCol];
    var productType = data[i][typeCol];
    var regularPrice = data[i][regularPriceCol];
    var salePrice = data[i][salePriceCol];
    var inStock = data[i][inStockCol];
    var stockQuantity = data[i][stockCol];
    var update = data[i][updateCol];
    var parent = data[i][parentCol];

    if (update) {
      var productData = {
        id: productId,
        regular_price: regularPrice ? regularPrice.toString() : "",
        sale_price: salePrice ? salePrice.toString() : ""
      };

      // Set stock status based on "In stock?" column
      if (inStock === 1) {
        productData.stock_status = "instock";
      } else if (inStock === 0 || inStock === "") {
        productData.stock_status = "outofstock";
      }

      // Set stock quantity if "Stock" column is not empty
      if (stockQuantity !== "") {
        productData.stock_quantity = stockQuantity;
        productData.manage_stock = true;
      }

      if (productType === 'simple') {
        simpleBatch.push(productData);
        if (simpleBatch.length >= batchSize) {
          sendBatchRequest(simpleBatch, siteUrl + 'wp-json/wc/v3/products/batch', consumerKey, consumerSecret);
          simpleBatch = [];
        }
      } else if (productType === 'variation') {
        var variationId = productId;
        var parentId = findParentProductId(data,i , idCol, typeCol, skuCol, parentCol);
        Logger.log("parent id: "+parentId);

        if (!variationBatches[parentId]) {
          variationBatches[parentId] = [];
        }

        variationBatches[parentId].push({
          id: variationId,
          regular_price: regularPrice ? regularPrice.toString() : "",
          sale_price: salePrice ? salePrice.toString() : "",
          stock_status: productData.stock_status,
          stock_quantity: stockQuantity ? stockQuantity.toString() : "",
          manage_stock: stockQuantity ? true : false
        });

        if (variationBatches[parentId].length >= batchSize) {
          sendBatchRequest(variationBatches[parentId], siteUrl + 'wp-json/wc/v3/products/' + parentId + '/variations/batch', consumerKey, consumerSecret);
          variationBatches[parentId] = [];
        }
      }

      sheet.getRange(i + 1, updateCol + 1).setValue(false);
    }
  }

  if (simpleBatch.length > 0) {
    sendBatchRequest(simpleBatch, siteUrl + 'wp-json/wc/v3/products/batch', consumerKey, consumerSecret);
  }

  for (var parentId in variationBatches) {
    if (variationBatches[parentId].length > 0) {
      sendBatchRequest(variationBatches[parentId], siteUrl + 'wp-json/wc/v3/products/' + parentId + '/variations/batch', consumerKey, consumerSecret);
    }
  }
}

function findParentProductId(data, i, idCol, typeCol, skuCol, parentCol) {
  var parentIdentifier = data[i][parentCol];
  Logger.log("parentIdentifier: "+parentIdentifier);
  for (var j = 1; j < data.length; j++) {
    if (data[j][typeCol] === 'variable') {
      if (parentIdentifier.startsWith("id:") && data[j][idCol] === parseInt(parentIdentifier.split(":")[1])) {
        return data[j][idCol];
      } else if (data[j][skuCol] && data[j][skuCol] === parentIdentifier) {
        return data[j][idCol];
      }
    }
  }

  return null;
}

function sendBatchRequest(batch, url, consumerKey, consumerSecret) {
  var payload = {
    update: batch
  };
  Logger.log(payload);
  var options = {
    method: "POST",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    headers: {
      "Authorization": "Basic " + Utilities.base64Encode(consumerKey + ":" + consumerSecret)
    }
  };

  try {
    var response = UrlFetchApp.fetch(url, options);
    Logger.log("Batch update successful: " + response.getContentText());
  } catch (error) {
    Logger.log("Error in batch update: " + error.message);
  }
}

Show code (Plus+ Version)

This code is for WPCookie PLUS+ members only.

Join WPCookie PLUS+

Already a member? Log in here

Deploy the Script: Copy the code into your Google Sheets App Script and deploy it.

Refresh Google Sheets: After deployment, refresh your Google Sheet. A new “WordPress” menu will appear.

Fetching Products

Open the WordPress menu and select Fetch Products.
Google Sheets will begin fetching your product data automatically.
If the process stops or you encounter a timeout error, click Fetch Products again to resume.

Updating Stock and Prices

To update prices, simply modify the price column in Google Sheets. Then, from the WordPress menu, select Send Updated Prices to sync the changes with your site.

To update stock

In the In Stock? column, enter 1 for in stock or 0 for out of stock.
In the Stock Quantity column, leave it blank if stock management isn’t required. Enter a number to enable stock management and set the stock quantity for the product.

That’s it! With these updates, managing your WooCommerce inventory and pricing has never been easier.

If you are using the caching plugin, please clear the cache after each price update. This practice ensures that your website displays the most current pricing information for your visitors.

If this article is difficult for you to read in text, you can watch the video version below.

How to update WooCommerce Prices from Google Sheets

Prerequisites

Step 1: Enable the WooCommerce REST API

Step 2: Export products to a CSV file

Step 3: Import products to your Google Sheet

Step 4: Add the Script

Update: How to Update Stock and Stock Quantity from Google Sheets

Maya

Related Posts

Save Contact Form 7 Data to Google Sheets without plugin

How to Create Eye-Catching Animated Image Border in WordPress without plugins

Testimonial Block Development