API Collaborator. «Зачетная книжка» сотрудника

В прошлой статье мы рассмотрели как с помощью 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». Этот список является источником данных для выпадающего списка «Сотрудник».

Про то как..

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

 

Please follow and like us:

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*