AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Ads
2.1K members online now
2.1K members online now
Dive into advanced features like Remarketing, Flexible Bid Strategies, AdWords Editor, and AdWords Scripts
Guide Me
star_border
Reply

Adwords Script SQR Report (Account Level)

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello community, 

I'm a beginner at Adwords Script and I'm looking to pull an SQR report on an 'Account' level which auto populates into a google spreadsheet which also emails me every time the script runs. I've tried using SQR scripts written by other people but it seems like the code is outdated and currently does not work. The script I tried using was:

 

var DATE_RANGE = 'LAST_14_DAYS';
var IGNORE_EXACT = false;
var TO = ["Email@Email.com"];
var SPREADSHEET_URL = "GoogleSpreedSheetURL";

function main() {
var columns = ['AccountDescriptiveName',
'CampaignName',
'AdGroupName',
'KeywordTextMatchingQuery',
'MatchType',
'Query',
'Device',
'Impressions',
'Clicks',
'Cost',
'Conversions',
'AverageCpc',
'CostPerConversion',
'ConversionRate',
'Ctr'];
var columnsStr = columns.join(',') + " ";

var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
sheet.clear();
sheet.appendRow(columns);

var reportIter = AdWordsApp.report(
'SELECT ' + columnsStr +
'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
'DURING ' + DATE_RANGE, {
includeZeroImpressions: false
}).rows();

while(reportIter.hasNext()) {
var row = reportIter.next();
if(IGNORE_EXACT && row['MatchType'].indexOf('exact') >= 0) { continue; }
var rowArray = [];
for(var i in columns) {
rowArray.push(row[columns[i]]);
}
sheet.appendRow(rowArray);
}

for(var i in TO) {
MailApp.sendEmail(TO[i], "Search Query Report Ready", SPREADSHEET_URL);
}
}

 

I've tried editing this in multiple ways but without success. Please note, the var 'TO' email and 'SPREADSHEET_URL' at the top are filled in with my actually email and spreadsheet url. Any ideas?

Thanks so much in advance!!

1 Expert replyverified_user

Adwords Script SQR Report (Account Level)

Top Contributor
# 2
Top Contributor

Hi J_S_,

I probably can't help you in any case, but do you get an error message? That might help others answer your question.

Best of Luck!

 

Pete

 

petebardo -- Deadhead doing AdWords

Adwords Script SQR Report (Account Level)

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Pete, 

 

The current error that I'm getting when running this script is:

Column 'MatchType' is not valid for report type SEARCH_QUERY_PERFORMANCE_REPORT. Double-check your SELECT clause. (line 28)

Adwords Script SQR Report (Account Level)

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Hi Pete, 

 

I actually got the code to work but now the issue I'm having is the 'Broad Match Modified' terms that are being pulled into google sheets which is giving a '#NAME?' error for the column 'KeywordTextMatchQuery' due to the '+' sign but the term is visible in the formula column. 

 

Here's the script that I have so far:

 

var DATE_RANGE = 'LAST_14_DAYS';
var IGNORE_EXACT = false;
var TO = ["xyz@email.com"];
var SPREADSHEET_URL = "GoogleSheetsUrlLink";

function main() {
var columns = ['AccountDescriptiveName',
'CampaignName',
'AdGroupName',
'KeywordTextMatchingQuery',
'QueryMatchTypeWithVariant',
'Query',
'Device',
'Impressions',
'Clicks',
'Cost',
'Conversions',
'AverageCpc',
'CostPerConversion',
'ConversionRate',
'Ctr'];
var columnsStr = columns.join(',') + " ";

var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
sheet.clear();
sheet.appendRow(columns);

var reportIter = AdWordsApp.report(
'SELECT ' + columnsStr +
'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
'DURING ' + DATE_RANGE, {
includeZeroImpressions: false
}).rows();

while(reportIter.hasNext()) {
var row = reportIter.next();
if(IGNORE_EXACT && row['QueryMatchTypeWithVariant'].indexOf('exact') >= 0) { continue; }
var rowArray = [];
for(var i in columns) {
rowArray.push(row[columns[i]]);
}
sheet.appendRow(rowArray);
}

for(var i in TO) {
MailApp.sendEmail(TO[i], "Search Query Report Ready", SPREADSHEET_URL);
}
}

 

 

So now the last thing I need is to append " ' " in front of the '+' within the 'KeywordTextMatchQuery' column.

 

Thanks again in advance for all the help!