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.