// Create a report in a Google spreadsheet with performance data by ad component	

// Metrics for each unique headline, description, path, and visible URL are aggregated	

// Use this data to find the best ad components for use in new ads or responsive search ads	

// Free AdWords Script courtesy of Optmyzr.com - try Optmyzr for more PPC tools and scripts	

// January 2019	

function main() {	

var currentSetting = {};	

currentSetting.spreadsheetUrl = "NEW";	

currentSetting.time = "LAST_30_DAYS";	

currentSetting.accountManagers = "example@example.com";	

currentSetting.emailAddresses = "example@example.com";	

currentSetting.campaignNameIncludesIgnoreCase = ""; // e.g. 'search';	

var currencyCode = "$";	

//END OF EDIT-SECTION	

var DEBUG = 1; 	

if(currentSetting.campaignNameIncludesIgnoreCase.indexOf("'") != -1) {	

var campaignSelectorString = 'AND CampaignName CONTAINS_IGNORE_CASE ' + '"' + currentSetting.campaignNameIncludesIgnoreCase + '"';	

} else {	

var campaignSelectorString = "AND CampaignName CONTAINS_IGNORE_CASE " + "'" + currentSetting.campaignNameIncludesIgnoreCase + "'";	

}	

if(DEBUG == 1) Logger.log("spreadsheet URL: " + currentSetting.spreadsheetUrl);	

var reportDate = new Date();	

var dateForFilename = reportDate.yyyymmdd();	

if(currentSetting.spreadsheetUrl.toLowerCase().indexOf("new") != -1)	

{	

var spreadsheet = SpreadsheetApp.create("Ad Template Report - " + AdWordsApp.currentAccount().getName() + " - " + dateForFilename + " (" + currentSetting.time + ")");	

currentSetting.spreadsheetUrl = spreadsheet.getUrl();	

} 	

var spreadsheet = SpreadsheetApp.openByUrl(currentSetting.spreadsheetUrl);	

currentSetting.accountManagersArray = currentSetting.accountManagers.replace(/\s/g, "").split(",");	

spreadsheet.addEditors(currentSetting.accountManagersArray);	

//get all sheets except first and delete them and insert new sheets every time to avoid name error	

var allSheets = spreadsheet.getSheets();	

for(var i=1,len=allSheets.length;i<len;i++){	

spreadsheet.deleteSheet(allSheets[i]);	

}	

	

	

// EXPANDED TEXT ADS	

var expandedTextAdSheet = spreadsheet.insertSheet("Expanded Text Ads");;	

expandedTextAdSheet.clear();	

expandedTextAdSheet.appendRow(["Headline1", "Headline2", "Headline3", "Description", "Description2", "Clicks", "Impressions", "Cost ("+currencyCode+")", "Conversions", "CTR", "Avg. CPC ("+currencyCode+")", "CPA ("+currencyCode+")", "ROAS", "Conv. Value ("+currencyCode+")"]);	

expandedTextAdSheet.setFrozenRows(1);	

expandedTextAdSheet.getRange("A1:O1").setFontWeight("bold");	

var headline1Sheet = spreadsheet.insertSheet("Headline 1");	

headline1Sheet.appendRow(["Headline1", "Clicks", "Impressions", "Cost ("+currencyCode+")", "Conversions", "CTR", "Avg. CPC ("+currencyCode+")", "CPA ("+currencyCode+")", "ROAS", "Conv. Value ("+currencyCode+")"]);	

headline1Sheet.setFrozenRows(1);	

headline1Sheet.getRange("A1:L1").setFontWeight("bold");	

var headline2Sheet = spreadsheet.insertSheet("Headline 2");	

headline2Sheet.appendRow(["Headline2", "Clicks", "Impressions", "Cost ("+currencyCode+")", "Conversions", "CTR", "Avg. CPC ("+currencyCode+")", "CPA ("+currencyCode+")", "ROAS", "Conv. Value ("+currencyCode+")"]);	

headline2Sheet.setFrozenRows(1);	

headline2Sheet.getRange("A1:L1").setFontWeight("bold");	

var headline3Sheet = spreadsheet.insertSheet("Headline 3");	

headline3Sheet.appendRow(["Headline3", "Clicks", "Impressions", "Cost ("+currencyCode+")", "Conversions", "CTR", "Avg. CPC ("+currencyCode+")", "CPA ("+currencyCode+")", "ROAS", "Conv. Value ("+currencyCode+")"]);	

headline3Sheet.setFrozenRows(1);	

headline3Sheet.getRange("A1:L1").setFontWeight("bold");	

var descriptionSheet = spreadsheet.insertSheet("Description");	

descriptionSheet.appendRow(["Description", "Clicks", "Impressions", "Cost ("+currencyCode+")", "Conversions", "CTR", "Avg. CPC ("+currencyCode+")", "CPA ("+currencyCode+")", "ROAS", "Conv. Value ("+currencyCode+")"]);	

descriptionSheet.setFrozenRows(1);	

descriptionSheet.getRange("A1:L1").setFontWeight("bold");	

var description2Sheet = spreadsheet.insertSheet("Description 2");	

description2Sheet.appendRow(["Description2", "Clicks", "Impressions", "Cost ("+currencyCode+")", "Conversions", "CTR", "Avg. CPC ("+currencyCode+")", "CPA ("+currencyCode+")", "ROAS", "Conv. Value ("+currencyCode+")"]);	

description2Sheet.setFrozenRows(1);	

description2Sheet.getRange("A1:L1").setFontWeight("bold");	

var path1Sheet = spreadsheet.insertSheet("Path1");	

path1Sheet.appendRow(["Path1", "Clicks", "Impressions", "Cost ("+currencyCode+")", "Conversions", "CTR", "Avg. CPC ("+currencyCode+")", "CPA ("+currencyCode+")", "ROAS", "Conv. Value ("+currencyCode+")"]);	

path1Sheet.setFrozenRows(1);	

path1Sheet.getRange("A1:L1").setFontWeight("bold");	

var path2Sheet = spreadsheet.insertSheet("Path2");	

path2Sheet.appendRow(["Path2", "Clicks", "Impressions", "Cost ("+currencyCode+")", "Conversions", "CTR", "Avg. CPC ("+currencyCode+")", "CPA ("+currencyCode+")", "ROAS", "Conv. Value ("+currencyCode+")"]);	

path2Sheet.setFrozenRows(1);	

path2Sheet.getRange("A1:L1").setFontWeight("bold");	

var pathSheet = spreadsheet.insertSheet("Path Combinations");	

pathSheet.appendRow(["Path1", "Path2", "Clicks", "Impressions", "Cost ("+currencyCode+")", "Conversions", "CTR", "Avg. CPC ("+currencyCode+")", "CPA ("+currencyCode+")", "ROAS", "Conv. Value ("+currencyCode+")"]);	

pathSheet.setFrozenRows(1);	

pathSheet.getRange("A1:L1").setFontWeight("bold");	

	

var expandedTextAdList = new Array();	

var expandedTextAdKeys = new Array();	

var headline1List = new Array();	

var headline2List = new Array();	

var headline3List = new Array();	

var descriptionList = new Array();	

var description2List = new Array();	

var path1List = new Array();	

var path2List = new Array();	

var pathList = new Array();	

var pathKeys = new Array();	

if(currentSetting.campaignNameIncludesIgnoreCase) { 	

var query = 'SELECT HeadlinePart1, HeadlinePart2, ExpandedTextAdHeadlinePart3, Description, ExpandedTextAdDescription2, Path1, Path2, Clicks, Impressions, Cost, Conversions, ConversionValue ' +	

//var query = 'SELECT HeadlinePart1, HeadlinePart2, Description, Path1, Path2, Clicks, Impressions, Cost, Conversions, ConversionValue ' +	

'FROM AD_PERFORMANCE_REPORT ' +	

'WHERE Cost > 0 ' +	

'AND AdType = ' + "'EXPANDED_TEXT_AD'" + ' ' +	

campaignSelectorString + ' ' +	

'DURING ' + currentSetting.time;	

} else {	

var query = 'SELECT HeadlinePart1, HeadlinePart2, ExpandedTextAdHeadlinePart3, Description, ExpandedTextAdDescription2, Path1, Path2, Clicks, Impressions, Cost, Conversions, ConversionValue ' +	

//var query = 'SELECT HeadlinePart1, HeadlinePart2, Description, Path1, Path2, Clicks, Impressions, Cost, Conversions, ConversionValue ' +	

'FROM AD_PERFORMANCE_REPORT ' +	

'WHERE Cost > 0 ' +	

'AND AdType = ' + "'EXPANDED_TEXT_AD'" + ' ' +	

'DURING ' + currentSetting.time;	

}	

//var report = AdWordsApp.report(query,{apiVersion:apiVersions.getApiVersion()});	

var report = AdsApp.report(query, {	

apiVersion: 'v201809'	

});	

var rows = report.rows();	

Logger.log(query);	

while(rows.hasNext()) {	

var row = rows.next();	

var clicks = parseInt(row['Clicks']);	

var impressions = parseInt(row['Impressions']);	

var cost = parseFloat(row['Cost'].replace(/,/g,""));	

var conversions = parseInt(row['Conversions'].replace(/,/g,""));	

var conversionValue = parseFloat(row['ConversionValue'].replace(/,/g,""));	

var headline1 = row['HeadlinePart1'];	

var headline2 = row['HeadlinePart2'];	

var headline3 = row['ExpandedTextAdHeadlinePart3'];	

var description = row['Description'];	

var description2 = row['ExpandedTextAdDescription2'];	

var path1 = row['Path1'];	

var path2 = row['Path2'];	

var adFootPrint = headline1 + "--n--" + headline2 + "--n--" + headline3 + "--n--" + description + "--n--" + description2;	

//var adFootPrint = headline1 + "--n--" + headline2 + "--n--" + "" + "--n--" + description + "--n--" + "";	

var pathFootPrint = path1 + "--n--" + path2;	

if(!expandedTextAdList[adFootPrint]) {	

expandedTextAdList[adFootPrint] = new Object();	

expandedTextAdList[adFootPrint].clicks = clicks;	

expandedTextAdList[adFootPrint].impressions = impressions;	

expandedTextAdList[adFootPrint].cost = cost;	

expandedTextAdList[adFootPrint].conversions = conversions;	

expandedTextAdList[adFootPrint].conversionValue = conversionValue;	

expandedTextAdKeys.push(adFootPrint);	

} else {	

expandedTextAdList[adFootPrint].clicks += clicks;	

expandedTextAdList[adFootPrint].impressions += impressions;	

expandedTextAdList[adFootPrint].cost += cost;	

expandedTextAdList[adFootPrint].conversions += conversions;	

expandedTextAdList[adFootPrint].conversionValue += conversionValue;	

}	

if(!headline1List[headline1]) {	

headline1List[headline1] = new Object();	

headline1List[headline1].clicks = clicks;	

headline1List[headline1].impressions = impressions;	

headline1List[headline1].cost = cost;	

headline1List[headline1].conversions = conversions;	

headline1List[headline1].conversionValue = conversionValue;	

} else {	

headline1List[headline1].clicks += clicks;	

headline1List[headline1].impressions += impressions;	

headline1List[headline1].cost += cost;	

headline1List[headline1].conversions += conversions;	

headline1List[headline1].conversionValue += conversionValue;	

}	

if(!headline2List[headline2]) {	

headline2List[headline2] = new Object();	

headline2List[headline2].clicks = clicks;	

headline2List[headline2].impressions = impressions;	

headline2List[headline2].cost = cost;	

headline2List[headline2].conversions = conversions;	

headline2List[headline2].conversionValue = conversionValue;	

} else {	

headline2List[headline2].clicks += clicks;	

headline2List[headline2].impressions += impressions;	

headline2List[headline2].cost += cost;	

headline2List[headline2].conversions += conversions;	

headline2List[headline2].conversionValue += conversionValue;	

}	

	

if(!headline3List[headline3]) {	

headline3List[headline3] = new Object();	

headline3List[headline3].clicks = clicks;	

headline3List[headline3].impressions = impressions;	

headline3List[headline3].cost = cost;	

headline3List[headline3].conversions = conversions;	

headline3List[headline3].conversionValue = conversionValue;	

} else {	

headline3List[headline3].clicks += clicks;	

headline3List[headline3].impressions += impressions;	

headline3List[headline3].cost += cost;	

headline3List[headline3].conversions += conversions;	

headline3List[headline3].conversionValue += conversionValue;	

}	

	

if(!descriptionList[description]) {	

descriptionList[description] = new Object();	

descriptionList[description].clicks = clicks;	

descriptionList[description].impressions = impressions;	

descriptionList[description].cost = cost;	

descriptionList[description].conversions = conversions;	

descriptionList[description].conversionValue = conversionValue;	

} else {	

descriptionList[description].clicks += clicks;	

descriptionList[description].impressions += impressions;	

descriptionList[description].cost += cost;	

descriptionList[description].conversions += conversions;	

descriptionList[description].conversionValue += conversionValue;	

}	

	

if(!description2List[description2]) {	

description2List[description2] = new Object();	

description2List[description2].clicks = clicks;	

description2List[description2].impressions = impressions;	

description2List[description2].cost = cost;	

description2List[description2].conversions = conversions;	

description2List[description2].conversionValue = conversionValue;	

} else {	

description2List[description2].clicks += clicks;	

description2List[description2].impressions += impressions;	

description2List[description2].cost += cost;	

description2List[description2].conversions += conversions;	

description2List[description2].conversionValue += conversionValue;	

}	

	

if(!path1List[path1]) {	

path1List[path1] = new Object();	

path1List[path1].clicks = clicks;	

path1List[path1].impressions = impressions;	

path1List[path1].cost = cost;	

path1List[path1].conversions = conversions;	

path1List[path1].conversionValue = conversionValue;	

} else {	

path1List[path1].clicks += clicks;	

path1List[path1].impressions += impressions;	

path1List[path1].cost += cost;	

path1List[path1].conversions += conversions;	

path1List[path1].conversionValue += conversionValue;	

}	

if(!path2List[path2]) {	

path2List[path2] = new Object();	

path2List[path2].clicks = clicks;	

path2List[path2].impressions = impressions;	

path2List[path2].cost = cost;	

path2List[path2].conversions = conversions;	

path2List[path2].conversionValue = conversionValue;	

} else {	

path2List[path2].clicks += clicks;	

path2List[path2].impressions += impressions;	

path2List[path2].cost += cost;	

path2List[path2].conversions += conversions;	

path2List[path2].conversionValue += conversionValue;	

}	

if(!pathList[pathFootPrint]) {	

pathList[pathFootPrint] = new Object();	

pathList[pathFootPrint].clicks = clicks;	

pathList[pathFootPrint].impressions = impressions;	

pathList[pathFootPrint].cost = cost;	

pathList[pathFootPrint].conversions = conversions;	

pathList[pathFootPrint].conversionValue = conversionValue;	

pathKeys.push(pathFootPrint);	

} else {	

pathList[pathFootPrint].clicks += clicks;	

pathList[pathFootPrint].impressions += impressions;	

pathList[pathFootPrint].cost += cost;	

pathList[pathFootPrint].conversions += conversions;	

pathList[pathFootPrint].conversionValue += conversionValue;	

}	

}	

//if(DEBUG == 1) Logger.log(expandedTextAdKeys.length + " Unique Ads Evaluated");	

for(var headline1 in headline1List) {	

var stats = headline1List[headline1];	

var clicks = stats.clicks;	

var impressions = stats.impressions;	

var cost = stats.cost;	

var conversions = stats.conversions;	

var conversionValue = stats.conversionValue;	

var ctr = clicks / impressions;	

var avgCpc = cost / clicks;	

var roas = conversionValue / cost;	

var cpa = cost / conversions;	

headline1Sheet.appendRow([headline1, clicks, impressions, cost, conversions, ctr, avgCpc, cpa, roas, conversionValue]);	

}	

var numRows = headline1Sheet.getDataRange().getNumRows();	

headline1Sheet.getRange(2, 2, numRows, 1).setNumberFormat("#,##0");	

headline1Sheet.getRange(2, 3, numRows, 1).setNumberFormat("#,##0");	

headline1Sheet.getRange(2, 4, numRows, 1).setNumberFormat("#,##0.00");	

headline1Sheet.getRange(2, 5, numRows, 1).setNumberFormat("#,##0");	

headline1Sheet.getRange(2, 6, numRows, 1).setNumberFormat("0.00%");	

headline1Sheet.getRange(2, 7, numRows, 1).setNumberFormat("#,##0.00");	

headline1Sheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00");	

headline1Sheet.getRange(2, 9, numRows, 1).setNumberFormat("0.00%");	

headline1Sheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00");	

	

for(var headline2 in headline2List) {	

var stats = headline2List[headline2];	

var clicks = stats.clicks;	

var impressions = stats.impressions;	

var cost = stats.cost;	

var conversions = stats.conversions;	

var conversionValue = stats.conversionValue;	

var ctr = clicks / impressions;	

var avgCpc = cost / clicks;	

var roas = conversionValue / cost;	

var cpa = cost / conversions;	

headline2Sheet.appendRow([headline2, clicks, impressions, cost, conversions, ctr, avgCpc, cpa, roas, conversionValue]);	

}	

var numRows = headline2Sheet.getDataRange().getNumRows();	

headline2Sheet.getRange(2, 2, numRows, 1).setNumberFormat("#,##0");	

headline2Sheet.getRange(2, 3, numRows, 1).setNumberFormat("#,##0");	

headline2Sheet.getRange(2, 4, numRows, 1).setNumberFormat("#,##0.00");	

headline2Sheet.getRange(2, 5, numRows, 1).setNumberFormat("#,##0");	

headline2Sheet.getRange(2, 6, numRows, 1).setNumberFormat("0.00%");	

headline2Sheet.getRange(2, 7, numRows, 1).setNumberFormat("#,##0.00");	

headline2Sheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00");	

headline2Sheet.getRange(2, 9, numRows, 1).setNumberFormat("0.00%");	

headline2Sheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00");	

	

for(var headline3 in headline3List) {	

var stats = headline3List[headline3];	

var clicks = stats.clicks;	

var impressions = stats.impressions;	

var cost = stats.cost;	

var conversions = stats.conversions;	

var conversionValue = stats.conversionValue;	

var ctr = clicks / impressions;	

var avgCpc = cost / clicks;	

var roas = conversionValue / cost;	

var cpa = cost / conversions;	

headline3Sheet.appendRow([headline3, clicks, impressions, cost, conversions, ctr, avgCpc, cpa, roas, conversionValue]);	

}	

var numRows = headline3Sheet.getDataRange().getNumRows();	

headline3Sheet.getRange(2, 2, numRows, 1).setNumberFormat("#,##0");	

headline3Sheet.getRange(2, 3, numRows, 1).setNumberFormat("#,##0");	

headline3Sheet.getRange(2, 4, numRows, 1).setNumberFormat("#,##0.00");	

headline3Sheet.getRange(2, 5, numRows, 1).setNumberFormat("#,##0");	

headline3Sheet.getRange(2, 6, numRows, 1).setNumberFormat("0.00%");	

headline3Sheet.getRange(2, 7, numRows, 1).setNumberFormat("#,##0.00");	

headline3Sheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00");	

headline3Sheet.getRange(2, 9, numRows, 1).setNumberFormat("0.00%");	

headline3Sheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00");	

	

for(var description in descriptionList) {	

var stats = descriptionList[description];	

var clicks = stats.clicks;	

var impressions = stats.impressions;	

var cost = stats.cost;	

var conversions = stats.conversions;	

var conversionValue = stats.conversionValue;	

var ctr = clicks / impressions;	

var avgCpc = cost / clicks;	

var roas = conversionValue / cost;	

var cpa = cost / conversions;	

descriptionSheet.appendRow([description, clicks, impressions, cost, conversions, ctr, avgCpc, cpa, roas, conversionValue]);	

}	

var numRows = descriptionSheet.getDataRange().getNumRows();	

descriptionSheet.getRange(2, 2, numRows, 1).setNumberFormat("#,##0");	

descriptionSheet.getRange(2, 3, numRows, 1).setNumberFormat("#,##0");	

descriptionSheet.getRange(2, 4, numRows, 1).setNumberFormat("#,##0.00");	

descriptionSheet.getRange(2, 5, numRows, 1).setNumberFormat("#,##0");	

descriptionSheet.getRange(2, 6, numRows, 1).setNumberFormat("0.00%");	

descriptionSheet.getRange(2, 7, numRows, 1).setNumberFormat("#,##0.00");	

descriptionSheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00");	

descriptionSheet.getRange(2, 9, numRows, 1).setNumberFormat("0.00%");	

descriptionSheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00");	

	

for(var description2 in description2List) {	

var stats = description2List[description2];	

var clicks = stats.clicks;	

var impressions = stats.impressions;	

var cost = stats.cost;	

var conversions = stats.conversions;	

var conversionValue = stats.conversionValue;	

var ctr = clicks / impressions;	

var avgCpc = cost / clicks;	

var roas = conversionValue / cost;	

var cpa = cost / conversions;	

description2Sheet.appendRow([description2, clicks, impressions, cost, conversions, ctr, avgCpc, cpa, roas, conversionValue]);	

}	

var numRows = description2Sheet.getDataRange().getNumRows();	

description2Sheet.getRange(2, 2, numRows, 1).setNumberFormat("#,##0");	

description2Sheet.getRange(2, 3, numRows, 1).setNumberFormat("#,##0");	

description2Sheet.getRange(2, 4, numRows, 1).setNumberFormat("#,##0.00");	

description2Sheet.getRange(2, 5, numRows, 1).setNumberFormat("#,##0");	

description2Sheet.getRange(2, 6, numRows, 1).setNumberFormat("0.00%");	

description2Sheet.getRange(2, 7, numRows, 1).setNumberFormat("#,##0.00");	

description2Sheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00");	

description2Sheet.getRange(2, 9, numRows, 1).setNumberFormat("0.00%");	

description2Sheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00");	

	

for(var path in path1List) {	

var stats = path1List[path];	

var clicks = stats.clicks;	

var impressions = stats.impressions;	

var cost = stats.cost;	

var conversions = stats.conversions;	

var conversionValue = stats.conversionValue;	

var ctr = clicks / impressions;	

var avgCpc = cost / clicks;	

var roas = conversionValue / cost;	

var cpa = cost / conversions;	

path1Sheet.appendRow([path, clicks, impressions, cost, conversions, ctr, avgCpc, cpa, roas, conversionValue]);	

}	

var numRows = path1Sheet.getDataRange().getNumRows();	

path1Sheet.getRange(2, 2, numRows, 1).setNumberFormat("#,##0");	

path1Sheet.getRange(2, 3, numRows, 1).setNumberFormat("#,##0");	

path1Sheet.getRange(2, 4, numRows, 1).setNumberFormat("#,##0.00");	

path1Sheet.getRange(2, 5, numRows, 1).setNumberFormat("#,##0");	

path1Sheet.getRange(2, 6, numRows, 1).setNumberFormat("0.00%");	

path1Sheet.getRange(2, 7, numRows, 1).setNumberFormat("#,##0.00");	

path1Sheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00");	

path1Sheet.getRange(2, 9, numRows, 1).setNumberFormat("0.00%");	

path1Sheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00");	

	

for(var path in path2List) {	

var stats = path2List[path];	

var clicks = stats.clicks;	

var impressions = stats.impressions;	

var cost = stats.cost;	

var conversions = stats.conversions;	

var conversionValue = stats.conversionValue;	

var ctr = clicks / impressions;	

var avgCpc = cost / clicks;	

var roas = conversionValue / cost;	

var cpa = cost / conversions;	

path2Sheet.appendRow([path, clicks, impressions, cost, conversions, ctr, avgCpc, cpa, roas, conversionValue]);	

}	

var numRows = path2Sheet.getDataRange().getNumRows();	

path2Sheet.getRange(2, 2, numRows, 1).setNumberFormat("#,##0");	

path2Sheet.getRange(2, 3, numRows, 1).setNumberFormat("#,##0");	

path2Sheet.getRange(2, 4, numRows, 1).setNumberFormat("#,##0.00");	

path2Sheet.getRange(2, 5, numRows, 1).setNumberFormat("#,##0");	

path2Sheet.getRange(2, 6, numRows, 1).setNumberFormat("0.00%");	

path2Sheet.getRange(2, 7, numRows, 1).setNumberFormat("#,##0.00");	

path2Sheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00");	

path2Sheet.getRange(2, 9, numRows, 1).setNumberFormat("0.00%");	

path2Sheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00");	

	

for(var i=0; i < expandedTextAdKeys.length; i++) {	

var adKey = expandedTextAdKeys[i];	

var ad = expandedTextAdList[adKey];	

var adParts = adKey.split("--n--");	

var headline1 = adParts[0];	

var headline2 = adParts[1];	

var headline3 = adParts[2]	

var description = adParts[3];	

var description2 = adParts[4];	

var clicks = ad.clicks;	

var impressions = ad.impressions;	

var cost = ad.cost;	

var conversions = ad.conversions;	

var conversionValue = ad.conversionValue;	

if(conversions) {	

var roas = conversionValue / cost;	

var cpa = cost / conversions;	

} else {	

var roas = 0;	

var cpa = 0;	

}	

var ctr = clicks / impressions;	

var avgCpc = cost/ clicks;	

//if(DEBUG == 1) Logger.log(adKey + " " + clicks + " " + impressions + " " + cost);	

if(DEBUG == 1) Logger.log(adKey + " clicks: " + clicks);	

expandedTextAdSheet.appendRow([headline1, headline2, headline3, description, description2, clicks, impressions, cost, conversions, ctr, avgCpc, cpa, roas, conversionValue]);	

}	

var numRows = expandedTextAdSheet.getDataRange().getNumRows();	

expandedTextAdSheet.getRange(2, 6, numRows, 1).setNumberFormat("#,##0");	

expandedTextAdSheet.getRange(2, 7, numRows, 1).setNumberFormat("#,##0");	

expandedTextAdSheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00");	

expandedTextAdSheet.getRange(2, 9, numRows, 1).setNumberFormat("#,##0");	

expandedTextAdSheet.getRange(2, 10, numRows, 1).setNumberFormat("0.00%");	

expandedTextAdSheet.getRange(2, 11, numRows, 1).setNumberFormat("#,##0.00");	

expandedTextAdSheet.getRange(2, 12, numRows, 1).setNumberFormat("#,##0.00");	

expandedTextAdSheet.getRange(2, 13, numRows, 1).setNumberFormat("0.00%");	

expandedTextAdSheet.getRange(2, 14, numRows, 1).setNumberFormat("#,##0.00");	

	

for(var i=0; i < pathKeys.length; i++) {	

var pathKey = pathKeys[i];	

var unit = pathList[pathKey];	

var parts = pathKey.split("--n--");	

var path1 = parts[0];	

var path2 = parts[1];	

var clicks = unit.clicks;	

var impressions = unit.impressions;	

var cost = unit.cost;	

var conversions = unit.conversions;	

var conversionValue = unit.conversionValue;	

if(conversions) {	

var roas = conversionValue / cost;	

var cpa = cost / conversions;	

} else {	

var roas = 0;	

var cpa = 0;	

}	

var ctr = clicks / impressions;	

var avgCpc = cost/ clicks;	

//if(DEBUG == 1) Logger.log(pathKey + " " + clicks + " " + impressions + " " + cost);	

if(DEBUG == 1) Logger.log(pathKey + " clicks: " + clicks);	

pathSheet.appendRow([path1, path2, clicks, impressions, cost, conversions, ctr, avgCpc, cpa, roas, conversionValue]);	

}	

var numRows = pathSheet.getDataRange().getNumRows();	

pathSheet.getRange(2, 3, numRows, 1).setNumberFormat("#,##0");	

pathSheet.getRange(2, 4, numRows, 1).setNumberFormat("#,##0");	

pathSheet.getRange(2, 5, numRows, 1).setNumberFormat("#,##0.00");	

pathSheet.getRange(2, 6, numRows, 1).setNumberFormat("#,##0");	

pathSheet.getRange(2, 7, numRows, 1).setNumberFormat("0.00%");	

pathSheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00");	

pathSheet.getRange(2, 9, numRows, 1).setNumberFormat("#,##0.00");	

pathSheet.getRange(2, 10, numRows, 1).setNumberFormat("0.00%");	

pathSheet.getRange(2, 11, numRows, 1).setNumberFormat("#,##0.00");	

// EMAIL	

var remainingQuota = MailApp.getRemainingDailyQuota();	

currentSetting.emailAddressesArray = currentSetting.emailAddresses.replace(/\s/g, "").split(",");	

if(remainingQuota > currentSetting.emailAddressesArray.length) {	

var subject = "Ad Template Report for " + AdWordsApp.currentAccount().getName() + " is ready";	

var body = "Visit this link for the ad template report for CID " + AdWordsApp.currentAccount().getCustomerId() + ": <a href=\""+ currentSetting.spreadsheetUrl+"\" target=\"_blank\">link</a>";	

MailApp.sendEmail({	

to:currentSetting.emailAddresses, 	

subject: subject,	

htmlBody: body	

});	

}	

currentSetting.notes = "Download Report: : <a href=\""+ currentSetting.spreadsheetUrl+"\" target=\"_blank\">link</a>";	

return(currentSetting.notes);	

}	

	

	

Date.prototype.yyyymmdd = function() {	

var yyyy = this.getFullYear().toString();	

var mm = (this.getMonth()+1).toString(); // getMonth() is zero-based	

var dd = this.getDate().toString();	

return yyyy + (mm[1]?mm:"0"+mm[0]) + (dd[1]?dd:"0"+dd[0]); // padding	

};