Google Ads Scripts — Alert On Disapproved Products In Google Merchant Center

Google Shopping Ads is one of the leading channels for generating e-Commerce sales. To use Google Shopping Ads, all you need to do is:

1. Set up a merchant center account.
2. Populate it with data.
3. Connect it with your Google Ads account

And that’s it…

However, managing multi Google Shopping / Merchant Center accounts with thousands of products is more challenging. One challenge is keeping up with disapproved products.

If the campaign manager fails to identify that a top-performing product was disapproved, the results can be a drop in sales and loss of revenues.

The following script simplifies identifying disapproved products in multiple Google Merchant Center Accounts by sending alert emails with the relevant information.

What does the script do exactly?

The script retrieves the list of GMC (Google Merchant Center) IDs and the corresponding emails from the spreadsheet. Then, looping on the GMC accounts, it identifies the products that were recently disapproved (we only want notifications on new disapproved products). Last step is sending notification about it in the format “*Product title* (Product Id: *PID*) was disapproved. The Explanation is *Reason for disapprovement*”

To implement the script, follow these simple steps:

First, copy this Google Spreadsheet. On each line in the “main” sheet, add the Merchant Center ID in column A and the corresponding email to receive alerts. The following screenshot shows the template for the script.

Now, coding time!

Let’s start by configuring the link to the spreadsheet:

var SPREADSHEET = “https://docs.google.com/spreadsheets/d/1Ey99QqnjbihT3Qb6";

The main function contains two functions to keep everything neat. After the main function, we will write these functions to make our code readable.

function main() {
 
 var gmc_ids_and_emails = get_gmc_ids_and_emails();
 verify_products_status(gmc_ids_and_emails);
 
}

The first function is for getting the GMC (Google Merchant Center) ID and the corresponding emails from the spreadsheet.

We generate an empty dictionary, connect to the spreadsheet via SpreadsheetApp, choose the “main” sheet, and finally populate the dictionary with the values and return it.

function get_gmc_ids_and_emails(){
 var gmc_ids_and_emails = {};
 var sheetdata = SpreadsheetApp.openByUrl(SPREADSHEET).getSheetByName(“main”).getRange(“A:B”).getValues();
 for (i=1;i<sheetdata.length;i++){
 if( sheetdata[i][0] != “”){
 var gmc_id = sheetdata[i][0];
 var gmc_email = sheetdata[i][1];
 gmc_ids_and_emails[gmc_id] = [gmc_id,gmc_email];
 }
 }
 return gmc_ids_and_emails;
}

Next, the function “verify_products_status” loops over the GMC accounts. It applies 3 functions for each GMC account: “get_dissaproved_products”, “update_disapproved_products_sheet”, and “send_alert_email”.

function verify_products_status(gmc_ids_and_emails){
 
 for(var gmc in gmc_ids_and_emails){
 var gmc_id = gmc_ids_and_emails[gmc][0];
 var gmc_email = gmc_ids_and_emails[gmc][1];
 var previous_dissaproved_products = get_previous_dissaproved_products(gmc_id);
 var dissaproved_products = get_dissaproved_products(gmc_id);
 update_disapproved_products_sheet(dissaproved_products,gmc_id);
 send_alert_email(previous_dissaproved_products,dissaproved_products,gmc_email,gmc_id);
 }
}

The function “get_previous_dissaproved_products” goes to the spreadsheet and locates a sheet with the same name as the GMC Id. If one doesn’t exist, it generates it.

What’s this sheet all about?

This sheet contains a list of previously disapproved products. We need this to send notifications only on new disapproved ads.

function get_previous_dissaproved_products(gmc_id){
 var previous_dissaproved_products = [];
 var ss = SpreadsheetApp.openByUrl(SPREADSHEET);
 if (ss.getSheetByName(gmc_id) == null){
 Logger.log(“Creating sheet for GMC “+gmc_id); 
 ss.insertSheet().setName(gmc_id);
 return previous_dissaproved_products;
 }
 var sheetdata = SpreadsheetApp.openByUrl(SPREADSHEET).getSheetByName(gmc_id).getRange(“A:A”).getValues();
 for (i=0;i<sheetdata.length;i++){
 if( sheetdata[i][0] != “”){
 previous_dissaproved_products.push(sheetdata[i][0]);
 }
 }
 return previous_dissaproved_products;
}

The function “get_dissaproved_products” loops over the products in the GMC account and retrieves their Ids, product titles, and why they are disapproved.

Looping over the GMC products is done in a “weird” way. The script needs to rotate over the product feed pages. That’s why we have the page token variable. Why can’t we get all the products at once? Beats me…

function get_dissaproved_products(gmc_id){
 var dissaproved_products = {}
 var totalProducts = 0;
 var pageToken;
 do {
 var productStatuses = ShoppingContent.Productstatuses.list(gmc_id, {pageToken: pageToken});
 if (productStatuses.resources) {
 for (var i = 0; i < productStatuses.resources.length; i++) {
 product = productStatuses.resources[i];
 for(j=0;j<product[‘destinationStatuses’].length;j++){
 if (product[‘destinationStatuses’][j][‘approvalStatus’] == ‘disapproved’) {
 if(product[‘itemLevelIssues’] == undefined) {
 var reason = “Disapproval reason not found”;
 }else{
 var reason = product[‘itemLevelIssues’][0].detail;
 }; 
 dissaproved_products[product.productId] = {
 id:product.productId,
 title:product.title,
 reason: reason
 }
 }
 }
 }
 } else {
 Logger.log(‘No more products in account ‘ + merchantId);
 }
 pageToken = productStatuses.nextPageToken;
 } while (pageToken);
 
 return dissaproved_products;
}

We are almost done. We need to update the spreadsheet with the disapproved products so the next time the script runs, it will not notice these products again.

The function “update_disapproved_products_sheet” does just that, and it’s pretty straightforward.

function update_disapproved_products_sheet(dissaproved_products,gmc_id){
 var sheet = SpreadsheetApp.openByUrl(SPREADSHEET).getSheetByName(gmc_id);
 sheet.clear();
 for (product_id in dissaproved_products){
 sheet.appendRow([dissaproved_products[product_id].id]);
 }
}

That’s it! Now, all we need is to send the notification email.

The function “send_alert_email” takes the previously disapproved products, the current disapproved products, and the account manager's email and makes the magic happen.

If the product doesn’t appear in the previously disapproved products list, or in other words, it is a newly disapproved product, it will add a line in the email stating the product name, productId, and the reason was disapproved.

Before sending the email, we create a short intro, so the account manager will know what this email is all about.

function send_alert_email(previous_dissaproved_products,dissaproved_products,gmc_email,gmc_id){
 var msg = “”;
 for(var p in dissaproved_products){
 var pid = dissaproved_products[p].id;
 var ptitle = dissaproved_products[p].title;
 var reason = dissaproved_products[p].reason;
 if(previous_dissaproved_products.indexOf(pid) < 0) {
 msg += “\n” + ptitle + “ (Product Id: “ + pid + “).”+ “\n” + “Explanation: “ + reason + “\n”; 
 }
 }
 if(msg!=””){
 var intro = “Hi!\n\nSome products in GMC id “ + gmc_id + “ were disapproved\n\n”;
 msg = intro + msg;
 MailApp.sendEmail(gmc_email, “Disapproved Products in GMC id “+gmc_id, msg)
 }
}

Final words

I hope you enjoyed this article about email alerts on disapproved products in Google Merchant Center. If you have any questions, feel free to ping me.