//Nastavení----------------------------------------------------------------------------------------------
//*******************************************************************************************************
var spreadsheet = 'https://docs.google.com/spreadsheets/d/1J1efr15eFtggt5sffsre544e5mNX_SGfU/edit';
var sheet = 'data';
//Token
var token = '0x101c0a3dd0654sd5fsdcgt1r56sf6f1s5f6sd65sdf4fs58ssf6fee1051c92b230514a-account@seznam.cz';
var sklik_account = 'account@seznam.cz'; //pokud používáte token z MCC účtu, zadejte název single 
//Kampaně (1 -> ANO; 0 -> NE);
var suspended = 1; //pozastavené
var deleted = 1; //smazané
var zero = 1; //nulové dny
/********************************************************************************************************
Skript:   Výpis kampaní do spreadsheetu pro Google Data Studio
Verze:    06.12.2017
Vytvořil: Stanislav Jílek [standajilek.cz]
********************************************************************************************************/

function main() {
//Nadefinování spreadsheetu
    var ss = SpreadsheetApp.openByUrl(spreadsheet).getSheetByName(sheet);

//Pole pro export do spreadsheetu
    var sheet_export = [];
    var sheet_row = [];

    try
    {
//Přihlášení
//client.login-------------------------------------------------------------------------------------------
        var client_login = sklik_api([token], 'client.loginByToken');
        var session = client_login.session;

//client.get---------------------------------------------------------------------------------------------
        var client_get = sklik_api([{'session': session}], 'client.get');
        //cyklus, který projde všechny účty a přiřadí správné userId vámi nadefinovaného účtu
        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;
            }
        }

//Datum--------------------------------------------------------------------------------------------------
//Počet dní zpátky
        var days_back = Math.floor((new Date() - new Date(new Date().getFullYear() - 1, 0, 1)) / 1000 / 60 / 60 / 24);

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

        var campaign_date = [];

        for (var i = 0; i < days_back; i++)
        {
            var date = new Date();
            date.setUTCDate(date.getUTCDate() - (days_back - i));
            campaign_date.push(Utilities.formatDate(date, 'GTM - 1', 'yyyyMMdd'));
        }

//createReport-------------------------------------------------------------------------------------------
        var mixedStatus = ['active', 'active-exhausted_day_budget', 'active-expired_budget', 'active-expired_clicks', 'active-expired_time', 'active-low_budget', 'active-waiting'];

        if (suspended == 1)
        {
            mixedStatus.push('suspended');
        }

        if (deleted == 1)
        {
            mixedStatus.push('deleted');
        }

        var createReport = sklik_api([{'session': session, 'userId': sklik_account_id},
            {//'isDeleted': false,
                'mixedStatus': mixedStatus,
                'dateFrom': start_date, 'dateTo': end_date},
            {'statGranularity': 'daily'}],
                'campaigns.createReport');

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

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

        for (var i = 0; i < offset; i++)
        {

//readReport---------------------------------------------------------------------------------------------    
            var 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', 'status', 'deleted', 'context', 'clicks', 'impressions', 'totalMoney', 'conversions', 'conversionValue', 'avgPos']}],
                    'campaigns.readReport');

            for (var j = 0; j < readReport.report.length; j++)
            {
                var l = 0;

                for (var k = 0; k < days_back; k++)
                {
                    var status = readReport.report[j].status;
                    if (readReport.report[j].deleted == true)
                    {
                        status = "deleted";
                    }

                    var type = "fulltext";
                    if (readReport.report[j].context == true)
                    {
                        type = "context";
                    }

                    var empty = [campaign_date[k], readReport.report[j].name, status, type, 0, 0, 0, 0, 0, 0];

                    if (readReport.report[j].stats[l] == undefined)
                    {
                        if (zero == 1)
                        {
                            sheet_row = empty;
                            sheet_export.push(sheet_row);
                        }
                    } else if (readReport.report[j].stats[l].date == campaign_date[k])
                    {
                        sheet_row = [campaign_date[k], 
                                     readReport.report[j].name, 
                                     status, 
                                     type, 
                                     readReport.report[j].stats[l].clicks, 
                                     readReport.report[j].stats[l].impressions, 
                                     readReport.report[j].stats[l].totalMoney / 100, 
                                     readReport.report[j].stats[l].conversions, 
                                     readReport.report[j].stats[l].conversionValue,
                                     readReport.report[j].stats[l].avgPos * readReport.report[j].stats[l].impressions];
                        sheet_export.push(sheet_row);
                        l++;
                    } else if (zero == 1)
                    {
                        sheet_row = empty;
                        sheet_export.push(sheet_row);
                    }
                }
            }
        }

//Smazání přebytečných sloupců
        if (ss.getMaxColumns() > sheet_row.length)
        {
            ss.deleteColumns(sheet_row.length + 1, ss.getMaxColumns() - sheet_row.length);
        }
//Hlavička tabulky
        ss.getRange(1, 1, 1, sheet_row.length).setValues([['Datum', 'Kampaň', 'Stav', 'Typ sítě', 'Prokliky', 'Zobrazení', 'Cena', 'Konverze', 'Hodnota konverze', 'Průměrná denní pozice * Zobrazení']]);
//Smazání předchozích dat
        ss.getRange(2, 1, ss.getLastRow(), sheet_row.length).clearContent();
//Export do spreadsheetu
        if (sheet_export.length > 0 && sheet_row.length > 0)
        {
            ss.getRange(2, 1, sheet_export.length, sheet_row.length).setValues(sheet_export);
        }

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

//Obnovení spreadsheetu
        SpreadsheetApp.flush();

    } catch (err)
    {
        Logger.log(err);
    }
}

//-------------------------------------------------------------------------------------------------------
//Funkce pro komunikaci
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)));
        }
    }
}