policy_issues_by_email.js

//Enter your email address where you want the email to be sent.
var RECIPIENT_EMAIL = "[email protected]";

//Enter the subject of the email.
var EMAIL_SUBJECT = 'Google Ads - Checked for policy issues.';

//Enter the label for all the accounts you wish to analyze.
var ACCOUNT_LABEL_TO_CHECK = "Monitor";

//Make sure that you update the getPolicyManagerURL function with your hardcoded OCID values


function main() {
  var accountSelector = AdsManagerApp
  .accounts()
  .withLimit(20)
  .withCondition('LabelNames CONTAINS "' + ACCOUNT_LABEL_TO_CHECK +'"');

  var results = [];
  var accountIterator = accountSelector.get();  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);

    var accountName = account.getName();
    var accountId = account.getCustomerId();
    Logger.log(accountName + ' (' + accountId + ') - ' + 'Starting to process the account.');
    
    var Ad_Policy_Issues = Check_For_Ad_Policy_Issues(account);
    var Extension_Policy_Issues = Check_For_Ad_Extension_Policy_Issues(account);
    
    results.push({
      "accountName":accountName, 
      "accountId":accountId,
      "Ad_Policy_Issues":Ad_Policy_Issues,
      "Extension_Policy_Issues":Extension_Policy_Issues      
    });
    
    Logger.log(accountName + ' (' + accountId + ') - ' + 'Finished processing account.');
  }  
  
  results.sort(compareValues("accountName","asc"));
  
  //Now send the email report.
  SendEmailReport(BuildEmail(results));
  
  Logger.log('Finished processing all accounts.');
}

function BuildEmail(results) {
  //This function will receive the results and build the HTML formatted email.
  var strHTMLBody = "<p>Checked each account for ad and ad extension policy issues.</p>" +
                    "<p>Filters used were: Campaign status: All enabled; Ad group status: All enabled; Ad status: All enabled.</p>" + 
                    "<p>This report ran on: " + Utilities.formatDate(new Date(), "America/Chicago", "EEE, MMM d, yyyy") +"</p>\n\n";
  
  var HeaderStyle = "font-family:Arial,sans-serif;font-size:14px;font-weight:normal;padding:5px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#000000;color:#ffffff;background-color:#343434;text-align:left;vertical-align:top";
  var ClientHeaderStyle = "font-family:Arial,sans-serif;font-size:14px;font-weight:normal;padding:5px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#000000;color:#ffffff;background-color:#b9b9b9;text-align:left;vertical-align:top";
  var RawDataRowStyleNormal = "font-family:Arial,sans-serif;font-weight:normal;padding:10px 10px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#000000;color:#333;background-color:#fff;text-align:left;vertical-align:top";
  var RawDataRowStyleError = "font-family:Arial,sans-serif;font-weight:normal;padding:10px 10px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#000000;color:#333;background-color:#f59a9a;text-align:left;vertical-align:top";

  strHTMLBody += '<table style="border-collapse:collapse;border-spacing:0;border-color:#ccc">\n';
  
  //Write out the header row
  strHTMLBody += '<tr>\n'+
    '<th style="' + HeaderStyle + '">Client</th>\n' +
    '<th style="' + HeaderStyle + '">Ad Policy Issues</th>\n' +
    '<th style="' + HeaderStyle + '">Ad Extension Policy Issues</th>\n' +
    '<th style="' + HeaderStyle + '">Policy Manager Link</th>\n' +
    '</tr>\n';
  
  for (i in results) {
    var accountName = results[i].accountName;
    var accountId = results[i].accountId;
    var Ad_Policy_Issues = results[i].Ad_Policy_Issues;
    var Extension_Policy_Issues = results[i].Extension_Policy_Issues;
    
    Logger.log(accountName + ' (' + accountId + ') - ' + JSON.stringify(Ad_Policy_Issues) + ' - ' + JSON.stringify(Extension_Policy_Issues));
    
    var Ad_Policy_Issues_Text = "";
    var Extension_Policy_Issues_Text = "";
    
    if (Ad_Policy_Issues.length > 0){
      for (i in Ad_Policy_Issues) {
        Ad_Policy_Issues_Text += Ad_Policy_Issues[i].policyIssue + ": (" + 
          ((Ad_Policy_Issues[i].approvalStatus != "Approved Limited") ? "<strong><font color=\"red\">" + Ad_Policy_Issues[i].approvalStatus + "</font></strong>" : Ad_Policy_Issues[i].approvalStatus)  + "): " + 
          "<strong>" + Ad_Policy_Issues[i].count + "</strong><br>\n";
      }
    } else {
      Ad_Policy_Issues_Text = "<strong><font color=\"green\">No ad policy issues.</font></strong>";    
    }
    
    if (Extension_Policy_Issues.length > 0){    
      for (i in Extension_Policy_Issues) {
        Extension_Policy_Issues_Text += Extension_Policy_Issues[i].policyIssue + ": (" + 
          ((Extension_Policy_Issues[i].approvalStatus != "Approved Limited") ? "<strong><font color=\"red\">" + Extension_Policy_Issues[i].approvalStatus + "</font></strong>" : Extension_Policy_Issues[i].approvalStatus)  + "): " + 
          "<strong>" + Extension_Policy_Issues[i].count + "</strong><br>\n";
      }
    } else {
      Extension_Policy_Issues_Text = "<strong><font color=\"green\">No ad extension policy issues.</font></strong>";    
    }
    
    
    var policyManagerURL = getPolicyManagerURL(accountId);
    
    strHTMLBody += '<tr>\n'+
      '<td style="' + RawDataRowStyleNormal + '">' + accountName + ' (' + accountId + ')' + '</td>\n' +
      '<td style="' + ((Ad_Policy_Issues.length == 0) ? RawDataRowStyleNormal : RawDataRowStyleError) + '">' + Ad_Policy_Issues_Text + '</td>\n' +
      '<td style="' + ((Extension_Policy_Issues.length == 0) ? RawDataRowStyleNormal : RawDataRowStyleError) + '">' + Extension_Policy_Issues_Text + '</td>\n' +
      '<td style="' + RawDataRowStyleNormal + '"><a href="' + policyManagerURL + '">Policy issues</a> ' + ((policyManagerURL.indexOf('00000000')>-1)?'<small style="color:red;">missing</small>':'') + ' <br /><a href="' + policyManagerURL.replace("policymanager/issues", "policymanager/resubmit") + '">Appeal history</a> ' + ((policyManagerURL.indexOf('00000000')>-1)?'<small style="color:red;">missing</small>':'') + '</td>\n' +
      '</tr>\n';
    
  } 
  strHTMLBody += '</table><br /><br />\n\n';
  
  return strHTMLBody;  
}

function SendEmailReport(strHTMLBody){

  // Process your client account here.
  if (RECIPIENT_EMAIL != '') {
    //now send.
    //Logger.log('Sending email to %s this is the body: %s',RECIPIENT_EMAIL, strHTMLBody);
    MailApp.sendEmail({
      to: RECIPIENT_EMAIL,
      subject: EMAIL_SUBJECT,
      htmlBody: strHTMLBody
    });      
  }
  Logger.log('Finished sending the report!');
}


function Check_For_Ad_Policy_Issues(account){
  //This function will look for ads with any policy issues.

  var Ad_Policy_Issues = [];
  var Ad_ids = [];
  var accountName = account.getName();
  var accountId = account.getCustomerId();
  
  Logger.log(accountName + ' (' + accountId + ') - ' + 'Starting to check for not fully approved ads.');

  var query = 'SELECT ad_group_ad.ad.name, ad_group_ad.ad.final_urls, ad_group_ad.ad.type, ' + 
      'ad_group_ad.policy_summary.approval_status,ad_group_ad.policy_summary.policy_topic_entries, ' + 
      'ad_group_ad.ad.id, ad_group_ad.status, ad_group_ad.ad.type,ad_group_ad.policy_summary.review_status ' + 
      'FROM ad_group_ad ' + 
      'WHERE campaign.status = "ENABLED" AND ad_group.status = "ENABLED" AND ad_group_ad.status = "ENABLED" ' +
      'ORDER BY ad_group_ad.ad.type, ad_group_ad.ad.id ';

  try {
    
    var result = AdsApp.search(query);    

    while (result.hasNext()) {
      var row = result.next();
      var policySummary = row.adGroupAd.policySummary;

      if (policySummary.approvalStatus != "APPROVED"){
        //Logger.log(JSON.stringify(row));  

        var ad_type = row.adGroupAd.ad.type;
        var ad_id = row.adGroupAd.ad.id;      
        Ad_ids.push(ad_id);

        var finalURLs = row.adGroupAd.ad.finalUrls;      

        //Logger.log("policySummary:" + policySummary + " - policySummary.policyTopicEntries:" + policySummary.policyTopicEntries );
        
        if (policySummary.policyTopicEntries){
          for (var i = 0;i < policySummary.policyTopicEntries.length; i++) {
            var policyIssue = normalize_text(policySummary.policyTopicEntries[i].topic);
            var approvalStatus = normalize_text(policySummary.approvalStatus);

            var pos = Ad_Policy_Issues.map(function(e) { return e.policyIssue; }).indexOf(policyIssue);
            if (pos === -1){

              Ad_Policy_Issues.push({
                "policyIssue":policyIssue,
                "approvalStatus":approvalStatus,
                "count":1
              });
            } else{
              //Logger.log(Ad_Policy_Issues[pos].policyIssue + " - " +  parseInt(Ad_Policy_Issues[pos].count))
              Ad_Policy_Issues[pos].count += 1;
            }
          }          
        } else {
          var policyIssue = policySummary.reviewStatus + " ADID:[" + ad_id + "]";
          var approvalStatus = normalize_text(policySummary.approvalStatus);

          var pos = Ad_Policy_Issues.map(function(e) { return e.policyIssue; }).indexOf(policyIssue);
          if (pos === -1){

            Ad_Policy_Issues.push({
              "policyIssue":policyIssue,
              "approvalStatus":approvalStatus,
              "count":1
            });
          }          
        }          
      }
    }

    //Here we need to try the backup approach to grabbing not approved ads
    var Report_Query = "SELECT Id, CombinedApprovalStatus, PolicySummary, AdType " +
                       "FROM AD_PERFORMANCE_REPORT " +
                       "WHERE CombinedApprovalStatus NOT_IN ['ELIGIBLE','APPROVED'] AND " +
                       "AdGroupStatus = ENABLED and CampaignStatus = ENABLED AND " +
                       "Status = ENABLED";

    //We just need to exclude any adIds that we already looked at if we have any.
    if (Ad_ids.length > 0){
      Report_Query += " AND Id NOT_IN [" + Ad_ids + "]";
    }  

    var report = AdWordsApp.report(Report_Query);  
    var rows = report.rows();

    while (rows.hasNext()) {
      //Loop through all the report of the NOT approved ads.
      var row = rows.next();    

      var adID = row["Id"];
      var CombinedApprovalStatus = normalize_text(row["CombinedApprovalStatus"]);
      var PolicySummary = normalize_text(row["PolicySummary"]);
      var AdType = row["AdType"];

      var pos = Ad_Policy_Issues.map(function(e) { return e.policyIssue; }).indexOf(PolicySummary);
      if (pos == -1){
        Ad_Policy_Issues.push({
          "policyIssue":PolicySummary,
          "approvalStatus":CombinedApprovalStatus,
          "count":1
        });
      } else{
        Ad_Policy_Issues[pos].count += 1;
      }
    }  
    
    Ad_Policy_Issues.sort(compareValues("count","desc"));  
    Logger.log("Ad_Policy_Issues: " + JSON.stringify(Ad_Policy_Issues));  
  } catch(err) {
    Logger.log(accountName + " (" + accountId + ") - " + "ERROR: Ad_Policy_Issues: " +  err);
    Ad_Policy_Issues.push({
                            "policyIssue":"ERROR GETTING policyIssue",
                            "approvalStatus":"ERROR: " + err,
                            "count":1
                          });
  } finally {
      return Ad_Policy_Issues; 
  }
}

function Check_For_Ad_Extension_Policy_Issues(account){
  //This function will look for ad extensions with policy issues
  
  var Extension_Policy_Issues = [];
  var accountName = account.getName();
  var accountId = account.getCustomerId();
  
  Logger.log(accountName + ' (' + accountId + ') - ' + 'Starting to check for not fully approved extensions.');

  var query = 'SELECT feed_item.attribute_values, feed_item.policy_infos, feed_item.id, feed_item.resource_name, segments.placeholder_type, feed_item.status ' + 
      'FROM feed_item ' + 
      'WHERE feed_item.status != "REMOVED" ' +
      'ORDER BY segments.placeholder_type,feed_item.id ';

  try {
    
    //# The second argument is optional.
    var result = AdsApp.search(query);    

    while (result.hasNext()) {
      var row = result.next();

      var policyInfos = row.feedItem.policyInfos;
      var attributeValues = row.feedItem.attributeValues;
      var placeholderType = row.segments.placeholderType;

      if (policyInfos[0].approvalStatus != "APPROVED" && placeholderType != "UNKNOWN" ){
        //Logger.log(JSON.stringify(row));

        for (i in policyInfos) {
          for (j in policyInfos[i].policyTopicEntries){
            var policyIssue = normalize_text(policyInfos[i].policyTopicEntries[j].topic);
            var approvalStatus = normalize_text(policyInfos[i].approvalStatus);
            var pos = Extension_Policy_Issues.map(function(e) { return e.policyIssue; }).indexOf(policyIssue);

            if (pos == -1){
              Extension_Policy_Issues.push({
                "policyIssue":policyIssue,
                "approvalStatus":approvalStatus,
                "extensionType":normalize_text(placeholderType),
                "count":1
              });
            } else{
              Extension_Policy_Issues[pos].count += 1;
            }  
          }
        }
      } 
    }
    
    Extension_Policy_Issues.sort(compareValues("count","desc"));  
    Logger.log("Extension_Policy_Issues: " + JSON.stringify(Extension_Policy_Issues));    
  } catch(err) {
    Logger.log(accountName + " (" + accountId + ") - " + "ERROR: Extension_Policy_Issues: " +  err);
    
    Extension_Policy_Issues.push({
                "policyIssue": "ERROR GETTING policyIssue",
                "approvalStatus":"ERROR: " + err,
                "extensionType":"",
                "count":1
              });
    
  } finally {
    return Extension_Policy_Issues; 
  }  
}

function compareValues(key, order) {
  //This function is used to sort arrays of objects based on a specific key
  //order = asc or desc
  
  return function innerSort(a, b) {
    if (!a.hasOwnProperty(key) || !b.hasOwnProperty(key)) {
      // property doesn't exist on either object
      return 0;
    }

    const varA = (typeof a[key] === 'string')
      ? a[key].toUpperCase() : a[key];
    const varB = (typeof b[key] === 'string')
      ? b[key].toUpperCase() : b[key];

    var comparison = 0;
    if (varA > varB) {
      comparison = 1;
    } else if (varA < varB) {
      comparison = -1;
    }
    return (
      (order === 'desc') ? (comparison * -1) : comparison
    );
  };
}

function normalize_text(string){
  //Fix the text so that we don't have any underscores or all uppercase words.
  //Also, clean up some other text issues.
  
  var new_string = string.replace(/_/g, " ").replace(/\w\S*/g, function(txt){return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();});
  
  new_string = new_string.replace(/\(Limited\)/g, "Limited");
  new_string = new_string.replace(/\[\"/g, "").replace(/\"\]/g, "");
  
  return new_string;   
}


function getPolicyManagerURL(accountID){
  
  //  This function is for creating the hotlinks 
  //  to the policy manager for specific client 
  //  accounts. Since Google Ads does not allow you 
  //  to programmatically grab the OCID, you need to 
  //  hard code that into this function.
  
  
  var PolicyManagerURL = "https://ads.google.com/aw/policymanager/issues?ocid=00000000&authuser=0";

  switch (accountID) {
    case "111-111-1111": //client 111-111-1111	
      PolicyManagerURL = PolicyManagerURL.replace("00000000", "1111111");
      break;
    case "222-222-2222": //client 222-222-2222	
      PolicyManagerURL = PolicyManagerURL.replace("00000000", "2222222");
      break;
    case "333-333-3333": //client 333-333-3333	
      PolicyManagerURL = PolicyManagerURL.replace("00000000", "3333333");
      break;    
  }
  return PolicyManagerURL;
}