//Nastavení----------------------------------------------------------------------------------------------
//*******************************************************************************************************
var spreadsheet = 'https://docs.google.com/spreadsheets/d/1J1efr15eFtggt5sffsre544e5mNX_SGfU/edit';
var token = '0x101c0a3dd0654sd5fsdcgt1r56sf6f1s5f6sd65sdf4fs58ssf6fee1051c92b230514a-account@seznam.cz';
var sklik_account = 'account@seznam.cz'; //pokud používáte token z MCC účtu, zadejte název single účtu
/********************************************************************************************************    
Skript:         Sklik API keywords
Verze:          06.12.2017
Návod vytvořil: Stanislav Jílek [standajilek.cz]
*********************************************************************************************************/

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

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

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

//createReport-------------------------------------------------------------------------------------------
    //Datum
    var start_date = new Date();
    start_date.setUTCDate(start_date.getUTCDate() - 7);
    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 keywords_createReport = sklik_api([{'session': session, 'userId': sklik_account_id},
        {'statisticsConditions': [{'columnName': 'impressions', 'operator': 'GT', 'intValue': 10}],
            'mixedStatus': ['active'],
            'isDeleted': false,
            'campaign': {'status': ['active'], 'isDeleted': false},
            'group': {'status': ['active'], 'isDeleted': false},
            'dateFrom': start_date,
            'dateTo': end_date}],
            'keywords.createReport');

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

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

    for (var i = 0; i < offset; i++)
    {
        try
        {       
//readReport---------------------------------------------------------------------------------------------
            var keywords_readReport = sklik_api([{'session': session, 'userId': sklik_account_id},
                report_id,
                {'offset': i * 5000,
                    'limit': 5000,
                    'allowEmptyStatistics': false,
                    'displayColumns': ['campaign.name', 'group.name', 'name', 'clicks', 'impressions', 'totalMoney', 'conversions', 'conversionValue']}],
                    'keywords.readReport');

            //cyklus pro procházení statistik
            if (keywords_readReport.report.length > 0)
            {
                for (var j = 0; j < keywords_readReport.report.length; j++)
                {
                    var campaign = keywords_readReport.report[j].campaign.name
                    var group = keywords_readReport.report[j].group.name
                    var keyword = keywords_readReport.report[j].name
                    var clicks = keywords_readReport.report[j].stats[0].clicks;
                    var impressions = keywords_readReport.report[j].stats[0].impressions;
                    var price = keywords_readReport.report[j].stats[0].totalMoney / 100;
                    var conversions = keywords_readReport.report[j].stats[0].conversions;
                    var conversions_value = keywords_readReport.report[j].stats[0].conversionValue;

                    //export do pole                     
                    sheet_row = [campaign, group, keyword, clicks, impressions, price, conversions, conversions_value];
                    sheet_export.push(sheet_row);
                }
            }
        } catch (err)
        {
        }
        Utilities.sleep(200);
    }

//hlavička tabulky  
    sheet.getRange('A1:H1').setValues([['Kampaň', 'Sestava', 'Klíčové slovo', 'Prokliky', 'Zobrazení', 'Cena', 'Konverze', 'Hodnota konverze']])
//smazání předchozích dat
    sheet.getRange("A2:H").clearContent();
//export do spreadsheetu  
    if (sheet_export.length > 0 && sheet_row.length > 0)
    {
        sheet.getRange(2, 1, sheet_export.length, sheet_row.length).setValues(sheet_export);
    }

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

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