//Url config spreadsheet----------------------------------------------------------------------------------
//********************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('config spreadsheet url');
/*********************************************************************************************************
Script: Merchant control
Version: 09.02.2023
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 = ShoppingContent.Accountstatuses.get(accountId, accountId).products;
products = products !== undefined ? products : [{country: settings[i][2].toLowerCase(), destination: "Shopping", statistics: {expiring: 0, pending: 0, active: 0, disapproved: 0}}];
for (var j = 0; j < products.length; j++)
{
if ((products[j].country.toLowerCase() === settings[i][2].toLowerCase() || settings[i][2] === "") && products[j].destination === "Shopping")
{
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].statistics;
var expiring = parseFloat(statistics.expiring);
var pending = parseFloat(statistics.pending);
var active = parseFloat(statistics.active);
var disapproved = parseFloat(statistics.disapproved);
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});
}
}
//--------------------------------------------------------------------------------------------------------
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)
}