This script will run through all your AdWords accounts and look for Ads that have been disapproved

mcc-ad-disaproval-reasons.js

/********************************************************************************
* This script will run through all your AdWords accounts and look for Ads that 
* have been disapproved
*
* @author Russell Savage <russellsavage@gmail.com>
* @version 1.0
* FreeAdWordsScripts.com
*
* THIS SOFTWARE IS PROVIDED BY Russell Savage ''AS IS'' AND ANY
* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL Russell Savage BE LIABLE FOR ANY
* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
********************************************************************************/
var SCRIPT_NAME = 'Disapproved Ads Checker';
var NOTIFY = ['your_email@example.com'];
var ACCOUNTS_FILE_NAME = 'AdWords-Scripts-DisapprovedAds-AccountList.json';
var SPREADSHEET_PREFIX = 'AdWords-DisapprovedAds-';

function main() {
  var accountsList = readJSONFile(ACCOUNTS_FILE_NAME);
  if(!accountsList) { accountsList = []; }
  if(accountsList.length === 0) {
    //new run so let's build our list of accounts
    var acctIter = MccApp.accounts().get();
    while(acctIter.hasNext()) {
      var acct = acctIter.next();
      accountsList.push({ id : acct.getCustomerId(), 
                          lastChecked : null });
    }
  }
  accountsList.sort(function(a,b) {
    if(a.lastChecked < b.lastChecked) {
      return -1;
    } else if(a.lastChecked > b.lastChecked) {
      return 1;
    } else {
      return 0;
    }
  });
  writeJSONFile(ACCOUNTS_FILE_NAME,accountsList);
  var toCheck = [];
  for(var i in accountsList) {
    toCheck.push(accountsList[i].id);
    if(toCheck.length==50) {
      break;
    }
  }
  Logger.log('Checking the following accounts: '+JSON.stringify(toCheck));
  MccApp.accounts().withIds(toCheck).executeInParallel('checkAdDisapprovalReasons', 'reportResults');
}

function checkAdDisapprovalReasons() {
  Logger.log('Processing account: '+AdWordsApp.currentAccount().getName());
  var adIter = AdWordsApp.ads().withCondition('CampaignStatus = ENABLED')
    .withCondition('AdGroupStatus = ENABLED')
    .withCondition('Status = ENABLED')
    .withCondition('ApprovalStatus = DISAPPROVED')
    .get();
  var results = [];
  while(adIter.hasNext()) {
    var ad = adIter.next();
    results.push({ 
      camp:ad.getCampaign().getName(),
      ag:ad.getAdGroup().getName(),
      headline:ad.getHeadline(),
      desc1:ad.getDescription1(),
      desc2:ad.getDescription2(),
      displayUrl:ad.getDisplayUrl(),
      destUrl:(ad.getDestinationUrl())?ad.getDestinationUrl():'',
      disapprovalReasons:ad.getDisapprovalReasons().join('. ')
    });
  }
  return JSON.stringify({
    accountId : AdWordsApp.currentAccount().getCustomerId(),
    accountName : AdWordsApp.currentAccount().getName(),
    disapprovedAdsCount : results.length,
    disapprovedAds : results
  });
}

function reportResults(responses) {
  var accountsList;
  var indexMap = {};
  accountsList = readJSONFile(ACCOUNTS_FILE_NAME);
  for(var i = 0; i < accountsList.length; i++) {
    indexMap[accountsList[i].id] = i;
  }

  var summaryEmailData = [];
  var dateTimeStr = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd HH:m:s');
  var spreadsheetName = SPREADSHEET_PREFIX+' - '+dateTimeStr;
  for(var i in responses) {
    if(!responses[i].getReturnValue()) { continue; }
    var res = JSON.parse(responses[i].getReturnValue());
    var sheetUrl = writeResultsToSpreadsheet(res,spreadsheetName);
    summaryEmailData.push({accountId:res.accountId,
                           accountName:res.accountName,
                           disapprovedAdsCount:res.disapprovedAdsCount,
                           sheetUrl:sheetUrl});
    accountsList[indexMap[res.accountId]].lastChecked = dateTimeStr;
  }
  if(summaryEmailData.length > 0) {
    sendSummaryEmail(summaryEmailData);
  }
  writeJSONFile(ACCOUNTS_FILE_NAME,accountsList); 
}

//This function builds the summary email and sends it to the people in
//the NOTIFY list
function sendSummaryEmail(summaryEmailData) {
  var subject = SCRIPT_NAME+' Summary Results';
  var body = subject;
  var htmlBody = '<html><body>'+subject;
  htmlBody += '<br/ ><br/ >';
  htmlBody += '<table border="1" width="95%" style="border-collapse:collapse;">';
  htmlBody += '<tr>';
  htmlBody += '<td align="left"><b>Acct Id</b></td>';
  htmlBody += '<td align="left"><b>Acct Name</b></td>';
  htmlBody += '<td align="center"><b>Disapproved Ads Found</b></td>';
  htmlBody += '<td align="center"><b>Full Report</b></td>';
  htmlBody += '</tr>';
  for(var i in summaryEmailData) {
    var row = summaryEmailData[i];
    htmlBody += '<tr><td align="left">'+ row.accountId +
               '</td><td align="left">' + row.accountName + 
               '</td><td align="center">' + row.disapprovedAdsCount + 
               '</td><td align="left"><a href="'+row.sheetUrl+'">' + 'Show Details' + 
           '</a></td></tr>';
  }
  htmlBody += '</table>';
  htmlBody += '<br/ >';
  htmlBody += Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(),'MMMM dd, yyyy @ hh:mma z');
  htmlBody += '.  Completed. '+Object.keys(summaryEmailData).length+' Accounts checked.<br/ >';
  htmlBody += 'Powered by <a href="http://www.freeadwordsscripts.com">FreeAdWordsScripts.com</a>.';
  htmlBody += '</body></html>';
  var options = { htmlBody : htmlBody };
  for(var i in NOTIFY) {
    MailApp.sendEmail(NOTIFY[i], subject, body, options);
  }
}

function writeResultsToSpreadsheet(res,name) {
  var file = getFile(name,true);
  var spreadsheet;
  var maxRetries = 3;
  while(maxRetries > 0) {
    try {
      spreadsheet = SpreadsheetApp.openById(file.getId());
      break;
    } catch(e) {
      maxRetries--;
      Utilities.sleep(1000);
    }
  }
  if(!spreadsheet) { throw 'Could not open file: '+name; }
  if(spreadsheet.getSheetByName('Sheet1')) {
    spreadsheet.getSheetByName('Sheet1').setName(res.accountId);
  }
  var sheet = spreadsheet.getSheetByName(res.accountId);
  if(!sheet) {
    sheet = spreadsheet.insertSheet(res.accountId, spreadsheet.getSheets().length);
  }
  var toWrite = [['Disapproval Reasons','Campaign','AdGroup','Headline','Description 1','Description 2','Display Url','Destination Url']];
  for(var i in res.disapprovedAds) {
    var row = res.disapprovedAds[i];
    toWrite.push([row.disapprovalReasons,
                  row.camp,
                  row.ag,
                  row.headline,
                  row.desc1,
                  row.desc2,
                  row.displayUrl,
                  row.destUrl]); 
  }
  var lastRow = sheet.getLastRow();
  var numRows = sheet.getMaxRows();
  if((numRows-lastRow) < toWrite.length) {
    sheet.insertRowsAfter(lastRow,toWrite.length-numRows+lastRow);
  }
  var range = sheet.getRange(lastRow+1,1,toWrite.length,toWrite[0].length);
  range.setValues(toWrite);
  if((sheet.getMaxColumns() - sheet.getLastColumn()) > 0) {
    sheet.deleteColumns(sheet.getLastColumn()+1, sheet.getMaxColumns() - sheet.getLastColumn());
  }
  file = DriveApp.getFileById(spreadsheet.getId());
  try {
    file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  } catch(e) {
    file.setSharing(DriveApp.Access.DOMAIN_WITH_LINK, DriveApp.Permission.VIEW);
  }
  //This gives you a link directly to the spreadsheet sheet.
  return (spreadsheet.getUrl() + '#gid=' + sheet.getSheetId());
}


//This function quickly writes the url data to a file
//that can be loaded again for the next run
function writeJSONFile(fileName,toWrite) {
  var file = getFile(fileName,false);
  file.setContent(JSON.stringify(toWrite));
}

//And this loads that stored file and converts it to an object
function readJSONFile(fileName) {
  var file = getFile(fileName,false);
  var fileData = file.getBlob().getDataAsString();
  if(fileData) {
    return JSON.parse(fileData);
  } else {
    return null;
  }
}
 
//This function finds a given file on Google Drive
//If it does not exist, it creates a new file
//if isSpreadsheet is set, it will create a new spreadsheet
//otherwise, it creates a text file.
function getFile(fileName,isSpreadsheet) {
  var maxRetries = 3;
  var errors = [];
  while(maxRetries > 0) {
    try {
      var fileIter = DriveApp.getFilesByName(fileName);
      if(!fileIter.hasNext()) {
        Logger.log('Could not find file: '+fileName+' on Google Drive. Creating new file.');
        if(isSpreadsheet) {
          return SpreadsheetApp.create(fileName);
        } else {
          return DriveApp.createFile(fileName,'');
        }
      } else {
        return fileIter.next();
      }
    } catch(e) {
      errors.push(e);
      maxRetries--;
      Utilities.sleep(1000);
    }
  }
  if(maxRetries === 0) {
    throw errors.join('. ');
  }
}

Very new to scripts so please bare with what probably are stupid questions. Just ran this at the MCC level but only seemed to get about 50 accounts back in the report. We have about 800 accounts, 225 currently active. Is there a way to a) only choose the accounts running this month - we can apply labels is that helps? b) Run for EVERY account. Any help would be much appreciated.

Hi amet,

The script is specifically designed to run through only 50 accounts per run and will automatically sort by the last checked date/time.

If you rerun the script say hourly it will do the first 50, the next 50, the next 50, etc.

If you would like to modify it for only active accounts in the last month, go to line 30 and update:

var acctIter = MccApp.accounts().get();
to
var acctIter = MccApp.accounts().withCondition("Impressions != 0").forDateRange("LAST_7_DAYS").get();

You will need to clear your AdWords-Scripts-DisapprovedAds-AccountList.json to force a new first run however.

(Note that it does actually check for only active campaigns after the MCC level already. Line 60 - 64)

Really useful! Only issue is the 'Disapproval Reasons' field is not being populated. any idea how to get that working? Thank you!

Hey Russ,

Thanks for the script!

One small thing, I'm trying to get only labelled accounts to be checked, so I changed line 30 from...
var acctIter = MccApp.accounts().get();
to
var acctIter = MccApp.accounts().withCondition("LabelNames CONTAINS 'MY - Search'").get();

It still does not seem to work though. Wanted to know if you had a workaround.

Best,

Jerry

Thank you Russel, very helpful.
Do you know how can i filter out ended campaigns and experiments?

Hi,

I have a question about this script.

Is it possible to make the script mail me a spreadsheet, only if the script find disapproved ads? And not email the spreadsheet if 0 disapproved ads is found?

Thank you in advance!

Regards
Jonathan

How do I do this, "You will need to clear your AdWords-Scripts-DisapprovedAds-AccountList.json to force a new first run however."

Hey Russ,

Thanks for the script!

One small thing, I'm trying to get only labelled accounts to be checked, so I changed line 30 from...
var acctIter = MccApp.accounts().get();
to
var acctIter = MccApp.accounts().withCondition("LabelNames CONTAINS 'MY - Search'").get();

It still does not seem to work though. Wanted to know if you had a workaround.

Best,

Jerry

Did anyone get this to work with the label filter?

Hey Russ, this is an excellent script, many thanks for the huge effort!

I have implemented it today at MCC level and it is working great.

One question - will it be possible to check disapproved ads only for the accounts that are labeled as Active?

Many thanks again and all the best, Ovidiu

Hey Russ, this is an excellent script, many thanks for the huge effort!

I have implemented it today at MCC level and it is working great.

One question - will it be possible to check disapproved ads only for the accounts that are labeled as Active?

Many thanks again and all the best, Ovidiu

Found the solution here: https://groups.google.com/forum/#!topic/adwords-scripts/PjZCrqaorDU
The idea is to delete the JSON file before running again the script based on labels.

First of all thanks for this script

There are a few things that aren't working for me and is there a way to simplify this too? First of all the script keeps running over the first 50, I have it set to run hourly. Secondly, I want it to only look over active accounts as I have over 800 accounts and around 400 are active I used

var acctIter = MccApp.accounts().withCondition("Impressions != 0").forDateRange("LAST_30_DAYS").get();

but this is still bringing back the same first 50 accounts

The next question can this be added to just one sheet? everything this runs its generating a be sheet

Thanks

Thanks everyone for the great comments! I haven't looked at this in years and i'm not sure of the latest changes to the AdWords Scripts interface, so this script is probably pretty out of date. If anyone out there would like to take over this script and make changes, I'd be happy to link to a fork. Thanks again!

but this is still bringing back the same first 50 accounts

Hey there! If executed in parallel, no more than 50 accounts are allowed, hence the limitation.

Excerpt below, full link here: https://developers.google.com/google-ads/scripts/docs/limits

Each account processed by an Ads Manager script gets its own quota as listed above.
When using executeInParallel method, a script can process up to 50 accounts.
The processAccount method from executeInParallel can return up to 10MB of data.

Try this workaround: http://www.freeadwordsscripts.com/2014/11/executeinparallel-on-more-than-50.html

Thanks everyone for the great comments! I haven't looked at this in years and i'm not sure of the latest changes to the AdWords Scripts interface, so this script is probably pretty out of date. If anyone out there would like to take over this script and make changes, I'd be happy to link to a fork. Thanks again!

Many thanks! Just to let you know that your scripts works like a charm, even after 6 years!! So, thanks again for the epic work!

Amazing! Thanks @ovisele!