Liên kết Redmine và Google Sheets qua JSON

Câu chuyện

Gần đây tôi đang chuẩn bị tham gia quản lý một dự án phần mềm. Công cụ quản lý dự án hiện tại chủ yếu là Redmine. Tuy nhiên do Redmine ko đáp ứng được nhu cầu vẽ biểu đồ nhằm phục vụ công việc, và server lại thuộc quyền quản lý của khách hàng ko dễ gì chỉnh sửa, nên tôi nghĩ đến việc lấy dữ liệu trên Redmine về Google Sheets để sử dụng chức năng vẽ biểu đồ của Google Sheets.

Tất nhiên, vì Redmine có cung cấp chức năng xuất dữ liệu với định dạng CSV, nên tôi hoàn toàn có thể làm theo cách xuất dữ liệu và copy trực tiếp vào Google Sheets. Nhưng vì việc cập nhật dữ liệu của biểu đồ sẽ phải làm hàng ngày trong khi tôi lại là một thằng lười chảy thây, và vì việc copy thủ công như vậy ko đáng mặt một thằng kỹ sư CNTT, cho nên tôi quyết định viết một công cụ nhỏ bằng Google App Script để lấy dữ liệu json từ Redmine về và chuyển thành dữ liệu trên Google Sheets chỉ bằng một nút bấm, với mã nguồn tham khảo từ địa chỉ https://qiita.com/silverlining-yamashita/items/a9295e5e4c6a9215d380.

Vì chỉ copy mã nguồn về chỉnh sửa thẩm mỹ tí chút thì cũng hơi nhạt, nên tôi quyết định nhân dịp này mài dũa lại khả năng refactor của bản thân, vốn đã bỏ xó hơn nửa năm nay từ khi chuyển sang công việc mới. Kết quả cuối cùng phải nói là khiến tôi khá tự hào với tư cách một thằng lập trình nghiệp dư, khi hàm dài nhất chỉ có 12 dòng code, cảm nhận chủ quan là mã nguồn gần như ko cần đến comment vẫn có thể hiểu được, và tính năng công cụ đã được cải tiến đáng kể so với ban đầu.

Vậy nên tôi xin được chia sẻ mã nguồn của công cụ ở đây, hy vọng là nó có thể giúp ích được cho ai đó trong tương lai. Đây là công cụ đầu tiên tôi viết bằng Google App Script lẫn javascript, nên nếu có điều gì thất thố xin được các cao nhân chỉ giáo.

Cách sử dụng

• Tạo một spreadsheet mới trong Google Sheets, đặt tên spreadsheet và bấm Ctrl+S để lưu.
• Trên thanh thực đơn, bấm Tools -> Script Editor
• Tại cửa sổ Script Editor, xóa tệp Code.gs và tạo mới 3 tệp Main.gs, Globals.gs và GetRedmineData.gs.
• Copy mã nguồn dưới đây vào các tệp tương ứng.
• Sửa giá trị các biến projectUrl, user và pass trong Globals.gs theo thiết lập của Redmine cần lấy dữ liệu
• ProjectUrl có thể lấy bằng cách bấm Overview trên cửa sổ Redmine và copy nội dung thanh địa chỉ của trình duyệt.
• Nhấn Ctrl+S để lưu, sau đó tắt cửa sổ Script Editor.
• Tắt và mở lại spreadsheet, trên thanh thực đơn sẽ hiện ra thực đơn mới là Manage Project, bấm vào đó và chọn Get Redmine Data.
• Trong spreadsheet sẽ tự động sinh ra hai sheet mới là Issues và Time Entries, trong đó chứa dữ liệu tương ứng từ Redmine dưới dạng bảng.

Vì mục đích công cụ là lấy dữ liệu để vẽ biểu đồ quản lý dự án, nên công cụ được thiết lập để mặc định lấy tất cả các issue từ Redmine (kể cả closed) và tất cả dữ liệu thời gian làm việc mà người dùng khác đã nhập. Công cụ hỗ trợ một số tùy biến bằng cách chỉnh sửa biến dataSheetInfos trong tệp Globals.gs như sau.

• sheetName: Tên sheet được tạo ra.
• filter: Điều kiện tìm kiếm dữ liệu (tham khảo https://www.redmine.org/projects/redmine/wiki/Rest_Issues)
• fields: Các trường dữ liệu lấy về.
• Có thể thêm phần tử mới vào dataSheetInfos nếu muốn lấy dữ liệu khác ngoài issues và time_entries.

Mã nguồn

Main.gs

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu("Manage Project");
  menu.addItem("Get Redmine Data", "getRedmineData"); //GetRedmineData.gs
  menu.addToUi();
}

Globals.gs

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var projectUrl = "projectUrl";

var user = "username";
var pass = "password";

var dataSheetInfos = [
  {
    jsonName: "issues",
    sheetName: "Issues",
    filter: "status_id=*",
    fields: [
      {name: "id", displayName: "Issue #"},
      {name: "tracker.name", displayName: "Tracker"},
      {name: "status.name", displayName: "Status"},
      {name: "priority.id", displayName: "Priority #"},
      {name: "priority.name", displayName: "Priority"},
      {name: "subject", displayName: "Subject"},
      {name: "assigned_to.name", displayName: "Assignee"},
      {name: "start_date", displayName: "Start Date"},
      {name: "due_date", displayName: "Due Date"},
      {name: "estimated_hours", displayName: "Estimated Hours"},
      {name: "done_ratio", displayName: "Done %"},
      {name: "custom/note", displayName: "Note"}
    ]
  }, {
    jsonName: "time_entries",
    sheetName: "Time Entries",
    filter: "",
    fields: [
      {name: "id", displayName: "Entry #"},
      {name: "issue.id", displayName: "Issue #"},
      {name: "user.name", displayName: "User"},
      {name: "activity.name", displayName: "Activity"},
      {name: "hours", displayName: "Hours"},
      {name: "spent_on", displayName: "Spent On"},
      {name: "created_on", displayName: "Created On"},
      {name: "updated_on", displayName: "Updated On"}
    ]
  }
];

GetRedmineData.gs

function getRedmineData() {
  var activeSheet = spreadsheet.getActiveSheet();    
  createOrUpdateDataSheets()
  spreadsheet.setActiveSheet(activeSheet);
}

function createOrUpdateDataSheets() {
  for (var dataSheetInfoId in dataSheetInfos) {
    var dataSheetInfo = dataSheetInfos[dataSheetInfoId];
    var data = getRedmineJsonData(dataSheetInfo["jsonName"], dataSheetInfo["filter"]);
    writeDataToSheet(data, dataSheetInfo["fields"], dataSheetInfo["sheetName"]);
  }
}

function getRedmineJsonData(jsonName, filter) {
  var jsonUrl = projectUrl + "/" + jsonName + ".json" + "?" + filter;
  var options = {
    "headers" : {"Authorization" : " Basic " + Utilities.base64Encode(user + ":" + pass)}
  };

  var jsonText = UrlFetchApp.fetch(jsonUrl,options).getContentText();
  var jsonObj = JSON.parse(jsonText);
  return jsonObj[jsonName];  
}

function writeDataToSheet(data, fields, sheetName) {
  var sheet = prepareSheet(sheetName);

  var startRowId = 1;
  var startColumnId = 1;
  var startDataRowId = startRowId + 1;

  writeFieldNamesToRow(fields, sheet, startRowId, startColumnId);

  for (var datumId = 0; datumId < data.length; datumId++) {
    var datumRowId = startDataRowId + datumId;
    writeDatumToRow(data[datumId], fields, sheet, datumRowId, startColumnId);
  }
}

function prepareSheet(sheetName) {
  var sheet = spreadsheet.getSheetByName(sheetName)  ;
  if (sheet == null) {
    sheet = spreadsheet.insertSheet(sheetName);
  }
  sheet.clear();
  return sheet;
}

function writeFieldNamesToRow(fields, sheet, rowId, startColumnId) {
  for (var fieldId = 0; fieldId < fields.length; fieldId++) {
    var cell = sheet.getRange(rowId, startColumnId + fieldId);
    var fieldName = fields[fieldId]["displayName"];
    cell.setValue(fieldName);    
  }
}

function writeDatumToRow(datum, fields, sheet, rowId, startColumnId) { 
  for (var fieldId = 0; fieldId < fields.length; fieldId++) {
    var cell = sheet.getRange(rowId, startColumnId + fieldId);
    var fieldValue = getDatumFieldValue(datum, fields[fieldId]["name"]);
    cell.setValue(fieldValue);    
  }
}

function getDatumFieldValue(datum, field) { 
  //object field
  if (field.indexOf(".") > -1) { 
    return getDatumFieldValueObject(datum, field);
  } 
  //custom field
  else if (field.indexOf("/") > -1) {
    return getDatumFieldValueCustom(datum, field);
  } 
  else {
    return nz(datum[field]);
  }
}

function getDatumFieldValueObject(datum, field) {
  var fieldSplit = field.split(".", 2);
  var mainField = fieldSplit[0];
  var subField = fieldSplit[1];
  return nz(nz(datum[mainField])[subField]);
}

function getDatumFieldValueCustom(datum, field) {
  var fieldSplit = field.split("/", 2);
  var customField = fieldSplit[1];

  for (var fieldId in datum['custom_fields']) {
    if (datum['custom_fields'][fieldId]['name'] = customField) {
      return nz(datum['custom_fields'][fieldId]['value']);
    }
  }  
  return "";
}

function nz(value) {
  if (value != undefined) {
    return value
  } else {
    return ""
  }
}