AdWords
1.8K members online now
1.8K members online now
Use AdWords conversion tracking and reporting to measure your results. Have a question about Google Analytics? Post it here, on the Google Analytics Community!
Guide Me
star_border
Reply

Account Summary Report script help

[ Edited ]
Follower ✭ ☆ ☆
# 1
Follower ✭ ☆ ☆

I replace the URL of the Code with a new one

but

SPREADSHEET dont give me the data of my Account what i must check or i must do?
i am asking here becase i iam young and new and i search for this 5 days now and i didnt find or perhaps i didnt understund if someone can help me.!!!
Preview completed
the code work but the SPREADSHEET dont show me give me data
2 Expert replyverified_user
Marked as Best Answer.
Solution
Accepted by topic author kostas x
September 2015

Re: Account Summary Report script help

Rising Star
# 2
Rising Star
Hello Kostas, make sure 'last check' mathes with date which you are willing to get statistic for.
Cheers, Alexey
UAWC Agency

Re: Account Summary Report script help

Top Contributor
# 3
Top Contributor

Hello,

 

@Jon_Gritton might assist you with this.

 

As an alternative, did you try posting this over dedicated AdWords script forum for more clarity. Here is the link:-

 

https://groups.google.com/forum/#!forum/adwords-scripts

 

Pankaj

Re: Account Summary Report script help

Follower ✭ ☆ ☆
# 4
Follower ✭ ☆ ☆
hello alexey thank you for your time
i must change something here you mean var yesterday = getYesterday();
var last_check = spreadsheet.getRangeByName('last_check').getValue();
// Checks every day from last day checked to yesterday inclusive. If there
// isn't a last date checked, checks yesterday.
var date;
if (last_check.length == 0) {
date = new Date(yesterday);
} else {
date = new Date(last_check);
date.setDate(date.getDate() + 1);
}

Re: Account Summary Report script help

Follower ✭ ☆ ☆
# 5
Follower ✭ ☆ ☆
hello pankaj1782 thank you for your help i will do right now Smiley Very Happy
Marked as Best Answer.
Solution
Accepted by topic author kostas x
September 2015

Re: Account Summary Report script help

[ Edited ]
Rising Star
# 6
Rising Star

You need to specify last check date inside your google spread**bleep**.
Here is a code from one of my old accounts (it's still working well).

<edited by a Community Manager as noted below>

 
function main() {

var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
spreadsheet.getRangeByName("account_id_report").setValue(AdWordsApp.currentAccount().getCustomerId());

var yesterday = getYesterday();
var last_check = spreadsheet.getRangeByName("last_check").getValue();
// Checks every day from last day checked to yesterday inclusive. If there isn't a last date checked,
// checks yesterday.
var date;
if (last_check.length == 0) {
date = new Date(yesterday);
} else {
date = new Date(last_check);
date.setDate(date.getDate() + 1);
}

var rows = [];

while (date.getTime() <= yesterday.getTime()) {
var row = getReportRowForDate(date);
rows.push([new Date(date), row['Cost'], row['AverageCpc'], row['Ctr'], row['AveragePosition'], row['Impressions'], row['Clicks']]);
date.setDate(date.getDate() + 1);
}

if (rows.length > 0) {
var access = new SpreadsheetAccess(SPREADSHEET_URL, "Report");
var emptyRow = access.findEmptyRow(6, 2);
if (emptyRow < 0) {
access.addRows(rows.length);
emptyRow = access.findEmptyRow(6, 2);
}
access.writeRows(rows, emptyRow, 2);

var last_check = spreadsheet.getRangeByName("last_check").setValue(yesterday);
var email = spreadsheet.getRangeByName("email").getValue();

if (email) {
sendEmail(email);
}
}
}

function sendEmail(email) {
var day = getYesterday();
var yesterdayRow = getReportRowForDate(day);
day.setDate(day.getDate() - 1);
var twoDaysAgoRow = getReportRowForDate(day);
day.setDate(day.getDate() - 5);
var weekAgoRow = getReportRowForDate(day);

var html = [];
html.push(
"<html>",
"<body>",
"<table width=800 cellpadding=0 border=0 cellspacing=0>",
"<tr>",
"<td colspan=2 align=right>",
"<div style='font: italic normal 10pt Times New Roman, serif; margin: 0; color: #666; padding-right: 5px;'>Powered by AdWords Scripts</div>",
"</td>",
"</tr>",
"<tr bgcolor='#3c78d8'>",
"<td width=500>",
"<div style='font: normal 18pt verdana, sans-serif; padding: 3px 10px; color: white'>Account Summary report</div>",
"</td>",
"<td align=right>",
"<div style='font: normal 18pt verdana, sans-serif; padding: 3px 10px; color: white'>", AdWordsApp.currentAccount().getCustomerId(), "</h1>",
"</td>",
"</tr>",
"</table>",
"<table width=800 cellpadding=0 border=0 cellspacing=0>",
"<tr bgcolor='#ddd'>",
"<td></td>",
"<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5px 5px; background-color: #ddd; text-align: left'>Yesterday</td>",
"<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5px 5px; background-color: #ddd; text-align: left'>Two Days Ago</td>",
"<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5x 5px; background-color: #ddd; text-align: left'>A week ago</td>",
"</tr>",
emailRow('Cost', 'Cost', yesterdayRow, twoDaysAgoRow, weekAgoRow),
emailRow('Average Cpc', 'AverageCpc', yesterdayRow, twoDaysAgoRow, weekAgoRow),
emailRow('Ctr', 'Ctr', yesterdayRow, twoDaysAgoRow, weekAgoRow),
emailRow('Average Position', 'AveragePosition', yesterdayRow, twoDaysAgoRow, weekAgoRow),
emailRow('Impressions', 'Impressions', yesterdayRow, twoDaysAgoRow, weekAgoRow),
emailRow('Clicks', 'Clicks', yesterdayRow, twoDaysAgoRow, weekAgoRow),
"</table>",
"</body>",
"</html>");
MailApp.sendEmail(email, "AdWords Account " + AdWordsApp.currentAccount().getCustomerId() + " Summary Report", "", {htmlBody: html.join("\n")});
}

function emailRow(title, column, yesterdayRow, twoDaysAgoRow, weekAgoRow) {
return "<tr> \
<td style='padding: 5px 10px'>" + title + "</td> \
<td style='padding: 0px 10px'>" + yesterdayRow[column] + "</td> \
<td style='padding: 0px 10px'>" + twoDaysAgoRow[column] + formatChangeString(yesterdayRow[column], twoDaysAgoRow[column]) + "</td> \
<td style='padding: 0px 10px'>" + weekAgoRow[column] + formatChangeString(yesterdayRow[column], weekAgoRow[column]) + "</td> \
</tr>";
}

// returns noon in the timezone of the account
function getYesterday() {
var now = new Date(Utilities.formatDate(new Date(),
AdWordsApp.currentAccount().getTimeZone(), "MMM dd,yyyy HH:mm:ss"));
var yesterday = new Date(now.getTime() - 24 * 3600 * 1000);
yesterday.setHours(12);
return yesterday;
}

function getReportRowForDate(date) {
var accountDate = new Date(Utilities.formatDate(date,
AdWordsApp.currentAccount().getTimeZone(), "MMM dd,yyyy HH:mm:ss"));
var dateString = Utilities.formatDate(accountDate, "PST", "yyyyMMdd");
return getReportRowForDuring(dateString + "," + dateString);
}

function getReportRowForDuring(during) {
var report = AdWordsApp.report(
"SELECT Cost, AverageCpc, Ctr, AveragePosition, Impressions, Clicks " +
"FROM ACCOUNT_PERFORMANCE_REPORT " +
"DURING " + during);
return report.rows().next();
}

function formatChangeString(newValue, oldValue) {
var x = newValue.indexOf('%');
if (x != -1) {
newValue = newValue.substring(0, x);
var y = oldValue.indexOf('%');
oldValue = oldValue.substring(0, y);
}

var change = parseFloat(newValue - oldValue).toFixed(2);
var changeString = change;
if (x != -1) {
changeString = change + '%';
}

if (change >= 0) {
return "<span style='color: #38761d; font-size: 8pt'> (+" + changeString + ")</span>";
} else {
return "<span style='color: #cc0000; font-size: 8pt'> (" + changeString + ")</span>";
}
}

function SpreadsheetAccess(spreadsheetUrl, sheetName) {
this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
this.sheet = this.spreadsheet.getSheetByName(sheetName);

// what column should we be looking at to check whether the row is empty?
this.findEmptyRow = function(minRow, column) {
var values = this.sheet.getRange(minRow, column, this.sheet.getMaxRows(), 1).getValues();
for (var i = 0; i < values.length; i ++) {
if (!values[i][0]) {
return i + minRow;
}
}
return -1;
}
this.addRows = function(howMany) {
this.sheet.insertRowsAfter(this.sheet.getMaxRows(), howMany);
}
this.writeRows = function(rows, startRow, startColumn) {
this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).setValues(rows);
}
}

 

Note: this post has been edited by a Community Manager, per Community Posting Guidelines, in order to protect privacy and account security. Please do not link to live spreadsheets or documents in a public forum.

Cheers, Alexey
UAWC Agency

Re: Account Summary Report script help

Follower ✭ ☆ ☆
# 7
Follower ✭ ☆ ☆
Alexey thankkk youu!!!!!! so much something last o copy your templete right now but the code say me Range not found (line 4) Can you say me that correct Ranges becase maybe this is the reason why i put my templete doesnt work
Marked as Best Answer.
Solution
Accepted by topic author kostas x
September 2015

Re: Account Summary Report script help

[ Edited ]
Rising Star
# 8
Rising Star

Kostas, you are welcome. If you set the "Last check" date in the spreadsheet to a date in the past, the script will fill in data between then and now. So please make sure that you have correct 'last check' date. Mine can be too far.

Did you dublicate spreadsheet from this template? <edited by a Community Manager as noted below>

 
Note: this post has been edited by a Community Manager, per Community Posting Guidelines, in order to protect privacy and account security. Please do not link to live spreadsheets or documents in a public forum.
Cheers, Alexey
UAWC Agency

Re: Account Summary Report script help

Follower ✭ ☆ ☆
# 9
Follower ✭ ☆ ☆
yeah i make a copy from this If you set the "Last check" date in the spreadsheet to a date in the past, the script will fill in data between then and now. So please make sure that you have correct 'last check' date. Mine can be too far. I cant understund this yet really and iam so sorry that i am so meddlesome :/

Re: Account Summary Report script help

Follower ✭ ☆ ☆
# 10
Follower ✭ ☆ ☆
if i dublicate your template ? it will be work alexey?