Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save kmeister2000/54195b7ba6eb5cd6cdd5cf962f882f6a to your computer and use it in GitHub Desktop.
Save kmeister2000/54195b7ba6eb5cd6cdd5cf962f882f6a to your computer and use it in GitHub Desktop.
a Google Script to update Shopify metafields in bulk from a spreadsheet.
// Written by Brendan Quigley
// Three Acres - threeacres.ca
//
// Creates or Updates product metafields in bulk using the product handle
// Steps to use:
// 1. Create a private shopify app with product read & write permission
// 2. Install the private app to generate an access token
// 3. Paste the code into the script editor on a google sheet
// 4. Replace [ myshopify.com ] with the Shopify URL ( 3 Places )
// 5. Replace [ Access Token ] with the access token ( 3 Places )
// 6. First time you run the script you'll have to accept permissions for Google
// Note: The script gets the active sheet, so make sure the sheet with the data is selected
// Product Handles go in Column A
// Metafield Values in Column B
// Metafield Key in the Cell B1, this is only the key not the liquid {{ product.metafield.custom.key }} only the last part
// If you need to access a namespace other than custom, you need to change the payload since that value is hardcoded
// It takes 2 seconds just to be safe about API calls
function updateProductMetafieldsInShopify() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
var productHandles = [];
var metafieldKey = sheet.getRange("B1").getValue();
// Get the product handles and metafield values
for (var i = 0; i < values.length; i++) {
var row = values[i];
if (i > 0 && row[0] !== "") { // Skip the header row and empty rows
productHandles.push(row[0]);
var metafieldValue = sheet.getRange(i+1, 2).getValue() !== undefined ? sheet.getRange(i+1, 2).getValue() : "";
// Log the values being passed to the updateProductMetafieldInShopify function
console.log("Updating product handle:", row[0], "Metafield key:", metafieldKey, "Metafield value:", metafieldValue);
updateMetafield(row[0], metafieldKey, metafieldValue);
}
}
}
function updateMetafield(productHandle, metafieldKey, newMetafieldValue) {
var shopUrl = "[ myshopify.com ]"; // THIS NEEDS TO BE REPLACED
var accessToken = "[ Access Token ]"; // THIS NEEDS TO BE REPLACED
// get product id
var productID = getProductID(productHandle);
if (productID == "Product not found") {
console.log("Product not found");
return;
}
// get metafield id
var metafieldID = getMetafieldID(productID, metafieldKey);
if (metafieldID == "Metafield not found") {
// create metafield if it doesn't exist
var url = "https://" + shopUrl + "/admin/api/2023-01/metafields.json";
var options = {
"method": "POST",
"headers": {
"X-Shopify-Access-Token": accessToken,
"Content-Type": "application/json"
},
"payload": JSON.stringify({
"metafield": {
"namespace": "custom",
"key": metafieldKey,
"value": newMetafieldValue,
"owner_id": productID,
"owner_resource": "product"
}
})
};
var response = UrlFetchApp.fetch(url, options);
console.log("Metafield created");
} else {
// update metafield value
var url = "https://" + shopUrl + "/admin/api/2023-01/product/" + productID + "/metafields/" + metafieldID + ".json";
var options = {
"method": "PUT",
"headers": {
"X-Shopify-Access-Token": accessToken,
"Content-Type": "application/json"
},
"payload": JSON.stringify({
"metafield": {
"value": newMetafieldValue
}
})
};
var response = UrlFetchApp.fetch(url, options);
console.log("Metafield updated");
}
}
function getProductID(productHandle) {
console.log("Getting Product ID");
var shopUrl = "[ myshopify.com ]"; // THIS NEEDS TO BE REPLACED
var accessToken = "[ Access Token ]"; // THIS NEEDS TO BE REPLACED
var url = "https://" + shopUrl + "/admin/api/2023-01/products.json?handle=" + productHandle;
var options = {
"method": "GET",
"headers": {
"X-Shopify-Access-Token": accessToken,
}
};
var response = UrlFetchApp.fetch(url, options);
var data = JSON.parse(response.getContentText());
if (data.products.length > 0) {
var productID = data.products[0].id;
console.log("Product ID Found" + productID);
return productID;
} else {
return "Product not found";
}
}
function getMetafieldID(productId, key) {
console.log("Getting Metafield ID");
var shopUrl = "[ myshopify.com ]"; // THIS NEEDS TO BE REPLACED
var accessToken = "[ Access Token ]"; // THIS NEEDS TO BE REPLACED
var url = "https://" + shopUrl + "/admin/api/2023-01/products/" + productId + "/metafields.json";
var options = {
"method": "GET",
"headers": {
"X-Shopify-Access-Token": accessToken,
}
};
var response = UrlFetchApp.fetch(url, options);
var data = JSON.parse(response.getContentText());
for (var i = 0; i < data.metafields.length; i++) {
if (data.metafields[i].key == key) {
var metafieldID = data.metafields[i].id;
console.log("Metafield ID Found" + metafieldID);
return metafieldID;
}
}
return "Metafield not found";
}
@lichael06
Copy link

Hi Thanks to this source code. Can someone confirm if this the right way to input handle and metafields on spreadsheet?
image

Thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment