Untitled

/**
*
* Report Disapproved Products
*
* Creates a report indicating which products are disapproved, including the reason why and a way to fix it
* Sends an alert via email whenever products are disapproved
*
* Version 1.0
*
* @author: Nils Rooijmans
*
* contact nils@nilsrooijmans.com for questions and the MCC version of the script 
*/
 
// CHANGE SETTINGS HERE
 
var SPREADSHEET_URL = "";  //insert a new blank spreadsheet url between the double quotes
var EMAIL = ""; //insert your email adresses between the double quotes. You can add multiple email addresses between the double quotes, just separate them via a comma, ie: "john@doe.com, jane@doe.com"
 
var MIN_CLICKS = 1;  // ignore products with less clicks during the date range specified via PERIOD, NOTE: minimum value is 1
var PERIOD = 'LAST_30_DAYS'; 
 
var GMC_ID = ""; // insert Google Merchant Center id between double quotes. NOTE: the account you use to authorize this Google Ads script should also have access to the merchant center account associated with the id  
 
var ACCOUNT_NAME = ""; // insert account name between double quotes. 
 
 
// NO CHANGES NEEDED BELOW THIS LINE
 
var SHEET_REPORT_HEADER = [
    "Product ID",
    "Product Title",
    "Product Link",
    "Clicks",
    "Disapproval Reason",
    "Solution",
    "Documentation"
  ];
 
 
function main() {
   
  // first we clear the report sheet and add header
  prepareSpreadsheet();  
   
  // let's get the products that are of interest
  var productsOfInterest = getProductsOfInterest();
   
  // let's check for disapprovals among the products of interest
  var disapprovedProducts = getDisapprovedProducts(productsOfInterest, GMC_ID);
   
  // finally, let's report on any issues
  if (disapprovedProducts.length > 0) { // there is at least one issue
    Logger.log("Total NR of Disapproved products: "+disapprovedProducts.length);
    reportResults(disapprovedProducts);
  }
}
 
 
function prepareSpreadsheet() {
 
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getActiveSheet();
  sheet.clear(); //remove earlies alerts
  sheet.clearConditionalFormatRules();
  sheet.appendRow(SHEET_REPORT_HEADER);
   
  var range=sheet.getRange(1,1,1,SHEET_REPORT_HEADER.length);
  range.setFontWeight("bold"); 
}
 
 
function getDisapprovedProducts(productsOfInterest, gmc_id) {
   
  var pageToken;
  var maxResults = 250;
   
  var disapprovedProducts = [];
 
  do {
     
    var productStatuses = ShoppingContent.Productstatuses.list(gmc_id, {pageToken: pageToken, maxResults: maxResults});
     
    if (productStatuses.resources) {
      for (var i = 0; i < productStatuses.resources.length; i++) {
        var product = productStatuses.resources[i];
         
        // productId's have the format like "productId" = "online:nl:NL:136258511"
        var productId = product.productId.split(':').pop();
         
        if (productsOfInterest[productId] != null) {
          //Logger.log("We've got a product of interest to check, \n  productId : "+productId+"\n  productTitle: "+productsOfInterest[productId].title+"\n  Nr of Clicks: "+productsOfInterest[productId].clicks);
          for(j=0; j<product['destinationStatuses'].length;j++){
            if (product['destinationStatuses'][j]['destination'] == "Shopping") { // only check Shopping ads
              if (product['destinationStatuses'][j]['status'] == "disapproved") {
                if(product['itemLevelIssues'] == undefined) {
                  var reason = "Disapproval reason unknown";
                }else{
                  var reason = product['itemLevelIssues'][0].description;
                  var solution = product['itemLevelIssues'][0].detail;
                  var documentation = product['itemLevelIssues'][0].documentation;
                }; 
                Logger.log("- the product is disapproved, productId : "+productId+" reason: "+reason);
 
                disapprovedProducts.push([
                  product.productId.split(':').pop(), // productId's have the format like "productId" = "online:nl:NL:136258511"
                  product.title,
                  product.link,
                  productsOfInterest[productId].clicks,
                  reason,
                  solution,
                  documentation
                ]);                
              }
            }
          }
        }
         
      }
    } else {
      Logger.log("No more products in account " + gmc_id);
    }
    pageToken = productStatuses.nextPageToken;
  } while (pageToken);
   
  Logger.log("NR of disapproved products: "+disapprovedProducts.length);
   
  return disapprovedProducts;
}
 
 
function getProductsOfInterest() {
 
  var productsOfInterest = {};
   
  var gaqlQuery = "SELECT segments.product_item_id, segments.product_title, metrics.clicks FROM shopping_performance_view WHERE metrics.clicks >= "+MIN_CLICKS+" AND segments.date DURING "+PERIOD;
   
  var results = AdsApp.search(gaqlQuery);
  var count = 0;
   
  while (results.hasNext()) {
     
    var result = results.next();
     
    var productId = result.segments.productItemId;
    var productTitle = result.segments.productTitle;
    var nrOfClicks = result.metrics.clicks;
     
    productsOfInterest[productId] = {
      id:productId,
      title:productTitle,
      clicks:nrOfClicks
    };  
     
    count++;
  }
 
  Logger.log("NR of products of interest: "+count);
   
  return productsOfInterest;
}
 
 
function reportResults(results) {
 
    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
    var sheet = ss.getActiveSheet();
    var lastRow = sheet.getLastRow();
 
    // write issues to sheet
    var range = sheet.getRange(lastRow+1, 1, results.length, SHEET_REPORT_HEADER.length);
    range.setValues(results);
    range.sort([{column: 4, ascending: false}]);  
   
    // send the email
    var emailBody = 
      "Number of Disapproved products: " + results.length + "\n" + 
      "See details: "+ SPREADSHEET_URL + "\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 - Disapproved Products Alert, (C) Nils Rooijmans \n" +
      "---\n";
 
    MailApp.sendEmail(EMAIL, "[GOOGLE ADS ALERT] - Disapproved Products - "+ACCOUNT_NAME, emailBody);
}