Automating Campaign Reporting with Google Ads Scripts: A Quick How-To

If you’ve ever manually pulled Google Ads campaign reports into Google Sheets, you know how much of a time sink it can be. Not only is it tedious, but it’s also the kind of task that begs to be automated. And let’s face it—automating tasks like this isn’t just smart, it’s what separates a good digital marketer from a great one.

In this post, I’m going to walk you through how to create a Google Ads script that will automatically pull key campaign metrics and drop them into a Google Sheet. We’ll skip the fluff and focus on the real meat: setting up your script to pull data like campaign name, status, campaign type, budget, impressions, clicks, cost, CTR, and average CPC. Sound good? Let’s dive in.

Why Bother with Google Ads Scripts?

Google Ads provides built-in reports, but let’s be real—they don’t always give you exactly what you need in the way you need it. Maybe you’re reporting weekly, maybe monthly, or maybe you're running some custom calculations (like CPL or ROI) that the Ads UI doesn’t cover. Plus, pulling reports manually? That’s a total productivity killer.

If you’re like me, you want to spend time optimizing ads and scaling campaigns, not crunching numbers manually. Google Ads Scripts can handle the repetitive part for you—by automating this task, you can just sit back and watch as your data flows into Google Sheets in real-time, ready for analysis.

What We’re Pulling Into Google Sheets

We’re going to pull in some basic metrics for now, but the script I’ll show you is flexible—you can modify it to include additional data fields like conversion metrics later. Here’s what we’re going to grab:

• Campaign Name: Because we need to know what we’re looking at.
• Status: You’ll see whether the campaign is ENABLED, PAUSED, or something else.
• Campaign Type: Search, Display, Shopping—you’ll know at a glance.
• Budget: What are we burning through daily?
• Cost: Actual spend, so we know what we’re really dishing out.
• Impressions: Eyes on your ads—how often they’re shown.
• Clicks: Self-explanatory, but necessary.
• CTR (Click-Through Rate): Calculated as (Clicks / Impressions) * 100, a key performance indicator for ad engagement.
• Avg. CPC (Cost Per Click): How much each click is costing you on average.

Skipping the manual labor and pulling this data into a Google Sheet automatically? Now we’re talking.

The Script: Automating Your Google Ads Campaign Data

Without further ado, here’s the magic script that does all the heavy lifting for you. It’s like having a personal assistant who just quietly drops a fresh report into Google Sheets every day (minus the need for coffee breaks).

javascriptCopy code<code>function main() {
  try {
    // Replace with your Google Sheet URL
    var SPREADSHEET_URL = 'YOUR_GOOGLE_SHEET_URL'; // Use your Google Sheets URL
    var SHEET_NAME = 'YOUR_SHEET_NAME'; // Name of the sheet where data will be written

    // Open the spreadsheet and the specific sheet
    var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
    var sheet = spreadsheet.getSheetByName(SHEET_NAME);

    // If the sheet does not exist, create it
    if (!sheet) {
      sheet = spreadsheet.insertSheet(SHEET_NAME);
    } else {
      sheet.clear(); // Clear existing data
    }

    // Write headers to the sheet
    sheet.appendRow(["Campaign", "Status", "Campaign type", "Budget", "Cost", "Impr.", "Clicks", "CTR", "Avg. CPC"]);

    // Construct the query to get the campaign performance data
    var query = 
      "SELECT CampaignName, CampaignStatus, AdvertisingChannelType, Amount, Cost, Impressions, Clicks " +
      "FROM CAMPAIGN_PERFORMANCE_REPORT " +
      "WHERE CampaignStatus IN ['ENABLED', 'PAUSED'] " + // Adjust status filters if necessary
      "DURING LAST_30_DAYS";

    Logger.log("Query: " + query);

    // Fetch the report
    var report = AdsApp.report(query);
    var rows = report.rows();

    // Write each row of data to the sheet
    while (rows.hasNext()) {
      var row = rows.next();
      
      // Calculate CTR and Avg. CPC
      var clicks = parseInt(row['Clicks']);
      var impressions = parseInt(row['Impressions']);
      var cost = parseFloat(row['Cost']);
      var ctr = impressions > 0 ? (clicks / impressions) * 100 : 0;
      var avgCpc = clicks > 0 ? cost / clicks : 0;
      
      sheet.appendRow([
        row['CampaignName'],
        row['CampaignStatus'],
        row['AdvertisingChannelType'],
        row['Amount'], // Budget column
        row['Cost'],
        row['Impressions'],
        row['Clicks'],
        ctr.toFixed(2) + "%",
        avgCpc.toFixed(2)
      ]);
    }

    Logger.log("Data successfully written to the sheet.");

  } catch (e) {
    Logger.log('Error: ' + e.message);
  }
}

Automate It: Set Up a Trigger for Daily Data

Now, the real beauty of this script is that you can set it to run automatically every day at a fixed time. This means when you roll into the office in the morning, your fresh data is sitting there in the Google Sheet, waiting for you—like a boss.

Here’s how to set up the trigger:

1. Go to the Google Ads Scripts Editor where you’ve pasted your script.
2. Click on the “Clock” icon at the top of the editor (it’s the "Triggers" button).
3. Create a New Trigger:
   • Choose the function to run: main.
   • Set the frequency to "Daily".
   • Select the time of day you want it to run. If you want your data to be ready first thing in the morning, set it to run at, say, 4 AM.
4. Save the Trigger.

Once this trigger is set, the script will run automatically at the specified time every day, so you’ll always have the latest data without having to lift a finger. It’s like having a virtual assistant who never sleeps.

Breaking It Down

• Placeholders: I’ve replaced the hardcoded Google Sheets URL (SPREADSHEET_URL) and the sheet name (SHEET_NAME) with placeholders. When you implement this script, just replace 'YOUR_GOOGLE_SHEET_URL' and 'YOUR_SHEET_NAME' with the actual values.
• Connect to Google Sheets: The script connects to the Google Sheet you’ve specified using its URL. Make sure you replace the placeholder URL with your actual Sheet URL.
• Clear the Sheet: We start fresh every time by clearing the old data. I don’t like clutter, so out with the old, in with the new.
• Pull Campaign Metrics: The script queries for campaign performance data for the last 30 days, only pulling in campaigns that are enabled or paused. You can modify that if you’re feeling ambitious and want more control.
• Calculate CTR and Avg. CPC: These metrics are calculated in real-time, because who wants to deal with math manually?
• Drop It Into Google Sheets: Once the data’s pulled, calculated, and formatted, the script writes everything into your sheet neatly, just waiting for you to analyze.
• Set a Trigger: By setting the script to run automatically every day, you’ll always have up-to-date data waiting for you in the morning. No more manual report pulling—just click open the sheet and start analyzing.

What’s Next?

Want to get more out of this script? You can easily extend it to pull additional metrics like Leads or calculate your Cost Per Lead (CPL) by tweaking the query. Heck, you could even automate it to send you daily reports if you want to get really fancy.

At the end of the day, this script is all about making your life easier. Automate what can be automated, so you can focus on strategy, optimization, and growth. Because let’s be honest—there’s a lot more value in that than spending hours pulling reports.

If you’ve got questions or want to tweak this script for your needs, hit me up in the comments or shoot me a message. Happy automating!