Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
This script will run through all your AdWords accounts and store your data in a Google Spreadsheet.
/********************************************************************************
* This script will run through all your AdWords accounts and store your data in
* a Google Spreadsheet.
*
* @author Russell Savage <russellsavage@gmail.com>
* @version 1.0
*
* THIS SOFTWARE IS PROVIDED BY Russell Savage ''AS IS'' AND ANY
* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL Russell Savage BE LIABLE FOR ANY
* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
********************************************************************************/
function main() {
MccApp.accounts().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',
'ExternalCustomerId',
'AccountDescriptiveName',
'Impressions',
'Clicks',
'Cost',
'Conversions',
'ConversionValue'
];
}
function getTotalsCols() {
return ['Date',
'Impressions',
'Clicks',
'Cost',
'Conversions',
'ConversionValue'];
}
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('ExternalCustomerId')];
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('Impressions')] +=
parseInt(accountResults[getCols().indexOf('Impressions')]);
totalResults[getTotalsCols().indexOf('Clicks')] +=
parseInt(accountResults[getCols().indexOf('Clicks')]);
totalResults[getTotalsCols().indexOf('Cost')] +=
parseFloat(accountResults[getCols().indexOf('Cost')]);
totalResults[getTotalsCols().indexOf('Conversions')] +=
parseFloat(accountResults[getCols().indexOf('Conversions')]);
totalResults[getTotalsCols().indexOf('ConversionValue')] +=
parseFloat(accountResults[getCols().indexOf('ConversionValue')]);
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
Something went wrong with that request. Please try again.