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 products)
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') {

      }

      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);
  }
}
Show code (for simple and variable products)
Need To Update More Fields?

If you need to update additional fields like product description, SKU, or stock status, drop me an email for a customized solution!

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,

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.

Share this post
Maya
Maya

Hi, my name is Maya and I’m a WordPress plugin developer. I created this website to share some of the helpful codes that I’ve used in my own projects.
If you’re looking for a custom plugin for your website, you can contact me by clicking on Hire a developer in the menu. I’d love to hear from you.

Articles: 56