var EXCLUSION_LIST_NAME = 'Automated exclusions';

// Maximum number of placements to be added to the exclusion list
var MAX_PLACEMENTS = 700;

// Lookback period for Impressions
var LAST_N_DAYS = 90;

// Criteria for exclusion
var MIN_IMPRESSIONS = 200;
var MAX_CLICKS = 0;
var MAX_CONVERSIONS = 0;

function main() {
  var listIter = AdsApp.excludedPlacementLists().withCondition("Name = '" + EXCLUSION_LIST_NAME + "'").get();
  var list;

  if (!listIter.hasNext()) {
    list = AdsApp.newExcludedPlacementListBuilder().withName(EXCLUSION_LIST_NAME).build();
  } else {
    list = listIter.next();
  }

  var existingPlacements = {};
  var placements = list.excludedPlacements().get();
  while (placements.hasNext()) {
    var pl = placements.next();
    existingPlacements[pl.getUrl()] = 1;
  }

  var existingPlacementsCount = Object.keys(existingPlacements).length;
  var PLACEMENTS_LEFT = MAX_PLACEMENTS - existingPlacementsCount;
  if (PLACEMENTS_LEFT < 1) {
    Logger.log('Maximum # placements already added. Exiting!');
    return;
  }

  var START = getGoogleAdsFormattedDate(LAST_N_DAYS, 'yyyyMMdd');
  var END = getGoogleAdsFormattedDate(0, 'yyyyMMdd');

  var query = [
    'SELECT detail_placement_view.target_url,',
    'metrics.impressions, metrics.clicks, metrics.conversions',
    'FROM detail_placement_view',
    'WHERE metrics.impressions > ' + MIN_IMPRESSIONS,
    'AND metrics.clicks = ' + MAX_CLICKS,
    'AND metrics.conversions = ' + MAX_CONVERSIONS,
    'AND segments.date BETWEEN "' + START + '" AND "' + END + '"',
    'ORDER BY metrics.impressions DESC'
  ].join(' ');

  Logger.log(query);
  var report = AdsApp.report(query);
  var rows = report.rows();
  var toAdd = [];
  while (rows.hasNext()) {
    var row = rows.next();
    var placementUrl = row['detail_placement_view.target_url'];
    
    if (!placementUrl) {
      continue;
    }
    
    var placementUrlCheck = placementUrl;

    if (existingPlacements[placementUrlCheck]) {
      continue;
    }

    toAdd.push(placementUrl);
    existingPlacements[placementUrl] = 1;
    PLACEMENTS_LEFT--;

    if (PLACEMENTS_LEFT < 1) {
      break;
    }
  }

  var columnHeads = [
    'Action', 'Placement Exclusion List Name', 'Placement url'
  ];
  var upload = AdsApp.bulkUploads().newCsvUpload(columnHeads);

  for (var z in toAdd) {
    upload.append({
      'Action': 'Add',
      'Placement Exclusion List Name': EXCLUSION_LIST_NAME,
      'Placement url': toAdd[z]
    });
  }

  Logger.log('Number of new placements to add: ' + toAdd.length);
  upload.apply();

  Logger.log('Placements were added via Bulk upload. Please check Uploads under Scripts section for logs.');

  // Apply the exclusion list to Display campaigns only
  var campaignIterator = AdsApp.campaigns()
    .withCondition("AdvertisingChannelType = 'DISPLAY'")
    .get();
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    campaign.addExcludedPlacementList(list);
  }

  Logger.log("Applied the exclusion list to all Display campaigns.");
}

function getGoogleAdsFormattedDate(d, format) {
  var date = new Date();
  date.setDate(date.getDate() - d);
  return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), format);
}