Skip to content

Instantly share code, notes, and snippets.

@chipoglesby
Forked from russorat/mcc-account-reporting.js
Last active August 9, 2016 18:21
Show Gist options
  • Save chipoglesby/e3e5e0cc786ef8807291 to your computer and use it in GitHub Desktop.
Save chipoglesby/e3e5e0cc786ef8807291 to your computer and use it in GitHub Desktop.
function main() {
MccApp.accounts().withCondition("Cost > 1.00").forDateRange("YESTERDAY").withLimit(50).executeInParallel('runOnEachAccount', 'finished');
}
function runOnEachAccount() {
Logger.log('Starting on: '+AdWordsApp.currentAccount().getCustomerId());
var results = getAccountReport();
Logger.log(results);
return JSON.stringify(results);
}
function getAccountReport() {
var OPTIONS = { includeZeroImpressions : true };
var cols = getCols();
var report = 'ACCOUNT_PERFORMANCE_REPORT';
var query = ['select',cols.join(','),'from',report,
'during','YESTERDAY'].join(' ');
var reportIter = AdWordsApp.report(query, OPTIONS).rows();
var resultsArray = [];
while(reportIter.hasNext()) {
var row = reportIter.next();
for(var i in cols) {
resultsArray.push(row[cols[i]]);
}
}
return resultsArray;
}
function getCols() {
return ['Date',
'AccountDescriptiveName',
'Cost'
];
}
function getTotalsCols() {
return ['Date',
'Cost'
];
}
function getSpreadsheet(name) {
var fileIter = DriveApp.getFilesByName(name);
var spreadsheet;
if(fileIter.hasNext()) {
spreadsheet = SpreadsheetApp.openById(fileIter.next().getId());
} else {
//create a new one
spreadsheet = SpreadsheetApp.create(name);
}
return spreadsheet;
}
function addResultsToSpreadsheet(spreadsheet,accountResults,sheetName) {
var sheet = getSheet(spreadsheet,sheetName,getCols());
if(shouldAddRow(sheet,accountResults,getCols())) {
sheet.appendRow(accountResults);
}
}
function finished(resultsArray) {
var spreadsheetName = 'MCC Account Report';
var spreadsheet = getSpreadsheet(spreadsheetName);
var totalResults;
for(var i in resultsArray) {
var accountResults = JSON.parse(resultsArray[i].getReturnValue());
var sheetName = accountResults[getCols().indexOf('AccountDescriptiveName')];
if(!sheetName) { continue; }
addResultsToSpreadsheet(spreadsheet,accountResults,sheetName);
totalResults = addToTotalResults(totalResults,accountResults);
}
var totalsSheetName = 'All Accounts';
addResultsToSpreadsheet(spreadsheet,totalResults,totalsSheetName);
if(spreadsheet.getSheetByName('Sheet1')) {
spreadsheet.deleteSheet(spreadsheet.getSheetByName('Sheet1'));
}
Logger.log('Your results are: '+spreadsheet.getUrl());
}
function addToTotalResults(totalResults,accountResults) {
if(!totalResults) { totalResults = ['',0,0,0,0,0]; }
totalResults[getTotalsCols().indexOf('Date')] =
accountResults[getCols().indexOf('Date')];
totalResults[getTotalsCols().indexOf('Cost')] +=
parseFloat(accountResults[getCols().indexOf('Cost')]);
return totalResults;
}
function shouldAddRow(sheet,results,cols) {
var shouldAdd = true;
var dates = sheet.getRange('A:A').getValues();
for(var i in dates) {
var dateStr = Utilities.formatDate(new Date(dates[i][0]),AdWordsApp.currentAccount().getTimeZone(),'yyyy-MM-dd');
if(dateStr === results[cols.indexOf('Date')]) {
return false;
}
}
return true;
}
function getSheet(spreadsheet,sheetName,columns) {
var sheet = spreadsheet.getSheetByName(sheetName);
if(!sheet) {
sheet = spreadsheet.insertSheet(sheetName);
sheet.appendRow(columns);
}
return sheet;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment