OMR | Shopping Whitelist

shoppingWhitelist.js

/*
 * Specify the link to your spreadsheet and the name of the relevant sheet here. You can use sheets to manage multiple accounts.
*/
function getSpreadsheet() {
  var spreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1vBAiXAb38Fdhwz00B7-sE3HMQJvQnUtc3amgunsvQcM/");
  var sheet = spreadsheet.getSheetByName( "AccountName" );
  return sheet.getRange("A:Z").getValues();
}

function main() {
  
  /*
   * You can modify the timerange for the search query lookup here. Currently the previous 30 days.
  */
  var today = new Date();
  var end = new Date( new Date().setDate(today.getDate()-1) ).toISOString().slice(0,10).replace(/-/g,"");
  var start = new Date( new Date().setDate(today.getDate()-30) ).toISOString().slice(0,10).replace(/-/g,"");
  
  var whitelist = {};
  var wl = [];
  var exclude = {};
  var sharedList = {};
  var sharedListExcludes = {};
  
  /*
   * Load Whitelists from Spreadsheet
  */
  var data = getSpreadsheet();
  
  for( var r=1; r < data.length; r++ ) {
    
    if( data[r][0].length == 0)
      break;
    
    if( data[r][3] != 'On')
      continue;
    
    whitelist[ data[r][0]+data[r][1] ] = [];
    wl.push( data[r][0]+data[r][1] );
    
    if( data[r][2].length > 0 ) {
      sharedList[ data[r][0]+data[r][1] ] = data[r][2];
    }
    
    for( var key in data[r] ) {
      if( key < 5 )
        continue;
      whitelist[ data[r][0]+data[r][1] ].push( data[r][key] );
    } 
    
  }
  
  /*
   * Load Search Query Report
  */  
  var report = AdWordsApp.report(
    'SELECT CampaignName, AdGroupName, AdGroupId, Query' +
    ' FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
    ' WHERE ' +
    ' Clicks >= 1 ' +
    ' AND CampaignStatus = ENABLED ' +
    ' AND AdGroupStatus = ENABLED ' +
    ' DURING ' + start + ',' + end );
  var rows = report.rows();
    
  while (rows.hasNext()) {
    var r = rows.next();
    var name = r.AdGroupName + r.CampaignName.replace(/.*(P\-?[0-9]).*/g,'$1').replace(/\-/g,'');
    
    if( wl.indexOf( name ) == -1 )
      continue;
    
    if( !(new RegExp( '.*(' + whitelist[ name ].join('|') + ').*') ).test(r.Query) ) {
      if(sharedList[name]) {
        if( typeof( sharedListExcludes[name] ) == "undefined" ) {
          sharedListExcludes[name] = [];
        }
        sharedListExcludes[name].push( r.Query );        
      } else {
        if( typeof( exclude[r.AdGroupId] ) == "undefined" ) {
          exclude[r.AdGroupId] = [];
        }
        exclude[r.AdGroupId].push( r.Query );
      }
    }

  }
  
  /*
   * Create the collected negative keywords
  */
  for( var aId in exclude ) {
    var a = AdWordsApp.shoppingAdGroups().withIds([parseInt(aId)]).get().next();
    for( var i = 0;i < exclude[aId].length; i++ ) {
      a.createNegativeKeyword('['+ exclude[aId][i] +']');
    }
  }
  
  for( var listName in sharedListExcludes ) {
    var l = AdWordsApp.negativeKeywordLists().withCondition('Name = "' + listName + '"').get().next();
    for( var i = 0;i < sharedListExcludes[listName].length; i++ ) {
      l.addNegativeKeyword('['+ sharedListExcludes[listName][i] +']');
    }
  }
 
}