こんにちは、株式会社Relicの北川です！
普段はNoteで情報発信しているのですが、この度弊社が「Qiita Engineer Festa 2023」にゴールドスポンサーとして協賛することになりましたので、Qiitaでも情報発信していこうと思います！
Noteの方もフォローしていただけると嬉しいです！
はじめに
スプレッドシートで課題管理をしている場合の課題
早速ですがみなさんはITプロダクトの開発時にスプレッドシートで課題管理をしていることはありますか？
クライアントワークの場合はお客様自身が要望を記載することもあると思います。
スプレッドシートは一覧性が高いので、管理しやすいのが特徴かと思います。
この場合、記載頂いた内容をチケット管理ツール（今回はBacklog）に、項目ごとにコピペしてチケット作成しているかと思いますが、この作業をGASを使ってスプレッドシートからBacklogチケットを作成するようにしてみましたので、記事にまとめてみました！
結論から
こんな感じで動作できるようになっています！
奮闘記
上記の動作になるまでの奮闘記を記載します。
ちなみに私はエンジニアキャリアを持つPMなので多少はプログラムは分かりますが、GASは初めて触りました。
そんな私でもできるので、似たようなキャリアの方や、同じような課題に悩んでいる方は試してみてください！
1.やりたいことを決める
まずはやりたいことを決めます。
最終的にGASを使ったものになりましたが、この時点では決めていませんでした。
Backlogチケットを作る方法として、Backlog APIを使うのですがBacklog APIにはSlack連携もあったので、はじめは「良さげなコマンドをSlackに打てばできるやろ！」ぐらいに思っていました。
…が、早々にその思惑は崩れました。。
一応「/backlog add」と打てばSlackからBacklogチケットを作成できるのですが、Slack上に専用のダイアログが表示されるだけなので、Backlog画面上にスプレッドシートの項目ごとのコピペと変わりないので、この方法は諦めました。
想定では、スプレッドシートの各課題の行に「/backlog add '新しい課題名' '詳細内容' '優先度中'...」という文字列をスプレッドシート上で生成して、SlackにコピペすればBacklogチケット作成できるのでは？と思っていたのですよね。。
次に、Backlog APIとGASを使ってSlack通知する記事を見つけました。
※Slackを使おうとしていた名残ですね。
この記事を読んでいて、BacklogチケットをGASで操作できることが分かったので、Backlog APIとGASを使ってスプレッドシート上から直接チケット作成する方法を選択することにしました。
2.GASでBacklog APIを触ってみる
2-1.環境準備
この記事の手順に沿って、環境を作りました。（”事前準備”の項目）
認証についてもこの記事の通りOauthを使っています。（”認証フローを作成する”の項目）
プチハマりした箇所
プロジェクトIDはここから取れました。
2-2.動作確認
一通り設定やソースコードをコピペしてGASのエディタからstartoath()を実行してみると、あっさりと認証に成功しました！
記事のおかげです！
これで指定のBacklogプロジェクトとの連携ができるようになったので、手始めに「getPriorityId() 優先順位IDの一覧の取得」を実行してみました。
こちらもあっさり取得できました！
同様に「getIssueTypeId（） 課題種別IDの一覧の取得」も試してみたところ、こちらもあっさりと取れました！
2-3.チケット作成の動作確認
ここまでは順調です！
では真打、チケットの作成をやってみようと思います！
「addKadai() 課題を投稿する」をエディタから実行！
なんかエラー出るなぁ。。
"message":"No such issueType. (projectId:123456, issueTypeId:1823228)"
issueTypeと言えば、「タスク」とか「バグ」とか課題種別ですね。
「"1823228"という課題種別がない」ということですね。
先ほどの記事にもこう書いてあるので、プロジェクトごとに異なる値になるのかなと思います。
さっき試した「getIssueTypeId（） 課題種別IDの一覧の取得」を実行して、実行ログに出てきた"issueTypeId"のうちどれかをソースコードに入れ直して、再度「addKadai()」を実行します。
できました！
GASエディタ画面からBacklogチケットを作成することはできました！
後から知ったのですが、ブラウザのURLからissueTypeIdは確認できました。
3.作成するチケット対象を抽出する
ここまではまぁまぁ順調です。ほとんど記事のおかげです！
ここまではGASエディタ上から関数実行だったので、ここからはスプレッドシート上でチケット作成対象を抽出してチケット作成できるようにしたいと思います。
3-1.スプレッドシートの用意
こんな感じのスプレッドシートを用意しました。
- A列…ユニークな番号
- B列…チケット名
- C列…チケット詳細
- D列…チケットへのリンク
D列は、すでに作成済みのチケットをリンクとして記載したいので、記載がない（＝空欄）のときだけ、チケット作成対象となるようにします。
そうしてチケット作成対象を返す関数を作ってみました。
3-2.チケット作成対象を返す関数
// ファイル、シートに応じて変更してください
const SHEET_NAME = "シート1" // 対象のシート名
const START_ROW = 2; // 対象範囲の開始行
const START_COL = 1; // 対象範囲の開始列
const NUMBER_COL = 1; // No.の列
const SKIPFLAG_COL = 4; // 空欄だったらチケット作成対象にするための列
// グローバル定数
const SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
const VALID_RANGE = SHEET.getRange(START_ROW, START_COL, SHEET.getLastRow(), SHEET.getLastColumn());
const ROWS = VALID_RANGE.getLastRow();
const COLS = VALID_RANGE.getLastColumn();
// チケット作成対象を返す
function getCreateTicketNo() {
let array = {};
for (let i=1; i<ROWS-1; i++) {
let temparray = [];
var number = VALID_RANGE.getCell(i, NUMBER_COL).getValue();
var skipFlag = "";
for (let j=1; j<=COLS; j++) {
// 空欄だったらチケット作成対象にするための列、に値が入っているものはスルー
skipFlag = VALID_RANGE.getCell(i, SKIPFLAG_COL).getValue();
if (skipFlag != "") continue;
temparray.push(VALID_RANGE.getCell(i,j).getValue());
}
if (skipFlag != "") continue;
array['no' + number] = temparray;
}
for (let key in array) {
Logger.log(key + "=" + array[key]);
}
return array;
}
3-3.実行結果
ちゃんとD列に記載がない行が取れていますね！
GASとSheet周りのお作法はこちらを参考にしました。
https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=ja
4.メニューからチケットを作成する
GASでスプレッドシートのメニューを作成します。
4-1.メニューの作成
// ファイル、シートに応じて変更してください
const TICKET_NAME_COL = 2; // チケット名の列
// グローバル定数
const CREATE_TICKET_LIST = getCreateTicketNo();
//メニューを構築する
function onOpen(e) {
let ui = SpreadsheetApp.getUi();
let menu = ui.createMenu('Backlog')
.addItem('認証の実行', 'startoauth')
.addItem('更新', 'update')
.addSeparator();
// サブメニューを動的に作る
let submenu = ui.createMenu('チケット作成');
for (let i=0; i<Object.keys(CREATE_TICKET_LIST).length; i++) {
let number = Object.keys(CREATE_TICKET_LIST)[i];
let ticket_name = CREATE_TICKET_LIST[number][TICKET_NAME_COL-1];
if (ticket_name != "") {
submenu.addItem(number + ":" + ticket_name, 'CREATE_TICKET.' + number);
}
}
menu.addSubMenu(submenu);
// メインメニューの残り
menu
.addSeparator()
.addItem('ログアウト', 'reset')
.addToUi();
}
// 更新
function update() {
onOpen();
}
ついでに、下記関数が呼ばれるメニューを追加しておきました。
- 更新
- onOpen()
メニューの作成では、スプレッドシート上の操作でD列が頻繁に変わってしまう可能性あるので、「更新」ボタンを作ることで、サブメニューが最新になるようにしています。
4-2.サブメニュー作成の解説
サブメニュー作成あたりが複雑になっているので解説をします。
// 抜粋です
// グローバル定数
const CREATE_TICKET_LIST = getCreateTicketNo();
// 中略
// サブメニューを動的に作る
let submenu = ui.createMenu('チケット作成');
for (let i=0; i<Object.keys(CREATE_TICKET_LIST).length; i++) {
let number = Object.keys(CREATE_TICKET_LIST)[i];
let ticket_name = CREATE_TICKET_LIST[number][TICKET_NAME_COL-1];
if (ticket_name != "") {
submenu.addItem(number + ":" + ticket_name, 'CREATE_TICKET.' + number);
}
}
まずグローバル定数として、getCreateTicketNo()でチケット作成対象配列を取得しているのは、サブメニューを作るより先に行う必要があるためです。
※いろいろ調べて分かりました。
サブメニューの数は動的に変化するので、いの一番でチケット作成対象を調べておく必要がある、ということですね。
次にサブメニューに指定する関数ですが、具体的にfor文を展開するとこうなっています。
// example) number = 'no1'
submenu.addItem('no1' + ":" + ticket_name, 'CREATE_TICKET.no1');
submenu.addItem('no2' + ":" + ticket_name, 'CREATE_TICKET.no2');
submenu.addItem('no5' + ":" + ticket_name, 'CREATE_TICKET.no5');
submenu.addItem('no8' + ":" + ticket_name, 'CREATE_TICKET.no8');
submenu.addItem('no10' + ":" + ticket_name, 'CREATE_TICKET.no10');
submenu.addItem('no13' + ":" + ticket_name, 'CREATE_TICKET.no13');
CREATE_TICKETは連想配列でvalueに関数を指定するようにしています。
addItem()で指定する関数には文字列で、引数を持つことができないようなので、このようにしています。
このことについては下記記事が参考になりました！
※実はここ結構ハマってしまっていました。。下記記事がなければ完成しなかったかもしれません！
4-3.CREATE_TICKET関連
CREATE_TICKETに関するソースコードはこんな感じです。
// ファイル、シートに応じて変更してください
const TICKET_DESC_COL = 3; // 詳細の列
// グローバル変数
var max_number;
const CREATE_TICKET = (function() {
let result = {};
for (let i=1; i<=max_number; i++) {
let array = CREATE_TICKET_LIST['no' + i];
if (array != null) {
result['no' + i] = function() {
let okcancel = Browser.msgBox("チケットを作成します。よろしいですか？", Browser.Buttons.OK_CANCEL);
if (okcancel == 'ok') {
let response = addKadai2(array[TICKET_NAME_COL-1], array[TICKET_DESC_COL-1]);
let length = array.length;
VALID_RANGE.getCell(array[length-1],SKIPFLAG_COL).setValue(response['issueKey']);
}
};
}
}
return result;
})();
こんな感じでサブメニューが押されたときに作動する関数を作っています。
addKadai2()はaddKadai()を参考に引数を持つように改修した関数です。詳細は後述しますね。
ここで、max_numberというグローバル変数が出てきていますが、チケット作成対象を返す関数getCreateTicketNo()に少し手を入れています。
これは行番号とNo.がSTART_ROW以上にずれていても正しく機能するようにするためです。
簡単にいうと、スプレッドシートの2行目にNo.1、3行目にNo.2、4行目にNo.3と続いていて、5行目にNo.100を置いていてもちゃんと動作するようにするためですね。
またチケット作成後は対象セルにチケット番号を入力しておきたい（入力がないものを作成対象としているのもある）ので、responseからissueKeyを取っています。
このとき入力対象のセルを指定するために、getCreateTicketNo（）ときに、シート上の行番号を保存しておくようにしています。
これもシート上の行番号とNo.がずれた時の対策にもなっています。
改修したチケット作成対象を返す関数getCreateTicketNo()はこちらになります。
// チケット作成対象を返す
function getCreateTicketNo() {
let array = {};
// 最大No.を初期化
max_number = 0;
for (let i=1; i<ROWS-1; i++) {
let temparray = [];
var number = VALID_RANGE.getCell(i, NUMBER_COL).getValue();
var skipFlag = "";
for (let j=1; j<=COLS; j++) {
// ここをフラグにするに値が入っているものはスルー
skipFlag = VALID_RANGE.getCell(i, SKIPFLAG_COL).getValue();
if (skipFlag != "") continue;
temparray.push(VALID_RANGE.getCell(i,j).getValue());
}
if (skipFlag != "") continue;
temparray.push(i); // シート上の行番号を覚えておく
array['no' + number] = temparray;
// 最大No.を保存しておく
if (max_number < number) max_number = number;
}
return array;
}
4-4.チケットの作成
チケット作成対象は抽出できた！
メニューから個別の関数を叩くことができた！
最後は実際のチケットを作成する、ですね！
// ファイル、シートに応じて変更してください
const ISSUE_TYPE_ID = "987654"; // 種別
const MILESTONE_ID = "123456"; // マイルストーン
//課題を投稿する
function addKadai2(summary, description){
// 中略
//リクエストボディ
let payload = {
"projectId": projectId,
"summary": summary,
"description": description,
"issueTypeId": ISSUE_TYPE_ID,
"milestoneId[]": MILESTONE_ID,
"priorityId": "3"
}
// 中略
return result;
// 中略
}
元のaddKada()からの変更点は、下記のとおりです。
- 引数にチケット名とチケット詳細を持つようにした
- milestoneIdを固定で入れるようにした
この辺りはBacklog APIを知る必要があるのでこちらを参考にしました。
プチハマりした箇所
「milestoneId[]」を「milestoneId」として書いていました。。
ちなみにマイルストーンIDもここから取れました。
これにて全て完了し、メニューからチケット作成ができるようになりました！
まとめ
最後に、解説のためぶつ切りだったので、ソースコード全文を載せておきます。
ソースコード全文
// ファイル、シートに応じて変更してください
const SHEET_NAME = "シート1" // 対象のシート名
const ISSUE_TYPE_ID = "987654"; // 種別「タスク」
const MILESTONE_ID = "123456"; // マイルストーン「リリース日未定」
const START_ROW = 2; // 対象範囲の開始行
const START_COL = 1; // 対象範囲の開始列
const NUMBER_COL = 1; // No.の列
const TICKET_NAME_COL = 2; // チケット名の列
const TICKET_DESC_COL = 3; // 詳細の列
const SKIPFLAG_COL = 4; // 空欄だったらチケット作成対象にするための列
// グローバル変数
var max_number;
// グローバル定数
const SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
const VALID_RANGE = SHEET.getRange(START_ROW, START_COL, SHEET.getLastRow(), SHEET.getLastColumn());
const ROWS = VALID_RANGE.getLastRow();
const COLS = VALID_RANGE.getLastColumn();
const CREATE_TICKET_LIST = getCreateTicketNo();
const CREATE_TICKET = (function() {
let result = {};
for (let i=1; i<=max_number; i++) {
let array = CREATE_TICKET_LIST['no' + i];
if (array != null) {
result['no' + i] = function() {
let okcancel = Browser.msgBox("チケットを作成します。よろしいですか？", Browser.Buttons.OK_CANCEL);
if (okcancel == 'ok') {
let response = addKadai2(array[TICKET_NAME_COL-1], array[TICKET_DESC_COL-1]);
let length = array.length;
VALID_RANGE.getCell(array[length-1],SKIPFLAG_COL).setValue(response['issueKey']);
}
};
}
}
return result;
})();
//メニューを構築する
function onOpen(e) {
let ui = SpreadsheetApp.getUi();
let menu = ui.createMenu('Backlog')
.addItem('認証の実行', 'startoauth')
.addItem('更新', 'update')
.addSeparator();
// サブメニューを動的に作る
let submenu = ui.createMenu('チケット作成');
for (let i=0; i<Object.keys(CREATE_TICKET_LIST).length; i++) {
let number = Object.keys(CREATE_TICKET_LIST)[i];
let ticket_name = CREATE_TICKET_LIST[number][TICKET_NAME_COL-1];
if (ticket_name != "") {
submenu.addItem(number + ":" + ticket_name, 'CREATE_TICKET.' + number);
}
}
menu.addSubMenu(submenu);
// メインメニューの残り
menu
.addSeparator()
.addItem('ログアウト', 'reset')
.addToUi();
}
// チケット作成対象を返す
function getCreateTicketNo() {
let array = {};
// 最大No.を初期化
max_number = 0;
for (let i=1; i<ROWS-1; i++) {
let temparray = [];
var number = VALID_RANGE.getCell(i, NUMBER_COL).getValue();
var skipFlag = "";
for (let j=1; j<=COLS; j++) {
// ここをフラグにするに値が入っているものはスルー
skipFlag = VALID_RANGE.getCell(i, SKIPFLAG_COL).getValue();
if (skipFlag != "") continue;
temparray.push(VALID_RANGE.getCell(i,j).getValue());
}
if (skipFlag != "") continue;
temparray.push(i); // シート上の行番号を覚えておく
array['no' + number] = temparray;
// 最大No.を保存しておく
if (max_number < number) max_number = number;
}
for (let key in array) {
Logger.log(key + "=" + array[key]);
}
return array;
}
// 更新
function update() {
onOpen();
}
//課題を投稿する
function addKadai2(summary, description){
let ui = SpreadsheetApp.getUi();
//トークン確認
var service = checkOAuth();
if (service.hasAccess()) {
//エンドポイントを構築
var endpoint = "https://" + spacename + ".backlog.com/api/v2/issues";
//リクエストヘッダ
let header = {
Authorization: 'Bearer ' + service.getAccessToken(),
"Content-Type": "application/x-www-form-urlencoded"
}
//リクエストボディ
let payload = {
"projectId": projectId,
"summary": summary,
"description": description,
"issueTypeId": ISSUE_TYPE_ID,
"milestoneId[]": MILESTONE_ID,
"priorityId": "3"
}
//リクエストオプション
let options = {
method: "POST",
headers: header,
muteHttpExceptions: true,
payload:payload
}
var response = UrlFetchApp.fetch(endpoint, options);
//リクエスト結果を取得する
const result = JSON.parse(response.getContentText());
console.log(JSON.stringify(result))
//レスポンスコードを取得する
const statusCode = response.getResponseCode();
//終了メッセージ
ui.alert("課題の登録が完了しました。")
return result;
}else{
ui.alert("認証が実行されていません。");
}
}
当初想定していた、Slackでのやり方よりスプレッドシートだけの操作で済むので、よりスマートなものになったと思っています！
皆さんの参考になれれば嬉しいです！
お知らせ
Relicではオンライン勉強会を定期的に開催しております。
過去のオンライン勉強会の一覧はこちらです。
さらに他のRelicメンバーが書いた記事は以下にまとめられていますので、よろしければ見ていただければと思います！
またRelicではさまざまな職種について積極的に採用中です。
地方拠点もありますので、U・Iターン大歓迎です！🙌
新規事業にご興味のある方は、Relic採用サイトからエントリーください！
それでは次の投稿でまたお会いしましょう！
参考文献
- https://www.redamoon.net/log/post/000069/
- https://officeforest.org/wp/2023/01/21/google-apps-script%E3%81%A7backlog%E3%82%92%E6%93%8D%E4%BD%9C%E3%81%97%E3%81%A6%E3%81%BF%E3%81%9F/
- https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=ja
- https://qiita.com/neonemo/items/86f34ecb0db3cfc51c8d
- https://developer.nulab.com/ja/docs/backlog/api/2/add-issue/#