Google Ads MCC URL Check

Overview

This spreadsheet will receive data from a Google Ads MCC Script to check all URLs with Impressions in the last 14 Days for their HTTP Status Codes.

Getting Started

1. Start by making a copy of the Google Sheet with the link above.

2. At the top of the page, go to the menu item: Sheet Controls > HTTP Status Check > Manually Update HTTP Status List and approve the script's authorization request. You may want to click this twice to double check...nothing should happen the second time.

3. Go to Sheet Controls > Create Hourly Trigger For HTTP Status List to schedule this list to check URLs each hour.

4. If you want to receive Email alerts when any URLs are flagged as having an HTTP Status Code > 399, go to Settings and enter your email and desired alert frequency. Recommend using hourly. Emails will only be sent if URLs are flagged.

5. Go to your Google Ads MCC and create a new Script.

6. Paste in the script below, edit the MY_GOOGLE_SHEET_URL to the URL of your new Google Sheet from Step 1, click Run, authorize the script, and click Run again.

7. URLs from all accounts should now appear on the Google Sheet you created above (See URL Import tab)

8. Go back to the main Scripts page and schedule the frequency for this script to whatever you see fit (I set it to hourly but Daily would work just as well.)

All done! Note that if you wanted to use this script just for a single account, you can use one of the Single Account reporting scripts (just change the AWQL to FINAL_URL_REPORT and include EffectiveFinalUrl in the list of metrics.

function main() {

  //***START CUSTOM VARIABLES***//

    //URL of Google Sheet to push data to

      var spreadsheetUrl = 'MY_GOOGLE_SHEET_URL';

    //name of tab on sheet where data will be delivered

      var sheetTabName = 'URL Import';

    //see documentation for preset dates under DateRangeLiteral: https://developers.google.com/adwords/api/docs/guides/awql#formal_grammar

      var dateRange = 'LAST_14_DAYS';

    //report type – see documentation here: https://developers.google.com/adwords/api/docs/appendix/reports/account-performance-report

      var reportType = 'FINAL_URL_REPORT';

    //select metrics from report above – see link from documentation – make sure all terms are comma separated with a space afterwards

      var metrics = 'ExternalCustomerId, CustomerDescriptiveName, EffectiveFinalUrl';

    //if you want to add a filter to your data using one of the metrics from the selected report, simply un-comment line 49 below and customize the filter..

    //..selector using a metric from the report you selected

  //***END CUSTOM VARIABLES***//

  //account iterator – currently gets all accounts, but can be adjusted to filter for specifics using this documentation:

  //https://developers.google.com/google-ads/scripts/docs/examples/ads-manager-scripts

  var accountIterator = MccApp.accounts().withCondition('Impressions > 0').forDateRange(dateRange).get();

  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

  var mccAccount = AdWordsApp.currentAccount();

  var sheet = spreadsheet.getSheetByName(sheetTabName);

  var headers = metrics.split(', ');

  var dataArray = [];

  //pull report and append rows of data

  while (accountIterator.hasNext()) {

    var account = accountIterator.next();

    MccApp.select(account);

    var report = AdWordsApp.report(

      'SELECT ' + metrics + ' ' +

      'FROM ' + reportType + ' ' +

      //'WHERE CampaignStatus = "ENABLED" ' +

      'DURING ' + dateRange);

    var rows = report.rows();

      while (rows.hasNext()) {

          var row = rows.next();

          var rowArray = [];

            for (var i = 0; i < headers.length; i++) {

              rowArray.push(row[headers[i]]);

            }

          dataArray.push(rowArray);

      }

  }

  //clear sheet & add header using metrics variable

  sheet.clearContents();

  sheet.appendRow(headers);

  sheet.getRange(2, 1, dataArray.length, headers.length).setValues(dataArray);

}