LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Thursday, July 4, 2019

JIRA API でプロジェクト一覧を取得したい


プロジェクトの一覧を取得したくて書いたコードです

以下2つの変数にそれぞれ対象のNAMEとIDを入れて get_result() を実行すると、指定したスプレッドシートにプロジェクト一覧が出力されます。

BASE_URL = 'https://NAME.atlassian.net';
ss_url = "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0";


tokenの取得方法
JIRAでAPIトークンを取得したい

以下のコード.gsではtokenをスクリプトのプロパティに入れています。



コード.gs
var BASE_URL = 'https://NAME.atlassian.net';

function get_result() {
  var options = {
    method: "get",
    contentType: "application/json",
    headers: {"Authorization": " Basic " + getToken()}
  };
  var url = BASE_URL + '/rest/api/2/project';
  var response = UrlFetchApp.fetch(url, options);
  //Logger.log(response);
  parseObj(response)
}

function parseObj(response) {
  var jobj = JSON.parse(response);
  var arrays = [];
  var values = ["expand", "self", "id", "key", "name", "avatarUrls", "projectCategory", "projectTypeKey", "simplified", "style", "isPrivate"]
  for(var i = 0; i < jobj.length; i++) {
    var expand = jobj[i]["expand"];
    var self = jobj[i]["self"];
    var id = jobj[i]["id"];
    var key = jobj[i]["key"];
    var name = jobj[i]["name"];
    var avatarUrls = jobj[i]["avatarUrls"];
    var projectCategory = jobj[i]["projectCategory"];
    var projectTypeKey = jobj[i]["projectTypeKey"];
    var simplified = jobj[i]["simplified"];
    var style = jobj[i]["style"];
    var isPrivate = jobj[i]["isPrivate"];
    arrays.push([expand, self, id, key, name, avatarUrls, projectCategory, projectTypeKey, simplified, style, isPrivate]);
  }
  set_values(arrays);
}


function set_values(arrays){
  var ss_url = "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(ss_url);
  var sh = ss.getSheets()[0];
  var last_row = sh.getLastRow();
  var start_row = last_row + 1;
  var start_col = 1;
  var num_rows = arrays.length;
  var num_cols = arrays[0].length;
  Logger.log([start_row, start_col, num_rows, num_cols])
  var range = sh.getRange(start_row, start_col, num_rows, num_cols);
  
  range.setValues(arrays); 
}


function getToken() {
  return PropertiesService.getScriptProperties().getProperty('token');
}



参考

Get all projects
https://developer.atlassian.com/cloud/jira/platform/rest/v2/#api-group-Projects

Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...