ExcludeSpamDisplayPlacements_SpreadSheetPlacementsList

/**
 * This MCC level script removes Display placements with Super High CTR AND no view through conversions AND no conversions 
 * (you can edit these values on the top level variables).
 * If one of the placements detected is anonymous.google, the script doesn't exclude it  
 * The script saves all detected placements in a spreadsheet
 *
 * @author Alberto Esteves 
 * URL: http://www.albertoestevescorreia.com/adwords-script-eliminar-ubicaciones-display-spam/
 * 
 * Inspired by:
 *  
 *  Dawson Reid
 *  Andrew Breen
 */


var impressionsMinimum = 2; // Select Display placements which have at least that number of impressions 
var maxCtr = 0.49; // Select Display placements which have at least that Click Through Rate percentage 
var conversionsLimit = 0; // Select Display placements which have no more than that number of conversions 
var ViewThroughConversionsLimit = 0; // Select Display placements which have no more than that number of post-impression conversions 
var mccLabelText = "no label"; // Filter AdWords accounts which have a MCC Account label
							   // If you don't want to filter by any MCC label, DO NOT make any change in this text 
var timePeriod = "ALL_TIME"; // Period of time to analyze. You can choose another value according to https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp_campaignselector?hl=es-419#forDateRange_1  							  							  
							  							  
var SPREADSHEET_URL = "INSERT YOUR SPREADSHEET URL HERE"; // Make a copy from https://docs.google.com/spreadsheets/d/1lcoNBICuHe4Y9oQNp3oI_1mi-jyZZzNF6ojmbCcxtNE/
var SHEET_NAME = "Spam placements"; // Name of the specific sheet in the spreadsheet.
var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = ss.getSheetByName(SHEET_NAME);
var lastRow = sheet.getLastRow(); // Last row from the spreadsheet which have a placement registered
var accountIds, accountId, accountIdAux, accountName;
var newRow = lastRow;
 
// -------------------------------------------------------


function removeLowPerformanceDisplayPlacements (placementSelector) {

  var placementIterator = placementSelector.get();
  var campaign, adGroup, excludeOperation, placement, placementUrl, placementStats;
  
  while (placementIterator.hasNext()) {
    placement = placementIterator.next();
    placementUrl = placement.getUrl();
  //  Logger.log(placementUrl);
	
	if (placementUrl != 'anonymous.google') {
 
      newRow = sheet.getRange(3,1).getValue() + 1; // Get the last row with data from the spreadsheet and goes to the next one
      placementStats = placement.getStatsFor(timePeriod);
	  campaign = placement.getCampaign();
      adGroup = placement.getAdGroup();
      excludeOperation = adGroup.display().newPlacementBuilder().withUrl(placementUrl).exclude(); // Exclude Display placement detected
      
	  // Add the Display placement excluded to the spreadsheet
      sheet.getRange(newRow,2).setValue(placementUrl);
      sheet.getRange(newRow,3).setValue(placementStats.getClicks());
      sheet.getRange(newRow,4).setValue(placementStats.getImpressions());
      sheet.getRange(newRow,5).setValue(accountIdAux);
      sheet.getRange(newRow,6).setValue(accountName);
      sheet.getRange(newRow,7).setValue(campaign.getName());
      sheet.getRange(newRow,8).setValue(campaign.getId());
      sheet.getRange(newRow,9).setValue(adGroup.getName());
      sheet.getRange(newRow,10).setValue(adGroup.getId());
      sheet.getRange(3,1).setValue(newRow);

      if (!excludeOperation.isSuccessful()) {
		  Logger.log("Could not exclude : " + placementUrl);
		}
	} else { 
      Logger.log("Don't exclude anonymous.google.com !");
	}
  } // End of While 
} // End of removeLowPerformanceDisplayPlacements function


function run () {
 var placementSelector = AdWordsApp.display().placements()
 	.withCondition("Ctr > "+maxCtr)
	.withCondition("Impressions >= "+impressionsMinimum)
    .withCondition("Conversions <= "+conversionsLimit)
	.withCondition("ViewThroughConversions <= "+ViewThroughConversionsLimit)
	.withCondition("CampaignStatus != REMOVED")
	.forDateRange(timePeriod);
  
  removeLowPerformanceDisplayPlacements(placementSelector);
} // End of run function


function executeInSequence (sequentialIds, executeSequentiallyFunc) {
 // Logger.log('Executing in sequence : ' + sequentialIds);
  sequentialIds.forEach(function (accountId) {
    var account = MccApp.accounts().withIds([accountId]).get().next();
    MccApp.select(account);
	accountIdAux = account.getCustomerId();
    accountName = account.getName();
    executeSequentiallyFunc();
  });
} // End of executeInSequence function


function main () {
  try {
    var accountIterator;

	if (mccLabelText != "no label") { // Filter accounts by MCC label
		accountIterator = MccApp.accounts().withCondition("LabelNames CONTAINS '" + mccLabelText +"'").orderBy('Name').get();
	} else { // Execute script for all accounts
		accountIterator = MccApp.accounts().orderBy('Name').get();
	}

    accountIds = [];

    while (accountIterator.hasNext()) {
      accountIds.push(accountId);
      accountId = accountIterator.next().getCustomerId();
    }
    accountIds.push(accountId);
  
  var sequentialIds = accountIds.slice(0);

  if (accountIds.length > 0) {
    executeInSequence(sequentialIds, run);
  }
} catch (exception) {
    // not an Mcc
    Logger.log('Running on non-MCC account.');
    run();
  }
}