Account Summary Report

// 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 Account Summary Report script generates an at-a-glance report
 *     showing the performance of an entire Google Ads account. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/account-summary
 *     for more details.
 *
 * @author Google Ads Scripts Team [[email protected]]
 *
 * @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
 *   - Add user-updateable fields, and ensure report row ordering.
 * - version 1.0.4
 *   - Improved code readability and comments.
 * - version 1.0.3
 *   - Added validation for external spreadsheet setup.
 * - version 1.0.2
 *   - Fixes date formatting bug in certain timezones.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */
/**
 * Configuration to be used for the Account Summary Report.
 */

CONFIG = {
  // URL of the report spreadsheet. This should be a copy of
  // https://docs.google.com/spreadsheets/d/1gYLXtDK93lWoTe3OBKvTlfcc7L_qHJFgWU9N6HwhZtU/copy
  'spreadsheet_url': 'YOUR_SPREADSHEET_URL',
  // More reporting options can be found at
  // https://developers.google.com/google-ads/scripts/docs/reference/adsapp/adsapp#report_2
  'reporting_options': {
    // Comment out the following line to default to the latest reporting
    // version.
    'apiVersion': 'v10'
  },
  /**
   * To add additional fields to the report, follow the instructions at
   * https://developers.google.com/google-ads/scripts/docs/solutions/account-summary#extending_the_report_optional
   */
  'report_fields': [
    {'columnName': 'metrics.cost_micros', 'displayName': 'Cost'},
    {'columnName': 'metrics.average_cpc', 'displayName': 'Avg. CPC'},
    {'columnName': 'metrics.ctr', 'displayName': 'CTR'},
    {'columnName': 'metrics.search_impression_share', 'displayName': 'Search Impr. share'},
    {'columnName': 'metrics.impressions', 'displayName': 'Impressions'},
    {'columnName': 'metrics.clicks', 'displayName': 'Clicks'}
  ]
};
const SPREADSHEET_URL = CONFIG.spreadsheet_url;
const REPORTING_OPTIONS = CONFIG.reporting_options;
const REPORT_FIELDS = CONFIG.report_fields;

/** The spreadsheet is updated to showcase the performance of the account.
 *  An email is sent to the email ID mentioned in the spreadsheet
 */
function main() {
  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  const spreadsheet = validateAndGetSpreadsheet();
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
  spreadsheet.getRangeByName('account_id_report').setValue(AdsApp.currentAccount().getCustomerId());
  const yesterday = getYesterday();
  const date = getFirstDayToCheck(spreadsheet, yesterday);
  const rows = [];
  const existingDates = getExistingDates();
  while (date.getTime() <= yesterday.getTime()) {
    if (!existingDates[date]) {
      let row = getReportRowForDate(date);
      rows.push([new Date(date)].concat(REPORT_FIELDS.map(function(field) {
        row[field.columnName] = format(field.columnName, row[field.columnName]);
        return row[field.columnName];
 })));
    spreadsheet.getRangeByName('last_check').setValue(date);
    }
    date.setDate(date.getDate() + 1);
  }
  if (rows.length > 0) {
    writeToSpreadsheet(rows);
    const email = spreadsheet.getRangeByName('email').getValue();
    if (email) {
      sendEmail(email);
    }
  }
}

/**Converts the metrics.cost_micros by dividing it by a million to match the
 * output with version v1.1.1 of the file.
 *
 * @param {string} value that needs to be converted.
 * @return {string} A value that is of type float.
 */
function formatMicros(value) {
  const micros = parseFloat(value / 1000000).toFixed(2);
  return `${micros}`;
}

/**
 * Formats decimal number into a percentage.
 *
 * @param {string} value The decimal number to format.
 * @return {string} The decimal number formatted as a percentage.
 */
function formatPercentage(value) {
 value=parseFloat(value*100).toFixed(2)+'%';
 return value;
}

/**
 * Formats Impression Share values.
 *
 * @param {string} value The Impression Share in Google Ads API format.
 * @return {string} The Impression Share formatted for the spreadsheet.
 */
function formatImpressionShare(value) {
   if (value <= 0.0999) {
       value='<10%';
    }
    else if (value>0.0999) {
     value=parseFloat(value*100)+'%';
    }
    else {
      value='--';
    }
  return value;
}

/**
 *  Formats clicks, impressions, ctr, average_cpc, cost_micros field values.
 *
 * @param {string} column The name of the field.
 * @param {string} value The value of the field.
 * @return {string} The formatted value of the field.
 */
function format(column, value) {
  switch (column) {
    case 'metrics.clicks':
    case 'metrics.impressions':
      return value;
    case 'metrics.ctr':
      return formatPercentage(value);
    case 'metrics.average_cpc':
    case 'metrics.cost_micros':
      return formatMicros(value);
    case 'metrics.search_impression_share':
      return formatImpressionShare(value);
    default:
      throw new Error(`Unknown field ${column}`);
  }
}

/**
 * Retrieves a lookup of dates for which rows already exist in the spreadsheet.
 *
 * @return {!Object} A lookup of existing dates.
 */
function getExistingDates() {
  const spreadsheet = validateAndGetSpreadsheet();
  const sheet = spreadsheet.getSheetByName('Report');
  const data = sheet.getDataRange().getValues();
  const existingDates = {};
  data.slice(5).forEach(function(row) {
    existingDates[row[1]] = true;
  });
  return existingDates;
}

/**
 * Sorts the data in the spreadsheet into ascending date order.
 */
function sortReportRows() {
  const spreadsheet = validateAndGetSpreadsheet();
  const sheet = 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);
  }
}

/**
 * Append the data rows to the spreadsheet.
 *
 * @param {!Array<!Array<string>>} rows The data rows.
 */
function writeToSpreadsheet(rows) {
  const access = new SpreadsheetAccess(SPREADSHEET_URL, 'Report');
  let emptyRow = access.findEmptyRow(6, 2);
  if (emptyRow < 0) {
    access.addRows(rows.length);
    emptyRow = access.findEmptyRow(6, 2);
  }
  access.writeRows(rows, emptyRow, 2);
  sortReportRows();
}
/**
 * Sends mail to specified email address in spreadsheet
 *
 * @param {string} email address
 */
function sendEmail(email) {
  const day = getYesterday();
  const yesterdayRow = getReportRowForDate(day);
  day.setDate(day.getDate() - 1);
  const twoDaysAgoRow = getReportRowForDate(day);
  day.setDate(day.getDate() - 5);
  const weekAgoRow = getReportRowForDate(day);
  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'>Yesterday</td>",
              "<td style='font: 12pt verdana, sans-serif; " +
                  'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
                  "text-align: left'>Two Days Ago</td>",
              "<td style='font: 12pt verdana, sans-serif; " +
                  'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
                  "text-align: left'>A week ago</td>",
            '</tr>');
  REPORT_FIELDS.forEach(function(field) {
    html.push(emailRow(
        field.displayName,field.columnName, yesterdayRow, twoDaysAgoRow,
        weekAgoRow));
  });
  html.push('</table>', '</body>', '</html>');
  MailApp.sendEmail(email, 'Google Ads Account ' +
      AdsApp.currentAccount().getCustomerId() + ' Summary Report', '',
      {htmlBody: html.join('\n')});
}

/**
 * Generates html summary report with the column names todays ,twodays ago, week ago
 *
 * @param {string} title of the report
 * @param {string} column field names
 * @param {!Object} yesterdayRow holds Object containing fields yesterday data
 * @param {!Object} twoDaysAgoRow holds Object containing fields twodays ago data
 * @param {!Object} weekAgoRow holds Object containing fields week ago data
 * @return {!html} html format
 */
function emailRow(title, column, yesterdayRow, twoDaysAgoRow, weekAgoRow) {
  yesterdayRow[column] = format(column,yesterdayRow[column]);
  twoDaysAgoRow[column] = format(column,twoDaysAgoRow[column]);
  weekAgoRow[column] = format(column,weekAgoRow[column]);
  const html = [];
  html.push('<tr>',
      "<td style='padding: 5px 10px'>" + title + '</td>',
      "<td style='padding: 0px 10px'>" +  yesterdayRow[column]+ '</td>',
      "<td style='padding: 0px 10px'>" + twoDaysAgoRow[column] +
          formatChangeString(yesterdayRow[column], twoDaysAgoRow[column]) +
      '</td>',
      "<td style='padding: 0px 10px'>" + weekAgoRow[column] +
          formatChangeString(yesterdayRow[column], weekAgoRow[column]) +
      '</td>',
      '</tr>');
  return html.join('\n');
}

/**
 * Retrieves Dates in the format yyyyMMdd
 *
 * @param {!Date} date value
 * @return {!Object} dateStrings
 */
function getReportRowForDate(date) {
  const timeZone = AdsApp.currentAccount().getTimeZone();
  const dateString = Utilities.formatDate(date, timeZone, 'yyyyMMdd');
  return getReportRowForDuring(dateString + ' AND ' + dateString);
}
/** Retrieves data from the select query
 *
 * @param {string} during on which dates to get the reports
 * @return {!Object} rows returns field values as an object
 */
function getReportRowForDuring(during) {
  const fields = REPORT_FIELDS.map(function(field) {return field.columnName;}).join(', ');
  const query = `SELECT ${fields} FROM customer WHERE segments.date BETWEEN ${during}`;
  const report = AdsApp.report(query,REPORTING_OPTIONS);
  return report.rows().next();
}

/**
 * Extracts the percentage value from a percentage string. E.g. given "12.3%" returns 12.3.
 *
 * @param {string} value A field value containing a percentage sign at the end.
 * @return {string} The percentage value.
 */
function extractPercentageValue(value){
   const index = value.indexOf('%');
   value = value.substring(0, index);
   return value;
}

/**
 * Formats a change between two values.
 *
 * @param {string} newValue The new value of a field.
 * @param {string} oldValue The old value of a field.
 * @return {string} A string representing the change in the field.
 */
function formatChangeString(newValue,oldValue) {
  const isPercentage = newValue.indexOf('%') >= 0;
  if(isPercentage){
    newValue = extractPercentageValue(newValue);
    oldValue = extractPercentageValue(oldValue);
  }
  const change = parseFloat(newValue - oldValue).toFixed(2);
  let changeString = change;
  if (isPercentage) {
    changeString = change + '%';
  }
  if (change >= 0) {
    return "<span style='color: #38761d; font-size: 8pt'> (+" +
        changeString + ')</span>';
  } else {
    return "<span style='color: #cc0000; font-size: 8pt'> (" +
        changeString + ')</span>';
  }
}

/**
 * The spreadsheet is accessed and updated.
 *
 * @param {string} spreadsheetUrl takes spreadsheets url
 * @param {string} sheetName is a sheet name
 */
function SpreadsheetAccess(spreadsheetUrl, sheetName) {
  this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  this.sheet = this.spreadsheet.getSheetByName(sheetName);

  // what column should we be looking at to check whether the row is empty?
  this.findEmptyRow = function(minRow, column) {
    const values = this.sheet.getRange(minRow, column,
        this.sheet.getMaxRows(), 1).getValues();
    for (let i = 0; i < values.length; i++) {
      if (!values[i][0]) {
        return i + minRow;
      }
    }
    return -1;
  };
  this.addRows = function(howMany) {
    this.sheet.insertRowsAfter(this.sheet.getMaxRows(), howMany);
  };
  this.writeRows = function(rows, startRow, startColumn) {
    this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).
        setValues(rows);
  };
}

/**
 * Gets a date object that is 00:00 yesterday.
 *
 * @return {!Date} A date object that is equivalent to 00:00 yesterday in the
 *     account's time zone.
 */
function getYesterday() {
  const yesterday = new Date(Date.now() - 24 * 3600 * 1000);
  return new Date(getDateStringInTimeZone('MMM dd, yyyy 00:00:00 Z',
      yesterday));
}

/**
 * Returned the last checked date + 1 day, or yesterday if there isn't
 * a specified last checked date.
 *
 * @param {!Spreadsheet} spreadsheet The export spreadsheet.
 * @param {!Date} yesterday The yesterday date.
 * @return {!Date} The date corresponding to the first day to check.
 */
function getFirstDayToCheck(spreadsheet, yesterday) {
  const last_check = spreadsheet.getRangeByName('last_check').getValue();
  let date;
  if (last_check.length == 0) {
    date = new Date(yesterday);
  } else {
    date = new Date(last_check);
    date.setDate(date.getDate() + 1);
  }
  return date;
}

/**
 * Produces a formatted string representing a given date in a given time zone.
 *
 * @param {string} format A format specifier for the string to be produced.
 * @param {!Date} date A date object. Defaults to the current date.
 * @param {string} timeZone A time zone. Defaults to the account's time zone.
 * @return {string} A formatted string of the given date in the given time zone.
 */
function getDateStringInTimeZone(format, date, timeZone) {
  date = date || new Date();
  timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, format);
}

/**
 * Validates the provided spreadsheet URL to make sure that it's set up
 * properly. Throws a descriptive error message if validation fails.
 *
 * @return {!Spreadsheet} The spreadsheet object itself, fetched from the URL.
 */
function validateAndGetSpreadsheet() {
  if ('YOUR_SPREADSHEET_URL' == SPREADSHEET_URL) {
    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(SPREADSHEET_URL);
  const email = spreadsheet.getRangeByName('email').getValue();
  if ('[email protected]' == 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;
}