Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Populate Google Sheet With Custom AdWords Data
/*
// AdWords Script: Put Data From AdWords Report In Google Sheets
// --------------------------------------------------------------
// Copyright 2017 Optmyzr Inc., All Rights Reserved
//
// This script takes a Google spreadsheet as input. Based on the column headers, data filters, and date range specified
// on this sheet, it will generate different reports.
//
// The goal is to let users create custom automatic reports with AdWords data that they can then include in an automated reporting
// tool like the one offered by Optmyzr.
//
//
// For more PPC management tools, visit www.optmyzr.com
//
*/
var DEBUG = 0; // set to 1 to get more details about what the script does while it runs; default = 0
var REPORT_SHEET_NAME = "report"; // the name of the tab where the report data should go
var SETTINGS_SHEET_NAME = "settings"; // the name of the tab where the filters and date range are specified
var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/1dttJTb547L81XYKdTQ56LcfO9hHhbb9wm06ZY5mKhEo/edit#gid=0"; // The URL to the Google spreadsheet with your report template
var EMAIL_ADDRESSES = "example@example.com"; // Get notified by email at this address when a new report is ready
function main() {
var currentSetting = new Object();
currentSetting.ss = SPREADSHEET_URL;
// Read Settings Sheet
var settingsSheet = SpreadsheetApp.openByUrl(currentSetting.ss).getSheetByName(SETTINGS_SHEET_NAME);
var rows = settingsSheet.getDataRange();
var numRows = rows.getNumRows();
var numCols = rows.getNumColumns();
var values = rows.getValues();
var numSettingsRows = numRows - 1;
var sortString = "";
var filters = new Array();
for(var i = 0; i < numRows; i++) {
var row = values[i];
var settingName = row[0];
var settingOperator = row[1];
var settingValue = row[2];
var dataType = row[3];
debug(settingName + " " + settingOperator + " " + settingValue);
if(settingName.toLowerCase().indexOf("report type") != -1) {
var reportType = settingValue;
} else if(settingName.toLowerCase().indexOf("date range") != -1) {
var dateRange = settingValue;
} else if(settingName.toLowerCase().indexOf("sort order") != -1) {
var sortDirection = dataType || "DESC";
if(settingValue) var sortString = "ORDER BY " + settingValue + " " + sortDirection;
var sortColumnIndex = 1;
}else {
if(settingOperator && settingValue) {
if(dataType.toLowerCase().indexOf("long") != -1 || dataType.toLowerCase().indexOf("double") != -1 || dataType.toLowerCase().indexOf("money") != -1 || dataType.toLowerCase().indexOf("integer") != -1) {
var filter = settingName + " " + settingOperator + " " + settingValue;
} else {
if(settingValue.indexOf("'") != -1) {
var filter = settingName + " " + settingOperator + ' "' + settingValue + '"';
} else if(settingValue.indexOf("'") != -1) {
var filter = settingName + " " + settingOperator + " '" + settingValue + "'";
} else {
var filter = settingName + " " + settingOperator + " '" + settingValue + "'";
}
}
debug("filter: " + filter)
filters.push(filter);
}
}
}
// Process the report sheet and fill in the data
var reportSheet = SpreadsheetApp.openByUrl(currentSetting.ss).getSheetByName(REPORT_SHEET_NAME);
var rows = reportSheet.getDataRange();
var numRows = rows.getNumRows();
var numCols = rows.getNumColumns();
var values = rows.getValues();
var numSettingsRows = numRows - 1;
// Read Header Row and match names to settings
var headerNames = new Array();
var row = values[0];
for(var i = 0; i < numCols; i++) {
var value = row[i];
headerNames.push(value);
//debug(value);
}
if(reportType.toLowerCase().indexOf("performance") != -1) {
var dateString = ' DURING ' + dateRange;
} else {
var dateString = "";
}
if(filters.length) {
var query = 'SELECT ' + headerNames.join(",") + ' FROM ' + reportType + ' WHERE ' + filters.join(" AND ") + dateString + " " + sortString;
} else {
var query = 'SELECT ' + headerNames.join(",") + ' FROM ' + reportType + dateString + " " + sortString;
}
debug(query);
var report = AdWordsApp.report(query);
try {
report.exportToSheet(reportSheet);
var subject = "Your " + reportType + " for " + dateRange + " for " + AdWordsApp.currentAccount().getName() + " is ready";
var body = "currentSetting.ss<br>You can now add this data to <a href='https://www.optmyzr.com'>Optmyzr</a> or another reporting system.";
MailApp.sendEmail(EMAIL_ADDRESSES, subject, body);
Logger.log("Your report is ready at " + currentSetting.ss);
Logger.log("You can include this in your scheduled Optmyzr reports or another reporting tool.");
} catch (e) {
debug("error: " + e);
}
}
function debug(text) {
if(DEBUG) Logger.log(text);
}
@JBarr83

This comment has been minimized.

Copy link

@JBarr83 JBarr83 commented Jul 11, 2017

When I run, I get "parsing error. Please check your selector. (line 103)"

@jrsmyth728

This comment has been minimized.

Copy link

@jrsmyth728 jrsmyth728 commented Aug 21, 2017

I am getting the same error as stated above. Any recommendations to resolve?

@git-arnaud

This comment has been minimized.

Copy link

@git-arnaud git-arnaud commented Aug 31, 2017

Hi Frederick! Same error as above. Did not find the solution. Thank you in advance for your time.

@RangermanCharly

This comment has been minimized.

Copy link

@RangermanCharly RangermanCharly commented Feb 21, 2018

Hi! Great script, thank you for it! I noticed it only works on the Account level. Is there a way to modify it to run it at the MCC level (including several accounts)? Thank you again!

@doherty192

This comment has been minimized.

Copy link

@doherty192 doherty192 commented Mar 13, 2018

Receiving the same parsing error on the "var report = AdWordsApp.report(query);: line.

Any suggestions?

@doherty192

This comment has been minimized.

Copy link

@doherty192 doherty192 commented Mar 15, 2018

Figured this out...

Be sure to choose the headings you'd like to populate FIRST on the spreadsheet.

I thought that the script would auto populate those spreadsheet headings, but no. You must first specify which spreadsheet heading you'd like populated.

Script works great.

@jonast75

This comment has been minimized.

Copy link

@jonast75 jonast75 commented Mar 13, 2019

Hi! Great script, thank you for it! I noticed it only works on the Account level. Is there a way to modify it to run it at the MCC level (including several accounts)? Thank you again!

Hi Frederick, thank you very much for this script!
I second the question from RangermanCharly, I would like to install it on MCC level as well but did not get it to work. Do you have any advice?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.