이 스크립트는 계정 요약 보고서를 확장하여 관리자 계정 수준에서 집계된 실적 통계 요약을 생성하고 보고서를 HTML 형식의 일일 이메일로 전송합니다.
사용 방법
스크립트는 관리자 계정 내 모든 계정에서 어제의 통계를 가져오고 스프레드시트를 업데이트합니다. 다음에 유의하세요.
스크립트 제한으로 인해 반복 예약 실행을 통해 모든 계정의 통계가 검색될 때까지 실행당 계정 50개에 대해 통계가 검색됩니다.
모든 계정에 대해 어제의 통계를 가져오면 통계가 집계되어 스프레드시트에 기록됩니다.
관리자 계정 내의 계정에는 다른 계정 시간대를 설정할 수 있습니다. 이는 통계를 집계에 사용할 수 있게 되는 시점에 영향을 미칩니다. 시간대 오프셋이 음수인 계정은 일찍이 시간대 오프셋이 양수인 계정에서는 통계 집계를 위해 준비되지 않습니다.
그래프는 통계를 표시하도록 사전 구성되어 있습니다. 이메일 주소가 지정된 경우 스크립트는 HTML 형식의 이메일을 전송합니다.
설정
아래 버튼을 클릭하여 Google Ads 계정에서 스프레드시트 기반 스크립트를 만들 수 있습니다.
템플릿 스프레드시트의 사본을 만들려면 아래 버튼을 클릭하세요.
스크립트에서
spreadsheet_url를 업데이트합니다.스크립트가 매시간 실행되도록 예약합니다.
보고서 확장 (선택사항)
customer 리소스에서 사용 가능한 측정항목을 추가하여 보고서를 확장할 수 있습니다.
이는 집계 수행 방식을 고려해야 하므로 이 보고서의 단일 계정 버전에 측정항목을 추가하는 것보다 약간 더 복잡합니다. 예를 들면 다음과 같습니다.
- 클릭수는 각 계정의 총계를 더하기만 하면 되므로 여러 계정을 대상으로 계산할 수 있는 간단한 측정항목입니다.
- 하지만 각 계정의 CTR 값을 평균하여 CTR을 계산할 수는 없습니다. 대신 모든 계정의 총 클릭수를 모든 계정의 총 노출수로 나누어야 합니다.
이 CTR의 두 번째 예는 특정 측정항목에서 보고서의 최종 측정항목 (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) ];새 필드에 대한 차트를 만듭니다.
- 스프레드시트의 기존 차트 중 하나를 클릭하고 차트 오른쪽 상단에 있는 점 3개 메뉴에서 차트 복사를 선택한 다음 마우스 오른쪽 버튼으로 클릭하고 붙여넣기를 클릭합니다.
- 시트에 새 차트가 생성됩니다. 새 차트 메뉴에서 차트 수정을 선택합니다.
- 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;
}
}