a Google Script to update Shopify metafields in bulk from a spreadsheet.

shopify_update_metafields_from_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";
}

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

Thank you!