How to Extract Google Ads Campaign Data Using a Script

What This Script Does

This script generates a detailed spreadsheet report containing all key components of your Google Ads campaigns created within the last 30 days. It organizes the data into separate sheets for campaigns, ad groups, ads, keywords, and extensions, making it easier to analyze your account structure and performance.

function main() {

  var spreadsheet = SpreadsheetApp.create("Comprehensive Campaign Details (Last 30 Days)");

  var campaignSheet = spreadsheet.getActiveSheet().setName("Campaigns");

  var adGroupSheet = spreadsheet.insertSheet("Ad Groups");

  var adSheet = spreadsheet.insertSheet("Ads");

  var keywordSheet = spreadsheet.insertSheet("Keywords");

  var extensionSheet = spreadsheet.insertSheet("Extensions");



  var thirtyDaysAgo = getDateString(30);



  var relevantCampaignIds = fetchCampaigns(campaignSheet, thirtyDaysAgo);

  fetchAdGroups(adGroupSheet, relevantCampaignIds);

  fetchAds(adSheet, relevantCampaignIds);

  fetchKeywords(keywordSheet, relevantCampaignIds);

  fetchExtensions(extensionSheet, thirtyDaysAgo);



  Logger.log("Report generated: " + spreadsheet.getUrl());

}



function getDateString(daysAgo) {

  var date = new Date();

  date.setDate(date.getDate() - daysAgo);

  return Utilities.formatDate(date, "GMT", "yyyyMMdd");

}



function fetchCampaigns(sheet, startDate) {

  sheet.appendRow([

    "Campaign ID", "Campaign Name", "Status", "Type", "Daily Budget", 

    "Bidding Strategy", "Start Date", "End Date", "Targeted Locations",

    "Excluded Locations", "Languages"

  ]);



  var relevantCampaignIds = [];

  var campaignIterator = AdsApp.campaigns()

    .withCondition("StartDate >= '" + startDate + "'")

    .get();

  while (campaignIterator.hasNext()) {

    var campaign = campaignIterator.next();

    

    var targetedLocations = getLocationNames(campaign.targeting().targetedLocations());

    var excludedLocations = getLocationNames(campaign.targeting().excludedLocations());

    var languages = getLanguageNames(campaign.targeting().languages());

    

    sheet.appendRow([

      campaign.getId(),

      campaign.getName(),

      campaign.isEnabled() ? "ENABLED" : "PAUSED",

      campaign.getAdvertisingChannelType(),

      campaign.getBudget().getAmount().toFixed(2),

      campaign.getBiddingStrategyType(),

      campaign.getStartDate(),

      campaign.getEndDate() || "No end date",

      targetedLocations,

      excludedLocations,

      languages

    ]);

    

    relevantCampaignIds.push(campaign.getId());

  }

  

  return relevantCampaignIds;

}



function getLocationNames(locationSelector) {

  // ... (no changes needed here)

}



function getLanguageNames(languageSelector) {

  // ... (no changes needed here)

}



function fetchAdGroups(sheet, relevantCampaignIds) {

  sheet.appendRow([

    "Campaign ID", "Campaign Name", "Ad Group ID", "Ad Group Name", 

    "Status"

  ]);



  var adGroupIterator = AdsApp.adGroups()

    .withCondition("CampaignId IN [" + relevantCampaignIds.join(",") + "]")

    .get();

  while (adGroupIterator.hasNext()) {

    var adGroup = adGroupIterator.next();

    var campaign = adGroup.getCampaign();

    sheet.appendRow([

      campaign.getId(),

      campaign.getName(),

      adGroup.getId(),

      adGroup.getName(),

      adGroup.isEnabled() ? "ENABLED" : "PAUSED"

    ]);

  }

}



function fetchAds(sheet, relevantCampaignIds) {

  var headers = [

    "Campaign ID", "Campaign Name", "Ad Group ID", "Ad Group Name", 

    "Ad ID", "Ad Type"

  ];

  

  for (var i = 1; i <= 15; i++) {

    headers.push("Headline " + i);

  }

  

  for (var i = 1; i <= 4; i++) {

    headers.push("Description " + i);

  }

  

  headers.push("Final URL");

  

  sheet.appendRow(headers);



  var adIterator = AdsApp.ads()

    .withCondition("CampaignId IN [" + relevantCampaignIds.join(",") + "]")

    .get();

  while (adIterator.hasNext()) {

    var ad = adIterator.next();

    var adGroup = ad.getAdGroup();

    var campaign = adGroup.getCampaign();

    

    var row = [

      campaign.getId(),

      campaign.getName(),

      adGroup.getId(),

      adGroup.getName(),

      ad.getId(),

      ad.getType()

    ];



    var headlines = [];

    var descriptions = [];

    try {

      headlines = ad.getHeadlines ? ad.getHeadlines() : [];

      descriptions = ad.getDescriptions ? ad.getDescriptions() : [];

    } catch (e) {

      // If getHeadlines or getDescriptions are not available, we'll have empty arrays

    }



    for (var i = 0; i < 15; i++) {

      row.push(headlines[i] || "N/A");

    }



    for (var i = 0; i < 4; i++) {

      row.push(descriptions[i] || "N/A");

    }



    var finalUrl = "N/A";

    try {

      finalUrl = ad.urls().getFinalUrl() || "N/A";

    } catch (e) {

      // If getFinalUrl is not available, we'll use "N/A"

    }

    row.push(finalUrl);



    sheet.appendRow(row);

  }

}



function fetchKeywords(sheet, relevantCampaignIds) {

  sheet.appendRow([

    "Campaign ID", "Campaign Name", "Ad Group ID", "Ad Group Name", 

    "Keyword ID", "Keyword Text", "Match Type", "Status"

  ]);



  var keywordIterator = AdsApp.keywords()

    .withCondition("CampaignId IN [" + relevantCampaignIds.join(",") + "]")

    .get();

  while (keywordIterator.hasNext()) {

    var keyword = keywordIterator.next();

    var adGroup = keyword.getAdGroup();

    var campaign = adGroup.getCampaign();

    

    sheet.appendRow([

      campaign.getId(),

      campaign.getName(),

      adGroup.getId(),

      adGroup.getName(),

      keyword.getId(),

      keyword.getText(),

      keyword.getMatchType(),

      keyword.isEnabled() ? "ENABLED" : "PAUSED"

    ]);

  }

}



function fetchExtensions(sheet, startDate) {

  // ... (no changes needed here)

}



function fetchSitelinkExtensions(sheet, startDate) {

  // ... (no changes needed here)

}



function fetchCalloutExtensions(sheet, startDate) {

  // ... (no changes needed here)

}

Script Output

Campaigns Sheet

Campaign ID Campaign Name Status Type Daily Budget Bidding Strategy Start Date End Date Targeted Locations Excluded Locations Languages
1234567890 Summer Promotion ENABLED SEARCH 50 MAXIMIZE_CONVERSIONS 20250420 No end date United States, Canada Mexico English, French
2345678901 Brand Campaign ENABLED SEARCH 35 MANUAL_CPC 20250422 20250630 United Kingdom, Germany Russia English, German
3456789012 Display Remarketing PAUSED DISPLAY 25 TARGET_ROAS 20250425 No end date Global China All

Ad Groups Sheet

Campaign ID Campaign Name Ad Group ID Ad Group Name Status
1234567890 Summer Promotion 11223344 Women’s Clothing ENABLED
1234567890 Summer Promotion 22334455 Men’s Apparel ENABLED
2345678901 Brand Campaign 33445566 Brand Terms ENABLED

Ads Sheet

Campaign ID Campaign Name Ad Group ID Ad Group Name Ad ID Ad Type Headline 1 Headline 2 Description 1 Final URL
1234567890 Summer Promotion 11223344 Women’s Clothing 123456 RESPONSIVE_SEARCH_AD Summer Sale 50% Off Shop our latest collection https://example.com/womens
2345678901 Brand Campaign 33445566 Brand Terms 234567 RESPONSIVE_SEARCH_AD Official Site Best Products Shop authentic products https://example.com

Keywords Sheet

Campaign ID Campaign Name Ad Group ID Ad Group Name Keyword ID Keyword Text Match Type Status
1234567890 Summer Promotion 11223344 Women’s Clothing 12345 summer dresses BROAD ENABLED
1234567890 Summer Promotion 11223344 Women’s Clothing 23456 summer fashion sale PHRASE ENABLED

Key Components Breakdown

  1. Main Function:
    • Creates a new Google Sheets spreadsheet with five sheets (tabs)
    • Calculates the date from 30 days ago
    • Calls specialized functions to populate each sheet
    • Logs the URL of the completed spreadsheet
  2. Helper Functions:
    • getDateString(): Formats dates in the required Google Ads API format (YYYYMMDD)
  3. Data Extraction Functions: a. fetchCampaigns():
    • Adds header row with campaign attributes
    • Retrieves campaigns created in the last 30 days
    • For each campaign, collects detailed information including:
      • Campaign settings (ID, name, status, type)
      • Budget and bidding strategy
      • Date ranges
      • Geographic targeting (locations included/excluded)
      • Language targeting
    • Returns a list of campaign IDs for use in other functions
    b. fetchAdGroups():
    • Lists all ad groups within the retrieved campaigns
    • Records campaign associations and status information
    c. fetchAds():
    • Handles different ad formats with a flexible structure
    • Captures up to 15 headlines and 4 descriptions to accommodate Responsive Search Ads
    • Includes error handling for different ad types that may not have all properties
    • Records final URLs
    d. fetchKeywords():
    • Lists all keywords within the relevant campaigns
    • Includes match type and status information
    • Maintains relationships to campaigns and ad groups
    e. fetchExtensions() (not fully shown):
    • Would extract ad extensions like sitelinks, callouts, etc.

Why This Is Useful?

This script provides several key benefits:

  1. Account Documentation: Creates a complete record of recent campaign structures, which is valuable for account management and client reporting
  2. Audit Preparation: Having this data organized makes it easier to audit account structures and identify optimization opportunities
  3. Cross-Account Analysis: The standardized format allows for easy comparison across multiple accounts or time periods
  4. Data Backup: Provides an external backup of your campaign structures outside of Google Ads
  5. Troubleshooting: Makes it easier to diagnose issues by having all related components in one document with clear relationships

The script includes robust error handling to ensure that variations in ad types or missing data don’t cause the script to fail, and it maintains the hierarchical relationships between campaigns, ad groups, ads, and keywords throughout the report.

Steps to Implement Google Ads Script

To implement Google Ads scripts effectively, I’ll walk you through the process step by step:

  1. Access Google Ads Scripts
    • Log into your Google Ads account
    • Click on “Tools & Settings” in the top navigation
    • Select “Scripts” under the “Bulk Actions” section
  2. Create a New Script
    • Click the blue “+” button
    • Give your script a descriptive name that reflects its purpose
  3. Write or Paste Your Script
    • Use the built-in editor to write JavaScript code
    • Google Ads scripts use a specialized JavaScript API specific to Google Ads
  4. Test Your Script
    • Click “Preview” to test your script without making changes
    • Review logs and check for errors in the execution panel
  5. Authorize API Access
    • Grant appropriate permissions when prompted
    • This allows the script to interact with your account data
  6. Schedule Your Script (Optional)
    • Set frequency (hourly, daily, weekly, monthly)
    • Choose specific times for execution
  7. Save and Run Your Script
    • Click “Save” to store your script
    • Click “Run” to execute it immediately if needed

Related Posts

Automate Google Search Ads with Google Scripts
Google Ads Script to Send Budget Alert Emails Automatically

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top