Google Ads Reporting Scripts

Use this sheet to get stats from Google Ads from a pre-set date range (THIS_MONTH below). Just create a new Google Sheet, copy & paste the URL where it says MY_GOOGLE_SHEET_URL and update the date range with any of these pre-set values (see "DateRangeLiteral" section). Name the tab in your Google Sheet: Account Import. Schedule the frequency to have this update automatically. This report pulls data from the Account level. To change this and change metrics/dimensions, see more info here.

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

var SheetURL = "MY_GOOGLE_SHEET_URL";

var AccountTabName = "Account Import";

var QUERIES = [

                {'query' : 'SELECT AccountCurrencyCode, AccountDescriptiveName, AccountTimeZone, CanManageClients, ConversionAdjustment, CustomerDescriptiveName, ExternalCustomerId, IsAutoTaggingEnabled, IsTestAccount, Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionRate, CostPerConversion, ConversionValue ' +

                   'FROM   ACCOUNT_PERFORMANCE_REPORT ' +

                   'WHERE  CampaignStatus = "ENABLED" AND ServingStatus = "SERVING" ' +

                   'DURING  THIS_MONTH',

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : AccountTabName,

                   '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);

  }

}

Use this sheet to get stats from Google Ads from a pre-set date range (Last 365 days below). Just create a new Google Sheet, copy & paste the URL where it says MY_GOOGLE_SHEET_URL and update the number of days that you want to get data for by changing the 365 in the startDate formula. Make sure that you create four tabs on your sheet with the names: Query Import, Keyword Import, Ad Group Import, and Campaign Import.  Schedule the frequency on the script to have this update automatically. This report pulls data from the Account level. To change this and change metrics/dimensions, see more info here.

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

var SheetURL = "MY_GOOGLE_SHEET_URL";

var QueryTabName = "Query Import";

var KeywordTabName = "Keyword Import";

var AdGroupTabName = "Ad Group Import";

var CampaignTabName = "Campaign 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, KeywordId, KeywordTextMatchingQuery, Query, QueryMatchTypeWithVariant, QueryTargetingStatus, Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionRate, CostPerConversion, ConversionValue ' +

                   'FROM   SEARCH_QUERY_PERFORMANCE_REPORT ' +

                   'WHERE  CampaignStatus = "ENABLED" AND Impressions > 1  AND Cost > 1 AND QueryTargetingStatus = "NONE" ' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : QueryTabName,

                   'reportVersion' : 'v201809'

                }, 

                {'query' : 'SELECT CampaignName, AdGroupName, Labels ' +

                   'FROM   ADGROUP_PERFORMANCE_REPORT ' +

                   'WHERE  CampaignStatus = "ENABLED" AND AdGroupStatus = "ENABLED" ' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : AdGroupTabName,

                   'reportVersion' : 'v201809'

                }, 

                {'query' : 'SELECT CampaignName, Labels ' +

                   'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +

                   'WHERE  CampaignStatus = "ENABLED" ' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : CampaignTabName,

                   'reportVersion' : 'v201809'

                }, 

                {'query' : 'SELECT Week, CampaignName, AdGroupName, Id, Criteria, KeywordMatchType, Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionRate, CostPerConversion, ConversionValue ' +

                   'FROM   KEYWORDS_PERFORMANCE_REPORT ' +

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

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : KeywordTabName,

                   '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);

  }

}

Use this script to pull simple reports from all accounts in your MCC. Simply set up an empty Google Sheet, copy the URL into the spreadsheetUrl variable and the tab name you want the data delivered to in the variable sheetTabName.  Then you can customize the report you want to select data from and the metrics you want included in the data in the "Custom Variables" section of the script below. Just follow the commented-out instructions.

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 = 'MY_GOOGLE_SHEET_TAB_NAME';

    //format start date of data: YYYYMMDD – see note in line 24 regarding end date if a day other than today is desired.

      var startDate = '20200101';

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

      var reportType = 'ACCOUNT_PERFORMANCE_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, Month, Impressions, Clicks, Cost, Conversions, ConversionValue';

    //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***//

  //end date (default is today's date – if other date is desired, simply change the endDate variable to a specific date formatted like the startDate variable above.)

  var now = new Date();

  var nowDate = new Date(now.getTime());

  var endDate = Utilities.formatDate(nowDate, 'GMT-5', 'yyyyMMdd');

  var dateRange = startDate+','+endDate;

  //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().get();

  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

  var mccAccount = AdWordsApp.currentAccount();

  var sheet = spreadsheet.getSheetByName(sheetTabName);

  //clear sheet & add header using metrics variable

  sheet.clearContents();

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

  sheet.appendRow(headers);

  //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]]);

            }

          sheet.appendRow(rowArray);

      }

  }

}

Use this sheet to bulk upload changes to the Google Ads platform for any given account. Simply paste the script below, and change the MY_SHEET_URL and MY_TAB_NAME to match the Google Sheet URL and the name of the specific tab within that file that contains your data. Note that data has to match a valid bulk upload template format.

function main() {

  // The format of this spreadsheet should match a valid bulk upload template.

  // See https://developers.google.com/google-ads/scripts/docs/features/bulk-upload

  // for the list of supported bulk upload templates.

  var SPREADSHEET_URL = 'MY_SHEET_URL';

  var spreadSheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  var sheet = spreadSheet.getSheetByName("MY_TAB_NAME");

  var upload = AdsApp.bulkUploads().newFileUpload(sheet);

  upload.forCampaignManagement();

  // Use upload.apply() to make changes without previewing.

  upload.apply();

}

Use this sheet to import data from Campaigns/URLs that contain specific labels. Simply paste this script into the Google Ads Script Editor, change the startDate at the top, create a new Google Sheet report using this link, and paste the link for your new file where it says MY_SHEET_URL.  Then the only thing left to do is update your Label IDs. If you know these...great. Just paste any/all Labels that you want to filter on into the list where it says ID_1, ID_2, etc. If you don't know what your Label IDs are, just use the next script.

//update experiment startDate in format below

var startDate = 'YYYYMMDD';

//to create a new report, use this template: https://docs.google.com/spreadsheets/d/1j9bZn_JIn4nrrYE50iVXMl9SFVQq8_1UHHGf_f8PKuU/copy and paste the link below

var spreadsheetUrl = 'MY_SHEET_URL';

//do not update variables below and do not change any tab names in the template spreadsheet! BUT make sure that all Campaigns & Ads that you want to measure have the "Experiment" label applied!

var endDate = new Date();

var endRange = Utilities.formatDate(endDate, 'America/Los_Angeles', 'YYYYMMdd');

var QUERIES = [

                {'query' : 'SELECT Date, Labels, CampaignName, CampaignStatus, ServingStatus, Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionRate, CostPerConversion, ConversionValue ' +

                   'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +

                   'WHERE  LabelIds CONTAINS_ANY ["ID_1", "ID_2", "ID_3", "ID_4"] ' +

                   'DURING ' + startDate + ',' + endRange,

                   'spreadsheetUrl' : spreadsheetUrl,

                   'tabName' : 'Campaign Data Import',

                   'reportVersion' : 'v201809'

                }, 

                {'query' : 'SELECT CreativeFinalUrls, CampaignStatus, Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionRate, CostPerConversion, ConversionValue ' +

                   'FROM   AD_PERFORMANCE_REPORT ' +

                   'WHERE  LabelIds CONTAINS_ANY ["ID_1", "ID_2", "ID_3", "ID_4"] ' +

                   'DURING ' + startDate + ',' + endRange,

                   'spreadsheetUrl' : spreadsheetUrl,

                   'tabName' : 'URL Data Import',

                   '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);

  }

}

Need your Label IDs for a filter? Just create a new Google Sheet, change the MY_SHEET_URL and run this script:

//update experiment startDate in format below

var startDate = 'YYYMMDD';

var spreadsheetUrl = 'MY_SHEET_URL';

var endDate = new Date();

var endRange = Utilities.formatDate(endDate, 'America/Los_Angeles', 'YYYYMMdd');

var QUERIES = [

                {'query' : 'SELECT LabelId, LabelName ' +

                   'FROM   LABEL_REPORT',

                   'spreadsheetUrl' : spreadsheetUrl,

                   'tabName' : 'Label IDs',

                   '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);

  }

}

Or just log the list of labels to the console of the script for quick access:

function main() {

  var query = 'SELECT LabelId, LabelName FROM LABEL_REPORT';

  var reportVersion = 'v201809';

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

  var rows = report.rows();

  while (rows.hasNext()) {

    var row = rows.next();

    var labelId = row['LabelId'];

    var labelName = row['LabelName'];

    Logger.log('Label ID: ' + labelId + ', Label Name: ' + labelName);

  }

}

Just a simple script to automate the addition/customization of Tracking Templates that will specify the Campaign Name in each campaign's UTMs for any third party software that cannot fully read GCLID data. Since Google's ValueTrack parameters do not allow for Campaign Names to be dynamically inserted into Tracking Templates, websites with third party tools (SalesForce, Pardot, etc.) may need the Campaign Name to be specified. This script will make the addition of each campaign's name in the UTMs a bit quicker and more dynamic. You can set it up in the account and even schedule it to run hourly to ensure that the Campaign Names are always up-to-date!

By default, this Script will only customize the "campaign" UTM parameter with each Campaign's Name at the Campaign-level Tracking Template. You can customize further by editing the UTM structure specified in line 10 of the script – just don't edit the encodedCampaignName portion!

More details on this script here.

function main() {

  //adds a custom tracking template at the campaign level to specify campaign names in the UTM parameters. You can customize the UTMs in line 10, but do not edit the encodedCampaignName portion.

  //get all campaigns in account. To filter see guidelines: https://developers.google.com/google-ads/scripts/docs/reference/adsapp/adsapp_campaignselector

  var campaignIterator = AdsApp.campaigns().get();

  //iterate addition of tracking template over every campaign that meets your filter criteria. Gets name of each campaign, encodes them for the URL, and sets the tracking template at the Campaign level.

  while (campaignIterator.hasNext()) {

    var campaign = campaignIterator.next();

    var campaignName=campaign.getName();

    var encodedCampaignName=encodeURIComponent(campaignName);

    campaign.urls().setTrackingTemplate('{lpurl}?utm_campaign='+encodedCampaignName)

  }

}

Quick script to get details on a single-day's (limited by API) click numbers by GCLID.

Documentation can be found here. Useful tool for uploading Conversions.

function main() {

  var sheetUrl = 'YOUR_SPREADSHEET_URL_HERE';  // Replace with your Google Sheet URL

  var sheetName = 'GCLID Report';  // Name of the sheet tab

  var reportQuery = `

    SELECT 

      click_view.gclid, 

      segments.date, 

      campaign.id, 

      campaign.name, 

      ad_group.id, 

      ad_group.name, 

      click_view.keyword, 

      click_view.keyword_info.text, 

      metrics.clicks

    FROM click_view

    WHERE segments.date = '20250318'

    ORDER BY segments.date ASC

  `;

  var report = AdsApp.report(reportQuery);

  var rows = report.rows();

  var sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);

  if (!sheet) {

    sheet = SpreadsheetApp.openByUrl(sheetUrl).insertSheet(sheetName);

  }

  // Clear existing data and set headers

  sheet.clear();

  sheet.appendRow(["GCLID", "Date", "Campaign ID", "Campaign Name", "Ad Group ID", "Ad Group Name", "Keyword ID", "Keyword", "Clicks"]);

  var dataRows = [];

  while (rows.hasNext()) {

    var row = rows.next();

    dataRows.push([

      row["click_view.gclid"],

      row["segments.date"],

      row["campaign.id"],

      row["campaign.name"], 

      row["ad_group.id"],

      row["ad_group.name"],

      row["click_view.keyword"], 

      row["click_view.keyword_info.text"], 

      row["metrics.clicks"]

    ]);

  }

  sheet.getRange(2, 1, dataRows.length, dataRows[0].length).setValues(dataRows);

  Logger.log("Report successfully sent to Google Sheets!");

}