//Url config spreadsheet-------------------------------------------------------------------------------------------------------------
//***********************************************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('config spreadsheet url');
/************************************************************************************************************************************
Script:      Cost import
Version:     15.01.2018
Created By:  Stanislav Jilek [standajilek.cz]
Idea:        Karel Rujzl [rujzl.cz] a Petra Vetrovska [vetrovka.cz]
/***********************************************************************************************************************************/

function main() {
//Settings*************************************************************************************************************************** 
//Spreadsheet
    var settings_sheet = ss_config.getSheetByName("cost_import");
    var settings = settings_sheet.getRange("A4:T" + settings_sheet.getLastRow()).getValues();

    //count of accounts
    for (var j = 0; j < settings.length; j++)
    {
//Google Analytics
        var account_id = settings[j][0];
        var web_property_id = settings[j][1];
        var import_id = settings[j][2];
        var days_back = settings[j][3];
//Sklik
        var sklik_token = settings[j][4];
        var sklik_account = settings[j][5];
        var sklik_source = settings[j][6];
        var sklik_medium = settings[j][7];
        var sklik_level = settings[j][8];
        var sklik_exchange_rate = settings[j][9];
        var sklik_filter = settings[j][10];
        var sklik_condition = settings[j][11];
//Facebook
        var facebook_token = settings[j][12];
        var facebook_account = settings[j][13];
        var facebook_source = settings[j][14];
        var facebook_medium = settings[j][15];
        var facebook_level = settings[j][16];
        var facebook_exchange_rate = settings[j][17];
        var facebook_filter = settings[j][18];
        var facebook_condition = settings[j][19];
//Array with data 
        var data = [["ga:date", "ga:medium", "ga:source", "ga:adCost", "ga:adClicks", "ga:impressions", "ga:campaign"]];

//***********************************************************************************************************************************
//DATE-------------------------------------------------------------------------------------------------------------------------------
        var start_date = new Date();
        start_date.setUTCDate(start_date.getUTCDate() - days_back);
        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');

//SKLIK------------------------------------------------------------------------------------------------------------------------------
        try
        {
            if (sklik_token != "")
            {
//client.login 
                var client_login = sklik_api([sklik_token], 'client.loginByToken');
                var session = client_login.session;

//client.get
                var client_get = sklik_api([{'session': session}], 'client.get');
                for (var i = 0; i < client_get.foreignAccounts.length; i++)
                {
                    if (sklik_account.toLowerCase() == client_get.foreignAccounts[i].username.toLowerCase())
                    {
                        var sklik_account_id = client_get.foreignAccounts[i].userId;
                    }
                }

//client.stats
                if (sklik_level.toLowerCase() == "account")
                {
                    var client_stats = sklik_api([{'session': session, 'userId': sklik_account_id}, {'dateFrom': start_date, 'dateTo': end_date, 'granularity': 'daily'}], 'client.stats');
                    for (var i = 0; i < client_stats.report.length; i++)
                    {
                        data.push([(client_stats.report[i].date).substr(0, 8), sklik_medium, sklik_source, (client_stats.report[i].price / 100 * sklik_exchange_rate).toFixed(2), client_stats.report[i].clicks, client_stats.report[i].impressions, ""]);
                    }
                }

//campaigns.createReport      
                if (sklik_level.toLowerCase() == "campaigns")
                {
                    var campaigns_createReport = sklik_api([{'session': session, 'userId': sklik_account_id}, {'dateFrom': start_date, 'dateTo': end_date}, {'statGranularity': 'daily'}], 'campaigns.createReport');

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

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

                    if (limit > 0)
                    {
                        for (var i = 0; i < offset; i++)
                        {
                            try
                            {
//campaigns.readReport    
                                var campaigns_readReport = sklik_api([{'session': session, 'userId': sklik_account_id},
                                    report_id,
                                    {'offset': i * (parseInt(5000 / days_back)),
                                        'limit': parseInt(5000 / days_back),
                                        'allowEmptyStatistics': false,
                                        'displayColumns': ['name', 'totalMoney', 'clicks', 'impressions']}],
                                        'campaigns.readReport');

                                for (var k = 0; k < campaigns_readReport.report.length; k++)
                                {
                                    for (var l = 0; l < days_back; l++)
                                    {
                                        try {
                                            if (new RegExp(sklik_filter.toString().toLowerCase(), "i").test(campaigns_readReport.report[k].name.toLowerCase()) == sklik_condition || sklik_filter == "")
                                            {
                                                data.push([campaigns_readReport.report[k].stats[l].date, sklik_medium, sklik_source, (campaigns_readReport.report[k].stats[l].totalMoney / 100 * sklik_exchange_rate).toFixed(2), campaigns_readReport.report[k].stats[l].clicks, campaigns_readReport.report[k].stats[l].impressions, '"' + campaigns_readReport.report[k].name + '"']);
                                            }
                                        } catch (err)
                                        {
                                        }
                                    }
                                }
                            } catch (err)
                            {
                                Logger.log("SKLIK: " + err);
                            }
                            Utilities.sleep(200);
                        }
                    }
                }

//client.logout
                var client_logout = sklik_api([{'session': session}], 'client.logout');
            }
        } catch (err)
        {
            Logger.log("SKLIK: " + err);
        }

//FACEBOOK---------------------------------------------------------------------------------------------------------------------------
        try
        {
            if (facebook_token != "")
            {
                var url = "/v2.11/act_" + facebook_account + "/insights?" +
                        "fields=date_start,spend,clicks,impressions,campaign_name" +
                        "&level=" + facebook_level.replace("s", "") +
                        "&time_range[since]=" + start_date + "&time_range[until]=" + end_date +
                        "&sort=date_start_ascending" +
                        "&time_increment=1" +
                        "&limit=1000000" +
                        "&access_token=" + facebook_token;

                var response = fb_api(url);

                for (var i = 0; i < response.data.length; i++)
                {
                    var campaign_name = "";
                    if (facebook_level == "campaigns")
                    {
                        campaign_name = response.data[i].campaign_name;
                    }

                    if (new RegExp(facebook_filter.toString().toLowerCase(), "i").test(campaign_name.toLowerCase()) == facebook_condition || facebook_filter == "" || facebook_level == "account")
                    {

                        if (campaign_name != "")
                        {
                            campaign_name = '"' + campaign_name + '"';
                        }

                        data.push([response.data[i].date_start.replace("-", "").replace("-", "").replace("-", ""),
                            facebook_medium,
                            facebook_source,
                            (response.data[i].spend * facebook_exchange_rate).toFixed(2),
                            response.data[i].clicks,
                            response.data[i].impressions,
                            campaign_name]);
                    }
                }
            }
        } catch (err)
        {
            Logger.log("FACEBOOK: " + err);
        }

//ANALYTICS--------------------------------------------------------------------------------------------------------------------------  
        try
        {
            if (data.length > 1)
            {
                //create file
                var blob = Utilities.newBlob(data.join("\n"), "application/octet-stream", "Cost import");
                //import file
                var upload = Analytics.Management.Uploads.uploadData(account_id, web_property_id, import_id, blob);
            }
        } catch (err)
        {
            Logger.log("ANALYTICS: " + err);
        }
    }
}

//***********************************************************************************************************************************
function sklik_api(parameters, method) {
    var url = 'https://api.sklik.cz/jsonApi/drak/' + 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 fb_api(settings_url) {
    var url = 'https://graph.facebook.com' + settings_url;
    var options = {'method': 'get', 'contentType': 'application/json', 'muteHttpExceptions': true};

    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)));
        }
    }
}