Вибачте цей текст доступний тільки в “RU”.
В прошлой статье мы рассмотрели как с помощью API Collaborator получать список людей, которые пользовались учебным порталом компании в заданный период. Теперь предлагаю рассмотреть реальный кейс.
Худенко Наталья, Руководитель Центра развития персонала производства, “Юрия-Фарм”.
У них достаточно часто возникала потребность печати “Зачетной книжки” сотрудника. Важно было получить выборку за указанный период всех назначенных пользователю учебных заданий и статус обучения по ним – что пройдено, с каким результатом, а что в процессе. Результат нужен в электронной таблице, приятно оформленным и подготовленным к печати. Вот что примерно требовалось:
Как будем решать
Сделаем копию электронной таблицы, которая получает выборку пользователей, и используем ее как основу. Этот список будет источником данных для выбора сотрудника для которого нужно сформировать Зачетную книжку.
Преименуем лист “main” в “users” и добавим новый лист, ему дадим главное имя – “main” – он будет содержать результирующую таблицу данных. Оформим его, заполним примерами данных. На нем же разместим элементы формы запроса – поля для указания дат и выбора сотрудника плюс кнопки запуска обработки данных. У меня получилось вот так:
В коде скрипта main.gs выполним соотвествующие замены:
- добавим в main.gs переменную
var SHEET_USERS = SPREADSHEET.getSheetByName("users"); - заменим во всем коде SHEET_MAIN на SHEET_USERS.
- заменим название функции function main() на function getUsersList()
- заменим объявление переменных, которые хранят даты периода
var date_period_begin = SHEET_USERS.getRange('D1').getValue();
var date_period_end = SHEET_USERS.getRange('D2').getValue();
// заменим на..
var date_period_begin = SHEET_MAIN.getRange('D3').getValue();
var date_period_end = SHEET_MAIN.getRange('E3').getValue();
После этого желательно проверить – все ли работает? Запустим функцию getUsersList() и проверим, что список пользователей обновляется без ошибок.
Планируемая последовательность действий пользователя по работе с “Зачетной книжкой”:
1. Пользователь указывает период выборки данных в ячейках подписанных “Период обучения от – до”.
2. Нажимает кнопку “Обновить список пользователей”. При этом запускается код, что был описан в предыдущей статье. Он обновляет список пользователей на листе “users”. Этот список является источником данных для выпадающего списка “Сотрудник”.
- сделать выпадающий список в Google Spreadsheet: https://www.youtube.com/watch?v=SUt9L_onZGo
- сделать кнопку в Google Spreadsheet: https://www.youtube.com/watch?v=qX7pA28r7BI
- сделать выпадающий список в Excel: https://www.youtube.com/watch?v=2HcrhbPU43s
- сделать кнопку в Excel: https://ichip.ru/kak-sozdat-knopku-v-excel.html
3. Выбирает из списка “Сотрудник” нужного человека и нажимает кнопку “Заполнить книжку”.
4. “Заполнить книжку” выполнит запрос на данные про выбранного человека, очистит книжку и заполнит ее результатами сотрудника по заданиям, которые попадают в указанный временной период.
Теперь заставим это все работать…
Готовим REST запросы
Нам потребуются 2 запроса:
– на данные выбранного пользователя (Имя, Фамилия, Должность, Подразделение, Фото). Все о пользователе отдает запрос к его профилю:
/api/rest.php/auth/users/<user_id>
– на выборку данных по заданиям из его “Истории обучения”:
/api/rest.php/tasks?page=1&count=<records_limit>&filter[date_assign]=<date_period_begin>&filter[date_assign]=<date_period_end>&sorting[date_assign]=desc&user_id=<user_id>&action=get-study-history-all-tasks
В этих запросах:
- <records_limit> – количество записей на странице выдачи результата;
- <date_period_begin> и <date_period_end> – даты периода в формате
"YYYY-MM-DD"; - <user_id> – идентификатор пользователя Collaborator.
Получение информации о пользователе
Получить данные указанного пользователя и разместить их на листе “Зачетной книжки” мы сможем этой функцией:
function getUserProfile(user_id){
checkJWT(); //check and udate JWT-key if nessesary
// /api/rest.php/auth/users/<user_id>
var requestURL = '/api/rest.php/auth/users/' + user_id;
var res = _getResByCollaboratorAPI(keyJWT, urlHome + requestURL);
if (JSON.stringify(res).substring(1, 6) == 'Error') {
Logger.log(JSON.stringify(res));
return 0;
}
// refill cells with user info
SHEET_MAIN.getRange('A4').setValue(res.fullname);
clearImages(); // clear photo
if (res.photo_thumb){
getImageFromURL(urlHome + res.photo_thumb); //get & insert photo_thumb
}
SHEET_MAIN.getRange('B8').setValue(res.position);
SHEET_MAIN.getRange('B9').setValue(res.department);
SHEET_MAIN.getRange('B10').setValue(res.city);
}
Эта функция кроме данных про ФИО, должность, подразделение и город человека еще получает и всталяет его фото.
Перед вставкой фото на лист выполняется очистка всех картинок на листе, кроме первой (это логотип компании – его не трогаем). Это делает функция:
function clearImages(){
var ReportSheet = SpreadsheetApp.getActive();
// Deletes all images (except image[0]) in a ReportSheet
var images = ReportSheet.getImages();
for (var i = 1; i < images.length; i++) {
images[i].remove();
}
}
Получение фото и вставка его на лист выполняется функцией:
function getImageFromURL(urlRequest){
var ReportSheet = SpreadsheetApp.getActive();
var sheet = SHEET_MAIN;
var img = UrlFetchApp.fetch(urlRequest).getBlob();
sheet.insertImage(img, 1, 5)
var images = sheet.getImages();
var img = images[images.length - 1];
var imgH = img.getHeight();
var imgW = img.getWidth();
var maxW = 150;
var newImgH = Math.round(imgH*(maxW/imgW));
img.setWidth(maxW)
.setHeight(newImgH)
.setAltTextTitle('Изображение')
.setAnchorCellYOffset(0)
.setAnchorCellXOffset(5);
}
Получение данных из истории обучения сотрудника
Из “Истории обучения” пользователя Collaborator нам нужно извлечь список заданий, у которых их дата назначения пользователю попадает в заданный период от <date_period_begin> до <date_period_end>.
Добавим переменную, которая будет хранить первую ячейку таблицы отчета.
var rngStartReport = SpreadsheetApp.getActive().getRangeByName('start_report');
Это именованная ячейка “start_report“. Относительно нее мы будем заполнять все остальные ячейки.
Функция заполнения отчета имеет вид:
function getUserLearningHistoryForPeriod(user_id, date_period_begin, date_period_end, records_limit) {
// get data of learning tasks for preset user who was assigned to this tasks in portal from <date_period_begin> to <date_period_end>
// example api request:
// api/rest.php/tasks?page=1&count=10&filter[date_assign]=2018-11-01&filter[date_assign]=2018-12-09&sorting[date_assign]=desc&user_id=3906&action=get-study-history-all-tasks
records_limit = records_limit || 1000;
var requestURL = '/api/rest.php/tasks?page=1&count=' + records_limit +
'&filter[date_assign]=' + dateToStr(date_period_begin) +
'&filter[date_assign]=' + dateToStr(date_period_end) +
'&sorting[date_assign]=desc&user_id=' + user_id +
'&action=get-study-history-all-tasks';
var res = _getResByCollaboratorAPI(keyJWT, urlHome + requestURL);
if (JSON.stringify(res).substring(1, 6) == 'Error') {
Logger.log(JSON.stringify(res));
return 0;
}
// fill cells in sheet "users"
SHEET_MAIN.getRange(rngStartReport.getRow(), 1, SHEET_MAIN.getLastRow(), 10).clearContent();
var rng = rngStartReport;
var data_tasks = res.data;
var accessdates = [];
data_tasks.forEach( function(item, i, data_tasks) {
rng.offset(i, 0).setValue(item.title);
rng.offset(i, 1).setValue(item.display_type);
rng.offset(i, 2).setValue(item.status);
rng.offset(i, 3).setValue(item.mark);
rng.offset(i, 4).setValue(item.date_assign);
//rng.offset(i, 5).setValue(item.date_start);
rng.offset(i, 5).setValue(item.date_finish);
});
}
Теперь осталось только определить <user_id> для выбранного из списка пользователя и все будет готово.
Определяем ID пользователя и получаем “Зачетную книжку”
Для работы запросов необходимо знать <user_id>. Вручную его вводить неудобно. Более “человечно” выбирать пользователя по его ФИО. И мы сделали выпадающий список, который помогает найти человека и выбрать его ФИО. Но это не дает нам нужный идентификатор. По-этому ради удобства использования прийдется немного поиграться…
В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP). Она прекрасно решает задачу нахождения идентификатора. Подробно описано тут: https://www.planetaexcel.ru/techniques/2/106/index.php?page=user&id=11
VLOOKUP ищет по диаппазону ячеек строку с заданным значением (поисковой фразой) и возвращает значение из другого столбца этого диаппазона, но в той же строке. Есть нюанс: эта функция производит поиск только по первому столбцу диапазона. Поэтому нам надо будет изменить функцию получения списка пользователей, чтобы поменятьстолбцы местами:
Измененная функция заполнения списка пользователей:
function getUsersList() {
checkJWT();
var records_limit = 100;
var date_period_begin = SHEET_MAIN.getRange('D3').getValue();
var date_period_end = SHEET_MAIN.getRange('E3').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_USERS.getRange(1, 1, 1000, 2).clearContent();
var rng = SHEET_USERS.getRange(1, 1);
var data_users = res.data;
data_users.forEach( function(item, i, data_users) {
rng.offset(i, 1).setValue(item.id);
rng.offset(i, 0).setValue(item.fullname);
});
}
В Google Spreadsheets есть полная аналогия функции ВПР (VLOOKUP). Подробно тут: https://support.google.com/docs/answer/3093318?hl=ru. Используем ее.
После перестановки столбцов не забудьте перенастроить правила проверки данных в ячейке с выпадающим списком на другой столбец (было users!B:B – исправляем на users!A:A).
На листе настроек добавим ячейку, которая будет содержать идентификатор пользователя Collaborator, которого мы выберем из выпадающего списка на “Зачетной книжке”. Назначим ей имя “user_id“. В этой ячейке вставим формулу:
=VLOOKUP(main!D5;users!A:B;2;FALSE)
В коде скрипта значение из этой ячейки получаем так:
var user_id = SpreadsheetApp.getActive().getRangeByName('user_id').getValue();
Итоговая функция заполнения “Зачетной книжки” имеет вид:
function main(){
var user_id = SpreadsheetApp.getActive().getRangeByName('user_id').getValue();
var date_period_begin = SHEET_MAIN.getRange('D3').getValue();
var date_period_end = SHEET_MAIN.getRange('E3').getValue();
checkJWT(); //check and udate JWT-key if nessesary
getUserProfile(user_id);
getUserLearningHistoryForPeriod(user_id, date_period_begin, date_period_end);
}
И последний штрих. Надо назначить на нарисованные кнопки скрипты, которые им следует запускать:
- Кнопка “Обновить список пользователей” – скрипт getUsersList ;
- Кнопка “Заполнить книжку” – скрипт main .
Теперь можно пользоваться и вот как это работает:
Итог
Вот и добрались до практических и полезных задач. Этот пример работы через API с Collaborator можно легко развить в формирование сертификатов за обучение, подсчет “хитрого” рейтинга и т.д.
Использование MS Excel или Google Spreadsheets выгодно тем, что в руках у пользователя оказываются огромные возможности настройки внешнего вида отчетов, дополнительной обработки и визуализации данных. Кроме этого он можем распечатать или сохранить результат в удобном формате, самостоятельно настраивая все параметры и не ожидая помощи от программистов 🙂
Как всегда – полный код скрипта main со всеми функциями можно найти тут: https://github.com/mirgor/lmscollaborator_api_examples
