Manager Account Summary Report

Reports icon

This script extends Account Summary Report to produce a summary of performance stats aggregated at the manager account level, and sends the report out as an HTML-formatted daily email.

Account summary spreadsheet screenshot

How it works

The script fetches yesterday's statistics from all accounts within the manager account and updates a spreadsheet. Note the following:

  • Statistics are retrieved for 50 accounts per execution, (because of script limits), until through repeated scheduled execution, statistics for all accounts are retrieved.

    Once statistics for yesterday have been retrieved for all accounts, the statistics are aggregated and written to the spreadsheet.

  • Accounts within a manager account can have different account timezone settings. This affects when statistics become available for aggregation: An account with a negative timezone offset won't have statistics ready for aggregation as early as an account with a positive timezone offset.

The graphs are preconfigured to display the statistics. If the email address is specified, the script sends out an HTML-formatted email.

Setup

  • Click the button below to create the spreadsheet-based script in your Google Ads account.

    Install the script template

  • Click the button below to make a copy of the template spreadsheet.

    Copy the template spreadsheet

  • Update spreadsheet_url in your script.

  • Schedule the script to run Hourly.

Extending the report (optional)

The report can be extended by adding further metrics from those available in the customer resource:

This is slightly more complex than adding further metrics to the single-account version of this report since you need to consider how aggregation is performed, for example:

  • Clicks is a simple metric to calculate across accounts as the totals from each account need only be added together.
  • However, CTR cannot be calculated by averaging the CTR values for each account. Instead, the total clicks for all accounts must be divided by the total impressions for all accounts.

This second example of CTR demonstrates that for a given metric, it might be necessary to obtain different query metrics (in this case, clicks and impressions) in order to calculate a final metric (CTR) for the report.

The following example illustrates adding a column for conversion rate to the report:

  1. Conversion rate is defined as total_conversions / total_clicks. Clicks is already in the list of fields to retrieve, but it is necessary to add conversions: 'query_fields': ['segments.date', 'metrics.cost_micros', 'metrics.impressions', 'metrics.clicks', 'metrics.search_impression_share', 'metrics.all_conversions'];
  2. Add a heading for Conversion rate to the display headings: 'display_fields': ['Cost', 'Avg. CPC', 'CTR', 'Search Impression Share', 'Impressions', 'Clicks', 'Conversion Rate'];
  3. The final step is to implement the aggregation in the processFinalResults function. An extract from the script is shown here, with the three small modifications explained in comments above each change:

    // Step 1: Running totals
    // For each new row, set up variables to store running totals.
    // Modification 1: Add a property to result to accumulate conversions
    const result = {impressions: 0, clicks: 0, cost: 0, searchImpressionShare: 0, conversions: 0};
    for (const row of rows) {
      // Each row of data represents a different account.
      // Cost, for example, requires only summing Cost across all accounts.
      result.cost += parseFloat(row['metrics.cost_micros'])/1000000;
      result.impressions += parseInt(row['metrics.impressions'], 10);
      result.clicks += parseInt(row['metrics.clicks'], 10);
      result.searchImpressionShare +=
          parseFloat(row['metrics.search_impression_share'], 10);
      // Modification 2: Accumulate conversions across all accounts.
      result.conversions += row['metrics.all_conversions'];
    }
    
    // Step 2: Final aggregation and presentation
    // Perform the final formatting to create a new row.
    const formattedRow = [
      separateDateString(completedResult.dateString),
      // Cost is an example where if different sub-accounts have different
      // currencies, adding them together is not meaningful. The below adds
      // "N/A" for "Not Applicable" in this case.
      isSingleCurrency ? result.cost.toFixed(2) : 'N/A',
      isSingleCurrency ? (result.cost / result.clicks).toFixed(2) : 'N/A',
      // CTR is calculated from dividing total clicks by total impressions,
      // not by summing CTRs from individual accounts.
      (result.clicks * 100 / result.impressions).toFixed(2),
      (result.searchImpressionShare / rows.length).toFixed(2),
      result.impressions, result.clicks,
      // Modification 3: Add the final calculation to the new row. In this case
      // we multiply by 100 to create a percentage:
      (result.conversions * 100 / result.clicks).toFixed(2)
    ];
    
  4. Create a chart for the new field:

    1. Click on one of the existing charts in your spreadsheet, and under the three dots menu in the top right of the chart select Copy Chart; then right-click and Paste.
    2. A new chart will be created on the Sheet. On the menu for the new chart, select Edit Chart.
    3. Change the Series column reference to the column containing the new data; for example, change G to H.
    4. Double-click on the chart title and change it to the new metric name.

Source code

// Copyright 2015, 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 Account Summary Report
 *
 * @overview The Manager Account Summary Report script generates an at-a-glance
 *     report showing the performance of an entire Google Ads Manager Account.
 *     https://developers.google.com/google-ads/scripts/docs/solutions/manager-account-summary
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.1
 *
 * @changelog
 * - version 2.1
 *   - Split into info, config, and code.
 * - version 2.0
 *   - Updated to use new Google Ads scripts features.
 * - version 1.1.1
 *   - Removed 'Avg. Pos.' from the HTML report because it is not calculated
 *     in the script
 * - version 1.1
 *   - Add user-updateable fields, and ensure report row ordering.
 * - version 1.0.1
 *   - Added validation for external spreadsheet setup.
 * - version 1.0
 *   - Released initial version.
 */
/**
 * Configuration to be used for the MCC Account Summary Report.
 */

CONFIG = {
  // URL of the default spreadsheet template. This should be a copy of
  // //docs.google.com/spreadsheets/d/1kACrT3Ne3HY8iWvgufn8AAEr0dq54flWDknpbC6iYfc/copy
  // Make sure the sheet is owned by or shared with same Google user executing the script
  'spreadsheet_url': 'INSERT_SPREADSHEET_URL_HERE',
  'reporting_options': {
    // Comment out the following line to default to the latest reporting
    // version.
    apiVersion: 'v11'
  },

  // The hour of the day (in the account's timezone) at or after which to
  // trigger the process of collating the Manager Account Report for yesterday's 
  // data. Set at least 3 hours into the day to ensure that data for yesterday 
  // is complete.
  'trigger_new_day_report_hour': 5,

  // The metrics to be pulled back from the customer resource.
  'query_fields': [
    'segments.date', 'metrics.cost_micros', 'metrics.impressions',
    'metrics.clicks', 'metrics.search_impression_share'
  ],

  /**
   * The metrics to be presented in the spreadsheet report. To add additional
   * fields to the report, follow the instructions at
   * https://developers.google.com/google-ads/scripts-legacy/docs/solutions/adsmanagerapp-account-summary#extending-the-report.
   */
  'display_fields': [
    'Cost', 'Avg. CPC', 'CTR', 'Search Impr. Share', 'Impressions', 'Clicks'
  ],
};
const SPREADSHEET_URL = CONFIG.spreadsheet_url;

const REPORTING_OPTIONS = CONFIG.reporting_options;
const QUERY_FIELDS = CONFIG.query_fields;
const DISPLAY_FIELDS = CONFIG.display_fields;

const TRIGGER_NEW_DAY_REPORT_HOUR = CONFIG.trigger_new_day_report_hour;
const MILLIS_PER_DAY = 24 * 3600 * 1000;
const MIN_NEW_DAY_REPORT_HOUR = 3;
const MAX_NEW_DAY_REPORT_HOUR = 24;

// The maximum number of accounts within the manager account that can be
// processed in a given day.
const MAX_PARALLEL_ACCOUNTS = 50;
const MAX_ACCOUNTS_IN_MANAGER_ACCOUNT = MAX_PARALLEL_ACCOUNTS * 24;
const MAX_ACCOUNTS_EXCEEDED_ERROR_MSG = 'There are too many accounts within ' +
    'this manager account structure for this script to be used, please ' +
    'consider alternatives for manager account reporting.';

const DEFAULT_EMPTY_EMAIL = 'foo@example.com';

let reportState = null;
let spreadsheetAccess = null;

/**
 * Main entry point for the script.
 */
function main() {
  SpreadsheetAccess.validateParameters();
  spreadsheetAccess = new SpreadsheetAccess(SPREADSHEET_URL, 'Report');
  // Retrieve a list of dates for which to fetch and create new rows.
  const newDates = spreadsheetAccess.getNextDates();
  // Initialise the object used to keep track of and collate report results on
  // Drive.
  reportState = new ReportState();
  reportState.addDatesToQueue(newDates);

  const nextAccounts = reportState.getNextAccounts();
  if (nextAccounts.length) {
    const dateQueue = reportState.getDateQueue();
    if (dateQueue.length) {
      AdsManagerApp.accounts()
          .withIds(nextAccounts)
          .executeInParallel(
              'processAccount', 'processIntermediateResults',
              JSON.stringify(dateQueue));
    }
  } else if (reportState.getCompletedDates().length) {
    processFinalResults();
  }
}

/**
 * @typedef {Object} ReportRow
 * @property {string} Date The date in the format YYYY-MM-DD.
 * @property {number} Cost
 * @property {number} Impressions
 * @property {number} Clicks
 */

/**
 * Runs the Report query via AWQL on each individual account. A list of dates
 * required are passed in from the calling manager account process. Each account
 * determines whether it is ready to request each of those dates: A sub account
 * of a manager accountcan have a different timezone to that of the manager
 * account, and therefore it is necessary to check on each account with the
 * local timezone.
 *
 * @param {string} dateQueueJson JSON string representing a list of dates to
 *     process, in ascending date order.
 * @return {string} Stringified Object.<ReportRow>
 */
function processAccount(dateQueueJson) {
  const dateQueue = JSON.parse(dateQueueJson);
  // It is necessary to represent the dates for yesterday and today in local
  // format.
  const tz = AdsApp.currentAccount().getTimeZone();
  const today = new Date();
  const yesterday = new Date((new Date()).getTime() - MILLIS_PER_DAY);
  const yesterdayString = Utilities.formatDate(yesterday, tz, 'yyyyMMdd');
  const results = {};
  for (const nextDate of dateQueue) {
    // Only retrieve the report if either (a) the date in question is earlier
    // than yesterday, or (b) the date in question is yesterday *and*
    // sufficient hours have passed for yesterday's results to be complete.
    if (nextDate < yesterdayString ||
        (nextDate === yesterdayString &&
         parseInt(Utilities.formatDate(today, tz, 'H')) >=
             TRIGGER_NEW_DAY_REPORT_HOUR)) {
      results[nextDate] = getReportRows(nextDate);
    }
  }
  return JSON.stringify(results);
}

/**
 * Retrieves a row from Account Performance Report for a specified date.
 *
 * @param {string} dateString The date in the form YYYYMMDD.
 * @return {ReportRow}
 */
function getReportRows(dateString) {
  let row = {};
  const fields = QUERY_FIELDS.join(',');
  const report = AdsApp.report(
        `SELECT ${fields} ` +
        `FROM customer ` +
        `WHERE segments.date = ${dateString}`,
      REPORTING_OPTIONS);
  if (report.rows().hasNext()) {
    row = report.rows().next();
  } else {
    QUERY_FIELDS.forEach(function(metric) {
      row[metric] = '0';
    });
    row.Date = separateDateString(dateString);
  }
  return row;
}

/**
 * Callback function called on completion of executing managed accounts. Adds
 * all the returned results to the ReportState object and then stores to Drive.
 *
 * @param {Array.<AdsManagerApp.ExecutionResult>} executionResultsList
 */
function processIntermediateResults(executionResultsList) {
  reportState = new ReportState();
  for (const executionResult of executionResultsList) {
    const customerId = executionResult.getCustomerId();
    const error = executionResult.getError();
    if (error) {
      console.log(
          `Error encountered processing account ${customerId}: ${error}`);
    } else {
      const results = JSON.parse(executionResult.getReturnValue());
      const completedDates = Object.keys(results);
      for (const completedDate of completedDates) {
        reportState.updateAccountResult(
            customerId, completedDate, results[completedDate]);
      }
    }
  }
  // Save changes to object on Drive.
  reportState.flush();
  if (reportState.getCompletedDates().length) {
    processFinalResults();
  }
}

/**
 * Writes any completed records - where statistics have been returned from all
 * managed accounts and aggregated - to the spreadsheet and optionally sends an
 * email alert.
 */
function processFinalResults() {
  spreadsheetAccess = new SpreadsheetAccess(SPREADSHEET_URL, 'Report');
  const completedResults = reportState.getCompletedDates();
  if (completedResults.length) {
    const isSingleCurrency = reportState.isSingleCurrency();
    for (const completedResult of completedResults) {
      const rows = completedResult.reportData;

      // Step 1: Running totals
      // For each new row, set up variables to store running totals.
      const result =
          {impressions: 0, clicks: 0, cost: 0, searchImpressionShare: 0};
      for (const row of rows) {
        // Each row of data represents a different account.
        // Cost, for example, requires only summing Cost across all accounts.
        result.cost += parseFloat(row['metrics.cost_micros'])/1000000;
        result.impressions += parseInt(row['metrics.impressions'], 10);
        result.clicks += parseInt(row['metrics.clicks'], 10);
        result.searchImpressionShare +=
            parseFloat(row['metrics.search_impression_share'], 10);
      }

      // Step 2: Final aggregation and presentation
      // Perform the final formatting to create a new row.
      const formattedRow = [
        separateDateString(completedResult.dateString),
        // Cost is an example where if different sub-accounts have different
        // currencies, adding them together is not meaningful. The below adds
        // "N/A" for "Not Applicable" in this case.
        isSingleCurrency ? result.cost.toFixed(2) : 'N/A',
        isSingleCurrency ? (result.cost / result.clicks).toFixed(2) : 'N/A',
        // CTR is calculated from dividing total clicks by total impressions,
        // not by summing CTRs from individual accounts.
        (result.clicks * 100 / result.impressions).toFixed(2),
        (result.searchImpressionShare / rows.length).toFixed(2),
        result.impressions, result.clicks
      ];

      spreadsheetAccess.writeNextEntry(formattedRow);
      spreadsheetAccess.sortReportRows();
      spreadsheetAccess.setDateComplete();
      reportState.removeDateFromQueue(completedResult.dateString);
    }
    const email = spreadsheetAccess.getEmail();
    if (email) {
      sendEmail(email);
    }
  }
}

/**
 * Constructs and sends email summary.
 *
 * @param {string} email The recipient's email address.
 */
function sendEmail(email) {
  const yesterdayRow = spreadsheetAccess.getPreviousRow(1);
  const twoDaysAgoRow = spreadsheetAccess.getPreviousRow(2);
  const weekAgoRow = spreadsheetAccess.getPreviousRow(5);

  const yesterdayColHeading = yesterdayRow ? yesterdayRow[0] : '-';
  const twoDaysAgoColHeading = twoDaysAgoRow ? twoDaysAgoRow[0] : '-';
  const weekAgoColHeading = weekAgoRow ? weekAgoRow[0] : '-';

  const html = [];
  html.push(
      '<html>', '<body>',
      '<table width=800 cellpadding=0 border=0 cellspacing=0>', '<tr>',
      '<td colspan=2 align=right>',
      '<div style=\'font: italic normal 10pt Times New Roman, serif; ' +
          'margin: 0; color: #666; padding-right: 5px;\'>' +
          'Powered by Google Ads Scripts</div>',
      '</td>', '</tr>', '<tr bgcolor=\'#3c78d8\'>', '<td width=500>',
      '<div style=\'font: normal 18pt verdana, sans-serif; ' +
          'padding: 3px 10px; color: white\'>Account Summary report</div>',
      '</td>', '<td align=right>',
      '<div style=\'font: normal 18pt verdana, sans-serif; ' +
          'padding: 3px 10px; color: white\'>',
      AdsApp.currentAccount().getCustomerId(), '</h1>', '</td>', '</tr>',
      '</table>', '<table width=800 cellpadding=0 border=0 cellspacing=0>',
      '<tr bgcolor=\'#ddd\'>', '<td></td>',
      '<td style=\'font: 12pt verdana, sans-serif; ' +
          'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
          'text-align: left\'>',
      yesterdayColHeading, '</td>',
      '<td style=\'font: 12pt verdana, sans-serif; ' +
          'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
          'text-align: left\'>',
      twoDaysAgoColHeading, '</td>',
      '<td style=\'font: 12pt verdana, sans-serif; ' +
          'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
          'text-align: left\'>',
      weekAgoColHeading, '</td>', '</tr>');

  let columnNumber = 1;
  for (const fieldName of DISPLAY_FIELDS) {
    html.push(emailRow(
        fieldName, columnNumber, yesterdayRow, twoDaysAgoRow, weekAgoRow));
    columnNumber++;
  }
  html.push('</table>', '</body>', '</html>');
  MailApp.sendEmail(
      email, 'Google Ads Account ' + AdsApp.currentAccount().getCustomerId() +
          ' Summary Report',
      '', {htmlBody: html.join('\n')});
}

/**
 * Constructs a row for embedding in the email message.
 *
 * @param {string} title The title for the row.
 * @param {number} column The index into each ReportRow object for the value to
 *     extract.
 * @param {ReportRow} yesterdayRow Statistics from yesterday, or the most recent
 *     last day processed.
 * @param {ReportRow} twoDaysAgoRow Statistics from 2 days ago, or the 2nd most
 *     recent day processed.
 * @param {ReportRow} weekAgoRow Statistics from a week ago, or the 7th most
 *     recent day processed.
 * @return {string} HTML representing a row of statistics.
 */
function emailRow(title, column, yesterdayRow, twoDaysAgoRow, weekAgoRow) {
  const html = [];
  let twoDaysAgoCell = '<td></td>';
  let weekAgoCell = '<td></td>';
  if (twoDaysAgoRow) {
    twoDaysAgoCell = `<td style='padding: 0px 10px'>` +
        `${twoDaysAgoRow[column]}` +
        `${formatChangeString(yesterdayRow[column], twoDaysAgoRow[column])}` +
        `</td>`;
  }
  if (weekAgoRow) {
    weekAgoCell = `<td style='padding: 0px 10px'>` +
        `${weekAgoRow[column]}` +
        `${formatChangeString(yesterdayRow[column], weekAgoRow[column])}` +
        `</td>`;
  }
  html.push(
      '<tr>', `<td style='padding: 5px 10px'> ${title} </td>`,
      `<td style='padding: 0px 10px'> ${yesterdayRow[column]} </td>`,
      twoDaysAgoCell, weekAgoCell, '</tr>');
  return html.join('\n');
}

/**
 * Formats HTML representing the change from an old to a new value in the email
 * summary.
 *
 * @param {number} newValue
 * @param {number} oldValue
 * @return {string} HTML representing the change.
 */
function formatChangeString(newValue, oldValue) {
  let newValueString = newValue.toString();
  let oldValueString = oldValue.toString();
  const x = newValueString.indexOf('%');
  if (x != -1) {
    newValueString = newValueString.substring(0, x);
    const y = oldValueString.indexOf('%');
    oldValueString = oldValueString.substring(0, y);
  }

  const change = parseFloat(newValueString - oldValueString).toFixed(2);
  let changeString = change;
  if (x != -1) {
    changeString = change + '%';
  }

  let color = 'cc0000';
  const template = '<span style=\'color: #%s; font-size: 8pt\'> (%s)</span>';
  if (change >= 0) {
    color = '38761d';
  }
  return Utilities.formatString(template, color, changeString);
}

/**
 * Convenience function fo reformat a string date from YYYYMMDD to YYYY-MM-DD.
 *
 * @param {string} date String in form YYYYMMDD.
 * @return {string} String in form YYYY-MM-DD.
 */
function separateDateString(date) {
  return [date.substr(0, 4), date.substr(4, 2), date.substr(6, 2)].join('-');
}

/**
 * @typedef {Object} AccountData
 * @property {string} currencyCode
 * @property {Object.<ReportRow>} records Results for individual dates.
 */

/**
 * @typedef {Object} State
 * @property {Array.<string>} dateQueue Holds an ordered list of dates requiring
 *    report entries.
 * @property {Object.<AccountData>} accounts Holds intermediate results for each
 *    account.
 */

/**
 * ReportState coordinates the ordered retrieval of report data across CIDs, and
 * determines when data is ready for writing to the spreadsheet.
 *
 */
class ReportState {

  /**
  * constructor for ReportState
  */
  constructor() {
    this.state_ = this.loadOrCreateState_();
  }

  /**
   * Either loads an existing state representation from Drive, or if one does
   * not exist, creates a new state representation.
   *
   * @return {State}
   * @private_
   */
  loadOrCreateState_() {
    const reportStateFiles =
        DriveApp.getRootFolder().getFilesByName(this.getFilename_());
    if (reportStateFiles.hasNext()) {
      const reportStateFile = reportStateFiles.next();
      if (reportStateFiles.hasNext()) {
        this.throwDuplicateFileException_();
      }
      reportState = JSON.parse(reportStateFile.getBlob().getDataAsString());
      this.updateAccountsList_(reportState);
    } else {
      reportState = this.createNewState_();
    }
    return reportState;
  }

  /**
   * Creates a new state representation on Drive.
   *
   * @return {State}
   * @private
   */
  createNewState_() {
    const accountDict = {};

    const accounts = AdsManagerApp.accounts().get();
    for (const account of accounts) {
      const stats = account.getStatsFor("LAST_MONTH");
      if (stats.getImpressions() > 0) {
        accountDict[account.getCustomerId()] = {
          records: {},
          currencyCode: account.getCurrencyCode()
        };
      }
    }

    const reportState = {dateQueue: [], accounts: accountDict};
    DriveApp.getRootFolder().createFile(
        this.getFilename_(), JSON.stringify(reportState));
    return reportState;
  }

  /**
   * Updates the state object to reflect both accounts that are added to
   * the manager account and accounts that are removed.
   *
   * @param {State} reportState The state as loaded from Drive.
   * @private_
   */
  updateAccountsList_(reportState) {
    const accountState = reportState.accounts;
    const accounts = AdsManagerApp.accounts().get();
    const accountDict = {};
    for (const account of accounts) {
      const customerId = account.getCustomerId();
      accountDict[customerId] = true;
      if (!accountState.hasOwnProperty(customerId)) {
        accountState[customerId] = {
          records: {},
          currencyCode: account.getCurrencyCode()
        };
      }
    }
    const forRemoval = [];
    const existingAccounts = Object.keys(accountState);
    for (const existingAccount of existingAccounts) {
      if (!accountDict.hasOwnProperty(existingAccount)) {
        forRemoval.push(existingAccount);
      }
    }
    forRemoval.forEach(function(customerId) {
      delete accountState[customerId]; });
  }

  /**
   * Adds dates to the state object, for which reports should be retrieved.
   *
   * @param {!Array.<string>} dateList A list of strings in the form YYYYMMDD,
   * that are to be marked as for report retrieval by each managed account.
   */
  addDatesToQueue(dateList) {
    if (dateList.length) {
      for (const dateString of dateList) {
        if (this.state_.dateQueue.indexOf(dateString) === -1) {
          this.state_.dateQueue.push(dateString);
        }
      }
      // Ensure the date queue is sorted oldest to newest.
      this.state_.dateQueue.sort();
      this.flush();
    }
  }

  /**
   * Retrieve the list of dates requiring report generation.
   *
   * @return {Array.<string>} An ordered list of strings in the form YYYYMMDD.
   */
  getDateQueue() {
    return this.state_.dateQueue;
  }

  /**
   * Removes a date from the list of dates remaining to have their reports
   * pulled and aggregated, and removes any associated saved statistics from the
   * state object also. Saves the state to Drive.
   *
   * @param {string} dateString Date in the format YYYYMMDD.
   */
  removeDateFromQueue(dateString) {
    const index = this.state_.dateQueue.indexOf(dateString);
    if (index > -1) {
      this.state_.dateQueue.splice(index, 1);
    }
    const accounts = this.state_.accounts;
    const accountKeys = Object.keys(accounts);
    for (const customerId of accountKeys) {
      const records = accounts[customerId].records;
      if (records.hasOwnProperty(dateString)) {
        delete records[dateString];
      }
    }
    this.flush();
  }

  /**
   * Stores results for a given account in the state object. Does not save to
   * Drive: As this may be called ~50 times in succession for each managed
   * account, call .flush() after all calls to save only once.
   *
   * @param {string} customerId The customerId for the results.
   * @param {string} dateString The date of the results in the form YYYYMMDD.
   * @param {ReportRow} results Statistics from Account Performance Report.
   */
  updateAccountResult(customerId, dateString, results) {
    const accounts = this.state_.accounts;
    if (accounts.hasOwnProperty(customerId)) {
      const records = accounts[customerId].records;
      records[dateString] = results;
    }
  }

  /**
   * Saves the report state object to Drive.
   */
  flush() {
    const reportStateFilename = this.getFilename_();
    const reportFiles =
        DriveApp.getRootFolder().getFilesByName(reportStateFilename);
    if (reportFiles.hasNext()) {
      const reportFile = reportFiles.next();
      if (reportFiles.hasNext()) {
        this.throwDuplicateFileException_();
      }
      reportFile.setContent(JSON.stringify(this.state_));
    } else {
      this.throwNoReportFileFoundException_();
    }
  }

  /**
   * Retrieves the list of accounts to process next. Return accounts in an
   * ordering where those accounts with the oldest incomplete date return first.
   *
   * @return {!Array.<string>} A list of CustomerId values.
   */
  getNextAccounts() {
    const nextAccounts = [];
    const accounts = this.state_.accounts;
    // Sort only to make it easier to test.
    const accountKeys = Object.keys(accounts).sort();
    // dateQueue is ordered from oldest to newest
    const dates = this.state_.dateQueue;
    let i = 0;
    let j = 0;
    while (i < dates.length && nextAccounts.length < MAX_PARALLEL_ACCOUNTS) {
      const date = dates[i];
      while (j < accountKeys.length &&
             nextAccounts.length < MAX_PARALLEL_ACCOUNTS) {
        const customerId = accountKeys[j];
        const records = accounts[customerId].records;
        if (!records.hasOwnProperty(date)) {
          nextAccounts.push(customerId);
        }
        j++;
      }
      i++;
    }
    return nextAccounts;
  }

  /**
   * @typedef {object} CompletedDate
   * @property {!string} dateString The date of the report data, in YYYYMMDD
   *     format.
   * @property {Array.<ReportRow>} reportData Rows of report data taken from
   *     each account within the manager account.
   */

  /**
   * Gets a list of the dates, and associated report data in the State object
   * for which all accounts have data (and are therefore ready for aggregation
   * and writing to a Spreadsheet).
   *
   * @return {!Array.<CompletedDate>} An array of CompletedDate objects, ordered
   *     from the oldest date to the most recent.
   */
  getCompletedDates() {
    const completedDates = [];
    const dateQueue = this.state_.dateQueue;
    for (const date of dateQueue) {
      completedDates.push({dateString: date, reportData: []});
    }
    const accounts = this.state_.accounts;
    const accountKeys = Object.keys(accounts);
    for (const customerId of accountKeys) {
      const records = accounts[customerId].records;
      const forRemoval = [];
      for (let k = 0; k < completedDates.length; k++) {
        const completedDate = completedDates[k];
        const dateString = completedDate.dateString;
        if (records.hasOwnProperty(dateString)) {
          completedDate.reportData.push(records[dateString]);
        } else {
          forRemoval.push(k);
        }
      }
      forRemoval.forEach(function(index) { completedDates.splice(index, 1); });
    }
    return completedDates;
  }

  /**
   * Generate a filename unique to this manager account for saving the
   * intermediate data on Drive.
   *
   * @return {string} The filename.
   * @private
   */
  getFilename_() {
    return AdsApp.currentAccount().getCustomerId() + '-account-report.json';
  }

  /**
   * Returns whether the accounts store in the state object all have the same
   * currency or not. This is relevant in determining whether showing an
   * aggregated cost and CTR is meaningful.
   *
   * @return {boolean} True if only one currency is present.
   */
  isSingleCurrency() {
    const accounts = this.state_.accounts;
    const accountKeys = Object.keys(accounts);
    for (let i = 1; i < accountKeys.length; i++) {
      if (accounts[accountKeys[i - 1]].currencyCode !==
          accounts[accountKeys[i]].currencyCode) {
        return false;
      }
    }
    return true;
  }

  /**
   * Sets the currency code for a given account.
   *
   * @param {string} customerId
   * @param {string} currencyCode , e.g. 'USD'
   */
  setCurrencyCode(customerId, currencyCode) {
    const accounts = this.state_.accounts;
    if (accounts.hasOwnProperty(customerId)) {
      accounts[customerId].currencyCode = currencyCode;
    }
  }

  /**
   * Throws an exception if there are multiple files with the same name.
   *
   * @private
   */
  throwDuplicateFileException_() {
    throw `Multiple files named ${this.getFileName_()} detected. Please ` +
        `ensure there is only one file named ${this.getFileName_()} ` +
        ` and try again.`;
  }

  /**
   * Throws an exception for when no file is found for the given name.
   *
   * @private
   */
  throwNoReportFileFoundException_() {
    throw `Could not find the file named ${this.getFileName_()} ` +
        ` to save the to.`;
  }
}

/**
 * Class used to ease reading and writing to report spreadsheet.
 */
class SpreadsheetAccess {

  /**
   * @param {string} spreadsheetUrl
   * @param {string} sheetName The sheet name to read/write results from/to.
   */
  constructor (spreadsheetUrl, sheetName) {
    // Offsets into the existing template sheet for the top left of the data.
    this.DATA_COL_ = 2;
    this.DATA_ROW_ = 6;
    this.spreadsheet_ = SpreadsheetAccess.
        validateAndGetSpreadsheet(spreadsheetUrl);
    this.sheet_ = this.spreadsheet_.getSheetByName(sheetName);
    this.accountTz_ = AdsApp.currentAccount().getTimeZone();
    this.spreadsheetTz_ = this.spreadsheet_.getSpreadsheetTimeZone();
    this.spreadsheet_.getRangeByName('account_id_report')
        .setValue(AdsApp.currentAccount().getCustomerId());

    const d = new Date();
    d.setSeconds(0);
    d.setMilliseconds(0);

    const s = new Date(
        Utilities.formatDate(d, this.spreadsheetTz_, 'MMM dd,yyyy HH:mm:ss'));
    this.spreadsheetOffset_ = s.getTime() - d.getTime();
  }

  /**
   * Retrieves a list of dates for which Account Report data is required. This
   * is based on the last entry in the spreadsheet. If the last entry value is
   * empty then yesterday is used, otherwise, all dates between the last entry
   * and yesterday are used, except those for which data is already in the Sheet.
   *
   * @return {!Array.<string>} List of dates in YYYYMMDD format.
   */
  getNextDates() {
    let nextDates = [];
    const y = new Date((new Date()).getTime() - MILLIS_PER_DAY);
    const yesterday = Utilities.formatDate(y, this.accountTz_, 'yyyyMMdd');
    const lastCheck = this.spreadsheet_.getRangeByName('last_check').getValue();

    if (lastCheck.length === 0) {
      nextDates = [yesterday];
    } else {
      let lastCheckDate =
          Utilities.formatDate(lastCheck, this.spreadsheetTz_, 'yyyyMMdd');
      while (lastCheckDate !== yesterday) {
        lastCheck.setTime(lastCheck.getTime() + MILLIS_PER_DAY);
        lastCheckDate =
            Utilities.formatDate(lastCheck, this.spreadsheetTz_, 'yyyyMMdd');
        nextDates.push(lastCheckDate);
      }
    }

    const sheet = this.spreadsheet_.getSheetByName('Report');
    const data = sheet.getDataRange().getValues();
    const existingDates = {};
    data.slice(5).forEach(function(row) {
      const existingDate =
          Utilities.formatDate(row[1], this.spreadsheetTz_, 'yyyyMMdd');
      existingDates[existingDate] = true;
    }, this);
    return nextDates.filter(function(d) {
      return !existingDates[d];
    });
  }

  /**
   * Updates the spreadsheet to set the date for the last saved report data.
   */
  setDateComplete() {
    const sheet = this.spreadsheet_.getSheetByName('Report');
    const data = sheet.getDataRange().getValues();
    if (data.length > 5) {
      const lastDate = data[data.length - 1][1];
      this.spreadsheet_.getRangeByName('last_check').setValue(lastDate);
    }
  }

  /**
   * Writes the next row of report data to the spreadsheet.
   *
   * @param {Array.<*>} row An array of report values
   */
  writeNextEntry(row) {
    const lastRow = this.sheet_.getDataRange().getLastRow();
    if (lastRow + 1 > this.sheet_.getMaxRows()) {
      this.sheet_.insertRowAfter(lastRow);
    }
    this.sheet_.getRange(lastRow + 1, this.DATA_COL_, 1, row.length).setValues([
      row
    ]);
  }

  /**
   * Retrieves the values for a previously written row
   *
   * @param {number} daysAgo The reversed index of the required row, e.g. 1 is
   *     the last written row, 2 is the one before that etc.
   * @return {Array.<*>} The array data, or null if the index goes out of bounds.
   */
  getPreviousRow(daysAgo) {
    const index = this.sheet_.getDataRange().getLastRow() - daysAgo + 1;
    if (index < this.DATA_ROW_) {
      return null;
    }
    const numColumns = DISPLAY_FIELDS.length;
    const row = this.sheet_.getRange(index, this.DATA_COL_, 1, numColumns + 1)
                  .getValues()[0];
    row[0] = Utilities.formatDate(row[0], this.spreadsheetTz_, 'yyyy-MM-dd');
    return row;
  }

  /**
   * Retrieves the email address set in the spreadsheet.
   *
   * @return {string}
   */
  getEmail() {
    return this.spreadsheet_.getRangeByName('email').getValue();
  }

  /**
   * Sorts the data in the spreadsheet into ascending date order.
   */
  sortReportRows() {
    const sheet = this.spreadsheet_.getSheetByName('Report');

    const data = sheet.getDataRange().getValues();
    const reportRows = data.slice(5);
    if (reportRows.length) {
      reportRows.sort(function(rowA, rowB) {
        if (!rowA || !rowA.length) {
          return -1;
        } else if (!rowB || !rowB.length) {
          return 1;
        } else if (rowA[1] < rowB[1]) {
          return -1;
        } else if (rowA[1] > rowB[1]) {
          return 1;
        }
        return 0;
      });
      sheet.getRange(6, 1, reportRows.length, reportRows[0].length)
          .setValues(reportRows);
    }
  }

  /**
   * Validates the parameters related to the data retrieval to make sure
   * they are within valid values.
   * @throws {Error} If the new day trigger hour is less than 3 or
   * greater than or equal to 24
   */
  static validateParameters() {
    if (TRIGGER_NEW_DAY_REPORT_HOUR < MIN_NEW_DAY_REPORT_HOUR ||
            TRIGGER_NEW_DAY_REPORT_HOUR >= MAX_NEW_DAY_REPORT_HOUR) {
      throw new Error('Please set the new day trigger hour at least 3 hours' +
        ' into the day and less than 24 hours after the start of the day');
    }
  }

  /**
   * Validates the provided spreadsheet URL and email address
   * to make sure that they're set up properly. Throws a descriptive error
   * message if validation fails.
   *
   * @param {string} spreadsheeturl The URL of the spreadsheet to open.
   * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
   * @throws {Error} If the spreadsheet URL or email hasn't been set
   */
  static validateAndGetSpreadsheet(spreadsheeturl) {
    if (spreadsheeturl == 'INSERT_SPREADSHEET_URL_HERE') {
      throw new Error('Please specify a valid Spreadsheet URL. You can find' +
          ' a link to a template in the associated guide for this script.');
    }
    const spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
    const email = spreadsheet.getRangeByName('email').getValue();
    if (email == DEFAULT_EMPTY_EMAIL) {
      throw new Error('Please either set a custom email address in the' +
          ' spreadsheet, or set the email field in the spreadsheet to blank' +
          ' to send no email.');
    }
    return spreadsheet;
  }
}