Adwords-Keywords-Cross-Matching

AdwordsKeywordsCrossMatching

/* Adwords Keywords Cross Matching
 * Description: Add Keywords from multiple Campaign(s)/AdGroup(s) as Negatives to other Campaign(s)/AdGroup(s).
 * Author:RitwikGA 
 * Copyright 2016 
 * Digishuffle.com 
 */
 
function  main()
{
  
 
  var AccountName=AdWordsApp.currentAccount().getName()
 
  //Create Spreadsheet 
  var url=getSpreadsheetURL("Rutu-"+AccountName+"-Keywords_Cross_Matching");
  var spreadsheet = SpreadsheetApp.openByUrl(url)
  
  //Create Sheets
  var sh2=spreadsheet.getSheets()[0].setName("Input_Keywords_Cross_Matching");
  
  var sh3=getsheet(spreadsheet,"Output_Keywords_Cross_Matching");
  
  
  sh3.getRange("A1").setValue("Keywords From Campaigns/AdGroups") 
  sh3.getRange("A1").setFontSize(14);
  sh3.getRange("A1").setFontWeight("bold")
  sh3.getRange("A1").setHorizontalAlignment("center")
  sh3.getRange("A1").setBackground("#336DCB");
  sh3.getRange("A1").setFontColor("#FFFFFF"); 
  sh3.setColumnWidth(1, 400);
 
  
  sh3.getRange("C1").setValue("Added as Negatives To Campaigns/AdGroups")
  sh3.getRange("D1").setValue("Date/Time") 
  sh3.getRange("C1:D1").setFontSize(14);
  sh3.getRange("C1:D1").setFontWeight("bold")
  sh3.getRange("C1:D1").setHorizontalAlignment("center")
  sh3.getRange("C1:D1").setBackground("#336DCB");
  sh3.getRange("C1:D1").setFontColor("#FFFFFF"); 
  sh3.setColumnWidth(3, 400);
  sh3.setColumnWidth(4, 200);
  
  
  sh2.getRange("A1").setValue("Keywords From") 
  sh2.getRange("A2").setValue("Campaign Contains")
  sh2.getRange("B2").setValue("Adgroup Contains")
  sh2.getRange("A1:B2").setFontSize(14);
  sh2.getRange("A1:B2").setFontWeight("bold")
  sh2.getRange("A1:B2").setHorizontalAlignment("center")
  sh2.getRange("A1:B2").setBackground("#336DCB");
  sh2.getRange("A1:B2").setFontColor("#FFFFFF"); 
  sh2.setColumnWidth(1, 200);
  sh2.setColumnWidth(2, 200);
  sh2.getRange("A1:B1").merge();
  
  sh2.getRange("E1").setValue("Add as Negatives To") 
  sh2.getRange("E2").setValue("Campaign Contains")
  sh2.getRange("F2").setValue("Adgroup Contains")
  sh2.getRange("E1:F2").setFontSize(14);
  sh2.getRange("E1:F2").setFontWeight("bold")
  sh2.getRange("E1:F2").setHorizontalAlignment("center")
  sh2.getRange("E1:F2").setBackground("#336DCB");
  sh2.getRange("E1:F2").setFontColor("#FFFFFF"); 
  sh2.setColumnWidth(5, 200);
  sh2.setColumnWidth(6, 200);
  sh2.getRange("E1:F1").merge();
  
  sh2.getRange("A3:C3").setBackground("#ffff00");
  sh2.getRange("E3:F3").setBackground("#ffff00");
  
  sh2.getRange("A10").setValue("*Enter atleast one of the Campaign/Adgroup")
  sh2.getRange("A11").setValue("*Enter one of the Negative Keyword Matchtype (BROAD, PHRASE, EXACT)")
  
  sh2.getRange("C2").setValue("MatchType")
  sh2.getRange("C2").setFontSize(14);
  sh2.getRange("C2").setFontWeight("bold")
  sh2.getRange("C2").setHorizontalAlignment("center")
  sh2.getRange("C2").setBackground("#336DCB");
  sh2.getRange("C2").setFontColor("#FFFFFF"); 
  
  
  
  
Logger.log("URL:\n"+sh2.getParent().getUrl())
  
if((sh2.getRange("A3").getValue()&&sh2.getRange("C3").getValue())||(sh2.getRange("B3").getValue()&&sh2.getRange("C3").getValue()))
{crossMatching(sh2,sh3)}
else{Logger.log("Open the URL & Enter atleast one of the Campaign/AdGroup Field. Check MatchType")}

}


function crossMatching(sh2,sh3)
{

var KEYWORDS_FROM_CAMPAIGN_CONTAINS=sh2.getRange("A3").getValue()        // Add KEYWORDS From These Campaigns to other Campaign's Negative Keywords.
//AND
var KEYWORDS_FROM_ADGROUP_CONTAINS=sh2.getRange("B3").getValue()                         // Add KEYWORDS From These AdGroups to other Adgroup's Negative Keywords.


var KEYWORDS_TO_CAMPAIGN_CONTAINS =sh2.getRange("E3").getValue()             // Add Keywords from other campaigns as NEGATIVE KEYWORDS To These Campaigns
//AND  
var KEYWORDS_TO_ADGROUP_CONTAINS =sh2.getRange("F3").getValue()        // Add Keywords from other adgroups as NEGATIVE KEYWORDS To These Adgroups


var NEGATIVE_KEYWORD_MATCH_TYPE=sh2.getRange("C3").getValue()       //BROAD, PHRASE, EXACT


var STATUS="ENABLED";                    //ENABLED, PAUSED
  

if(KEYWORDS_FROM_CAMPAIGN_CONTAINS!=''&&KEYWORDS_FROM_ADGROUP_CONTAINS!='')  
{var report = AdWordsApp.report(
     'SELECT CampaignName,AdGroupName,Criteria ' +
     'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
  'WHERE  Status = "'+STATUS+'" AND CampaignName CONTAINS "'+KEYWORDS_FROM_CAMPAIGN_CONTAINS+'" AND AdGroupName CONTAINS "'+KEYWORDS_FROM_ADGROUP_CONTAINS+'" AND AdGroupStatus = "ENABLED" AND CampaignStatus = "ENABLED" ')}
  else if(KEYWORDS_FROM_CAMPAIGN_CONTAINS!='')
  {var report = AdWordsApp.report(
     'SELECT CampaignName,AdGroupName,Criteria ' +
     'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
  'WHERE  Status = "'+STATUS+'" AND CampaignName CONTAINS "'+KEYWORDS_FROM_CAMPAIGN_CONTAINS+'" AND AdGroupStatus = "ENABLED" AND CampaignStatus = "ENABLED" ')}
   else if(KEYWORDS_FROM_ADGROUP_CONTAINS!='')
   { var report = AdWordsApp.report(
     'SELECT CampaignName,AdGroupName,Criteria ' +
     'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
  'WHERE  Status = "'+STATUS+'" AND AdGroupName CONTAINS "'+KEYWORDS_FROM_ADGROUP_CONTAINS+'" AND AdGroupStatus = "ENABLED" AND CampaignStatus = "ENABLED" ')}
  else {Logger.log("Enter Atleast One of the Campaign/AdGroup Field");return}
  
  
  var rows = report.rows();  
  if(rows.hasNext())
  {Logger.log("Scanning Campaigns/AdGroups...")} else{Logger.log("No Such Campaign/AdGroup. Check your Filters!!");return}
  
  var cam={}
  
  if(KEYWORDS_FROM_ADGROUP_CONTAINS!='')
  {
    
  while (rows.hasNext()) {
   var row = rows.next();
   
   if(cam[row['AdGroupName']]==undefined)  
   {cam[row['AdGroupName']]=[]}
   
   cam[row['AdGroupName']].push(row['Criteria'])
    }
 
 
  } else {
   
   while (rows.hasNext()) {
   var row = rows.next();
   
   if(cam[row['CampaignName']]==undefined)  
   {cam[row['CampaignName']]=[]}
   
   cam[row['CampaignName']].push(row['Criteria'])
    }
 
  }
  
 


  
if(KEYWORDS_TO_ADGROUP_CONTAINS==''&&KEYWORDS_FROM_ADGROUP_CONTAINS=='')
{
for (i in cam)  
{
  
  var rw=sh3.getLastRow()+1
  sh3.getRange(rw,1,1,1).setValue("Campaign: "+i)
  sh3.getRange(rw,2,1,1).setValue("::====>")
  sh3.getRange(rw,4,1,1).setValue(Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMMM dd, yyyy HH:mm:ss Z')) 
  var campiterate=AdWordsApp.campaigns().withCondition("Name != '"+i+"'").withCondition("Name contains '"+KEYWORDS_TO_CAMPAIGN_CONTAINS+"'").get()
  
  if(!campiterate.hasNext()){Logger.log("No Campaigns Matched/Cannot add the same keywords as negatives.");return;}
  while(campiterate.hasNext())
  {
  var campaign=campiterate.next();
  sh3.getRange(rw++,3,1,1).setValue("Campaign: "+campaign.getName())
  
  switch (NEGATIVE_KEYWORD_MATCH_TYPE)
  {  
   case "BROAD":
   for (j in cam[i])
   {campaign.createNegativeKeyword(cam[i][j].replace(/\+/g,"")) }
   break;
      
   case "PHRASE":
   for (j in cam[i])
   {campaign.createNegativeKeyword('"'+cam[i][j].replace(/\+/g,"")+'"') }
   break;
      
   case "EXACT":
   for (j in cam[i])
   {campaign.createNegativeKeyword('['+cam[i][j].replace(/\+/g,"")+']') }
   break;
      
   default:
   for (j in cam[i])
   {campaign.createNegativeKeyword(cam[i][j]) } 
   break;
   }
  
  } 
}
  
}
else 
{
 for (i in cam)  
 {
  
  var rw=sh3.getLastRow()+1
  sh3.getRange(rw,1,1,1).setValue("Adgroup: "+i)
  sh3.getRange(rw,2,1,1).setValue("::====>")
  sh3.getRange(rw,4,1,1).setValue(Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMMM dd, yyyy HH:mm:ss Z'))
  if(KEYWORDS_TO_ADGROUP_CONTAINS=='')
  {var adgroupiterate=KEYWORDS_TO_CAMPAIGN_CONTAINS==''?AdWordsApp.adGroups().withCondition("Name != '"+i+"'").get():AdWordsApp.adGroups().withCondition("Name != '"+i+"'").withCondition("CampaignName contains '"+KEYWORDS_TO_CAMPAIGN_CONTAINS+"'").get()}
  else
  {
  if(KEYWORDS_FROM_CAMPAIGN_CONTAINS=='')  
  {var adgroupiterate=KEYWORDS_TO_CAMPAIGN_CONTAINS==''?AdWordsApp.adGroups().withCondition("Name != '"+i+"'").withCondition("Name contains '"+KEYWORDS_TO_ADGROUP_CONTAINS+"'").get()
  :AdWordsApp.adGroups().withCondition("Name != '"+i+"'").withCondition("Name contains '"+KEYWORDS_TO_ADGROUP_CONTAINS+"'").withCondition("CampaignName contains '"+KEYWORDS_TO_CAMPAIGN_CONTAINS+"'").get()
  }
  else {
  var adgroupiterate=KEYWORDS_TO_CAMPAIGN_CONTAINS==''?AdWordsApp.adGroups().withCondition("Name != '"+i+"'").withCondition("Name contains '"+KEYWORDS_TO_ADGROUP_CONTAINS+"'").withCondition("CampaignName != '"+KEYWORDS_FROM_CAMPAIGN_CONTAINS+"'").get()
  :AdWordsApp.adGroups().withCondition("Name != '"+i+"'").withCondition("Name contains '"+KEYWORDS_TO_ADGROUP_CONTAINS+"'").withCondition("CampaignName contains '"+KEYWORDS_TO_CAMPAIGN_CONTAINS+"'").get()
  
  }
  
  }
  
  if(!adgroupiterate.hasNext()){Logger.log("No AdGroups Matched/Cannot add the same keywords as negatives.");return;}
  
  while(adgroupiterate.hasNext())
  {
  var adgroup=adgroupiterate.next();
  
  sh3.getRange(rw++,3,1,1).setValue("Adgroup: "+adgroup.getName())
  switch (NEGATIVE_KEYWORD_MATCH_TYPE)
  {  
   case "BROAD":
   for (j in cam[i])
   {adgroup.createNegativeKeyword(cam[i][j].replace(/\+/g,"")) }
   break;
      
   case "PHRASE":
   for (j in cam[i])
   {adgroup.createNegativeKeyword('"'+cam[i][j].replace(/\+/g,"")+'"') }
   break;
      
   case "EXACT":
   for (j in cam[i])
   {adgroup.createNegativeKeyword('['+cam[i][j].replace(/\+/g,"")+']') }
   break;
      
   default:
   for (j in cam[i])
   {adgroup.createNegativeKeyword(cam[i][j]) } 
   break;
  }
  }}}


sh3.getRange(rw,1,1,1).setValue("DONE!!!!")
}


//Get Spreadhsheet 
function getSpreadsheetURL(name)
{
  var files = DriveApp.searchFiles('title contains "'+name+'"');
  if(files.hasNext()){
     var file = files.next();
 return file.getUrl();
  } else 
 {
var sh_new=SpreadsheetApp.create(name)
return sh_new.getUrl()
 }
}

//Get Sheet
function getsheet(sht,name){
  var sh2 =sht.getSheetByName(name);
  if(sh2)
   {
    return sht.getSheetByName(name)}
    else 
    { var sh2=sht.insertSheet(name)
           return sh2 
    }
}