Create a Google Ads bulksheet to turn your ads into Responsive Search Ads (RSAs) more easily

Create RSAs from ETAs - Optmyzr Free Script

/*************************************************
* 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}]);
}