//Url config spreadsheet----------------------------------------------------------------------------------
//********************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('config spreadsheet url');
var developer_email = 'developer email';
/*********************************************************************************************************
Script: Merchant control
Version: 08.08.2026
Created By: Stanislav Jilek [standajilek.cz]
/********************************************************************************************************/
function main() {
//Settings------------------------------------------------------------------------------------------------
var settings_sheet = ss_config.getSheetByName("merchant_control");
var mail = settings_sheet.getRange("B2").getValue();
var subject = settings_sheet.getRange("B3").getValue();
var send_mail = 0;
var row = 0;
var settings = settings_sheet.getRange("A5:E" + settings_sheet.getLastRow()).getValues();
//HTML body table
var table = "<table border='1' style='border-collapse: collapse;' cellpadding='5'>";
table += "<tr><td colspan='6' bgcolor='#4fabe5'><strong>Google Merchant Center control</strong></td></tr>";
table += "<tr bgcolor='#ffd75d'><th>Account</th><th>Products</th><th>Active</th><th>Disapproved</th><th>Pending</th><th>Expiring</th></tr>";
//Cycle for a number of accounts--------------------------------------------------------------------------
for (var i = 0; i < settings.length; i++)
{
var accountId = settings[i][1];
try {
if (accountId != "")
{
var products = aggregate_product_statuses(accountId);
products = products !== undefined ? products : [{country: settings[i][2].toLowerCase(), reportingContext: "SHOPPING_ADS", stats: {expiringCount: 0, pendingCount: 0, activeCount: 0, disapprovedCount: 0}}];
for (var j = 0; j < products.length; j++)
{
if ((products[j].country.toLowerCase() === settings[i][2].toLowerCase() || settings[i][2] === "") && products[j].reportingContext === "SHOPPING_ADS")
{
var accountName = products.length > 1 && settings[i][2] === "" ? settings[i][0] + ": " + products[j].country + " (id: " + accountId + ")" : settings[i][0] + " (id: " + accountId + ")";
var statistics = products[j].stats !== undefined ? products[j].stats : {};
var expiring = parseFloat(statistics.expiringCount) || 0;
var pending = parseFloat(statistics.pendingCount) || 0;
var active = parseFloat(statistics.activeCount) || 0;
var disapproved = parseFloat(statistics.disapprovedCount) || 0;
var sum = expiring + pending + active + disapproved;
var percent = null_control(disapproved / sum, disapproved, sum);
Logger.log(accountName + " | products: " + sum + " (active: " + active + ", disapproved: " + disapproved + " (" + (percent * 100).toFixed(2) + " %), pending: " + pending + ", expiring: " + expiring + ")");
if ((percent > settings[i][3]) || (sum < settings[i][4]))
{
table += "<tr bgcolor='" + row_color(row) + "'><td nowrap>" + accountName + "</td>" +
"<td nowrap align='right'><font color='" + sum_color(sum, settings[i][4]) + "'>" + number_format(sum.toFixed(0)) + "</font></td>" +
"<td nowrap align='right'>" + number_format(active.toFixed(0)) + "</td>" +
"<td nowrap align='right'><font color='" + disapproved_color(percent, settings[i][3]) + "'>" + number_format(disapproved.toFixed(0)) + " (" + (percent * 100).toFixed(2) + " %)</font></td>" +
"<td nowrap align='right'>" + number_format(pending.toFixed(0)) + "</td>" +
"<td nowrap align='right'>" + number_format(expiring.toFixed(0)) + "</td></tr>";
send_mail = 1;
row++;
}
}
}
}
} catch (err)
{
Logger.log(settings[i][0] + " (id: " + accountId + ") | " + err);
}
}
//Send mail-----------------------------------------------------------------------------------------------
if (send_mail == 1)
{
MailApp.sendEmail({to: mail, subject: subject, htmlBody: table});
}
}
//--------------------------------------------------------------------------------------------------------
var gcp_wait = 0;
function aggregate_product_statuses(accountId) {
try {
return (MerchantIssueResolution.Accounts.AggregateProductStatuses.list("accounts/" + accountId).aggregateProductStatuses)
} catch (err)
{
if (String(err).indexOf("not registered") === -1)
{
throw err
}
if (developer_email === "" || developer_email === "developer email")
{
throw new Error("GCP projekt neni zaregistrovan, vyplnte developer_email na zacatku skriptu")
}
MerchantAccounts.Accounts.DeveloperRegistration.registerGcp({developerEmail: developer_email}, "accounts/" + accountId + "/developerRegistration");
try {
return (MerchantIssueResolution.Accounts.AggregateProductStatuses.list("accounts/" + accountId).aggregateProductStatuses)
} catch (e)
{
if (gcp_wait === 0)
{
Utilities.sleep(300000);
gcp_wait = 1;
}
return (MerchantIssueResolution.Accounts.AggregateProductStatuses.list("accounts/" + accountId).aggregateProductStatuses)
}
}
}
//--------------------------------------------------------------------------------------------------------
function number_format(number) {
number = number.toString();
number = number.split("").reverse().join("");
number = number.substr(0, 3) + " " + number.substr(3, 3) + " " + number.substr(6, 3) + " " + number.substr(9, 3) + " " + number.substr(12, 3);
number = number.split("").reverse().join("");
number = number.trim();
return(number)
}
//--------------------------------------------------------------------------------------------------------
function row_color(row) {
if (row % 2 == 0)
{
row = "#ffffff";
} else
{
row = "#d5d5d5";
}
return(row)
}
//--------------------------------------------------------------------------------------------------------
function null_control(number, a, b) {
if (a == 0 || b == 0) {
number = 0;
}
return (number);
}
//--------------------------------------------------------------------------------------------------------
function sum_color(number, control) {
var color = "black";
if (control > number)
{
color = "red";
}
return(color)
}
//--------------------------------------------------------------------------------------------------------
function disapproved_color(number, control) {
var color = "black";
if (control < number)
{
color = "red";
}
return(color)
}