//Url config spreadsheet-------------------------------------------------------------------
//*****************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('config spreadsheet url');
var settings_row = 3;
/******************************************************************************************
Script:     Url 404 control   
Version:    Adwords & Sklik 22.09.2023
Created By: Stanislav Jilek [standajilek.cz]
/*****************************************************************************************/

function main() {
//Settings---------------------------------------------------------------------------------
    var settings_sheet = ss_config.getSheetByName("url_404_control");

    var mail = settings_sheet.getRange("A" + settings_row).getValue();
    var subject = settings_sheet.getRange("B" + settings_row).getValue();
    var sklik_token = settings_sheet.getRange("C" + settings_row).getValue();
    var sklik_account = settings_sheet.getRange("D" + settings_row).getValue();
    var adwords_account_id = settings_sheet.getRange("E" + settings_row).getValue();

//Variables--------------------------------------------------------------------------------
    var script_start = new Date();
    var minute = 29;
    var script_limit = 1000 * 60 * minute;

    var adwords_ads_url = [];
    var adwords_keywords_url = [];
    var adwords_sitelinks_url = [];
    var adwords_stats_url = [];

    var sklik_ads_url = [];
    var sklik_ads_404_url = [];
    var sklik_keywords_url = [];
    var sklik_sitelinks_url = [];
    var sklik_stats_url = [];

    var all_url = [];
    var unique_url_sklik_404 = [];
    var unique_url = [];
    var url_404 = [];
    var count_200 = 0;
    var count_404 = 0;
    var currency = "";

//ADWORDS**********************************************************************************
//Mcc select-------------------------------------------------------------------------------  
    try
    {
        MccApp.select(MccApp.accounts().withIds([adwords_account_id]).get().next());
    } catch (err)
    {
    }

    try
    {
        var account_name = AdsApp.currentAccount().getName();
        var currency = AdsApp.currentAccount().getCurrencyCode();

//Final urls------------------------------------------------------------------------------- 
        //Adwords ads  
        var adwords_ads = AdsApp.ads()
            .withCondition("campaign.status = ENABLED")
            .withCondition("ad_group.status = ENABLED")
            .withCondition("ad_group_ad.status = ENABLED")
            .withCondition("metrics.clicks > 0")
            .forDateRange("YESTERDAY")
            .get();

        while (adwords_ads.hasNext())
        {
            var row = adwords_ads.next();
            var url = row.urls().getFinalUrl();
            if (url != null) {
                url = url.substr(0, (url.toLowerCase().indexOf("utm") >= 0) ? url.toLowerCase().indexOf("utm") - 1 : url.length);
                adwords_ads_url.push(url);
                adwords_stats_url.push({url: url, clicks: row.getStatsFor("YESTERDAY").getClicks(), cost: row.getStatsFor("YESTERDAY").getCost()});
            }
        }

        //Adwords keywords  
        var adwords_keywords = AdsApp.keywords()
            .withCondition("campaign.status = ENABLED")
            .withCondition("ad_group.status = ENABLED")
            .withCondition("ad_group_criterion.status = ENABLED")
            .withCondition("metrics.clicks > 0")
            .forDateRange("YESTERDAY")
            .get();

        while (adwords_keywords.hasNext())
        {
            var row = adwords_keywords.next();
            var url = row.urls().getFinalUrl();
            if (url != null) {
                url = url.substr(0, (url.toLowerCase().indexOf("utm") >= 0) ? url.toLowerCase().indexOf("utm") - 1 : url.length);
                adwords_keywords_url.push(url);
                adwords_stats_url.push({url: url, clicks: row.getStatsFor("YESTERDAY").getClicks(), cost: row.getStatsFor("YESTERDAY").getCost()});
            }
        }

        //Adwords settings control
        if (adwords_stats_url.length == 0) {
            Logger.log("GOOGLE ADS: V tomto účtu nebyl zaznamenán za včerejší den ani jeden proklik, je vše v pořádku? Máte vše správně nastaveno?");
        }
    } catch (err)
    {
        Logger.log("GOOGLE ADS: " + err);
    }

//SKLIK************************************************************************************    
//Sklik login------------------------------------------------------------------------------
    try
    {
        var sklik_login = sklik_api([sklik_token], 'client.loginByToken');
        var session = sklik_login.session;

        //Sklik get userID
        var sklik_get = sklik_api([{'session': session}], 'client.get');

        for (var i = 0; i < sklik_get.foreignAccounts.length; i++)
        {
            if (sklik_account.toLowerCase() == sklik_get.foreignAccounts[i].username.toLowerCase())
            {
                var sklik_account_id = sklik_get.foreignAccounts[i].userId;
            }
        }

//Final urls-------------------------------------------------------------------------------   
        //Yesterday
        var yesterday = new Date();
        yesterday.setUTCDate(yesterday.getUTCDate() - 1);
        yesterday = Utilities.formatDate(yesterday, "GTM - 1", 'yyyy-MM-dd');

        //Sklik ads
        try
        {
            var sklik_ads_create_report = sklik_api([{'session': session, 'userId': sklik_account_id},
                {'statisticsConditions': [{'columnName': 'clicks', 'operator': 'GT', 'intValue': 0}],
                    'status': ['active'],
                    'isDeleted': false,
                    'campaign': {'status': ['active'], 'isDeleted': false},
                    'group': {'status': ['active'], 'isDeleted': false},
                    'dateFrom': yesterday, 'dateTo': yesterday}],
                    'ads.createReport');

            var report_id = sklik_ads_create_report.reportId;
            var limit = sklik_ads_create_report.totalCount;

            var offset = Math.ceil(limit / 5000);

            if (limit > 0)
            {
                for (var i = 0; i < offset; i++)
                {
                    try
                    {
                        var sklik_ads_read_report = sklik_api([{'session': session, 'userId': sklik_account_id},
                            report_id,
                            {'offset': i * 5000,
                                'limit': 5000,
                                'allowEmptyStatistics': false,
                                'displayColumns': ['adType', 'clickthruUrl', 'finalUrl', 'clicks', 'totalMoney']}],
                                'ads.readReport');

                        for (var j = 0; j < sklik_ads_read_report.report.length; j++)
                        {
                            var url = sklik_ads_read_report.report[j].finalUrl;
                            if (url != null)
                            {
                                url = url.substr(0, (url.toLowerCase().indexOf("utm") >= 0) ? url.toLowerCase().indexOf("utm") - 1 : url.length);
                                sklik_ads_url.push(url);
                                sklik_stats_url.push({url: url, clicks: sklik_ads_read_report.report[j].stats[0].clicks, cost: sklik_ads_read_report.report[j].stats[0].totalMoney / 100});
                            }
                        }
                    } catch (err)
                    {
                    }
                    Utilities.sleep(200);
                }
            }
        } catch (err)
        {
            Logger.log("SKLIK ads:" + err);
        }

        //Sklik ads 404
        try
        {
            var sklik_ads_create_report = sklik_api([{'session': session, 'userId': sklik_account_id},
                {'adStatus': ['deny', 'deny_invalid_url'],
                    'status': ['active'],
                    'isDeleted': false,
                    'campaign': {'status': ['active'], 'isDeleted': false},
                    'group': {'status': ['active'], 'isDeleted': false},
                    'dateFrom': yesterday, 'dateTo': yesterday}],
                    'ads.createReport');

            var report_id = sklik_ads_create_report.reportId;
            var limit = sklik_ads_create_report.totalCount;

            var offset = Math.ceil(limit / 5000);

            if (limit > 0)
            {
                for (var i = 0; i < offset; i++)
                {
                    try
                    {
                        var sklik_ads_read_report = sklik_api([{'session': session, 'userId': sklik_account_id},
                            report_id,
                            {'offset': i * 5000,
                                'limit': 5000,
                                'allowEmptyStatistics': true,
                                'displayColumns': ['adType', 'clickthruUrl', 'finalUrl']}],
                                'ads.readReport');

                        for (var j = 0; j < sklik_ads_read_report.report.length; j++)
                        {
                            var url = sklik_ads_read_report.report[j].finalUrl;
                            if (url != null)
                            {
                                url = url.substr(0, (url.toLowerCase().indexOf("utm") >= 0) ? url.toLowerCase().indexOf("utm") - 1 : url.length);
                                sklik_ads_url.push(url);
                                sklik_ads_404_url.push({url: url, clicks: 0, cost: 0});
                            }
                        }
                    } catch (err)
                    {
                    }
                    Utilities.sleep(200);
                }
            }
        } catch (err)
        {
            Logger.log("SKLIK ads 404:" + err);
        }

        //Sklik keywords
        try
        {
            var sklik_keywords_create_report = sklik_api([{'session': session, 'userId': sklik_account_id},
                {'statisticsConditions': [{'columnName': 'clicks', 'operator': 'GT', 'intValue': 0}],
                    'mixedStatus': ['active'],
                    'isDeleted': false,
                    'urls': [{'operator': 'CONTAINS', 'value': 'http'}],
                    'campaign': {'status': ['active'], 'isDeleted': false},
                    'group': {'status': ['active'], 'isDeleted': false},
                    'dateFrom': yesterday, 'dateTo': yesterday}],
                    'keywords.createReport');

            var report_id = sklik_keywords_create_report.reportId;
            var limit = sklik_keywords_create_report.totalCount;

            var offset = Math.ceil(limit / 5000);

            if (limit > 0)
            {
                for (var i = 0; i < offset; i++)
                {
                    try
                    {
                        var sklik_keywords_read_report = sklik_api([{'session': session, 'userId': sklik_account_id},
                            report_id,
                            {'offset': i * 5000,
                                'limit': 5000,
                                'allowEmptyStatistics': false,
                                'displayColumns': ['url', 'clicks', 'totalMoney']}],
                                'keywords.readReport');

                        for (var j = 0; j < sklik_keywords_read_report.report.length; j++)
                        {
                            var url = sklik_keywords_read_report.report[j].url;
                            if (url != null)
                            { 
                                url = url.substr(0, (url.toLowerCase().indexOf("utm") >= 0) ? url.toLowerCase().indexOf("utm") - 1 : url.length);
                                sklik_keywords_url.push(url);
                                sklik_stats_url.push({url: url, clicks: sklik_keywords_read_report.report[j].stats[0].clicks, cost: sklik_keywords_read_report.report[j].stats[0].totalMoney / 100});
                            }
                        }
                    } catch (err)
                    {
                    }
                    Utilities.sleep(200);
                }

            }
        } catch (err)
        {
            Logger.log("SKLIK keywords:" + err);
        }

        //Sklik sitelinks
        try
        {
            var sklik_sitelinks = sklik_api([{'session': session, 'userId': sklik_account_id}], 'sitelinks.list');

            if (sklik_sitelinks.sitelinks.length > 0)
            {
                for (var i = 0; i < sklik_sitelinks.sitelinks.length; i++)
                {
                    var url = sklik_sitelinks.sitelinks[i].url;
                    if (sklik_sitelinks.sitelinks[i].deleted != true && url != null)
                    {  
                        url = url.substr(0, (url.toLowerCase().indexOf("utm") >= 0) ? url.toLowerCase().indexOf("utm") - 1 : url.length);
                        sklik_sitelinks_url.push(url);
                        sklik_stats_url.push({url: url, clicks: 0, cost: 0});
                    }
                }
            }
        } catch (err)
        {
            Logger.log("SKLIK sitelinks:" + err);
        }

        //Sklik logout
        var sklik_logout = sklik_api([{'session': session}], 'client.logout');

        //Sklik settings control
        if (sklik_stats_url.length == 0 && sklik_ads_404_url.length == 0) {
            Logger.log("SKLIK: V tomto účtu nebyl zaznamenán za včerejší den ani jeden proklik, je vše v pořádku? Máte vše správně nastaveno?");
        }
    } catch (err)
    {
        Logger.log("SKLIK:" + err);
    }

//*****************************************************************************************      
//Connection all urls----------------------------------------------------------------------
    all_url = all_url.concat(adwords_stats_url, sklik_stats_url);

//Remove duplicates------------------------------------------------------------------------
    all_url.reduce(function (res, value) {
        if (!res[value.url]) {
            res[value.url] = {
                clicks: 0,
                cost: 0,
                url: value.url
            };
            unique_url_sklik_404.push(res[value.url])
        }
        res[value.url].clicks += value.clicks;
        res[value.url].cost += value.cost;
        return res;
    }, {});

    unique_url_sklik_404.sort(sort_array);

    unique_url_sklik_404 = unique_url_sklik_404.concat(sklik_ads_404_url).reverse();

    unique_url_sklik_404.reduce(function (res, value) {
        if (!res[value.url]) {
            res[value.url] = {
                clicks: 0,
                cost: 0,
                url: value.url
            };
            unique_url.push(res[value.url])
        }
        res[value.url].clicks += value.clicks;
        res[value.url].cost += value.cost;
        return res;
    }, {});

//Test urls-------------------------------------------------------------------------------- 
    var max_url = unique_url.length;
    var script_end = new Date();
    var i = 0;

    while ((i <= max_url) && ((script_end - script_start) <= script_limit))
    {
        try
        {
        
            try {
                var response_code = UrlFetchApp.fetch(unique_url[i].url, {muteHttpExceptions: true}).getResponseCode();
            } catch (err)
            {
                Utilities.sleep(500);
                try {
                    var response_code = UrlFetchApp.fetch(unique_url[i].url, {muteHttpExceptions: true}).getResponseCode();
                } catch (err)
                {
                    Utilities.sleep(500);
                    var response_code = UrlFetchApp.fetch(unique_url[i].url, {muteHttpExceptions: true}).getResponseCode();
                }
            }

            if (response_code == 404) {

                //Create adwords label
                var adwords_label = [];
                if (adwords_ads_url.indexOf(unique_url[i].url) != -1) {
                    adwords_label.push("ads");
                }
                if (adwords_keywords_url.indexOf(unique_url[i].url) != -1) {
                    adwords_label.push("keywords");
                }
                if (adwords_sitelinks_url.indexOf(unique_url[i].url) != -1) {
                    adwords_label.push("sitelinks");
                }
                adwords_label = adwords_label.join(", ");

                //Create sklik label
                var sklik_label = [];
                if (sklik_ads_url.indexOf(unique_url[i].url) != -1) {
                    sklik_label.push("ads");
                }
                if (sklik_keywords_url.indexOf(unique_url[i].url) != -1) {
                    sklik_label.push("keywords");
                }
                if (sklik_sitelinks_url.indexOf(unique_url[i].url) != -1) {
                    sklik_label.push("sitelinks");
                }
                sklik_label = sklik_label.join(", ");

                //Adwords sum stats                              
                var adwords_clicks = 0;
                var adwords_cost = 0;
                for (var j = 0; j < adwords_stats_url.length; j++)
                {
                    if (adwords_stats_url[j].url == unique_url[i].url)
                    {
                        adwords_clicks += adwords_stats_url[j].clicks;
                        adwords_cost += adwords_stats_url[j].cost;
                    }
                }

                //Sklik sum stats                              
                var sklik_clicks = 0;
                var sklik_cost = 0;
                for (var j = 0; j < sklik_stats_url.length; j++)
                {
                    if (sklik_stats_url[j].url == unique_url[i].url)
                    {
                        sklik_clicks += sklik_stats_url[j].clicks;
                        sklik_cost += sklik_stats_url[j].cost;
                    }
                }

                var clicks = adwords_clicks + sklik_clicks;
                var cost = adwords_cost + sklik_cost;

                url_404.push({url: unique_url[i].url, adwords_label: adwords_label, adwords_clicks: adwords_clicks, adwords_cost: adwords_cost, sklik_label: sklik_label, sklik_clicks: sklik_clicks, sklik_cost: sklik_cost, clicks: clicks, cost: cost});

                count_404 += 1;
            } else {
                count_200 += 1;
            }
        } catch (error)
        {
        }
        Utilities.sleep(200);
        var script_end = new Date();
        i += 1;
    }

//Logger----------------------------------------------------------------------------------  
    Logger.log("Checked top unique url -> " + (count_200 + count_404));
    Logger.log("Response code 200 -> " + count_200);
    Logger.log("Response code 404 -> " + count_404);

//Send mail-------------------------------------------------------------------------------
    if (url_404.length > 0) {

        //Sort array
        url_404.sort(sort_array).reverse();

        //Create html
        var html = "<table border='1' style='border-collapse: collapse;' cellpadding='5'>" +
                "<tr bgcolor='#ffd75d'><th rowspan='2'>URL 404</th><th colspan='3' bgcolor='#4fabe5'>GOOGLE ADS</th><th colspan='3' bgcolor='#ff4646'>SKLIK</th><th colspan='2'>SUM</th></tr>" +
                "<tr bgcolor='#ffd75d'><th bgcolor='#7dc7f5'>Label</th><th bgcolor='#7dc7f5'>Clicks</th><th bgcolor='#7dc7f5'>Cost</th><th bgcolor='#fd9a9a'>Label</th><th bgcolor='#fd9a9a'>Clicks</th><th bgcolor='#fd9a9a'>Cost</th><th bgcolor='#fde59e'>Clicks</th><th bgcolor='#fde59e'>Cost</th></tr>"

        for (var i = 0; i < url_404.length; i++)
        {
            html = html + "<tr bgcolor='" + row_color(i) + "'><td nowrap>" + url_404[i].url + "</td>" +
                    "<td nowrap bgcolor='#bae4fe'>" + url_404[i].adwords_label + "</td>" +
                    "<td nowrap align='right' bgcolor='#bae4fe'>" + number_format(url_404[i].adwords_clicks.toFixed(0)) + "</td>" +
                    "<td nowrap align='right' bgcolor='#bae4fe'>" + number_format(url_404[i].adwords_cost.toFixed(0)) + " " + currency + "</td>" +
                    "<td nowrap bgcolor='#fbb3b3'>" + url_404[i].sklik_label + "</td>" +
                    "<td nowrap align='right' bgcolor='#fbb3b3'>" + number_format(url_404[i].sklik_clicks.toFixed(0)) + "</td>" +
                    "<td nowrap align='right' bgcolor='#fbb3b3'>" + number_format(url_404[i].sklik_cost.toFixed(0)) + " CZK</td>" +
                    "<td nowrap align='right'>" + number_format(url_404[i].clicks.toFixed(0)) + "</td>" +
                    "<td nowrap align='right'>" + number_format(url_404[i].cost.toFixed(0)) + " " + currency + "</td></tr>"
        }

        html = html + "</table>";

        MailApp.sendEmail({to: mail, subject: subject, htmlBody: html});
    }
}

//*****************************************************************************************
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 sort_array(a, b) {
    if (a.cost === b.cost) {
        return 0;
    } else {
        return (a.cost < b.cost) ? -1 : 1;
    }
}
//----------------------------------------------------------------------------------------- 
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 = "#ececec";
    }
    return(row)
}