Skip to content
Permalink
Branch: master
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
69 lines (56 sloc) 2.24 KB
//Settings
var SHEET_URL = ""; //place the sheet link here.
var EMAIL_TO = ""; //email adress to send report to.
//Optional Settings
var MAIN_SOURCE_SHEET_NAME = "Source"; //Don't change the name of the sheet if you use standart docs template
var LOW_QS_SHEET_NAME = "LowQS"; //Don't change the name of the sheet if you use standart docs template
//End of Settings section
function main() {
var currentAccount = AdWordsApp.currentAccount();
var mainSourceSheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(MAIN_SOURCE_SHEET_NAME);
var lowQSSourceSheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(LOW_QS_SHEET_NAME);
function createReports() {
var mainReport = AdWordsApp.report(
"SELECT Criteria, CampaignName, Cost, Impressions, Clicks, QualityScore, PostClickQualityScore, CreativeQualityScore, SearchPredictedCtr " +
"FROM KEYWORDS_PERFORMANCE_REPORT " +
"WHERE Impressions > 0 " +
"AND QualityScore > 1 " +
"DURING LAST_30_DAYS");
mainReport.exportToSheet(mainSourceSheet);
var lowQSReport = AdWordsApp.report(
"SELECT Criteria, CampaignName, Cost, Impressions, Clicks, QualityScore " +
"FROM KEYWORDS_PERFORMANCE_REPORT " +
"WHERE Impressions > 0 " +
"AND QualityScore < 4 " +
"DURING LAST_30_DAYS");
lowQSReport.exportToSheet(lowQSSourceSheet);
}
function highlightCells() {
var range = mainSourceSheet.getRange("G:I");
var numRows = range.getNumRows();
var numCols = range.getNumColumns();
for (var i = 1; i <= numRows; i++) {
for (var j = 1; j <= numCols; j++) {
var currentValue = range.getCell(i, j).getValue();
if (currentValue == "Below average") {
range.getCell(i, j).setBackground("lightcoral");
}
}
}
}
function sortSheets() {
mainSourceSheet.sort(4, false);
lowQSSourceSheet.sort(3, false);
}
function sendEmails() {
MailApp.sendEmail(EMAIL_TO, currentAccount.getName() + ": QualityScore Report", "Hello! This is an email report of your account Quality Score. Your report link: " + SHEET_URL);
}
function logger() {
Logger.log("Report sent to " + EMAIL_TO);
}
createReports();
highlightCells();
sendEmails();
sortSheets();
logger();
}
You can’t perform that action at this time.