Error due to "ProductitemId" yet no "ProductItemId" called in script

[Ralph Sibanda]

Hi Google,

I am receiving this error:

QueryError.UNRECOGNIZED_FIELD: Unrecognized field in the query: 'ProductItemId'. at setCustomLabels (Code:97:36) at main (Code:24:3)

Here is the script

// Start script - variable above are correct
function main() {
  var products = getFilteredShoppingProducts(daysAgo); // Retrieve filtered shopping products data
  products.sort(function (a, b) { // Sort products
    return a[0] > b[0];
  });
  products = products.slice(0, 999999); // Limit the number of products
  setCustomLabels(products); // Apply custom labels to products
  pushToSpreadsheet(products); // Push data to the spreadsheet
}

function getFilteredShoppingProducts(daysAgo) {
  var today = new Date(); // Get today's date
  var daysAgo = new Date(today.getFullYear(), today.getMonth(), today.getDate() - daysAgo); // Calculate date 'daysAgo' days ago
  var dateFrom = Utilities.formatDate(daysAgo, AdWordsApp.currentAccount().getTimeZone(), "yyyyMMdd"); // Format start date
  var dateTo = Utilities.formatDate(today, AdWordsApp.currentAccount().getTimeZone(), "yyyyMMdd"); // Format end date
  var query =
    "SELECT segments.product_item_id, segments.product_title , metrics.impressions, metrics.clicks, metrics.ctr, metrics.cost_micros, metrics.conversions, metrics.conversions_value, segments.product_type_l1 " +
    "FROM shopping_performance_view " +
    "WHERE segments.date BETWEEN " +
    dateFrom +
    " AND " +
    dateTo; // Query to fetch product performance data

  var products = []; // Array to store filtered products
  var count = 0; // Counter for logging purposes
  var report = AdWordsApp.report(query); // Execute the query
  var rows = report.rows(); // Get rows from the report

  while (rows.hasNext()) {
    var row = rows.next(); // Get the next row
    var offer_id = row["segments.product_item_id"]; // Get product ID
    var impressions = row["metrics.impressions"].toString(); // Get impressions
    var clicks = row["metrics.clicks"].toString(); // Get clicks
    var cost_micros = row["metrics.cost_micros"].toString(); // Get cost in micros
    var cost = cost_micros / 1000000; // Convert cost to standard unit
    var conversions = row["metrics.conversions"].toString(); // Get conversions
    var conversionValue = row["metrics.conversions_value"].toString(); // Get conversion value
    var productType = row["segments.product_type_l1"]; // Get product type

    var costStr = typeof cost === "string" ? cost : cost.toString(); // Ensure cost is a string
    var convValuePerCost = (conversionValue.replace(",", "") / costStr.replace(",", "")).toString(); // Calculate conversion value per cost
    if (isNaN(convValuePerCost)) {
      convValuePerCost = 0; // Handle NaN case
    }

    // Filter for bags
    if (productType == "bags") {
      var isProductType = "";
      if (conversions >= 1) {
        isProductType = "best-seller"; // Label as best-seller if conversions >= 1
      } else {
        isProductType = "under-index"; // Label as under-index otherwise
      }
      products.push([offer_id, impressions, clicks, cost, conversions, conversionValue, convValuePerCost, isProductType]); // Add product data to the array
      count += 1; // Increment counter
    }
  }

  Logger.log(count); // Log the count of filtered products
  return products; // Return the array of filtered products
}

function setCustomLabels(products) {
  for (var i = 0; i < products.length; i++) {
    var product = products[i];
    var productId = product[0];
    var customLabel = product[7]; // Get the label from the product array

    // Use the correct methods to find and update the product items
    var shoppingAdGroupsIterator = AdsApp.shoppingAdGroups()
      .withCondition("AdGroupStatus = ENABLED")
      .get();

    while (shoppingAdGroupsIterator.hasNext()) {
      var shoppingAdGroup = shoppingAdGroupsIterator.next();
      var productGroupsIterator = shoppingAdGroup.productGroups()
        .withCondition("ProductItemId = '" + productId + "'")
        .get();

      while (productGroupsIterator.hasNext()) {
        var productGroup = productGroupsIterator.next();
        productGroup.setCustomLabel(0, customLabel); // Set custom label at custom_label_0 field
        Logger.log('Product ID: ' + productId + ' labeled as ' + customLabel);
      }
    }
  }
}

function pushToSpreadsheet(data) {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL); // Open the spreadsheet by URL
  var sheet = spreadsheet.getSheetByName("Flowbelizer"); // Get the sheet by name
  var lastRow = sheet.getMaxRows(); // Get the maximum number of rows
  sheet.getRange("A2:H" + lastRow).clearContent(); // Clear existing content in the specified range

  var start_row = 2; // Starting row for data
  var endRow = start_row + data.length - 1; // Calculate ending row for data
  var range = sheet.getRange("A" + start_row + ":" + "H" + endRow); // Define the range for data insertion

  if (data.length > 0) {
    range.setValues(data); // Set values in the defined range
  }

  return;
}

[Nils Rooijmans]

you are trying to select productgroups based on the  ProductItemId , 

      var productGroupsIterator = shoppingAdGroup.productGroups()
        .withCondition("ProductItemId = '" + productId + "'")
        .get();

This is not possible and throws the error. 
Not sure about the logic of this script, feel like it is generated with an AI. 
If so, TIP: feed the AI the documentation on product group selectors and ask again.

Hope this helps,

Nils Rooijmans
https://nilsrooijmans.com
See my Google Ads Scripts FAQ to avoid the same mistakes I made: https://nilsrooijmans.com/google-ads-scripts-faq/

[Google Ads Scripts Forum Advisor]

Hi,

Thank you for reaching out to the Google Ads Scripts support team.

@ Nils - Thank you for your input.

You may follow Nil's suggestion. Let me know how it goes on your end and If you face any issue, provide us with the following details to investigate the issue from our side: 

• Google Ads account ID / CID
• Name of the script

You can send the details via Reply privately to the author option, or direct private reply to this email.

This message is in relation to case "ref:!00D1U01174p.!5004Q02tJOcJ:ref" (ADR-00242405)

Thanks,
 

Google Ads Scripts Team