Customizable Report

The Customizable Report solution is a framework that automatically generates a spreadsheet from a report and emails it to a list of recipients. The default report fetches some Performance Max campaigns, but you can customize it for any valid GAQL query.

How it works

The script runs the report and outputs the results to a spreadsheet, then emails a link to the spreadsheet to the configured recipients. It offers various customization options around which spreadsheet to use, and which sheet within that spreadsheet.

Configuration

The CONFIG for this script offers various options.

scriptName

Name of the script to be used in the email subject.

emailAddresses

An array of strings of all the email recipients.

spreadsheet

URL of the spreadsheet that results are written to. If unspecified or commented out (default), a new spreadsheet is created. If specified, then the first sheet of the spreadsheet will be overwritten with new results. If you want to maintain a historical record, leave this option commented out.

queryFields, queryResource, and queryConditions

These fields let you customize a report query by specifying various components of the query. You can create these programmatically in the CONFIG. For example, it might be useful to specify a query condition that limits the results to the previous day, and then run the script each day.

query

This lets you specify a completely custom GAQL query. If specified, then the queryFields, queryResource, and queryConditions fields will be ignored, and this query will be used instead.

Setup

• Create a new Google Ads script with the source code below.
• Set up the CONFIG section for your use case. See the previous section.
  • Make sure you update the emailAddresses, even if you're leaving other values at their defaults.
• Schedule the script to run at a cadence that makes sense for your use case.

Configuration example

The default CONFIG is set up to run a Performance Max report. You can customize this to run any other type of report by updating the fields within the CONFIG. For example, if you'd just like to get a list of campaigns with some basic metrics from yesterday:

const CONFIG = {
    ... email and spreadsheet config ...

    queryFields: [
      'campaign.name',
      'metrics.cost',
      'metrics.impressions'
    ],
    queryResource: 'campaign',
    queryConditions: 'segments.date DURING YESTERDAY'
}

Check out the Google Ads API reporting documentation for a complete list of available reports.

Source code

// Copyright 2023, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name Customizeable Performance Max Report
 *
 * @overview The Customizeable Performance Max Report script generates an overview
 *     showing the performance of your account's Performance Max campaigns by default.
 *     It is easily customizeable to perform any kind of report you desire, however. See
 *     [INSERT URL HERE] for complete customization instructions.
 *
 * @author Google Ads Scripts Team [[e-mail]]
 *
 * @version 1.0
 *
 * @changelog
 * - version 1.0
 *   - Released initial version.
 */

const CONFIG = {
  // The name your script will be referred to as in the email. Specify a custom name so you
  // can set up inbox filters or easily disambiguate multiple instances of this script.
  scriptName: "Customizable Report Script",

  // Comma separated list of email address to send the summary to.
  // The email will include a link to the spreadsheet with full information.
  // If commented out, no email will be sent.
  emailAddresses: [
    "EXAMPLE_ADDRESS_1", "EXAMPLE_ADDRESS_2"
  ],

  // The spreadsheet to load to store the results of the search query. If
  // commented out, a new spreadsheet will be created and the new spreedsheet
  // URL will be logged.
  //
  // The script will always overwrite the first sheet in the spreadsheet. If
  // you specify an existing spreadsheet, the old data will be deleted. If you
  // want a historical record, make sure to leave this commented out.
  // spreadsheet: "INSERT_SPREADSHEET_URL_HERE",

  // The fields that will be in the SELECT clause of the report query.
  queryFields: [
    'campaign.id',
    'campaign.url_expansion_opt_out',
    'campaign.status',
    'campaign.bidding_strategy_type',
    'metrics.clicks',
    'metrics.cost_micros',
    'metrics.impressions',
    'metrics.conversions',
    'metrics.all_conversions'
  ],

  // The resource that will be in the FROM clause of the report query.
  queryResource: 'campaign',

  // The conditions that will be in the WHERE clause of the report query.
  queryConditions: [
    "campaign.advertising_channel_type = 'PERFORMANCE_MAX'",
    "metrics.clicks > 0"
  ],

  // This will automatically add a condition with the given date range along
  // with any other conditions specified above.
  // See valid values at: https://developers.google.com/google-ads/api/docs/query/date-ranges#date-range
  queryDateRange: "YESTERDAY"

  // You can specify your full custom query instead of constructing a query from components
  // above. If you specify a query here, then it will override any query_* fields set above.
  //query = "INSERT_CUSTOM_QUERY_HERE"
};

/**
 * The entrypoint of the script. Fetches the spreadsheet, runs the report, and
 * emails the results.
 */
function main() {
  // Fetch the spreadsheet info first, before running the report, so that we can save time
  // if the spreadsheet is misconfigured.
  const spreadsheetInfo = ensureSpreadsheet();

  const reportInfo = runReport();
  copyReportResultsToSheet(reportInfo, spreadsheetInfo.sheet);

  sendEmail(CONFIG.emailAddresses, spreadsheetInfo.url);
}

/**
 * Return a valid spreadsheet according to the CONFIG, or else throw an error.
 *
 * @return {!Object} spreadsheet An object containing the sheet within the
 *     spreadsheet where results should be stored, as well as the URL of the
 *     spreadsheet.
 */
function ensureSpreadsheet() {
  let spreadsheet;

  if (CONFIG.spreadsheet) {
    if (CONFIG.spreadsheet == "ENTER_SPREADSHEET_URL_HERE") {
      throw new Error("Please customize the spreadsheet URL with the URL of an existing spreadsheet, " +
        "or comment it out to create a new spreadsheet.");
    }
    console.log(`Opening spreadsheet at URL ${CONFIG.spreadsheet}`);
    spreadsheet = SpreadsheetApp.openByUrl(CONFIG.spreadsheet);
  } else {
    const now = new Date();
    const timeZone = AdsApp.currentAccount().getTimeZone();
    spreadsheet = SpreadsheetApp.create(
      "Spreadsheet for Customizable Report Script - " +
      Utilities.formatDate(now, timeZone, "yyyy-MM-dd'T'HH:mm:ss'Z'")
    );
    console.log(`Created new spreadsheet at URL ${spreadsheet.getUrl()}`);
  }

  const sheet = spreadsheet.getSheets()[0];

  return {
    sheet: sheet,
    url: spreadsheet.getUrl()
  };
}

/**
 * Construct the query and run the report.
 *
 * @return {!Object} report The report results.
 */
function runReport() {
  const query = constructQuery();
  return AdsApp.report(query);
}

/**
 * Construct the query according to the CONFIG.
 *
 * @return {string} query The constructed query.
 */
function constructQuery() {
  if (CONFIG.query) return CONFIG.query;

  if (isEmpty(CONFIG.queryFields) || isEmpty(CONFIG.queryResource)) {
    throw new Error("Must specify either query or queryFields and queryResource in the CONFIG.");
  }

  let query = `SELECT
    ${CONFIG.queryFields.join(", ")}
    FROM ${CONFIG.queryResource}`;

  let conditions = CONFIG.queryConditions.join(" AND ");
  if (CONFIG.queryDateRange) {
    const dateRangeConditions = `segments.date DURING ${CONFIG.queryDateRange}`;
    if (conditions) {
      conditions += ` AND ${dateRangeConditions}`;
    } else {
      conditions = dateRangeConditions;
    }
  }
  if (conditions) {
    query += ` WHERE ${conditions}`;
  }

  return query;
}

/**
 * Checks whether the given JavaScript object is empty.
 *
 * @param {Object?} object The object to check.
 *
 * @return {boolean} isEmpty True if the object is empty, false otherwise.
 */
function isEmpty(object) {
  return object === null || object === undefined || object === '';
}

/**
 * Copy the results of a report to the given spreadsheet.
 *
 * @param {!Object} reportInfo The report results.
 * @param {!Object} sheet The sheet the report results should be copied to.
 */
function copyReportResultsToSheet(reportInfo, sheet) {
  sheet.clear();
  reportInfo.exportToSheet(sheet);
}

/**
 * Sends the link to the given spreadsheet URL to the given email addresses.
 *
 * @param {!Array.<string>} emailAddresses The email addresses to send the email to.
 * @param {string} spreadsheetUrl The URL of the spreadsheet.
 */
function sendEmail(emailAddresses, spreadsheetUrl) {
  if (emailAddresses.length == 0 || emailAddresses.includes("EXAMPLE_ADDRESS_1")) {
    console.log("Email is not configured. Not sending email.");
    console.log(`Check results at ${spreadsheetUrl}`);
    return;
  }

  const recipients = emailAddresses.join(',');
  const subject = `Google Ads scripts report result for ${CONFIG.scriptName}`;
  const body = `Execution has completed for ${CONFIG.scriptName}. See the results at ${spreadsheetUrl}`;
  console.log(`Sending summary email to ${recipients}`);
  MailApp.sendEmail(recipients, subject, body);
}