此脚本是对帐号摘要报告的扩展,能够生成在经理帐号一级汇总的效果统计信息摘要,并以 HTML 格式的每日电子邮件形式发送报告。
运作方式
该脚本会从经理帐号内的所有帐号中提取前一天的统计信息并更新电子表格。请注意以下几点:
每次执行时,系统都会检索 50 个帐号的统计信息(由于存在脚本限制),直到经过安排的重复执行,系统才会检索所有帐号的统计信息。
在检索到所有帐号的前一天的统计信息后,这些统计信息将汇总并写入电子表格。
经理帐号中的帐号可以采用不同的帐号时区设置。这会影响统计信息何时可供汇总:时区偏移量为负数的帐号不像时区偏移量为正的帐号之前准备好统计信息可供汇总。
显示统计信息的图表已经过预先配置。如果指定了电子邮件地址,脚本会发送一封 HTML 格式的电子邮件。
初始设置
点击下面的按钮,在您的 Google Ads 帐号中创建基于电子表格的脚本。
点击下面的按钮以创建电子表格模板的副本。
更新脚本中的
spreadsheet_url
。将脚本设为每小时运行一次。
延长报告时间(可选)
您可以通过添加 customer
资源中可用指标的更多指标来扩展报告:
这比在此报告的单帐号版本中添加更多指标略微复杂一些,因为您需要考虑汇总的执行方式,例如:
- 点击次数是跨帐号计算的简单指标,因为每个帐号的总计值只需要相加。
- 不过,点击率并不能通过对每个帐号的点击率值取平均值来计算。相反,所有帐号的总点击次数必须除以所有帐号的总展示次数。
第二个点击率示例表明,对于给定指标,可能需要获取不同的查询指标(在本例中为点击次数和展示次数),才能计算报告的最终指标 (CTR)。
下例展示了如何在报告中添加“转化率”列:
- 转化率定义为 total_conversions / total_clicks。点击次数已存在于要检索的字段列表中,但您必须添加转化:
'query_fields': ['segments.date', 'metrics.cost_micros', 'metrics.impressions', 'metrics.clicks', 'metrics.search_impression_share', 'metrics.all_conversions'];
- 向显示标题添加“转化率”标题:
'display_fields': ['Cost', 'Avg. CPC', 'CTR', 'Search Impression Share', 'Impressions', 'Clicks', 'Conversion Rate'];
最后一步是在
processFinalResults
函数中实现聚合。下面显示了该脚本的一段摘要,以及每项变更上方的注释说明了三项小的修改:// 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) ];
为新字段创建图表:
- 点击电子表格中的某个现有图表,在图表右上角的三点状菜单下选择复制图表,然后右键点击并粘贴。
- 系统会在工作表中创建一个新图表。在新图表的菜单中,选择修改图表。
- 将 Series 列引用更改为包含新数据的列;例如,将
G
更改为H
。 - 双击图表标题,将其更改为新的指标名称。
源代码
// 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;
}
}