這段指令碼會擴充帳戶摘要報表，產生在管理員帳戶層級匯總的成效統計資料摘要，並以 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) ];為新欄位建立圖表：
- 按一下試算表中的其中一個現有圖表，在圖表右上角的三點選單下方選取「Copy Chart」(複製圖表)，然後按一下滑鼠右鍵並選取 [Paste] (貼上)。
- 系統會在工作表中建立新圖表。在新圖表的選單中，選取「Edit Chart」。
- 將「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;
}
}