Google Ads Reporting Automation: Scripts, Templates & Sheets

Reporting is one of the most tedious parts of performance marketing, and also one of the most necessary. Pulling campaign data, collating it, formatting it for whoever needs to see it. That's not optional. It's the job. It's just not a fun part of the job.

The pain compounds with scale. One account, the built-in Google Ads reports mostly cover it. A handful of accounts, each on its own cadence, and you're clicking through the same UI columns and date pickers over and over, exporting the same shape of data by hand. If you've got interns or juniors on the team, this is exactly the kind of task you don't want them burning hours on. That time is better spent on optimization, not manually pulling reports.

This is where Google Ads Scripts earn their keep. Below is a script I use to pull core campaign metrics straight into a Google Sheet, no manual export. It's intentionally simple. If you've never touched Google Ads Scripts before, this is a reasonable first one.

Google Ads has reports. It just doesn't have yours.

Google Ads gives you built-in reports, but they rarely match how you actually report. Maybe you're on a weekly cadence, maybe monthly, maybe you're calculating CPL or ROI, numbers the Ads UI just doesn't surface. And pulling any of it by hand, on a recurring basis, eats time you don't get back.

The script below pulls nine fields for each campaign: name, status, campaign type, budget, cost, impressions, clicks, CTR, and average CPC. CTR and average CPC aren't raw fields Google Ads returns. The script calculates both from clicks, impressions, and cost. Everything else comes straight off the API.

What replaces the manual pull

Here's the script. It runs once a day, pulls the fields above for every enabled or paused campaign, and writes them to the sheet you point it at.

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

Making it run on its own

You can set this to run automatically at a fixed time every day, so the data is already in the sheet by the time you open it.

• Go to the Google Ads Scripts Editor where you pasted the script.
• Click the clock icon (Triggers).
• Create a new trigger: choose the function to run (main), set frequency to Daily, and pick a time.
• Save the trigger.

Once it's saved, the script runs on its own every day. No manual pulling, no reminder to do it.

What actually breaks quietly

Two things about this script are worth knowing before you rely on it.

LAST_30_DAYS is a rolling window, not a calendar month. If you're reporting month over month, that mismatch will throw your numbers off unless you account for it.

The error handling only logs to Logger.log. If the trigger fails, nothing tells you. You'll find out when you open the sheet and the data's stale, not when it actually breaks. Worth pairing with an email alert if you're relying on this daily.

If I were telling myself where to take this next: pull Leads and calculate Cost Per Lead by extending the query, and if you want to get precise about it, wire the trigger to email you when it runs, or when it fails. But start here. This version is intentionally basic, meant as a first Google Ads script, not the last one you'll write.