Webmasterei · February 15, 2018 09:26
diff --git a/autoExcludeAdGroupKeyWords.js b/autoExcludeAdGroupKeyWords.js

 //------------------------------------------------
 // This Script aims to Auto-Exclude Keywords 
 // from source labeled AdGroups and place them as
 // negative Keywords within another AdGroup within 
 // the same Campaign
 // This Script was built for Campaigns splitted in
 // BROAD MATCH and EXACT MATCH Keywords AdGroups.
 // More info: https://webmasterei-prange.de/adwords-script-automatisches-ausschliessen-von-keywords-in-anzeigengruppen
 // Strongly inspiered by: Remko van der Zwaag & PDDS see: http://remkovanderzwaag.nl/blog/updated-adwords-script-auto-add-negative-keywords-dsa
 //------------------------------------------------

 var spreadsheetId = 'ENTER SPREADSHEET ID';
 var prefillSpreadsheet = false;                                    // When set to true, gets all accounts from the MCC account
                                                                   // and automagically adds their name and id to the spreadsheet
                                                                   // Use once, doesn't check for existing records
                                                                   // switch back to false after use
                                                                   // PREFERABLY RUN USING PREVIEW (true), CHANGE TO false AND SAVE

 // The label a camaign is watched
 // * Default - overwritable from spreadsheet
 var campaign_label = 'BROAD | EXACT';

 // The label on the source campaign(s)
 // * Default - overwritable from spreadsheet
 var adgroup_source_label = 'EXACT';

 // The label on the target group
 // * Default - overwritable from spreadsheet
 var adgroup_target_label = 'BROAD';


 function mapRowToInfo(row) {
  return {
    custId: row[1].trim(),
    cust: row[0],
    campaign_label: row[2],
    adgroup_source_label: row[3],
    adgroup_target_label: row[4]
  };
 }


 function main() {
  try {
    if (prefillSpreadsheet) {
      MccApp.accounts()
        .withLimit(50)
        .executeInParallel("getSSAccountInfo","saveSSAccountInfo");
    } else {
      var ids = getSpreadsheetIds();
      if (ids.length > 0) {
        MccApp.accounts()
          .withIds(ids)
          .withLimit(50)
          .executeInParallel("processAccount");
      }
    }
  } catch (e) {
    processAccount();
  }
 }

 function getSSAccountInfo() {
  var result = {
    custId: AdWordsApp.currentAccount().getCustomerId(),
    cust: AdWordsApp.currentAccount().getName()
  };
  Logger.log(result);
  return JSON.stringify(result);
 }

 function saveSSAccountInfo(response) {
  var ss;
  try {
    ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
  }
  ss = ss.getSheets()[0];
  ss.appendRow(["Account Name", "Account ID", "Campaign Label", "Source Label", "Target Label"]);
  for (var i in response) {
    if(!response[i].getReturnValue()) { continue; }
    var rep = JSON.parse(response[i].getReturnValue());
    Logger.log(rep);
    ss.appendRow([rep.cust, rep.custId]);
  }
 }

 function getSpreadsheetIds() {
  var ids = [],
      ss,
      reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/;

  try {
    ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
    return ids;
  }
  ss = ss.getSheets()[0];
  var rows = parseInt(ss.getLastRow());
  var range = ss.getRange("A1:Z" + rows).getValues();
  for (var i = 0; i < rows; i++) {
    var account = mapRowToInfo(range[i]);
    if (!reAWId.test(account.custId) || account.skip) {
      continue;
    }
    ids.push(account.custId);
  }
  return ids;
 }

 function getAccountInfo() {
  var ss;
  var reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/;
  var protoAccount = {
    custId: AdWordsApp.currentAccount().getCustomerId(),
    cust: AdWordsApp.currentAccount().getName(),
    include_label: campaign_label,
    source_label: adgroup_source_label,
    target_label: adgroup_target_label
  };
  try {
    ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
    return protoAccount;
  }
  ss = ss.getSheets()[0];
  var rows = parseInt(ss.getLastRow());
  var range = ss.getRange("A1:Z" + rows).getValues();
  var ret_account;

  for (var i = 0; i < rows; i++) {
    var account = mapRowToInfo(range[i]);
    if (account.skip) {
      continue;
    }
    if (!reAWId.test(account.custId) || account.custId !== protoAccount.custId) {
      continue;
    }

    for(var key in account) {
      if (account[key] === '' || account[key] === '_ALL_') {
        account[key] = protoAccount[key];
      }
    }
    return account;
  }
  return protoAccount;
 }


 function processAccount() {
  var account = getAccountInfo();
   try {
     var campaigns = getCampaignsWithLabel(account.campaign_label);
   } catch (ex) {
     return;
   }
   if (campaigns.length === 0) {
     return;
   }
   var include_list = [];
   if (account.campaign_label !== '') {
     try {
       var iterator = getCampaignsWithLabel(account.campaign_label);
       while (iterator.hasNext()) {
         include_list.push(iterator.next().getId());
       }
     } catch (ex) {
       // pass
     }
   }
 while (campaigns.hasNext()) {
   var campaign = campaigns.next();
   var campaignName = campaign.getName()
   var keywords = getSourceAdgroup(adgroup_source_label,campaignName);
   setTargetAdgroupKeywords(adgroup_target_label,keywords,campaignName);
 }
 }
 
 function getCampaignWithLabel(label) {
  var campaignIterator = getCampaignsWithLabel(label);

  if (campaignIterator.hasNext()) {
    return campaignIterator.next();
  }

  return null;
 }

 function getCampaignsWithLabel(label) {
  
  return AdWordsApp.campaigns()
    .withCondition("LabelNames CONTAINS_ANY ['" + label + "']")
    .get();
 }

 function getCampaignsWithLabelArray(label) {
  var iterator = getCampaignsWithLabel(label);
  var campaigns = [];
  while (iterator.hasNext()) {
    campaigns.push(iterator.next());
  }
  return campaigns;
 }

 function getSourceAdgroup(adgroup_source_label,campaignName){
  var adGroupSelector = AdWordsApp
     .adGroups()
     .withCondition("CampaignName = '" + campaignName + "'")
     .withCondition("LabelNames CONTAINS_ANY ['" + adgroup_source_label + "']")
     .withCondition("Status = ENABLED");
 var adGroupIterator = adGroupSelector.get();
  var keywords = [];
  while (adGroupIterator.hasNext()) {
   var adGroup = adGroupIterator.next();
    keywords = keywords.concat(getKeyWords(adGroup));
  }
  return keywords;
 }

 function getKeyWords(adGroup){
  var keywords = []
  var keywordSelector = AdWordsApp
     .keywords()
     .withCondition("AdGroupName = '" + adGroup.getName() + "'")
     .withCondition("Status = ENABLED");

 var keywordIterator = keywordSelector.get();
  while(keywordIterator.hasNext()){
    var keyword = keywordIterator.next();
    keyword = keyword.getText();
    keywords.push(keyword)
  }
 return keywords;
 }

 function setTargetAdgroupKeywords(label,keywords,campaignName){
  var adGroupSelector = AdWordsApp
     .adGroups()
     .withCondition("LabelNames CONTAINS_ANY ['" + label + "']")
     .withCondition("CampaignName = '" + campaignName + "'")
 var adGroupIterator = adGroupSelector.get();
  
 while (adGroupIterator.hasNext()) {
   var adGroup = adGroupIterator.next();
   removeNegativeKeywords(adGroup);
   setNegativeKeywords(keywords,adGroup);
 }
 }

 function removeNegativeKeywords(targetAdGroup){
  var negativeKeywordSelector = targetAdGroup.negativeKeywords();
  var negativeKeywordIterator = negativeKeywordSelector.get();
  while (negativeKeywordIterator.hasNext()) {
   var negativeKeyword = negativeKeywordIterator.next();
   negativeKeyword.remove();
 }
 }

 function setNegativeKeywords(keywords,targetAdGroup) {
      //Logger.log(targetAdGroup.getCampaign().getName()+' '+targetAdGroup.getName()+' has AdGroups: '+keywords.length+' Number of Keywords:'+keywords.length);
  for (var i=0;i< keywords.length; i++) {
    var keyword = keywords[i];
      Logger.log(keyword);
      targetAdGroup.createNegativeKeyword(keyword);
  }
 }

	//------------------------------------------------
	// This Script aims to Auto-Exclude Keywords
	// from source labeled AdGroups and place them as
	// negative Keywords within another AdGroup within
	// the same Campaign
	// This Script was built for Campaigns splitted in
	// BROAD MATCH and EXACT MATCH Keywords AdGroups.
	// More info: https://webmasterei-prange.de/adwords-script-automatisches-ausschliessen-von-keywords-in-anzeigengruppen
	// Strongly inspiered by: Remko van der Zwaag & PDDS see: http://remkovanderzwaag.nl/blog/updated-adwords-script-auto-add-negative-keywords-dsa
	//------------------------------------------------

	var spreadsheetId = 'ENTER SPREADSHEET ID';
	var prefillSpreadsheet = false; // When set to true, gets all accounts from the MCC account
	// and automagically adds their name and id to the spreadsheet
	// Use once, doesn't check for existing records
	// switch back to false after use
	// PREFERABLY RUN USING PREVIEW (true), CHANGE TO false AND SAVE

	// The label a camaign is watched
	// * Default - overwritable from spreadsheet
	var campaign_label = 'BROAD \| EXACT';

	// The label on the source campaign(s)
	// * Default - overwritable from spreadsheet
	var adgroup_source_label = 'EXACT';

	// The label on the target group
	// * Default - overwritable from spreadsheet
	var adgroup_target_label = 'BROAD';


	function mapRowToInfo(row) {
	return {
	custId: row[1].trim(),
	cust: row[0],
	campaign_label: row[2],
	adgroup_source_label: row[3],
	adgroup_target_label: row[4]
	};
	}


	function main() {
	try {
	if (prefillSpreadsheet) {
	MccApp.accounts()
	.withLimit(50)
	.executeInParallel("getSSAccountInfo","saveSSAccountInfo");
	} else {
	var ids = getSpreadsheetIds();
	if (ids.length > 0) {
	MccApp.accounts()
	.withIds(ids)
	.withLimit(50)
	.executeInParallel("processAccount");
	}
	}
	} catch (e) {
	processAccount();
	}
	}

	function getSSAccountInfo() {
	var result = {
	custId: AdWordsApp.currentAccount().getCustomerId(),
	cust: AdWordsApp.currentAccount().getName()
	};
	Logger.log(result);
	return JSON.stringify(result);
	}

	function saveSSAccountInfo(response) {
	var ss;
	try {
	ss = SpreadsheetApp.openById(spreadsheetId);
	} catch (e) {
	}
	ss = ss.getSheets()[0];
	ss.appendRow(["Account Name", "Account ID", "Campaign Label", "Source Label", "Target Label"]);
	for (var i in response) {
	if(!response[i].getReturnValue()) { continue; }
	var rep = JSON.parse(response[i].getReturnValue());
	Logger.log(rep);
	ss.appendRow([rep.cust, rep.custId]);
	}
	}

	function getSpreadsheetIds() {
	var ids = [],
	ss,
	reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/;

	try {
	ss = SpreadsheetApp.openById(spreadsheetId);
	} catch (e) {
	return ids;
	}
	ss = ss.getSheets()[0];
	var rows = parseInt(ss.getLastRow());
	var range = ss.getRange("A1:Z" + rows).getValues();
	for (var i = 0; i < rows; i++) {
	var account = mapRowToInfo(range[i]);
	if (!reAWId.test(account.custId) \|\| account.skip) {
	continue;
	}
	ids.push(account.custId);
	}
	return ids;
	}

	function getAccountInfo() {
	var ss;
	var reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/;
	var protoAccount = {
	custId: AdWordsApp.currentAccount().getCustomerId(),
	cust: AdWordsApp.currentAccount().getName(),
	include_label: campaign_label,
	source_label: adgroup_source_label,
	target_label: adgroup_target_label
	};
	try {
	ss = SpreadsheetApp.openById(spreadsheetId);
	} catch (e) {
	return protoAccount;
	}
	ss = ss.getSheets()[0];
	var rows = parseInt(ss.getLastRow());
	var range = ss.getRange("A1:Z" + rows).getValues();
	var ret_account;

	for (var i = 0; i < rows; i++) {
	var account = mapRowToInfo(range[i]);
	if (account.skip) {
	continue;
	}
	if (!reAWId.test(account.custId) \|\| account.custId !== protoAccount.custId) {
	continue;
	}

	for(var key in account) {
	if (account[key] === '' \|\| account[key] === '_ALL_') {
	account[key] = protoAccount[key];
	}
	}
	return account;
	}
	return protoAccount;
	}


	function processAccount() {
	var account = getAccountInfo();
	try {
	var campaigns = getCampaignsWithLabel(account.campaign_label);
	} catch (ex) {
	return;
	}
	if (campaigns.length === 0) {
	return;
	}
	var include_list = [];
	if (account.campaign_label !== '') {
	try {
	var iterator = getCampaignsWithLabel(account.campaign_label);
	while (iterator.hasNext()) {
	include_list.push(iterator.next().getId());
	}
	} catch (ex) {
	// pass
	}
	}
	while (campaigns.hasNext()) {
	var campaign = campaigns.next();
	var campaignName = campaign.getName()
	var keywords = getSourceAdgroup(adgroup_source_label,campaignName);
	setTargetAdgroupKeywords(adgroup_target_label,keywords,campaignName);
	}
	}

	function getCampaignWithLabel(label) {
	var campaignIterator = getCampaignsWithLabel(label);

	if (campaignIterator.hasNext()) {
	return campaignIterator.next();
	}

	return null;
	}

	function getCampaignsWithLabel(label) {

	return AdWordsApp.campaigns()
	.withCondition("LabelNames CONTAINS_ANY ['" + label + "']")
	.get();
	}

	function getCampaignsWithLabelArray(label) {
	var iterator = getCampaignsWithLabel(label);
	var campaigns = [];
	while (iterator.hasNext()) {
	campaigns.push(iterator.next());
	}
	return campaigns;
	}

	function getSourceAdgroup(adgroup_source_label,campaignName){
	var adGroupSelector = AdWordsApp
	.adGroups()
	.withCondition("CampaignName = '" + campaignName + "'")
	.withCondition("LabelNames CONTAINS_ANY ['" + adgroup_source_label + "']")
	.withCondition("Status = ENABLED");
	var adGroupIterator = adGroupSelector.get();
	var keywords = [];
	while (adGroupIterator.hasNext()) {
	var adGroup = adGroupIterator.next();
	keywords = keywords.concat(getKeyWords(adGroup));
	}
	return keywords;
	}

	function getKeyWords(adGroup){
	var keywords = []
	var keywordSelector = AdWordsApp
	.keywords()
	.withCondition("AdGroupName = '" + adGroup.getName() + "'")
	.withCondition("Status = ENABLED");

	var keywordIterator = keywordSelector.get();
	while(keywordIterator.hasNext()){
	var keyword = keywordIterator.next();
	keyword = keyword.getText();
	keywords.push(keyword)
	}
	return keywords;
	}

	function setTargetAdgroupKeywords(label,keywords,campaignName){
	var adGroupSelector = AdWordsApp
	.adGroups()
	.withCondition("LabelNames CONTAINS_ANY ['" + label + "']")
	.withCondition("CampaignName = '" + campaignName + "'")
	var adGroupIterator = adGroupSelector.get();

	while (adGroupIterator.hasNext()) {
	var adGroup = adGroupIterator.next();
	removeNegativeKeywords(adGroup);
	setNegativeKeywords(keywords,adGroup);
	}
	}

	function removeNegativeKeywords(targetAdGroup){
	var negativeKeywordSelector = targetAdGroup.negativeKeywords();
	var negativeKeywordIterator = negativeKeywordSelector.get();
	while (negativeKeywordIterator.hasNext()) {
	var negativeKeyword = negativeKeywordIterator.next();
	negativeKeyword.remove();
	}
	}

	function setNegativeKeywords(keywords,targetAdGroup) {
	//Logger.log(targetAdGroup.getCampaign().getName()+' '+targetAdGroup.getName()+' has AdGroups: '+keywords.length+' Number of Keywords:'+keywords.length);
	for (var i=0;i< keywords.length; i++) {
	var keyword = keywords[i];
	Logger.log(keyword);
	targetAdGroup.createNegativeKeyword(keyword);
	}
	}