Prompt 1

Help me write a Google Ads Script, that can export search query data from all active campaigns from the last 30 days into a given (empty) Google Sheet.

Result 1

Below is a sample Google Ads Script that retrieves search query performance data (campaign name, ad group name, search query, impressions, clicks, and cost) for all active campaigns over the last 30 days and exports it to a specified Google Sheet.

Before running the script:

• Replace YOUR_SPREADSHEET_URL with the URL (or use openById with your spreadsheet ID) of your (empty) Google Sheet.
• If needed, adjust the header columns or query fields.

function main() {
  // Replace with the URL of your empty Google Sheet.
  var SPREADSHEET_URL = '<https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID_HERE/edit>';

  // Open the spreadsheet and select the first sheet (adjust if you need a different sheet)
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheets()[0];

  // Clear any existing content in the sheet
  sheet.clear();

  // Set up the header row
  var headers = ['Campaign Name', 'Ad Group Name', 'Search Query', 'Impressions', 'Clicks', 'Cost'];
  sheet.appendRow(headers);

  // Build the query to fetch search query data from active campaigns over the last 30 days
  var reportQuery =
    "SELECT CampaignName, AdGroupName, Query, Impressions, Clicks, Cost " +
    "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
    "WHERE CampaignStatus = 'ENABLED' " +
    "DURING LAST_30_DAYS";

  // Run the report
  var report = AdsApp.report(reportQuery);
  var rows = report.rows();

  // Loop through each row in the report and write the data to the sheet
  while (rows.hasNext()) {
    var row = rows.next();
    var data = [
      row['CampaignName'],
      row['AdGroupName'],
      row['Query'],
      row['Impressions'],
      row['Clicks'],
      row['Cost']
    ];
    sheet.appendRow(data);
  }
}

How It Works

• Opening the Spreadsheet: The script opens the given Google Sheet using its URL and selects the first sheet.
• Clearing the Sheet & Setting Headers: It clears any existing content and writes a header row to label your data columns.
• Building and Running the Report Query: It creates a query against the SEARCH_QUERY_PERFORMANCE_REPORT to fetch data from all campaigns with status ENABLED over the last 30 days.
• Writing Data to the Sheet: The script iterates over each row returned by the report and appends it to the Google Sheet.

Simply paste this script into your Google Ads Scripts interface, update the spreadsheet URL, and run it to export your search query data.

This should help you get started with automating the data export for your active campaigns.

Prompt 2