/** * * Report Disapproved Ads * * Creates a report indicating which ads are disapproved, including the reason why * Sends an alert via email whenever ads are disapproved * * @author: Nils Rooijmans * * contact nils@nilsrooijmans.com for questions and the MCC version of the script */ // CHANGE SETTINGS HERE var SPREADSHEET_URL = ""; //insert a new blank spreadsheet url between the quotes var EMAIL = ""; //insert your email adresses between the quotes // NO CHANGES NEEDED BELOW THIS LINE var SHEET_REPORT_HEADER = [ "Campaign Name", "AdGroup Name", "AdID", "Ad Type", "Ad Status", "Disapproval Reason" ]; function main() { // first we clear the report sheet and add header prepareSpreadsheet(); // now let's check for disapprovals var disapprovedAds = getDisApprovedAds(); // finally, let's report on any issues if (disapprovedAds.length > 0) { // there is at least one issue Logger.log("Total NR of Ads Disapproved: "+disapprovedAds.length); reportResults(disapprovedAds); } } function prepareSpreadsheet() { var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL); var sheet = ss.getActiveSheet(); sheet.clear(); //remove earlies alerts sheet.clearConditionalFormatRules(); sheet.appendRow(SHEET_REPORT_HEADER); } function getDisApprovedAds() { var issues = []; var whereClause = "WHERE CampaignStatus = 'ENABLED' AND AdGroupStatus = 'ENABLED' AND Status = 'ENABLED' AND CombinedApprovalStatus = 'DISAPPROVED' "; var awqlQuery = "SELECT CampaignName, AdGroupName, Id, AdType, CombinedApprovalStatus, PolicySummary FROM AD_PERFORMANCE_REPORT "+whereClause; //debug("awqlQuery: "+awqlQuery); var rows=AdsApp.report(awqlQuery).rows() ; while (rows.hasNext()) { var row=rows.next() ; var campaignName = row["CampaignName"]; var adGroupName = row["AdGroupName"]; var adId = row["Id"]; var adType = row["AdType"]; var approvalStatus = row["CombinedApprovalStatus"]; var policySummary = row["PolicySummary"]; issues.push([ campaignName, adGroupName, adId, adType, approvalStatus, policySummary ]); } return issues; } function reportResults(results) { var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL); var sheet = ss.getActiveSheet(); var lastRow = sheet.getLastRow(); // write issues to sheet var range = sheet.getRange(lastRow+1, 1, results.length, SHEET_REPORT_HEADER.length); range.setValues(results); // send the email var emailBody = "Number of Ads Disapproved: " + results.length + "\n" + "See details: "+ SPREADSHEET_URL + "\n\n" + "For more FREE Google Ads Scripts to improve your results and make your working day feel like a breeze, visit https://nilsrooijmans.com \n" + "---\n" + "This email is generated by a copy of the free Google Ads Script - Disapproved Ads Checker, (C) Nils Rooijmans \n" + "---\n"; MailApp.sendEmail(EMAIL, "[GOOGLE ADS ALERT] - Disapproved Ads", emailBody); }