Ad groups (date range)

This script fetches performance data from a specific campaign, then writes this data into a spreadsheet on scheduled cadence. It structures the data to show the highest performing ad groups for a specified date period of your choice.

To set up this script, follow these steps:

1. Open your Google Ads account.

2. Go to the "Tools & Settings" menu and select "Scripts".

3. Click the "Create" or "+" button to create a new script.

4. Copy and paste the following script.

function main() {
  try {
    var sheet = SpreadsheetApp.openByUrl('INSERT-SPREADSHEET-URL').getSheetByName('INSERT-SHEET-NAME'); // Replace spreadsheet URl and sheet name
    var campaignName = 'INSERT-CAMPAIGN-NAME';  // replace with your campaign name
    var startDate = '20230619';  // Replace with the start date
    var endDate = '20230719';  // Replace with the end date
    var DATE_RANGE = startDate + ',' + endDate;

    // Create human-readable date range for spreadsheet
    var formattedStartDate = formatDate(startDate);
    var formattedEndDate = formatDate(endDate);
    var formattedDateRange = formattedStartDate + ' - ' + formattedEndDate;

    // Set the report attributes
    var REPORT_TYPE = 'ADGROUP_PERFORMANCE_REPORT';
    var COLUMNS = [
      'DateRange',
      'CampaignId',
      'CampaignName',
      'AdGroupId',
      'AdGroupName',
      'Impressions',
      'Clicks',
      'Cost',
      'Conversions',
      'Ctr',
      'AverageCpc',
      'ConversionRate'

    ];

    // Pull the report
    var report = AdsApp.report(
      'SELECT ' + COLUMNS.slice(1).join(', ') +  // Exclude 'DateRange' from the query
      ' FROM ' + REPORT_TYPE +
      ' WHERE CampaignName = "' + campaignName + '"' +
      ' DURING ' + DATE_RANGE
    );

    // Append the headers if the report has rows
    if (report.rows().hasNext()) {
      sheet.appendRow(COLUMNS);

      // Process report rows and append to the sheet
      var rows = report.rows();
      while (rows.hasNext()) {
        var row = rows.next();
        var impressions = row['Impressions'];
        if (impressions > 0) {
          var rowArray = [formattedDateRange];  // Start with the date range
          for (var i = 1; i < COLUMNS.length; i++) {  // Start at 1 to exclude 'DateRange'
            var column = COLUMNS[i];
            rowArray.push(row[column]);
          }
          sheet.appendRow(rowArray);
        }
      }

      Logger.log('Data appended to the active sheet.');
    } else {
      Logger.log('No data available for the specified date range.');
    }
  } catch (error) {
    Logger.log('Error: ' + error.toString());
  }
}

function formatDate(dateString) {
  var year = dateString.substring(0, 4);
  var month = dateString.substring(4, 6);
  var day = dateString.substring(6, 8);
  return year + '-' + month + '-' + day;
}

1. Replace the spreadsheet URL, sheet name, dates, and campaign name.

2. Save the script.

3. Review and authorize the script to access your Google Ads and Google Sheets accounts.

4. Run the script manually for the first time to populate the initial values in the spreadsheet.

5. (Optional) Schedule the script to run on a specific cadence (ie: daily, weekly, monthly, etc.)

Note: Ensure that you have the necessary permissions to access and modify the target Google Spreadsheet.