Newly Disapproved Products.js

/******************************************
 * Newly Disapproved Products 
 *
 * Google Ads Script for identifying newly disapproved in Google Merchant Center, 
 * if product has clicks before
 *
 * Created by: Dmytro Tonkikh, chiliad & TrueClicks
 *
 * Telegram channel : t.me/adwordsscripts
 * Websites :  https://adsapp.report
 *         https://trueclicks.com
 *
 * - Automated Identification
 * - Detailed Notifications
 * - Easy to Set Up
 ******************************************/



// Make a copy of this Google Sheet: https://docs.google.com/spreadsheets/u/1/d/1-PfwEaaReSvOup8ki_HbuZlCCj2xXpzCulA8NpuriHg/copy
var ss = SpreadsheetApp.openByUrl("INSERT URL OF YOUR GOOGLE SHEET COPY ");

//Insert your Merchant Center ID
var merchantId = '';
var clicksThreshold = 0;

var timePeriod = "";
//Available values for timePeriod variable: TODAY, YESTERDAY, LAST_7_DAYS, THIS_MONTH, LAST_MONTH, LAST_14_DAYS, LAST_30_DAYS
//Leave timePeriod empty for ALL_TIME product stats.

var emailRecipient = "[email protected]";

//DO NOT CHANGE SETTING BELOW
var productItemIds = {};
var products = {};
var languages = languageConstants();

function main() {

    var currentProducts = getProductData();
    disapprovedProducts();
    getProductStats();
    var newDisapprovals = Object.keys(products).filter(x => !Object.keys(currentProducts).includes(x));

    if (newDisapprovals.length > 0) {

        var newValues = newDisapprovals.map(function(x) { return [x, products[x].title, Utilities.formatDate(new Date(), AdsApp.currentAccount().getTimeZone(), "yyyy-MM-dd")] }).filter(function(x) { if (products[x[0]].clicks >= clicksThreshold) { return x } });
        var newValuesSheet = ss.getSheetByName('new values');
        newValuesSheet.getRange(newValuesSheet.getLastRow() + 1, 1, newValues.length, newValues[0].length).setValues(newValues);
        sendEmail(newValues);
    }

    var headers = ['id', 'title', 'clicks', 'conversions', 'aggregatedDestinationStatus', 'availability'];
    var values = Object.keys(products).map(function(x) { return headers.map(function(n) { return products[x][n] }) });
    values.unshift(headers);
    ss.getSheetByName('data').clear().getRange(1, 1, values.length, values[0].length).setValues(values);

}

function getProductData() {

    var retVal = {}
    var values = ss.getSheetByName('data').getDataRange().getValues();
    var headers = values.shift();
    values.forEach(function(x) { retVal[x[headers.indexOf('id')]] = headers.reduce(function(s, c, n) { s[c] = x[n]; return s }, {}) });
    return retVal;

}

function getProductStats() {

    var query = "SELECT segments.product_item_id, segments.product_feed_label, segments.product_language, metrics.clicks, metrics.conversions FROM shopping_performance_view WHERE segments.product_item_id IN ('" + Object.keys(productItemIds).join("','") + "')";

    if (timePeriod !== "") {
        query = query + " AND segments.date DURING " + timePeriod;
    }

    var report = AdsApp.search(query);

    while (report.hasNext()) {
        var row = report.next();
        var productId = ["online", languages[row.segments.productLanguage], row.segments.productFeedLabel, row.segments.productItemId].join(':');

        if (products[productId]) {
            products[productId].clicks += parseInt(row.metrics.clicks)
        }
    }

}

function disapprovedProducts() {

    var retVal = {}

    var pageToken;

    var query = `SELECT
                product_view.id,
                product_view.offer_id,
                product_view.title,
                product_view.brand,
                product_view.currency_code,
                product_view.price_micros,
                product_view.language_code,
                product_view.condition,
                product_view.channel,
                product_view.availability,
                product_view.shipping_label,
                product_view.gtin,
                product_view.item_group_id,
                product_view.creation_time,
                product_view.expiration_date,
                product_view.aggregated_destination_status,
                product_view.item_issues
              FROM ProductView
              WHERE product_view.aggregated_destination_status = 'NOT_ELIGIBLE_OR_DISAPPROVED' AND product_view.availability = 'IN_STOCK'`

    do {
        var report = ShoppingContent.Reports.search({ query: query, pageToken: pageToken }, merchantId);
        if (report.results) {

            report.results.forEach(function(p) {
                p.productView.clicks = 0;
                p.productView.conversions = 0
                retVal[p.productView.id] = p.productView;
                products[p.productView.id] = p.productView;
                var offerId = p.productView.offerId;
                productItemIds[offerId] = true;
            })

            pageToken = report.nextPageToken

        }
    } while (pageToken)

    return retVal
}

function languageConstants() {

    var retVal = []
    var search = AdsApp.search("SELECT language_constant.resource_name, language_constant.code FROM language_constant");

    while (search.hasNext()) {
        retVal.push(search.next())
    }
    return retVal.reduce(function(s, c) { s[c.languageConstant.resourceName] = c.languageConstant.code; return s }, {})

}

function sendEmail(values) {

    var htmlBody = `<!DOCTYPE html>
<html>
<head>
    <style>
        body { font-family: Arial, sans-serif; }
        .container { width: 600px; margin: auto; }
        .header { background-color: #f2f2f2; padding: 20px; text-align: center; }
        .content { padding: 20px; }
        .footer { background-color: #f2f2f2; padding: 10px; text-align: center; font-size: 12px; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h2>Disapproved Products Update</h2>
        </div>
        <div class="content">
            <p>Google Ads script identified <b>${values.length} product(s)</b> newly disapproved in your Merchant Center account.</p>
            <p>Check products in <a href="${ss.getUrl()}">Google Spreadsheet</a></p>
            <p>To review the specific reasons for each disapproval and to take the necessary steps for resolution, please follow the instructions below:</p>
            <ol>
                <li>Log in to your <a href="https://merchants.google.com/mc/products/diagnostics?a=${merchantId}">Merchant Center account</a>.</li>
                <li>Navigate to the "Diagnostics" page to review the disapproved products and their issues.</li>
                <li>Update your product data according to the provided guidelines.</li>
                <li>Resubmit your products for review.</li>
            </ol>
            <p>For detailed guidance on resolving these issues, please visit our <a href="https://support.google.com/merchants/">Help Center</a>. If you require further assistance, do not hesitate to contact support.</p>
            <p>Your prompt action is appreciated to ensure your products are available to shoppers without interruption.</p>
            <p>Best regards,</p>
            <p>Google Ads Script</p>
        </div>
        <div class="footer">
            This is an automated message. Please do not reply directly to this email.
        </div>
    </div>
</body>
</html>`

    MailApp.sendEmail({ to: emailRecipient, subject: "Newly Disapproved Products", name: "Google Ads script", htmlBody: htmlBody })
}