Internal Account Audits

// Reference: https://developers.google.com/adwords/api/docs/guides/awql

var SheetURL = "MY_GOOGLE_SHEET_URL";

//DO NOT EDIT BELOW

var KeywordTabName = "Keyword Import";

var BidStrategyTabName = "Bid Strategy Import";

var LandingPageTabName = "Landing Page Import";

var PlacementTabName = "Placement Import";

// GET LAST 365 Days

var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

var now = new Date();

var startDate = new Date(now.getTime() - 365 * MILLIS_PER_DAY);

var endDate = new Date(now.getTime() - 0 * MILLIS_PER_DAY);

var from = Utilities.formatDate(startDate, "GMT-5", "yyyyMMdd");;

var to = Utilities.formatDate(endDate, "GMT-5", "yyyyMMdd");

  Logger.log(from);

  Logger.log(to);

var DateRange = from+','+to;

var QUERIES = [

                {'query' : 'SELECT CampaignName, AdGroupName, Criteria, AdGroupName, KeywordMatchType, Impressions, Clicks, Cost, Conversions, ConversionValue, QualityScore, CreativeQualityScore, PostClickQualityScore, SearchPredictedCtr ' +

                   'FROM   KEYWORDS_PERFORMANCE_REPORT ' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : KeywordTabName,

                   'reportVersion' : 'v201809'

                },

                {'query' : 'SELECT CampaignName, AdvertisingChannelType, BiddingStrategyType, EnhancedCpcEnabled, Impressions, Clicks, Cost, Conversions, ConversionValue ' +

                   'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : BidStrategyTabName,

                   'reportVersion' : 'v201809'

                },

                {'query' : 'SELECT CampaignName, AdvertisingChannelType, EffectiveFinalUrl, Impressions, Clicks, Cost, Conversions, ConversionValue ' +

                   'FROM   FINAL_URL_REPORT ' +

                   'WHERE  CampaignStatus = "ENABLED" AND Impressions > 1 ' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : LandingPageTabName,

                   'reportVersion' : 'v201809'

                },

                {'query' : 'SELECT CampaignName, Criteria, Impressions, Clicks, Cost, Conversions, ConversionValue ' +

                   'FROM   PLACEMENT_PERFORMANCE_REPORT ' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : PlacementTabName,

                   'reportVersion' : 'v201809'

                }

              ];

function main() {

  for(var i in QUERIES) {

    var queryObject = QUERIES[i];

    var query = queryObject.query;

    var spreadsheetUrl = queryObject.spreadsheetUrl;

    var tabName = queryObject.tabName;

    var reportVersion = queryObject.reportVersion;

    //Logger.log(spreadsheetUrl + " " + query);

    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

    var sheet = spreadsheet.getSheetByName(tabName);

    var report = AdWordsApp.report(query, {apiVersion: reportVersion});

    report.exportToSheet(sheet);

  }

}