/**
*
* Report Missing RSA Ad Groups
*
* Creates a Google Sheet reporting ad groups that have less than the required number of
* Respsonsive Search Ads (RSAs)
*
*
* @author: Nils Rooijmans
*
* Version 1.0 
*
* contact nils@nilsrooijmans.com for questions and a High Performance MCC version of the script 
*/
 
 
/*** [REQUIRED] ADD YOUR SETTINGS HERE ***/

var SPREADSHEET_URL = "";  //insert a new blank spreadsheet url, be sure to include the complete url!
var EMAIL_ADDRESSES = ""; //insert your email address, multiple email addresses seperated by comma
var EMAIL_SUBJECT = "GAds Script - [INSERT YOUR ACCOUNT NAME HERE] - Missing RSA Ad Groups"; // subject of emails


/*** [OPTIONAL] YOU MIGHT WANT TO CHANGE SOME CONFIGURATIONS HERE ***/

var MIN_NR_OF_RSA_PER_AD_GROUP = 1; 
// set to your requirement for the minimum number of RSAs per ad group

var IMPRESSION_ALERT_THRESHOLD = 1000; 
// threshold for email alerts; issues with ad groups that have had less impressions will still be reported,
// owever an email is only sent if at least one ad group has had over this number of impressions during last 7 days

var SEND_EMAIL = true; // set to false if you do not want to send alert email


/*** DO NOT CHANGE ANYTHING BELOW THIS LINE ***/

var DEBUG = false; 

var EMAIL_BODY = 
    "\n"+
    "***\n"+
    "This script will check all enabled ad groups in all enabled search campaigns, \n"+
    "  for each ad group it will check if the ad group has at least "+MIN_NR_OF_RSA_PER_AD_GROUP+" RSA(s) enabled, \n"+
    "    if the ad group does not have the minimum number of RSAs, it is logged in a Google Sheet, the rows in the sheet are sorted by number of impressions of the ad group \n"+
    "    also, if the ad group does not have the minimum number of RSAs, AND the ad group has had over "+IMPRESSION_ALERT_THRESHOLD+" impressions during last 7 days, an email alert will be sent to"+EMAIL_ADDRESSES+" \n"+
    "\n"+
    "Here's the link to the output of the script: "+ SPREADSHEET_URL+ "\n---\n\n"+
    "For more FREE Google Ads Scripts to improve your results and make your working day feel like a breeze, visit https://nilsrooijmans.com \n" + 
    "---\n" + 
    "This email is generated by a copy of the free Google Ads Script - Report Missing RSA Ad Groups, (C) Nils Rooijmans \n" +
    "---\n";

var sendAlertMail = false; 


function main() {
  
  // prepare output sheet
  var sheet;
    try {
      sheet = prepareOutputSheet();  
    } catch(e) {
      alert("Reporting issue: "+e);
      return;
    }
  
  // process campaigns
  var results = processCampaigns();
 
  if (results.length > 0) {

    // report results
    addOutputToSheet(results, sheet);   

    // send alert mail if conditions are met
    if (sendAlertMail) {
      sendEmail(results.length); 
    }
  }  
}


function processCampaigns() {

  var results = [];
  
  var campaignSelector = AdsApp.campaigns()
    .withCondition('Status = "ENABLED"')
    .withCondition("AdvertisingChannelType = SEARCH") // only process search campaigns
    .withCondition("CampaignExperimentType = BASE"); // only process Base campaigns, not Experiments
  
  var campaignIterator = campaignSelector.get();
  Logger.log("NR of campaigns to process: "+campaignIterator.totalNumEntities());
  
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    var result = processCampaign(campaign);
    results = results.concat(result);
  }

  return results;
}


function processCampaign(campaign) {
  
  debug("Processing campaign: "+campaign.getName());
  var campaignId = campaign.getId();
  
  var results = [];
  
  var adGroupSelector = AdsApp.adGroups()
    .withCondition('CampaignId = "'+campaignId+'"')
    .withCondition('Status = "ENABLED"');
  
  var adGroupIterator = adGroupSelector.get();
  debug("NR of adGroups to process: "+adGroupIterator.totalNumEntities());
  
  while (adGroupIterator.hasNext()) {
    var adGroup = adGroupIterator.next();
    var result = processAdGroup(adGroup);
    results = results.concat(result);
  }

  return results;
}


function processAdGroup(adGroup) {
  
  
  var campaignId = adGroup.getCampaign().getId();
  var campaignName = adGroup.getCampaign().getName();
  
  var adGroupId = adGroup.getId();
  var adGroupName = adGroup.getName();
  
  debug("Processing adGroup: "+adGroupName);
  
  var impressions = adGroup.getStatsFor('LAST_7_DAYS').getImpressions();
  
  var results = [];
  
  var adSelector = AdsApp.ads()
    .withCondition('CampaignId = "'+campaignId+'"')
    .withCondition('AdGroupId = "'+adGroupId+'"')
    .withCondition("Type IN [RESPONSIVE_SEARCH_AD]")
    .withCondition('Status = "ENABLED"');
  
  var adIterator = adSelector.get();
  var nrOfRSAs = adIterator.totalNumEntities();
  debug("NR of RSAs in this ad group: "+nrOfRSAs);
        
  
  if (nrOfRSAs < MIN_NR_OF_RSA_PER_AD_GROUP) { // this adGroup does not meet the minimum required number of RSAs
    results.push([campaignName, adGroupName, impressions, nrOfRSAs]);
    if (impressions > IMPRESSION_ALERT_THRESHOLD) { 
      sendAlertMail = true;
    }
  }

  return results;
}

function prepareOutputSheet() {
  
  if(SPREADSHEET_URL == ""){
    throw("Create new Google Sheet and copy paste complete url in config variable 'SPREADSHEET_URL' at the top of the script");
  }  
  
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  var sheet = spreadsheet.getActiveSheet();
  
  try {
    sheet.clear();
    addHeaderToOutputSheet(sheet);
  } catch(e) {
    throw(e);
  }
 
  return sheet;
}


function addHeaderToOutputSheet(sheet) {
  
  var header = [
    "campaignName",
    "adGroupName",
    "impressions",
    "number of RSAs"
    ];
  
  sheet.appendRow(header);
  
  var range=sheet.getRange(1,1,1,header.length);
  range.setFontWeight("bold");  
}


function addOutputToSheet(output, sheet) {
  
  if (!(output.length > 0)) return; // nothing to add to sheet

  var numberOfRows=sheet.getLastRow() ;
  debug("NR of rows in output sheet: "+numberOfRows);
  
  sheet.insertRowsBefore(2, output.length); // add empty rows below header row

  var startRow = 2;
  
  var range=sheet.getRange(startRow, 1, output.length, output[0].length) ;
  range.setValues(output) ;
  range.sort([{column: 3, ascending: false}]);    

  Logger.log("\nNumber of rows added to output sheet: "+output.length+"\n\n");
  
}



function sendEmail(number) {

  if (SEND_EMAIL) {
    // send the email
    var emailBody = 
        "Number of issues: " + number + "\n" + 
        "See details: "+ SPREADSHEET_URL+ "\n" + EMAIL_BODY;

    MailApp.sendEmail(EMAIL_ADDRESSES, EMAIL_SUBJECT, emailBody);
    Logger.log("Sending mail");
  }  
}


/** HELPER FUNCTIONS **/

function debug(string) {
  if (DEBUG == true) {
    Logger.log("* "+string);
  }
}

function alert(string) {
  Logger.log("##### ERROR: "+string+" #####");
}