Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save siliconvallaeys/9b3967340f58738e62329b777e33c6fd to your computer and use it in GitHub Desktop.
Save siliconvallaeys/9b3967340f58738e62329b777e33c6fd to your computer and use it in GitHub Desktop.
Create a Google Ads bulksheet to turn your ads into Responsive Search Ads (RSAs) more easily
/*************************************************
* RSA Suggestions
* @version: 1.2
* @author: Naman Jindal (Optmyzr)
*
* INSTRUCTIONS
* ------------
* Create a copy of the Google spreadsheet at https://docs.google.com/spreadsheets/d/1rLYAFkZDtoyB6bQPJ2eniF-Od-3YSoUXxTb4ZfglQ50/copy
* and enter the URL of your copy
* on the line immediately below that starts with
* var REPORT_URL =
* The data will be written to the tab with the name
* you entered on the line that starts with
* var TAB_NAME =
* The generated sheet is compatible with Google Ads
* bulk uploads. Before uploading, filter to include
* only new ads you want to create.
* Always preview a bulk upload first before applying!
***************************************************/
// Copy the spreadsheet at https://docs.google.com/spreadsheets/d/1rLYAFkZDtoyB6bQPJ2eniF-Od-3YSoUXxTb4ZfglQ50/copy
// into your account and then enter the URL of your own copy of the sheet on the following line:
var REPORT_URL = 'YOUR_OWN_SPREADSHEET URL WITH HEADERS ALREADY SET UP - COPY FROM ABOVE';
var TAB_NAME = 'ads bulk sheet';
function main() {
var adgroupMap = {};
var query = [
'SELECT campaign.name, campaign.id, ad_group.name, ad_group.id, ad_group_ad.ad.id,ad_group_ad.status,metrics.impressions,',
'ad_group_ad.ad.type,ad_group_ad.ad.expanded_text_ad.description,',
'ad_group_ad.ad.expanded_text_ad.description2,',
'ad_group_ad.ad.expanded_text_ad.headline_part1,',
'ad_group_ad.ad.expanded_text_ad.headline_part2,',
'ad_group_ad.ad.expanded_text_ad.headline_part3,',
'ad_group_ad.ad.expanded_text_ad.path1,',
'ad_group_ad.ad.expanded_text_ad.path2,',
'ad_group_ad.ad.final_mobile_urls,',
'ad_group_ad.ad.final_urls',
'FROM ad_group_ad',
'WHERE ad_group_ad.ad.type = EXPANDED_TEXT_AD',
'AND metrics.impressions >= 0',
'AND ad_group.status = ENABLED',
'AND campaign.status = ENABLED',
'AND ad_group_ad.status = ENABLED',
'AND segments.date DURING LAST_30_DAYS ORDER BY metrics.impressions DESC'
].join(' ');
var rows = AdsApp.report(query).rows()
while (rows.hasNext()){
var row = rows.next();
var agId = row['ad_group.id'];
if(!adgroupMap[agId]) {
adgroupMap[agId] = {
'campaign': row['campaign.name'],
'ad_group': row['ad_group.name'],
'ad_status': row['ad_group_ad.status'],
'headlines': {},
'descriptions': {},
'path1': row['ad_group_ad.ad.expanded_text_ad.path1'],
'path2': row['ad_group_ad.ad.expanded_text_ad.path2'],
'url': row['ad_group_ad.ad.final_urls'] ? row['ad_group_ad.ad.final_urls'][0] : '',
'mobileUrl': row['ad_group_ad.ad.final_mobile_urls'] ? row['ad_group_ad.ad.final_mobile_urls'][0] : ''
}
}
adgroupMap[agId]['headlines'][row['ad_group_ad.ad.expanded_text_ad.headline_part1']] = 1;
adgroupMap[agId]['headlines'][row['ad_group_ad.ad.expanded_text_ad.headline_part2']] = 1;
if(row['ad_group_ad.ad.expanded_text_ad.headline_part3']) {
adgroupMap[agId]['headlines'][row['ad_group_ad.ad.expanded_text_ad.headline_part3']] = 1;
}
adgroupMap[agId]['descriptions'][row['ad_group_ad.ad.expanded_text_ad.description']] = 1;
adgroupMap[agId]['descriptions'][row['ad_group_ad.ad.expanded_text_ad.description2']] = 1;
if(!adgroupMap[agId]['path1'] && row['ad_group_ad.ad.expanded_text_ad.path1']) {
adgroupMap[agId]['path1'] = row['ad_group_ad.ad.expanded_text_ad.path1']
}
if(!adgroupMap[agId]['path2'] && row['ad_group_ad.ad.expanded_text_ad.path2']) {
adgroupMap[agId]['path2'] = row['ad_group_ad.ad.expanded_text_ad.path2']
}
}
if(!Object.keys(adgroupMap).length) {
Logger.log('No Active ETA found in the Account');
return;
}
var output = [];
for(var agId in adgroupMap) {
var row = adgroupMap[agId];
var headlines = Object.keys(row['headlines']);
var descriptions = Object.keys(row['descriptions']);
var status = 'Ok';
if(headlines.length < 6 || descriptions.length < 2) {
status = 'Needs more assets';
}
while(headlines.length < 15) {
headlines.push('');
}
while(descriptions.length < 4) {
descriptions.push('');
}
output.push([
row['campaign'], row['ad_group'], 'New', status, 'Enabled', row['url'],
headlines[0], '--', headlines[1], '--', headlines[2], '--', headlines[3], '--', headlines[4], '--', headlines[5], '--',
headlines[6], '--',headlines[7], '--',headlines[8], '--',headlines[9], '--',headlines[10], '--',headlines[11], '--',
headlines[12], '--',headlines[13], '--',headlines[14], '--',
'--', descriptions[0], '--', descriptions[1], '--', descriptions[2], '--', descriptions[3], '--',
row['path1'], row['path2'], '--', row['mobileUrl'],
'--', '--', '', '', '', '', '', 'Responsive search ad'
])
}
var query = [
'SELECT campaign.name, campaign.id, ad_group.name, ad_group.id, ad_group_ad.ad.id,ad_group_ad.status,metrics.impressions,',
'ad_group_ad.ad_strength,ad_group_ad.action_items,ad_group_ad.ad.type,ad_group_ad.ad.added_by_google_ads,',
'ad_group_ad.ad.responsive_search_ad.descriptions,',
'ad_group_ad.ad.responsive_search_ad.headlines,',
'ad_group_ad.ad.responsive_search_ad.path1,',
'ad_group_ad.ad.responsive_search_ad.path2,',
'ad_group_ad.ad.final_mobile_urls,',
'ad_group_ad.ad.final_urls',
'FROM ad_group_ad',
'WHERE ad_group_ad.ad.type = RESPONSIVE_SEARCH_AD',
'AND metrics.impressions >= 0',
'AND ad_group.status = ENABLED',
'AND campaign.status = ENABLED',
'AND ad_group_ad.status = ENABLED',
'AND segments.date DURING LAST_30_DAYS ORDER BY metrics.impressions DESC'
].join(' ');
var rows = AdsApp.report(query).rows()
while (rows.hasNext()){
var row = rows.next();
var out = [
row['campaign.name'], row['ad_group.name'], 'Existing', '', 'Enabled',
row['ad_group_ad.ad.final_urls'] ? row['ad_group_ad.ad.final_urls'][0] : '',
];
var headlineRows = row['ad_group_ad.ad.responsive_search_ad.headlines'];
var descriptionRows = row['ad_group_ad.ad.responsive_search_ad.descriptions'];
var headlines = [];
for(var x in headlineRows) {
headlines.push(headlineRows[x].text, '--');
}
var descriptions = []
for(var x in descriptionRows) {
descriptions.push(descriptionRows[x].text, '--');
}
while(headlines.length < 30) {
headlines.push('', '--');
}
while(descriptions.length < 8) {
descriptions.push('', '--');
}
out = out.concat(headlines).concat(['--']).concat(descriptions);
var suggestion = row['ad_group_ad.added_by_google_ads'];
var mobileUrl = row['ad_group_ad.ad.final_mobile_urls'];
out.push(
row['ad_group_ad.ad.responsive_search_ad.path1'], row['ad_group_ad.ad.responsive_search_ad.path2'],
suggestion, mobileUrl, '--', '--', '', row['ad_group_ad.status'], '', row['ad_group_ad.ad_strength'],
row['ad_group_ad.action_items'], 'Responsive search ad'
);
output.push(out);
}
if(!output.length) {
Logger.log('No data found.');
return;
}
var outputSheet = SpreadsheetApp.openByUrl(REPORT_URL).getSheetByName(TAB_NAME);
outputSheet.getRange(2,1,outputSheet.getLastRow(),outputSheet.getLastColumn()).clearContent();
outputSheet.getRange(2,1,output.length,output[0].length).setValues(output).sort([{'column': 1, 'ascending': true}, {'column': 2, 'ascending': true}]);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment