GMC Disapproved Product Alerts Google Ads Script

Last updated: 14/11/2024

Tweaked the Disapproved Products Alerts script by Nils Rooijmans as it wasn’t working for me last time I ran it with a few tweaks that reports on the total products disapproved in the Shopping and anything that had at least one click in the last 90 days.

// SETTINGS
var SPREADSHEET_URL = ""; // sheets.new
var GMC_ID = "";  
var INCLUDE_LOCAL_PRODUCTS = false;  
var RECIPIENT_EMAIL = "";  
var MAX_RESULTS_PER_PAGE = 250;

// REPORT HEADERS
var SHEET_REPORT_HEADER = [
    "Product ID",
    "Product Title",
    "Product Link",
    "Shopping Destination Status",
    "Disapproval Reasons",
    "Policy Link",
    "Clicks"
];

// MAIN FUNCTION
function main() {
    var allDisapprovedProducts = fetchDisapprovedProducts();
    var disapprovedWithClicks = addClickDataFromGoogleAds(allDisapprovedProducts);

    if (disapprovedWithClicks.length > 0) {
        writeReport(disapprovedWithClicks);
        sendEmailNotification(allDisapprovedProducts.length, disapprovedWithClicks.length);
    } else {
        sendEmailNotification(allDisapprovedProducts.length, 0);
    }

    Logger.log(`Script completed successfully. Total disapproved products: ${allDisapprovedProducts.length}, with clicks > 0: ${disapprovedWithClicks.length}`);
}

// FETCH DISAPPROVED PRODUCTS
function fetchDisapprovedProducts() {
    var disapprovedProducts = [];
    var pageToken;

    Logger.log("Fetching disapproved products from Google Merchant Center...");

    do {
        var productStatuses = ShoppingContent.Productstatuses.list(GMC_ID, {pageToken: pageToken, maxResults: MAX_RESULTS_PER_PAGE});
        
        if (productStatuses.resources) {
            productStatuses.resources.forEach(function(product) {
                var productId = product.productId.split(':').pop().toLowerCase();
                if (INCLUDE_LOCAL_PRODUCTS || !product.productId.startsWith("local:")) {
                    var isDisapproved = false;
                    var reasons = new Set();
                    var policyLink = null;

                    product.destinationStatuses.forEach(function(status) {
                        if (status.destination === "Shopping" && status.status === "disapproved") {
                            isDisapproved = true;
                            (product.itemLevelIssues || []).forEach(function(issue) {
                                if (issue.servability === "disapproved") {
                                    reasons.add(issue.description);
                                    if (!policyLink && issue.documentation) {
                                        policyLink = issue.documentation;
                                    }
                                }
                            });
                        }
                    });

                    if (isDisapproved) {
                        disapprovedProducts.push({
                            productId: productId,
                            title: product.title || "No title",
                            link: product.link || "No link available",
                            shoppingStatus: "disapproved",
                            reasons: Array.from(reasons).join("; "),
                            policyLink: policyLink || "No policy link provided",
                            clicks: 0
                        });
                    }
                }
            });
        }
        pageToken = productStatuses.nextPageToken;
    } while (pageToken);

    Logger.log("Total disapproved products retrieved from GMC: " + disapprovedProducts.length);
    return disapprovedProducts;
}

// ADD CLICK DATA AND FILTER PRODUCTS WITH CLICKS > 0
function addClickDataFromGoogleAds(disapprovedProducts) {
    Logger.log("Fetching click data from Google Ads for products with clicks > 0...");
    var productClickMap = {};
    var dateRange = getLast90DaysDateRange();

    var gaqlQuery = `
        SELECT segments.product_item_id, metrics.clicks 
        FROM shopping_performance_view 
        WHERE segments.product_item_id IN (${disapprovedProducts.map(p => `'${p.productId}'`).join(", ")})
        AND segments.date BETWEEN '${dateRange.startDate}' AND '${dateRange.endDate}'
        AND metrics.clicks > 0
    `;
    var results = AdsApp.search(gaqlQuery);

    while (results.hasNext()) {
        var result = results.next();
        productClickMap[result.segments.productItemId] = result.metrics.clicks || 0;
    }

    // Only keep products with clicks > 0 and assign click values
    disapprovedProducts = disapprovedProducts.filter(function(product) {
        if (productClickMap[product.productId] > 0) {
            product.clicks = productClickMap[product.productId];
            return true;
        }
        return false;
    });

    // Sort products by clicks in descending order
    disapprovedProducts.sort((a, b) => b.clicks - a.clicks);

    Logger.log("Total disapproved products with clicks > 0: " + disapprovedProducts.length);
    return disapprovedProducts;
}

// WRITE REPORT TO GOOGLE SHEETS
function writeReport(data) {
    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
    var sheet = ss.getActiveSheet();
    sheet.clear();
    sheet.appendRow(SHEET_REPORT_HEADER);

    var formattedData = data.map(function(item) {
        return [
            item.productId,
            item.title,
            item.link,
            item.shoppingStatus,
            item.reasons,
            item.policyLink,
            item.clicks
        ];
    });
    sheet.getRange(2, 1, formattedData.length, SHEET_REPORT_HEADER.length).setValues(formattedData);

    Logger.log("Report successfully written to Google Sheets.");
}

// HELPER FUNCTION TO GET LAST 90 DAYS DATE RANGE
function getLast90DaysDateRange() {
    var today = new Date();
    var endDate = today.toISOString().slice(0, 10);
    var startDate = new Date(today);
    startDate.setDate(today.getDate() - 90);
    return { startDate: startDate.toISOString().slice(0, 10), endDate: endDate };
}

// SEND EMAIL NOTIFICATION WITH TOTAL AND CLICKS > 0 DISAPPROVALS
function sendEmailNotification(totalDisapprovals, disapprovalsWithClicks) {
    var accountName = AdsApp.currentAccount().getName(); 
    var subject = `GMC Disapproval Alert - ${accountName}`;
    var body = `${totalDisapprovals} total disapproved products found in Google Merchant Center, with ${disapprovalsWithClicks} disapproved products having clicks > 0.\nView report: ${SPREADSHEET_URL}\n\nGMC Disapproved Products Alert - victorserban.com`;

    MailApp.sendEmail(RECIPIENT_EMAIL, subject, body);
}