// Copy the link of the new sheet and paste it below -
var SPREADSHEET_URL ="YOUR_SPREADSHEET_URL";


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


// Currently default filter is Clicks < 1 i.e. Zero Clicks
//var FILTERS = "Conversions < 1 AND Cost > 10";
var FILTERS = "metrics.clicks < 1"
// 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, Cost, Conversions FROM SHOPPING_PERFORMANCE_REPORT WHERE " +
  FILTERS + " DURING "+ TIME_DURATION;
  */
  
  var query = "SELECT segments.product_item_id, metrics.cost_micros, metrics.conversions FROM shopping_performance_view WHERE " + FILTERS + " AND segments.date DURING " + TIME_DURATION
  
  
  var products = [];
  var count = 0;
  
  var report = AdsApp.report(query);
  
  for (const row of report.rows()) {
    
  var offer_id = fixPrefix(8,row['segments.product_item_id']);
  var cost = row['metrics.cost_micros'];
  var conversion = row['metrics.conversions'];
  
  products.push([offer_id, cost, conversion]);
  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:C'+lastRow).clearContent();
  var start_row=2;
  var endRow=start_row+data.length-1;
  var range = sheet.getRange('A'+start_row+':'+'C'+endRow);
  if (data.length>0){range.setValues(data);}
  return;
}

function fixPrefix (charCount, text) {
  
  if (text == null) {
   
    return "";
    
  } else {
    
    var n=text.slice(charCount);
    return "shopify_" + n.toUpperCase();
  }



}