Чаще всего наши клиенты применяют API Collaborator для построения нестандартных отчетов. «Нестандартные» — в том смысле, что они индивидуальны для каждого клиента, отображают данные в том порядке и виде который ему нужен для, например, презентации руководству или мониторинга влияния обучения на показатели продаж.
Любой отчет — это выборка данных плюс их аналитическая обработка и визуализация ее результата. Предлагаю начать с примеров выборки данных.
В первой статье (API Collaborator. Начинаем цикл практических кейсов…) я показал как получить доступ к API Collaborator. Мы написали код, который авторизирует нас в системе и выдает нам временный ключ доступа к API-интерфейсу. Теперь пора попробовать решить простые практические задачи по получению даннных через API.
Уточняем задачу и проверяем, что можем получить
Нам нужно получить список пользователей, которые заходили в систему в период с 01.11.2018 по 01.12.2018. Нужна возможность изменять период и по команде обновлять список пользователей. Позже полученный список будет нужен для выборок данных, которые связаны с пользователями. По-этому нам нужно знать идентификатор пользователя (user_id) и тот признак, по которму мы сами его сможем «опознать». Достаточным вариантом такого признака будет «Фамилия Имя Отчество».
REST API запрос, который возвращает описанную выборку выглядит так:
/api/rest.php/auth/users?page=1&count=10&filter[last_activity]=2018-11-01&filter[last_activity]=2018-12-01&sorting[last_activity]=desc
Разберем параметры запроса:
- /api/rest.php/auth/users — адрес API интерфейса перечня пользователей;
- page=1 и count=10 — говорит о том, что мы хотим получить первую страницу данных (page=1), и хотим чтобы все данные разбивались по 10 записей на страницу (count=10);
- filter[last_activity]=2018-11-01 и filter[last_activity]=2018-12-01 — фильтруем (filter) данные по дате последней активности пользователя (last_activity) — устанаваливаем нужный нам период выборки;
- sorting[last_activity]=desc — задаем по дате последней активности (last_activity) обратный (desc) порядок сортировки записей (sorting) в выборке, т.е. от самых новых к самым старым.
То, что выборка разбивается на страницы, позволяет уменьшить объемы данных при их передаче от системы. Нам никто не мешает задать большее количество записей на страницу, например, тысячу, но тогда прийдется выжидать больше времени на обработку такой выборки.
На этот запрос API Collaborator формирует ответ в формате JSON, который выглядит так:
Теперь я приведу пример тех данных, которые API Collaborator возвращает по каждому пользователю, они помещены в массив data[…]. Про назначение переменных можно догадаться из их названия, поэтому я буду это объяснять только когда понадобится с ними работать.
{
"id": 91139,
"login": "userlogin",
"firstname": "Мирослав",
"secondname": "Боцула",
"international_name": "Myroslav Botsula",
"patronymic": "Павлович",
"facebook_id": null,
"google_id": null,
"email": "[email protected]",
"work_contact": "",
"hide_email": false,
"gender": "male",
"birth_date": "02.04.1976",
"hide_birth_date": false,
"hide_phone": false,
"tags":["команда", "admin", "супервайзер", "collaborator"],
"is_active": true,
"is_deleted": false,
"status": "0",
"last_activity": "1543569613000",
"date_of_employment": null,
"date_of_assignment_current_position": null,
"need_edit": false,
"need_change_password": false,
"is_chief": true,
"use_two_factor_auth": false,
"position": "CEO",
"city": "Вінниця",
"department": "Відділ маркетингу",
"type": "",
"uid": "",
"created_at": "1482443797000",
"updated_at": "2018-11-26 17:46:09",
"fullname": "Боцула Мирослав",
"age": 42,
"permissions": ["analytics.can_see_analytics", "auth.can_delete_user",…],
"read_only": false,
"can_send_info_email": false,
"roles": [{id: 9, is_guest: false, title: "Администратор."},…],
"photo_thumb": "/static/e7/f2/e7f20e450c910ebdc9e2eb1a00498763.jpg"
},
Переходим непосредственно к коду. Как и раньше, рассмотрим два варианта реализации задачи — в Google Sheets и в Microsoft Excel. Как начать работать с редактором кода/макросов в этих средах — см. первую статью (API Collaborator. Начинаем цикл практических кейсов…) . Напишем код, который выведет на лист электронной таблицы две колонки с данными user_id и fullname.
Также создадим в электронной книге отдельный лист «settings» для настройки и хранения параметров нашей программы. Это упростит настройку логина и пароля для получения ключа доступа, хранение самого ключа и позволит удобно задавать период выборки данных.
Решаем задачу на Google Apps Sсript
Шаг 1. Создаем лист настроек
Создадим в Google Sheets новую рабочую книгу с листами «main» и «settings«. Первый лист будет содержать результат работы нашей программы, второй — ее настройки. Лист «settings» можно красиво оформить, но важно задать именованные диапазоны на каждую переменную, которая будет хранить настройки — см. рисунок.
Конечно, можно обращаться к ним по адресам — B3, B5 и т.д., но мне кажется это неудобным и при большом количестве переменных приведет к путаннице — т.е. мы будем терять время, чтоб разобраться что к чему.
Я подготовил четыре именованных ячейки:
[table id=1 /]
Теперь можно улучшить код для аутентификации нашего API-пользователя. Создадим в рабочей книге Google Sheets скрипт «main» в котором будет основной код нашей задачи. Также добавим к скриптам книги collaborator_api.gs с функцией _getAccessToken(URL, login, pass) для получения JWT-ключа. Самую последнюю версию collaborator_api.gs можно найти тут.
Внимание! По сравнению с кодом в первой статье _getAccessToken(URL, login, pass) обновлена — добавлен параметр URL и возврат keyJWT. Еще я добавил обработку ошибок, которые могут возникнуть. В таких случаях вместо keyJWT функция вернет код ошибки.
function _getAccessToken(URL, login, pass){
var keyJWT;
var res;
var options = {
'method' : 'post',
'contentType': 'application/json;charset=UTF-8',
'payload' : '{"email":"' + login + '","password":"' + pass + '"}',
'muteHttpExceptions' : true
};
var response = UrlFetchApp.fetch(URL + '/api/rest.php/auth/session', options);
var responseCode = response.getResponseCode();
switch(responseCode) {
case 200:
res = JSON.parse(response.getContentText());
keyJWT = res.access_token;
Logger.log('User: ' + res.fullname + '\n' + keyJWT);
break;
case 400:
keyJWT = 'Error 400';
Logger.log('Ошибка доступа: ' + responseCode + '\nПользователь не найден. Проверьте правильность ввода логина и пароля.');
break;
default:
keyJWT = 'Error ' + responseCode;
Logger.log('Ошибка доступа: ' + responseCode + '\nПроверьте, все ли параметры запроса правильные.');
break;
}
return keyJWT;
}
В скрипте «main» объявим переменные, через которые мы сможем обращаться к листам рабочей книги и именованным ячейкам листа «settings».
var SPREADSHEET = SpreadsheetApp.getActiveSpreadsheet();
var SHEET_MAIN = SPREADSHEET.getSheetByName("main");
var SHEET_SETTINGS = SPREADSHEET.getSheetByName("settings");
var rngJWT = SpreadsheetApp.getActive().getRangeByName('set_JWT');
var keyJWT = rngJWT.getValue();
var urlHome = SpreadsheetApp.getActive().getRangeByName('set_url').getValue();
- SPREADSHEET — наша рабочая книга,
- SHEET_MAIN — объект-лист «main»,
- SHEET_SETTINGS — объект-лист «settings»,
- rngJWT — объект-ячейка, которая содержит JWT-ключ,
- keyJWT — значение ключа,
- urlHome — адрес стартовой страницы портала Collaborator.
За получение ключа доступа будет отвечать отдельная функция getAccess(), ее код ниже. Получать новый ключ всякий раз, когда нам нужно выполнить API-запрос нерационально. Потому добавим простую проверку: если длина строки ключа keyJWT будет меньше, допустим, 20 символов (ячейка rngJWT с ключом пуста или содержит что-то неправильное, например, код ошибки), то получаем новый ключ. Иначе — считываем существующий ключ из ячейки rngJWT. Далее перед работой с API будем вызывать эту проверку функцией checkJWT().
function getAccess(){
var set_url = SpreadsheetApp.getActive().getRangeByName('set_url').getValue();
var set_login = SpreadsheetApp.getActive().getRangeByName('set_login').getValue();
var set_pass = SpreadsheetApp.getActive().getRangeByName('set_pass').getValue();
rngJWT.setValue(_getAccessToken(set_url, set_login, set_pass));
}
function checkJWT(){
if(keyJWT < 20 ){
getAccess();
}
keyJWT = rngJWT.getValue();
}
Переименуем функцию myFunction() в main() , вставим в нее вызов функцию проверки ключа и запустим ее.
function main() {
checkJWT();
}
Результатом будет авторизация в системе пользователя, логин и пароль которого записан в ячейках листа «settings». Если все пройдет без ошибок, то в ячейке с подписью «JWT:» появится строка ключа доступа.
Все описанные выше действия можно просмотреть в этом ролике:
Шаг 2. Добавляем функцию для отправки запроса к API Collaborator и получения ответа от сервера
Наша программа должна отправить REST API запрос системе Collaborator и обработать ответ от системы. Для этого мы добавим еще одну функцию в скрипт collaborator_api.gs, вот эта функция:
function _getResByCollaboratorAPI(keyJWT, urlRequest) {
var response = UrlFetchApp.fetch(urlRequest, {
headers: {
'Authorization': 'Bearer ' + keyJWT,
'Content-Type' : 'application/json;charset=UTF-8'
},
'method' : 'get',
'muteHttpExceptions' : true
});
var responseCode = response.getResponseCode();
var res;
switch(responseCode) {
case 200:
res = JSON.parse(response.getContentText());
//Logger.log(res);
break;
case 400:
res = 'Error 400';
Logger.log('Ошибка доступа: ' + responseCode + '\nПроверьте правильность ввода логина и пароля.');
break;
default:
res = 'Error ' + responseCode;
Logger.log('Ошибка доступа: ' + responseCode +
'\nПроверьте, все ли параметры запроса правильные.\n' +
'\n' + urlRequest);
break;
}
return res;
}
Функция принимает на вход два значения keyJWT и urlRequest — наш REST API запрос на выборку пользователей.
Далее используя метод UrlFetchApp.fetch() отправляет запрос на сервер системы и возвращает ответ сервера в переменную response. Запрос не отправляет на систему никаких данных, только команду на выборку. Такие запросы выполняются HTTP методом GET. По-этому для указан параметр ‘method’ : ‘get’.
Переменная responseCode будет получать код состояния ответа сервера, это позволит нам обработать ошибки запроса, что выполняется далее в инструкции switch(responseCode) {...}
Ответ сервера будет преобразован в объект JSON инструкцией res = JSON.parse(response.getContentText()); и это и будет результатом работы функции. Если возникнет ошибка, то результатом функции будет информация об ошибке в виде ‘Error 400’.
Теперь проверим как отработает эта функция по нашему запросу. Вставим в функцию main() несколько инструкций.
Переменная requestURL будет содержать API наш запрос на выборку пользователей.
var requestURL = '/api/rest.php/auth/users?page=1&count=10&filter[last_activity]=2018-11-01&filter[last_activity]=2018-12-01&sorting[last_activity]=desc'
В переменную res будем получать ответ сервера по запросу: var res = _getResByCollaboratorAPI(keyJWT, urlHome + requestURL); Полученный результат выведем в Журнал лога выполнения скрипта командой Logger.log(JSON.stringify(res));
Чтобы просмотреть Журнал логов нужно нажать комбинацию клавиш Ctrl + Enter или выбрать команду из меню «Вид» — «Журналы».
В итоге наша функция main() будет иметь вид:
function main() {
checkJWT();
var requestURL = '/api/rest.php/auth/users?page=1&count=10&filter[last_activity]=2018-11-01&filter[last_activity]=2018-12-01&sorting[last_activity]=desc';
var res = _getResByCollaboratorAPI(keyJWT, urlHome + requestURL);
Logger.log(JSON.stringify(res));
}
Запустим функцию и убедимся в получении ответа сервера.
Шаг 3. Создаем функцию обработки дат
Еще нам потребуется функция для преобразования данных типа дата-время, которые мы будем брать из ячеек электронной таблицы, в текстовую строку вида ‘2018-11-01’. Вот єта функция, добавьте ее в код скрипта main.js.
function dateToStr(date_val){
// return date value as <yyyy-mm-dd> format for URL request
var dd = date_val.getDate();
var mm = date_val.getMonth()+1; //January is 0!
var yyyy = date_val.getFullYear();
if(dd < 10) {
dd = '0' + dd
}
if(mm < 10) {
mm = '0' + mm
}
return yyyy + '-' + mm + '-' + dd;
}
Работу функции легко проверить. Можно вписать в любую ячуйку таблицы дату, например в ячейку А1 вписать 12.12.2017, а в соседней создать формулу =dateToStr(A1). Теперь просто меняем значение в ячейке А1 — наблюдаем как меняется результат нашей функции.
Шаг 4. Создаем функции для получения списка пользователей
Почти все готово для формирования запроса на сервер и получения списка пользователей. Создадим на рабочем листе ячейки для хранения дат начала и конца периода выборки пользователей. Пусть в D1 будет дата начала периода, а в D2 — его завершения. Список пользлователей будем выводить в столбцах A (идентификатор пользователя) и B (ФИО). Получем примерно тако вид листа «main»:
Изменяем функцию «main». Ее окончательний вид такой:
function main() {
checkJWT();
var records_limit = 100;
var date_period_begin = SHEET_MAIN.getRange('D1').getValue();
var date_period_end = SHEET_MAIN.getRange('D2').getValue();
var requestURL = '/api/rest.php/auth/users?page=1&count=' + records_limit +
'&filter[last_activity]=' + dateToStr(date_period_begin) +
'&filter[last_activity]=' + dateToStr(date_period_end) +
'&sorting[last_activity]=desc';
var res = _getResByCollaboratorAPI(keyJWT, urlHome + requestURL);
if (JSON.stringify(res).substring(1, 6) == 'Error') {
Logger.log(JSON.stringify(res));
return 0;
}
SHEET_MAIN.getRange(1, 1, 1000, 2).clearContent();
var rng = SHEET_MAIN.getRange(1, 1);
var data_users = res.data;
data_users.forEach( function(item, i, data_users) {
rng.offset(i, 0).setValue(item.id);
rng.offset(i, 1).setValue(item.fullname);
});
}
В этом коде добавлены три переменные:
- records_limit — будет хранить максимальное количество записей, которую будет выдавать запрос;
- date_period_begin и date_period_end — держат даты начала и конца периода выборки. Эти даты можем получить напрямую из ячеек таблицы через свойства ячейки:
SHEET_MAIN.getRange('D2').getValue()
Эти переменные подзволят нам изменять параметры запроса. По-этому переменная с командой запроса теперь имеет вид:
var requestURL = '/api/rest.php/auth/users?page=1&count=' + records_limit + '&filter[last_activity]=' + dateToStr(date_period_begin) + '&filter[last_activity]=' + dateToStr(date_period_end) + '&sorting[last_activity]=desc';
Еще добавим простую обработку ответа сервера. В случае ошибки ответ будет содержать текст «Error» и мы в этом случае просто прервем работу программы.
if (JSON.stringify(res).substring(1, 6) == 'Error') {
Logger.log(JSON.stringify(res));
return 0;
}
Следущая часть кода выполняет вывод списка пользователей на лист. Сперва мы очищаем первые 2 столбца листа от возможных значений — SHEET_MAIN.getRange(1, 1, 1000, 2).clearContent();
Затем запоминаем в переменную rng стартовую ячейку var rng = SHEET_MAIN.getRange(1, 1); и затем относительно нее в цикле заполняем строки первых двух столбцов.
Результат:
Итог
Мы улучшили код модуля по работе с API Collaborator и теперь он может отправлять GET-запросы системе и получать от нее ответы.
Подготовили функцию для получения списка пользователей, которые были активны в системе в заданный промежуток времени. Вывели этот список на лист электронной книги Google Spreadsheet.
Весь код этого примера можно скачать тут: https://github.com/mirgor/lmscollaborator_api_examples
