function onOpen()
{
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var searchMenuEntries = [ {name: "Отчет за сентябрь", functionName: "test"}];
    ss.addMenu("Kaiten.io", searchMenuEntries);
}

function getInfo(urlparams)
{
  var USERNAME = 'makcrx';
  var PASSWORD = '6UJrDSYY';
  var apiUrl = 'https://analitika.kaiten.io/api/v1/';

  var headers = {
    "Authorization" : "Basic " + Utilities.base64Encode(USERNAME + ':' + PASSWORD)
  };

  var params = {
    "method":"GET",
    "headers":headers
  };

  var url = apiUrl + urlparams;
  var response = UrlFetchApp.fetch(url, params);

  return JSON.parse(response.getContentText());
}

function getTimelogs(cardID) {
  var dataSet = getInfo('cards/' + cardID + '/time-logs');
  return dataSet;
}

function test() {
  getReport({
    board_id: 6111,
    updated_after: '2017-09-01',
    updated_before: '2017-10-01',
    limit: 99999,
  });
}

function getReport(params) {
  var main = SpreadsheetApp.getActiveSpreadsheet();

  var p = [];
  for (var key in params) {
    var value = params[key];
    p.push(key + '=' + value);
  }
  var urlparams = p.join('&');
  //var dataSet = getInfo('cards?board_id=6111&updated_after=' + params.updated_after + '&limit=99999');
  var dataSet = getInfo('cards?' + urlparams);

  // названия колонок
  var colnames = ['Card ID', 'Name', 'Type', 'Tags', 'Status'];
  var personnames = ['Максим Шулин', 'Вячеслав Нестеренко', 'Алексей Зубарев', 'Марина Юрочкина'];
  colnames = colnames.concat(personnames);

  var rows = {};

  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];

    var cardID = data.id;
    var cardName = data.title;
    var cardType = data.type ? data.type.name : '';

    var tags = [];
    if (data.tags)
      for (j = 0; j < data.tags.length; j++) {
        tags.push(data.tags[i] ? data.tags[i].name : '');
      }
    tags = tags.join(', ');

    var status = data.column.title;
    var updated = data.updated;

    // API-запрос логов
    var timelogs = getTimelogs(cardID);

    var times = {};
    for (j = 0; j < personnames.length; j++) {
      var name = personnames[j];
      times[name] = '';
    }

    if (timelogs)
      for (j = 0; j < timelogs.length; j++) {
        var log = timelogs[j];

        var name = log.user.full_name;
        var time = log.time_spent;

        if (times[name] != '')
          times[name] += time;
        else
          times[name] = time;
      }

    // помещаем данные для отображения в таблице в rows
    if (status == 'Согласование' || status == 'Готово' || status == 'Принято') {
      if (!rows[cardType]) {
        rows[cardType] = [];
        rows[cardType].push(colnames);
      }

      var row = [cardID, cardName, cardType, tags, status];
      for (j=0; j < personnames.length; j++) {
        var name = personnames[j];
        var time = times[name];
        row.push(time);
      }

      rows[cardType].push(row);
    }
  }


  // Отображаем данные в таблицах
  for (cardType in rows) {
    var sheet = main.getSheetByName(cardType);
    if (!sheet)
      sheet = main.insertSheet(cardType);

    dataRange = sheet.getRange(1, 1, rows[cardType].length, colnames.length);
    dataRange.setValues(rows[cardType]);

    // Total
    sheet.getRange(1, 10).setValue('Total/hr');
    sheet.getRange(2, 10, rows[cardType].length-1, 1).setFormulaR1C1("=SUM(R[0]C[-4]:R[0]C[-1])/60");

    // Результирующая строка
    sheet.getRange(rows[cardType].length + 1, 2).setValue('Итого:');
    sheet.getRange(rows[cardType].length + 1, 3).setFormulaR1C1("=COUNTA(R[-" + (rows[cardType].length - 1) + "]C[0]:R[-1]C[0])");
    sheet.getRange(rows[cardType].length + 1, 6, 1, personnames.length + 1).setFormulaR1C1("=SUM(R[-" + (rows[cardType].length - 1) + "]C[0]:R[-1]C[0])");

    // Format
    sheet.getRange(1, 1, 1, 20).setFontWeight('bold');
    sheet.getRange(rows[cardType].length + 1, 1, 1, 20).setFontWeight('bold');
    sheet.getRange(1, 10, rows[cardType].length + 1, 1).setNumberFormat('0.00');
    sheet.getRange(1, 1, rows[cardType].length + 1, 2).setHorizontalAlignment("left");
    sheet.getRange(1, 3, rows[cardType].length + 1, 20).setHorizontalAlignment("right");
    sheet.getRange(rows[cardType].length + 1, 2).setHorizontalAlignment("right");
  }
}