Ad groups (last 30 days)

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 over the last 30 days.

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 with your spreadsheet URL and sheet name
    var campaignName = 'INSERT-CAMPAIGN-NAME';  // replace with your campaign name
    var endDate = new Date();
    var startDate = new Date(endDate.getTime() - 30 * 24 * 60 * 60 * 1000);
    var DATE_RANGE = Utilities.formatDate(startDate, 'GMT', 'yyyyMMdd') + ',' + Utilities.formatDate(endDate, 'GMT', 'yyyyMMdd');

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

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

    // Check if the first row is already present in the sheet (header row)
    var existingData = sheet.getDataRange().getValues();
    var headerRow = existingData[0];
    var headerRowExists = headerRow.join(',') == COLUMNS.join(',');

    // Append the header row if it doesn't exist
    if (!headerRowExists) {
      sheet.appendRow(COLUMNS);
    }

    // Process report rows and append to the sheet (batch appending)
    var rows = report.rows();
    var dataToAppend = [];
    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]);
        }
        dataToAppend.push(rowArray);
      }
    }
    
    // Append the data in one batch
    if (dataToAppend.length > 0) {
      sheet.getRange(sheet.getLastRow() + 1, 1, dataToAppend.length, dataToAppend[0].length).setValues(dataToAppend);
      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(date) {
  return Utilities.formatDate(date, 'GMT', 'yyyy-MM-dd');
}

1. Replace the spreadsheet URL, sheet name, 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.