//Url config spreadsheet----------------------------------------------------------------------------------
//********************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl("config spreadsheet url");
/*********************************************************************************************************
Script: Budget guard
Version: 03.10.2018
Created By: Stanislav Jilek [standajilek.cz]
Idea: Google team: Eliska Kubatova, Anna Petrova, Pavel Jasek [https://www.dlouhychvost.cz/budget-guard/]
/********************************************************************************************************/
function main() {
//Mail settings ------------------------------------------------------------------------------------------
var settings_sheet = ss_config.getSheetByName("budget_guard");
var mail = settings_sheet.getRange("B2").getValue();
var subject = settings_sheet.getRange("B3").getValue();
var send_mail = 0;
var budget_condition = settings_sheet.getRange("B5").getValue();
//HTML body table
var table = "<table border='1' style='border-collapse: collapse;' cellpadding='5'>";
//--------------------------------------------------------------------------------------------------------
//ADWORDS*************************************************************************************************
try {
//Adwords settings
var adwords_settings = settings_sheet.getLastRow() >= 7 ? settings_sheet.getRange("C7:C" + settings_sheet.getLastRow()).getValues() : [];
//Cycle for a number of accounts
for (var i = 0; i < adwords_settings.length; i++)
{
if (adwords_settings[i][0] != "")
{
try {
//Mcc select
try
{
MccApp.select(MccApp.accounts().withIds([adwords_settings[i][0]]).get().next());
} catch (err)
{
}
var account_name = AdsApp.currentAccount().getName();
var currency = AdsApp.currentAccount().getCurrencyCode();
var header = 0;
var row_number = 0;
var report = AdsApp.report("SELECT AssociatedCampaignName, Cost, Amount FROM BUDGET_PERFORMANCE_REPORT WHERE Cost > 0 DURING YESTERDAY").rows();
while (report.hasNext())
{
var row = report.next();
var campaign_name = row["AssociatedCampaignName"];
var cost = parseFloat(row["Cost"].split(",").join(""));
var budget = parseFloat(row["Amount"].split(",").join(""));
var control = cost / budget;
if (control > budget_condition)
{
if (header == 0)
{
table += "<tr><td colspan='4' bgcolor='#4fabe5'><strong>GOOGLE ADS: " + account_name + "</strong></td></tr><tr bgcolor='#ffd75d'><th>Campaign</th><th>Budget</th><th>Cost yesterday</th><th>Control</th></tr>";
header = 1;
}
table += "<tr bgcolor='" + row_color(row_number) + "'><td nowrap>" + campaign_name + "</td>" +
"<td nowrap align='right'>" + number_format(budget.toFixed(0)) + " " + currency + "</td>" +
"<td nowrap align='right'>" + number_format(cost.toFixed(0)) + " " + currency + "</td>" +
"<td nowrap align='right'>" + (control * 100).toFixed(0) + " %</td></tr>";
row_number++;
send_mail = 1;
}
}
} catch (err) {
Logger.log("GOOGLE ADS: " + err);
}
}
}
} catch (err) {
Logger.log("GOOGLE ADS: " + err);
}
//--------------------------------------------------------------------------------------------------------
//SKLIK***************************************************************************************************
try {
//Sklik settings
var sklik_settings = settings_sheet.getLastRow() >= 7 ? settings_sheet.getRange("A7:A" + settings_sheet.getLastRow()).getValues() : [];
//Login to Sklik
var token = settings_sheet.getRange("B4").getValue();
//--------------------------------------------------------------------------------------------------------
//client.login
var client_login = sklik_api([token], 'client.loginByToken');
//--------------------------------------------------------------------------------------------------------
//client.get
var client_get = sklik_api([{'session': client_login.session}], 'client.get')
var sklik_account = [];
for (var i = 0; i < sklik_settings.length; i++) //Sklik account
{
if (sklik_settings[i][0] != "")
{
for (var j = 0; j < client_get.foreignAccounts.length; j++)
{
if (sklik_settings[i][0].toLowerCase() == client_get.foreignAccounts[j].username.toLowerCase())
{
sklik_account.push([client_get.foreignAccounts[j].userId, client_get.foreignAccounts[j].username, sklik_settings[i][1]]);
}
}
if (sklik_settings[i][0].toLowerCase() == client_get.user.username.toLowerCase())
{
sklik_account.push([client_get.user.userId, client_get.user.username, sklik_settings[i][1]]);
}
}
}
//Cycle for a number of accounts
for (var i = 0; i < sklik_account.length; i++)
{
var header = 0;
var row_number = 0;
//Yesterday----------------------------------------------------------------------------------------------
var start_date = new Date();
start_date.setUTCDate(start_date.getUTCDate() - 1);
start_date = Utilities.formatDate(start_date, 'GTM - 1', 'yyyy-MM-dd');
var end_date = new Date();
end_date.setUTCDate(end_date.getUTCDate() - 1);
end_date = Utilities.formatDate(end_date, 'GTM - 1', 'yyyy-MM-dd');
//createReport-------------------------------------------------------------------------------------------
var createReport = sklik_api([{'session': client_login.session, 'userId': sklik_account[i][0]},
{'isDeleted': false,
'dateFrom': start_date,
'dateTo': end_date}],
'campaigns.createReport');
var report_id = createReport.reportId;
var limit = createReport.totalCount;
var offset = Math.ceil(limit / 5000);
for (var k = 0; k < offset; k++)
{
try
{
//readReport---------------------------------------------------------------------------------------------
var readReport = sklik_api([{'session': client_login.session, 'userId': sklik_account[i][0]},
report_id,
{'offset': k * 5000,
'limit': 5000,
'allowEmptyStatistics': false,
'displayColumns': ['name', 'totalMoney', 'budget.dayBudget']}],
'campaigns.readReport');
if (readReport.report.length > 0)
{
for (var j = 0; j < readReport.report.length; j++)
{
var campaign_name = readReport.report[j].name;
var cost = readReport.report[j].stats[0].totalMoney / 100;
var budget = readReport.report[j].budget.dayBudget / 100;
var control = cost / budget;
if (control > budget_condition)
{
if (header == 0)
{
table += "<tr><td colspan='4' bgcolor='#ff4646'><strong>SKLIK: " + sklik_account[i][1] + "</strong></td></tr><tr bgcolor='#ffd75d'><th>Campaign</th><th>Budget</th><th>Cost yesterday</th><th>Control</th></tr>";
header = 1;
}
table += "<tr bgcolor='" + row_color(row_number) + "'><td nowrap>" + campaign_name + "</td>" +
"<td nowrap align='right'>" + number_format(budget.toFixed(0)) + " CZK</td>" +
"<td nowrap align='right'>" + number_format(cost.toFixed(0)) + " CZK</td>" +
"<td nowrap align='right'>" + (control * 100).toFixed(0) + " %</td></tr>";
row_number++;
send_mail = 1;
}
}
}
} catch (err)
{
}
Utilities.sleep(200);
}
}
//client.logout-------------------------------------------------------------------------------------------
var client_logout = sklik_api([{'session': client_login.session}], 'client.logout');
} catch (err) {
Logger.log("SKLIK: " + err);
}
table += "</table>";
//Send mail-----------------------------------------------------------------------------------------------
if (send_mail == 1)
{
MailApp.sendEmail({to: mail, subject: subject, htmlBody: table});
}
}
//********************************************************************************************************
function sklik_api(parameters, method) {
var url = 'https://api.sklik.cz/drak/json/' + method;
var options = {'method': 'post', 'contentType': 'application/json', 'muteHttpExceptions': true, 'payload': JSON.stringify(parameters)};
try {
return(JSON.parse(UrlFetchApp.fetch(url, options)));
} catch (err)
{
Utilities.sleep(1000);
try {
return(JSON.parse(UrlFetchApp.fetch(url, options)));
} catch (err)
{
Utilities.sleep(1000);
return(JSON.parse(UrlFetchApp.fetch(url, options)));
}
}
}
//--------------------------------------------------------------------------------------------------------
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)
}