Getting OfferID/ItemID from shopping campaigns with correct case

[Pradeep Sankaran]

Hi

I have the script below to export  OfferID (or item ID) that meet a specific criteria in my  shopping campaigns to a google shee. In this case, my criteria is >50 clocks and <0.4 conversions.

Next I use this google sheet as a supplemental feed in merchant center, and then define feed rules to add a custom label to item IDs in the sheet. Goal ultimately is to use inventory filter in my google ad campaign to exclude some item IDs.

The issue I see is that merchant center feed rules are case sensitive. The google sheet output from script has only small letters for OfferID. 

Does someone know how to extract OfferID with the correct case using script in google ads? Or make merchant center feed rules work in a way that is not case sensitive when matching item IDs?

--------------

// Create a copy of the google sheet here: https://docs.google.com/spreadsheets/d/1CNKQD-i38SZDTa5AqDeMt-SKIGHZkDXfdimUOmomwH0/edit

// Copy the link of the new sheet and paste it below - 

var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/xxx/edit";

// Enter your filters below, for multiple filters use AND clause. E.g. Impressions > 100 AND Clicks < 1 

// Currently default filter is Clicks < 1 i.e. Zero Clicks

var FILTERS = "Clicks > 50 AND Conversions < 0.4";

// Enter time duration below. Possibilities: 

// TODAY | YESTERDAY | LAST_7_DAYS | LAST_WEEK | LAST_BUSINESS_WEEK | THIS_MONTH | LAST_MONTH |

// LAST_14_DAYS | LAST_30_DAYS | THIS_WEEK_SUN_TODAY | THIS_WEEK_MON_TODAY | LAST_WEEK_SUN_SAT

// Currently default time duration is set to: LAST_30_DAYS

var TIME_DURATION = "LAST_30_DAYS";

var COUNT_LIMIT = 999999;

function main(){

  

  var products = getFilteredShoppingProducts();

  products.sort(function(a,b){return a[0] > b[0];});

  products = products.slice(0, COUNT_LIMIT);

  pushToSpreadsheet(products);

  

}

function getFilteredShoppingProducts(){

  var query = "SELECT OfferId FROM SHOPPING_PERFORMANCE_REPORT WHERE " + FILTERS + " DURING "+ TIME_DURATION;

  

  var products = [];

  var count = 0;

  var report = AdWordsApp.report(query);

  var rows = report.rows();

  while (rows.hasNext()){

    var row = rows.next();

    var offer_id = row['OfferId'].toString();

    products.push([offer_id]);

    count+= 1;

  }

  

  Logger.log(count);

  return products;

}

function pushToSpreadsheet(data){

  

  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  var sheet = spreadsheet.getSheetByName('Custom_Label');

    

  var lastRow = sheet.getMaxRows();

  sheet.getRange('A2:A'+lastRow).clearContent();

  

  var start_row=2;

  var endRow=start_row+data.length-1;

  var range = sheet.getRange('A'+start_row+':'+'A'+endRow);

  if (data.length>0){range.setValues(data);}

  

  return;

  

}

---------

Regards,

Pradeep

[Google Ads API Forum Advisor Prod]

Hi Pradeep,

Thank you for reaching out. As this is a Google Ads Scripts related issue please raise your question in the Google Ads Scripts Forum as they are better equipped with this type of domain knowledge.

Thanks and regards,
Xiaoming, Google Ads API Team

Xiaoming Google Ads API Team

ref:_00D1U1174p._5004Q24Z0A4:ref