Questo script estende il report di riepilogo dell'account per produrre un riepilogo delle statistiche sul rendimento aggregate a livello di account amministratore e invia il report come email giornaliera in formato HTML.
Come funziona
Lo script recupera le statistiche di ieri da tutti gli account all'interno dell'account amministratore e aggiorna un foglio di lavoro. Tieni presente quanto riportato di seguito:
Le statistiche vengono recuperate per 50 account per esecuzione (a causa dei limiti di script), finché, dopo l'esecuzione pianificata ripetuta, vengono recuperate le statistiche per tutti gli account.
Una volta recuperate le statistiche relative a ieri per tutti gli account, le statistiche vengono aggregate e scritte nel foglio di lavoro.
Gli account all'interno di un account amministratore possono avere impostazioni di fuso orario diverse. Questo influisce sul momento in cui le statistiche diventano disponibili per l'aggregazione: un account con offset di fuso orario negativo non disporrà di statistiche pronte per l'aggregazione fin da un account con un offset di fuso orario positivo.
I grafici sono preconfigurati per visualizzare le statistiche. Se l'indirizzo email è specificato, lo script invia un'email in formato HTML.
Configurazione
Fai clic sul pulsante di seguito per creare lo script basato su foglio di lavoro nel tuo account Google Ads.
Fai clic sul pulsante qui sotto per creare una copia del modello di foglio di lavoro.
Aggiorna
spreadsheet_urlnello script.Pianifica l'esecuzione dello script ogni ora.
Estendere il report (facoltativo)
Il report può essere esteso aggiungendo ulteriori metriche da quelle disponibili
nella risorsa di customer:
Questa procedura è leggermente più complessa rispetto all'aggiunta di altre metriche alla versione di questo report per un singolo account, poiché è necessario considerare il modo in cui viene eseguita l'aggregazione, ad esempio:
- Clic è una metrica semplice da calcolare per più account, poiché i totali di ciascun account devono essere solo sommati.
- Tuttavia, il CTR non può essere calcolato calcolando la media dei valori del CTR per ogni account. Invece, il numero totale di clic per tutti gli account deve essere diviso per le impressioni totali di tutti gli account.
Questo secondo esempio di CTR dimostra che, per una determinata metrica, potrebbe essere necessario ottenere metriche di query diverse (in questo caso, clic e impressioni) per calcolare una metrica finale (CTR) per il report.
L'esempio seguente illustra l'aggiunta di una colonna per il tasso di conversione al report:
- Il tasso di conversione è definito come total_conversions / total_clicks. La voce Clic è già presente nell'elenco dei campi da recuperare, ma è necessario aggiungere le conversioni:
'query_fields': ['segments.date', 'metrics.cost_micros', 'metrics.impressions', 'metrics.clicks', 'metrics.search_impression_share', 'metrics.all_conversions']; - Aggiungi un'intestazione per Tasso di conversione alle intestazioni display:
'display_fields': ['Cost', 'Avg. CPC', 'CTR', 'Search Impression Share', 'Impressions', 'Clicks', 'Conversion Rate']; Il passaggio finale consiste nell'implementare l'aggregazione nella funzione
processFinalResults. Qui viene mostrato un estratto dello script, con le tre piccole modifiche spiegate nei commenti sopra ogni modifica:// 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) ];Crea un grafico per il nuovo campo:
- Fai clic su uno dei grafici esistenti nel foglio di lavoro e seleziona Copia grafico nel menu con tre puntini in alto a destra del grafico, quindi fai clic con il tasto destro del mouse e Incolla.
- Nel foglio verrà creato un nuovo grafico. Nel menu del nuovo grafico, seleziona Modifica grafico.
- Modifica il riferimento della colonna Serie con la colonna contenente i nuovi
dati; ad esempio, modifica
GinH. - Fai doppio clic sul titolo del grafico e modificalo con il nuovo nome della metrica.
Codice sorgente
// 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;
}
}