【GAS】高速化するには？＋Backlogテンプレートを使って項目を置き換え＋Slack投稿とチケット完了操作を追加してみる

はじめに

以前投稿しました下記記事の GAS をパワーアップしてみました！
ですので、以前の記事をご覧いただけますと幸いです！

今回の結論

1. GAS 処理を高速化する
2. Backlog のテンプレートを使ってシートから自由に項目を置き換えられるようにする
3. シート上でステータス変更した時に処理を追加する
   • シート上で「処理済み」に変更したら Slack 通知する
   • シート上で「完了」に変更したら Backlog チケットも完了にする

実施したこと

1. GAS 処理を高速化する

まずは、メニュー構築やメニューからチケット作成する際の処理が遅くなっていた箇所を高速化することにしました。
ではどうして遅くなっているのかを調べていたところ、下記記事に辿り着きました。

前回のソースコードを見ると、確かにファイルオープン時などに処理される、getCreateTicketNo() 関数には、for ループの中で getValue() を複数回使っていますね。。

以前のソースコード一部抜粋

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 = {};

  // 最大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;
}

for ループ中に getValue() しているところを、for ループに入る前に一旦変数に落としてみることにしました。（VALID_RANGE を VALIED_VALUE に変更）
この変更に伴い、ROWS と COLS も配列アクセスになるので、for ループは 0 スタートにしています。

また1行ごとに走査するイテレーション処理では、”チケット作成対象外にする列”を処理の初めに判定し、対象外だったら早々に continue するようにして処理を早めています。

これらにより以前よりもはるかに早い処理ができるようになりました。

const VALID_VALUE = SHEET.getRange(START_ROW, START_COL, SHEET.getLastRow(), SHEET.getLastColumn()).getValues();
const ROWS = VALID_VALUE.length;
const COLS = VALID_VALUE[0].length;

// チケット作成対象を返す
function getCreateTicketNo() {
  let array = {};

  // 最大No.を初期化
  max_number = 0;
  for (let i=0; i<ROWS-1; i++) {
    let skipFlag = VALID_VALUE[i][SKIPFLAG_COL-START_COL];
    if (skipFlag != "") continue; // チケット作成対象にするセルが空欄でなかったら次の行へ

    let temparray = [];
    for (let j=0; j<COLS; j++) {
      temparray.push(VALID_VALUE[i][j]);
    }
    temparray.push(i+START_ROW); // シート上の行番号を覚えておく

    let number = VALID_VALUE[i][NUMBER_COL-START_COL];
    array['no' + number] = temparray;

    // 最大No.を保存しておく
    if (max_number < number) max_number = number;
  }
  for (let key in array) {
    Logger.log(key + "=" + array[key]);
  }
  return array;
}

1-1. チケット作成対象がなかった時の処理もついでに追加する

ついでに、メニュー作成時にチケット作成対象がなかった場合にエラーが発生していたので修正しています。
変数 submenu_count の部分が追加になります。
何もしない関数を作って、それを割り当てるやり方にしましたが、もっとスマートなやり方があったかもしれません。。

//メニューを構築する
function onOpen(e) {
  let ui = SpreadsheetApp.getUi();
  let menu = ui.createMenu('Backlog')
      .addItem('認証の実行', 'startoauth')
      .addItem('更新', 'update')
      .addSeparator();

  // サブメニューを動的に作る
  let submenu = ui.createMenu('チケット作成');
  let submenu_count = 0;
  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);
      submenu_count++;
    }
  }
  if (submenu_count == 0) submenu.addItem('対象チケットなし', 'do_nothing');
  menu.addSubMenu(submenu);

  // メインメニューの残り
  menu
    .addSeparator()
    .addItem('ログアウト', 'reset')
    .addToUi();
}

// 対象チケットがないときに何もしない関数を使う
function do_nothing() {
}

2. Backlog のテンプレートを使ってシートから自由に項目を置き換えられるようにする

Backlog のプロジェクト設定には「タスク」や「バグ」などの種別があります。
種別ごとに”テンプレート”といって、チケット作成時にその種別を選んだ場合、チケット詳細に自動で入力されるテキスト情報がありますよね。
このテンプレートを使って、スプレッドシートの項目名が一致するものを自動的に置き換えてチケット作成するようにしてみました。

今回はテンプレートはこんな感じにしてみます。

## 内容
{contents}

## PR URL
{pr_url}

要件をまとめるとこんな感じです。

• シートの見出し名を {test} のようにし、テンプレート側と一致したら、その行の {test} 列を、テンプレート側の {test} に置き換えてチケット作成する

2-1. テンプレートを取得する

以前にもあった、getIssueTypeId() で取得できていましたのでテンプレートの取得は簡単でした。
getIssueTypeId() を元に、新たにテンプレートのテキスト情報を返す関数を作ってみました。

// 種別に設定されているtemplateを取得する
function getIssueTypeTemplate(issueTypeId){
  let ui = SpreadsheetApp.getUi();

  //トークン確認
  var service = checkOAuth();

  if (service.hasAccess()) {  
    //エンドポイントを構築
    var endpoint = "https://" + spacename + ".backlog.com/api/v2/projects/" + projectId + "/issueTypes";

    //リクエストヘッダ
    let header = {
      Authorization: 'Bearer ' + service.getAccessToken()
    }

    //リクエストオプション
    let options = {
      method: "GET",
      headers: header,
      muteHttpExceptions: true
    }

    var response = UrlFetchApp.fetch(endpoint, options);

    //リクエスト結果を取得する
    const result = JSON.parse(response.getContentText());

    console.log(result)

    // 種別IDと一致するテンプレートを返却する
    for(let i=0; i<result.length; i++){
      if (result[i].id == issueTypeId) {
        return result[i].templateDescription;
      }
    }
    return '';
  }else{
    return '';
  }
}

2-2. テンプレート内の {} で括られた文字列とシートの見出しが一致している列を抽出する

テンプレートテキストが取得できたので、次はテンプレートテキスト内にある {} で括られたテキストを抜き出して、シートの見出し列のテキストを比較して、一致していたらその列のテキストをテンプレートの {} 部分に置き換える処理を作ります。

まずはテンプレートテキストから {} で括られた箇所を抽出します。
抽出には正規表現を使うことにしました。

  // テンプレート内の{.+}で指定した文字を抽出する
  let regex = /{.+}/g;
  let templ_desc_split = templ_desc.split(regex);
  let templ_desc_match = templ_desc.match(regex);

ちょっと分かりにくいので、実行してみたログを記載しておきます。

templ_desc_split=
## 内容
,

## PR URL
,
templ_desc_match={contents},{pr_url}

templ_desc_split には　{.+}　に引っかかった文字列を境に、要素を区切った配列になって、
templ_desc_match には　{.+}　に引っかかった文字列の配列になっています。

正規表現とは？
ChatGPT に「正規表現を簡単に説明してください」と聞いた回答です。

正規表現は、文字列のパターンを表現するための記述方法です。文字列の中から特定のパターンにマッチする部分を検索したり、置換したりする際に利用されます。

これでテンプレート側に設定してある {} で括られた文字列は取得できたので、シート側の見出しも取得して一致しているか比較していきます。

const MENU_ROW = 1;               // 見出し行
const MENU_COL = 1;               // 見出し列
const MENU_VALUE = SHEET.getRange(MENU_ROW, MENU_COL, 1, SHEET.getLastColumn()).getValues()[0];

  // 見出し列にある{.+}とテンプレートに設定してある{.+}が一致している列を探す({.+}をkey、列番号をvalueにする配列を作る)
  let templ_text_cols = {};
  for (let i=0; i<templ_desc_match.length; i++) {
    for (let j=0; j<MENU_VALUE.length; j++) {
      if (templ_desc_match[i] == MENU_VALUE[j]) {
        templ_text_cols[MENU_VALUE[j]] = j+1;  // 0スタートなので+1
      }
    }
  }

MENU_VALUE はシート上の見出し行を列ごとに並べたテキストの配列になります。
比較のロジック部分ですが、

• templ_desc_match つまり {.+} に引っかかった文字列の配列1つずつに対して、
• 見出し行の列を1つずつ比較して、
• 一致していたら、変数に格納する

ということをやっています。
変数の格納については、"{.+}" を key に、"シート上の列番号"を value にする配列にしています。

	{{pr_url}=6.0, {contents}=5.0}

次にシート上のチケット対象行の、{} で括られた文字列の列のセルに、入力されているテキスト情報を取得して、テンプレート側の {} で括られた文字列部分に置き換える処理を作ります。

  // 指定行の取得する
  let array = CREATE_TICKET_LIST[num_str];

  // 見出し列{.+}に入力されている文字列を抽出する
  let ret = templ_desc_split;
  let sheet_row = array[array.length-1];  // 保存しておいたシート上の行番号を使う
  for (let i=0; i<Object.keys(templ_text_cols).length; i++) {
    // 配列のkey({.+})を取得
    let key = Object.keys(templ_text_cols)[i];

    // シート上にある{.+}列を取得
    let templ_text_value = SHEET.getRange(sheet_row, templ_text_cols[key],1, 1).getValue();

    // 入力されている文字列をテンプレートテキストにくっつける
    ret[i] += templ_text_value;
  }

  // 1つの文字列にする
  ret = ret.join("");
  Logger.log(ret);
  return ret;

num_str は 'no2' のような文字列です。
※関数の引数としてもらうようにしています。ソースコード全文は後ほど。

CREATE_TICKET_LIST 配列って？
前回の時点でありましたが補足しておきます。
チケット作成対象だけを、シート行ごと保存している配列です。

シートのメニュー操作で「Backlog > チケット作成 > No.X」を選んだときには、
CREATE_TICKET_LIST['nox'] となるようにしています。

CREATE_TICKET_LIST['nox'] には、シート上の行番号も保存しています。
※ getCreateTicketNo() 関数参照

ここもちょっとややこしいので解説すると、for 文は、

	{{pr_url}=6.0, {contents}=5.0}

この数分回していて、チケット作成対象の行、{.+} の列、に入力されているテキストを取得して、
templ_desc_split を代入した、ret の配列1要素ごとに、追記しています。

ret=
## 内容
詳細をここに書くとチケットに反映される No.2
,

## PR URL
PR URLを書くとチケットに反映される No.2

あとはこの配列を1つの文字列にして完了です。

## 内容
詳細をここに書くとチケットに反映される No.2

## PR URL
PR URLを書くとチケットに反映される No.2

これで Backlog のテンプレートの {.+} と、シート見出しの {.+} が一致した列の、チケット作成対象の行、にあるテキストを、テンプレートの {.+} に置き換えたテキスト情報が作れましたね。

この部分の関数のソースコード全文

const MENU_ROW = 1;               // 見出し行
const MENU_COL = 1;               // 見出し列
const MENU_VALUE = SHEET.getRange(MENU_ROW, MENU_COL, 1, SHEET.getLastColumn()).getValues()[0];

// 種別に設定されているテンプレートテキストを使って、{.+}で指定されている列を抽出して、詳細文を作る
function createDescWithTemplDesc(templ_desc, num_str) {
  // 指定行の取得する
  let array = CREATE_TICKET_LIST[num_str];
  
  // テンプレート内の{.+}で指定した文字を抽出する
  let regex = /{.+}/g;
  let templ_desc_split = templ_desc.split(regex);
  let templ_desc_match = templ_desc.match(regex);
  
  // 見出し列にある{.+}とテンプレートに設定してある{.+}が一致している列を探す({.+}をkey、列番号をvalueにする配列を作る)
  let templ_text_cols = {};
  for (let i=0; i<templ_desc_match.length; i++) {
    for (let j=0; j<MENU_VALUE.length; j++) {
      if (templ_desc_match[i] == MENU_VALUE[j]) {
        templ_text_cols[MENU_VALUE[j]] = j+1;  // 0スタートなので+1
      }
    }
  }
  
  // 見出し列{.+}に入力されている文字列を抽出する
  let ret = templ_desc_split;
  let sheet_row = array[array.length-1];  // 保存しておいたシート上の行番号を使う
  for (let i=0; i<Object.keys(templ_text_cols).length; i++) {
    // 配列のkey({.+})を取得
    let key = Object.keys(templ_text_cols)[i];

    // シート上にある{.+}列を取得
    let templ_text_value = SHEET.getRange(sheet_row, templ_text_cols[key],1, 1).getValue();

    // 入力されている文字列をテンプレートテキストにくっつける
    ret[i] += templ_text_value;
  }

  // 1つの文字列にする
  ret = ret.join("");
  Logger.log(ret);
  return ret;
}

2-3. チケット作成時にこのテキスト情報を使う

あとは作ったテキスト情報を、チケットの詳細部分に使うだけですね。

  // 種別IDに設定してあるテンプレートを取得する
  let templ_desc = getIssueTypeTemplate(ISSUE_TYPE_ID);

  // テンプレートの変数を使ってシートの変数をマージさせた詳細文を作る
  let fixed_desc = createDescWithTemplDesc(templ_desc,'no'+i);

  // チケットを作る
  let ticket_name = array[TICKET_NAME_COL-1];
  let response = addKadai2(ticket_name, fixed_desc);

  // シート上にチケット番号を書き出す
  let sheet_row = array[array.length-1];  // 保存しておいたシート上の行番号を使う
  SHEET.getRange(sheet_row,SKIPFLAG_COL,1,1).setValue(response['issueKey']);

addKadai2() は以前と変わっていません。
"i" とか、"array" とか出ていますが、抜粋なのでソースコード全文は最後においておきます。

この実装により、テンプレート側に新しい {.+} を追加したくなっても、ソースコードを変更することなくテンプレートへの追加と、シートの見出しの追加だけで、項目を増やすことができるようになりました！

3.　シート上でステータス変更した時に処理を追加する

前回の記事でシート上からチケット作成するフローを記載しましたが、チケット作成後もフローがあると思います。

• 実装完了後の動作確認依頼を連絡するために、シートのステータスを「処理済み」に変更する
• 動作確認完了後、シートのステータスを「完了」に変更する

これらに関して、それぞれ以下のようなことをできるようにしてみました。

• 実装完了後の動作確認依頼を連絡するために、シートのステータスを「処理済み」に変更したら、Slack で通知ができるようにする
• 動作確認完了後、シートのステータスを「完了」に変更したら、対象の Backlog チケットも完了にする

これにより、シートの操作は「要求や要件定義」に使い、Backlog チケットは「開発が実装時に情報を記載する」ことに使う、ことでそれぞれの役割分担ができるようになったと思っています。

3-1. シート上で「処理済み」に変更したら Slack 通知する

GAS からの Slack 通知は思ったよりも簡単でした。
Webhook URL を取得して、テキスト情報などを投げるだけでした。
下記記事が参考になっています。

ちなみにメンションをつけるときは下記の記事が参考になりました。

const SLACK_POST_URL = "(ここに取得したWebhookURLを貼る)"; // Slack投稿用のWebhookURL

// Slack投稿する
function notifySlack(message) {
  let jsonData = {
    "text" : message
  }
  
  // 上の送信内容を設定
  let payload = JSON.stringify(jsonData)
  
  // オプションを設定
  let options = {
    "method" : "post",
    "contentType" : "application/json",
    "payload" : payload
  };
  
  // Slackに通知する
  UrlFetchApp.fetch(SLACK_POST_URL, options);  
}

Slack 送信テストはできたので、シートのステータスが変更したときに実行されるようにしてみます。
シートの編集時に起動する、onEdit() 関数を使います。

onEdit() 関数はシートの編集時ならいつでも実行してしまうので、処理が遅くならないように、発動させたい箇所の編集ではなかった場合は、すぐに処理を中断するようにしています。

const STATUS_COL = 7;             // ステータスの列

// 編集時の挙動
function onEdit(e) {
  // 編集箇所が特定のシート以外の時は処理を中断する
  let activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (activeSheet.getName() != SHEET_NAME) {
    return;
  }

  // 編集箇所がステータスの列以外の場合は中断する
  let activeCell = activeSheet.getActiveCell();
  if (activeCell.getColumn() != STATUS_COL) {
    return;
  }

  // 略

}

こんな感じで関数ブロックの最後に到達する前に、早々にノックアウトをさせていきます。

編集箇所のシートが”シート1”じゃなかったら、以降の処理はしない
↓
編集箇所がステータス列じゃなかったら、以降の処理はしない
↓
・・・

以降の処理で使うようなデータの取得や getValue() は、この段階で使用しないので無駄な処理をしないようにします。

[略]の部分は下記になります。

const SHEET_STATUS_PROCESSED = "処理済み";  // シート上のステータス「処理済み」
// 処理済み時にSlackに投稿するメッセージ
const PROCESSED_MSG = "\
<@ABCDE123456>\n\
下記項目を実装しました。\n\
ご確認よろしくお願いします。\n";

  // 編集箇所のステータスの値によって処理を分岐
  let activeValue = activeCell.getValue();
  let values = activeSheet.getRange(activeCell.getRow(),START_COL,1,ROWS).getValues()[0]; // 編集箇所の行を取得

  // 「処理済み」の場合はSlackを投稿
  if (activeValue == SHEET_STATUS_PROCESSED) {
    let ticket_name = values[TICKET_NAME_FULL_COL-1]; // 編集行のチケット名を取得

    let box_msg = "下記項目のステータスを「" + SHEET_STATUS_PROCESSED + "」に変更しました。\\nSlack通知しますか？\\n\\n" + "・" + ticket_name;
    let okcancel = Browser.msgBox("Slack通知の確認", box_msg, Browser.Buttons.OK_CANCEL);
    if (okcancel == 'ok') {
      // OKならSlack投稿をする
      let slack_msg = PROCESSED_MSG + "・" + ticket_name;
      notifySlack(slack_msg);
    }

    // 略(3-2. シート上で「完了」に変更したら Backlog チケットも完了にする)で解説します。
  }

念のため Slack 通知前にメッセージボックスで確認をすることにしています。

onEdit() 内で notifySlack() を使ってSlack通知する場合、GASのトリガー設定が必要でした。
下記記事を参考に設定しています。

https://qiita.com/iwa_gino/items/b7b954880587bd10e1cb

これでシート上のステータスを「処理済み」にすると Slack が投稿されるようになりました。

3-2. シート上で「完了」に変更したら Backlog チケットも完了にする

GAS で Backlog チケットを完了にするのも比較的簡単でした。
下記の記事を参考に、チケット情報を更新する関数を作ってみました。

//　課題を更新する
function updateKadai(issueKey, statusId, comment) {
  let ui = SpreadsheetApp.getUi();

  //トークン確認
  var service = checkOAuth();

  if (service.hasAccess()) {  
    //エンドポイントを構築
    var endpoint = "https://" + spacename + ".backlog.com/api/v2/issues/" + issueKey;

    //リクエストヘッダ
    let header = {
      Authorization: 'Bearer ' + service.getAccessToken(),
      "Content-Type": "application/x-www-form-urlencoded"
    }

    //リクエストボディ
    let payload = {
      "statusId": statusId,
      "comment": comment
    }

    //リクエストオプション
    let options = {
      method: "PATCH",
      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();
 
    return result;
  
  }else{
    return null;
  }
}

引数に、課題ID、種別ID、コメントテキストを取ります。
Slack 処理のときと同じように、シート上のステータスを「完了」に編集したときに、この関数が呼ばれるようにします。

const TICKET_NO_COL = 4;          // チケット番号の列
const SHEET_STATUS_DONE = "完了"; // シート上のステータス「完了」
const ISSUE_ID_DONE = "4"; // Backlog上のステータス「完了」のID
// 完了時にBacklogに投稿するメッセージ
const DONE_MSG = "シートのステータスを「完了」にしたため「完了」にしました。";

  // 略

  // 「完了」の場合は対象のBacklogチケットを完了にする
  } else if (activeValue == SHEET_STATUS_DONE) {
    let ticket_no = values[TICKET_NO_COL-1]; // 編集行のチケットNo.を取得
    let response = updateKadai(ticket_no, ISSUE_ID_DONE, DONE_MSG);
  }

これで完了ですね。
ちなみに、「処理済み」に変更時はメッセージボックスの確認をしていましたが、「完了」に変更時はメッセージボックスの確認を入れませんでした。
それは、「処理済み」変更は開発側、「完了」変更はクライアント側で運用するため、クライアント側に新たな操作を追加しないためとなります。

まとめ

最後に、解説のためぶつ切りだったので、ソースコード全文を載せておきます。

ソースコード全文

// ファイル、シートに応じて変更してください
const SHEET_NAME = "シート1";       // 対象のシート名
const ISSUE_TYPE_ID = "987654";   // 種別「タスク」
const MILESTONE_ID = "123456";    // マイルストーン「リリース日未定」
const MENU_ROW = 1;               // 見出し行
const MENU_COL = 1;               // 見出し列
const START_ROW = 2;              // 対象範囲の開始行
const START_COL = 1;              // 対象範囲の開始列
const NUMBER_COL = 1;             // No.の列
const TICKET_NAME_COL = 2;        // チケット名の列
const SKIPFLAG_COL = 4;           // 空欄だったらチケット作成対象にするための列
const TICKET_NO_COL = 4;          // チケット番号の列
const STATUS_COL = 7;             // ステータスの列
const SHEET_STATUS_PROCESSED = "処理済み";  // シート上のステータス「処理済み」
const SHEET_STATUS_DONE = "完了";          // シート上のステータス「完了」
const ISSUE_ID_DONE = "4";        // Backlog上のステータス「完了」のID
const SLACK_POST_URL = "(ここに取得したWebhookURLを貼る)"; // Slack投稿用のWebhookURL
// 処理済み時にSlackに投稿するメッセージ
const PROCESSED_MSG = "\
<@ABCDE123456>\n\
下記項目を実装しました。\n\
ご確認よろしくお願いします。\n";
// 完了時にBacklogに投稿するメッセージ
const DONE_MSG = "シートのステータスを「完了」にしたため「完了」にしました。";

// グローバル変数
var max_number;

// グローバル定数
const SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
const MENU_VALUE = SHEET.getRange(MENU_ROW, MENU_COL, 1, SHEET.getLastColumn()).getValues()[0];
const VALID_VALUE = SHEET.getRange(START_ROW, START_COL, SHEET.getLastRow(), SHEET.getLastColumn()).getValues();
const ROWS = VALID_VALUE.length;
const COLS = VALID_VALUE[0].length;
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') {
          // 種別IDに設定してあるテンプレートを取得する
          let templ_desc = getIssueTypeTemplate(ISSUE_TYPE_ID);

          // テンプレートの変数を使ってシートの変数をマージさせた詳細文を作る
          let fixed_desc = createDescWithTemplDesc(templ_desc,'no'+i);

          // チケットを作る
          let ticket_name = array[TICKET_NAME_COL-1];
          let response = addKadai2(ticket_name, fixed_desc);

          // シート上にチケット番号を書き出す
          let sheet_row = array[array.length-1];  // 保存しておいたシート上の行番号を使う
          SHEET.getRange(sheet_row,SKIPFLAG_COL,1,1).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('チケット作成');
  let submenu_count = 0;
  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);
      submenu_count++;
    }
  }
  if (submenu_count == 0) submenu.addItem('対象チケットなし', 'do_nothing');
  menu.addSubMenu(submenu);

  // メインメニューの残り
  menu
    .addSeparator()
    .addItem('ログアウト', 'reset')
    .addToUi();
}

// 編集時の挙動
function onEdit(e) {

  // 編集箇所が特定のシート以外の時は処理を中断する
  let activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (activeSheet.getName() != SHEET_NAME) {
    return;
  }

  // 編集箇所がステータスの列以外の場合は中断する
  let activeCell = activeSheet.getActiveCell();
  if (activeCell.getColumn() != STATUS_COL) {
    return;
  }

  // 編集箇所のステータスの値によって処理を分岐
  let activeValue = activeCell.getValue();
  let values = activeSheet.getRange(activeCell.getRow(),START_COL,1,ROWS).getValues()[0]; // 編集箇所の行を取得

  // 「処理済み」の場合はSlackを投稿
  if (activeValue == SHEET_STATUS_PROCESSED) {
    let ticket_name = values[TICKET_NAME_COL-1]; // 編集行のチケット名を取得

    let box_msg = "下記項目のステータスを「" + SHEET_STATUS_PROCESSED + "」に変更しました。\\nSlack通知しますか？\\n\\n" + "・" + ticket_name;
    let okcancel = Browser.msgBox("Slack通知の確認", box_msg, Browser.Buttons.OK_CANCEL);
    if (okcancel == 'ok') {
      // OKならSlack投稿をする
      let slack_msg = PROCESSED_MSG + "・" + ticket_name;
      notifySlack(slack_msg);
    }

  // 「完了」の場合は対象のBacklogチケットを完了にする
  } else if (activeValue == SHEET_STATUS_DONE) {
    let ticket_no = values[TICKET_NO_COL-1]; // 編集行のチケットNo.を取得
    let response = updateKadai(ticket_no, ISSUE_ID_DONE, DONE_MSG);
  }
}

// チケット作成対象を返す
function getCreateTicketNo() {
  let array = {};

  // 最大No.を初期化
  max_number = 0;
  for (let i=0; i<ROWS-1; i++) {
    let skipFlag = VALID_VALUE[i][SKIPFLAG_COL-START_COL];
    if (skipFlag != "") continue; // チケット作成対象にするセルが空欄でなかったら次の行へ

    let temparray = [];
    for (let j=0; j<COLS; j++) {
      temparray.push(VALID_VALUE[i][j]);
    }
    temparray.push(i+START_ROW); // シート上の行番号を覚えておく

    let number = VALID_VALUE[i][NUMBER_COL-START_COL];
    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 do_nothing() {
}

//課題を投稿する
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("認証が実行されていません。");
  }
}

// 種別に設定されているtemplateを取得する
function getIssueTypeTemplate(issueTypeId){
  let ui = SpreadsheetApp.getUi();

  //トークン確認
  var service = checkOAuth();

  if (service.hasAccess()) {  
    //エンドポイントを構築
    var endpoint = "https://" + spacename + ".backlog.com/api/v2/projects/" + projectId + "/issueTypes";

    //リクエストヘッダ
    let header = {
      Authorization: 'Bearer ' + service.getAccessToken()
    }

    //リクエストオプション
    let options = {
      method: "GET",
      headers: header,
      muteHttpExceptions: true
    }

    var response = UrlFetchApp.fetch(endpoint, options);

    //リクエスト結果を取得する
    const result = JSON.parse(response.getContentText());

    console.log(result)

    // 種別IDと一致するテンプレートを返却する
    for(let i=0; i<result.length; i++){
      if (result[i].id == issueTypeId) {
        return result[i].templateDescription;
      }
    }
    return '';
  }else{
    return '';
  }
}

// 種別に設定されているテンプレートテキストを使って、{.+}で指定されている列を抽出して、詳細文を作る
function createDescWithTemplDesc(templ_desc, num_str) {
  // 指定行の取得する
  let array = CREATE_TICKET_LIST[num_str];
  
  // テンプレート内の{.+}で指定した文字を抽出する
  let regex = /{.+}/g;
  let templ_desc_split = templ_desc.split(regex);
  let templ_desc_match = templ_desc.match(regex);
  
  // 見出し列にある{.+}とテンプレートに設定してある{.+}が一致している列を探す({.+}をkey、列番号をvalueにする配列を作る)
  let templ_text_cols = {};
  for (let i=0; i<templ_desc_match.length; i++) {
    for (let j=0; j<MENU_VALUE.length; j++) {
      if (templ_desc_match[i] == MENU_VALUE[j]) {
        templ_text_cols[MENU_VALUE[j]] = j+1;  // 0スタートなので+1
      }
    }
  }
  
  // 見出し列{.+}に入力されている文字列を抽出する
  let ret = templ_desc_split;
  let sheet_row = array[array.length-1];  // 保存しておいたシート上の行番号を使う
  for (let i=0; i<Object.keys(templ_text_cols).length; i++) {
    // 配列のkey({.+})を取得
    let key = Object.keys(templ_text_cols)[i];

    // シート上にある{.+}列を取得
    let templ_text_value = SHEET.getRange(sheet_row, templ_text_cols[key],1, 1).getValue();

    // 入力されている文字列をテンプレートテキストにくっつける
    ret[i] += templ_text_value;
  }

  // 1つの文字列にする
  ret = ret.join("");
  Logger.log(ret);
  return ret;
}

// Slack投稿する
function notifySlack(message) {
  let jsonData = {
    "text" : message
  }
  
  // 上の送信内容を設定
  let payload = JSON.stringify(jsonData)
  
  // オプションを設定
  let options = {
    "method" : "post",
    "contentType" : "application/json",
    "payload" : payload
  };
  
  // Slackに通知する
  UrlFetchApp.fetch(SLACK_POST_URL, options);  
}

//　課題を更新する
function updateKadai(issueKey, statusId, comment) {
  let ui = SpreadsheetApp.getUi();

  //トークン確認
  var service = checkOAuth();

  if (service.hasAccess()) {  
    //エンドポイントを構築
    var endpoint = "https://" + spacename + ".backlog.com/api/v2/issues/" + issueKey;

    //リクエストヘッダ
    let header = {
      Authorization: 'Bearer ' + service.getAccessToken(),
      "Content-Type": "application/x-www-form-urlencoded"
    }

    //リクエストボディ
    let payload = {
      "statusId": statusId,
      "comment": comment
    }

    //リクエストオプション
    let options = {
      method: "PATCH",
      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();
 
    return result;
  
  }else{
    return null;
  }
}

シートでの役割とチケットでの役割を明確にすることで、それぞれの責任範囲が明確になったと思います。

• シートでは要求、要件を決めること、進行状況の可視化などに責任を持つようにする
• チケットでは詳細設計情報、PR　URL、ブランチ名、などに責任を持つようにする

皆さんの参考になれれば嬉しいです！

お知らせ

Relicではオンライン勉強会を定期的に開催しております。
過去のオンライン勉強会の一覧はこちらです。

さらに他のRelicメンバーが書いた記事は以下にまとめられていますので、よろしければ見ていただければと思います！

またRelicではさまざまな職種について積極的に採用中です。
地方拠点もありますので、U・Iターン大歓迎です！🙌
新規事業にご興味のある方は、Relic採用サイトからエントリーください！

それでは次の投稿でまたお会いしましょう！

参考文献

• https://gas.excelspeedup.com/gas-spreadsheet-speedup/
• https://note.com/skipla/n/na3f7f9cd9b7d
• https://off.tokyo/blog/mention-gas-slack-api/
• https://qiita.com/iwa_gino/items/b7b954880587bd10e1cb
• https://blog.serverworks.co.jp/tech/2019/05/27/backlogkadaistatusupd/
• https://developer.nulab.com/ja/docs/backlog/api/2/update-issue/#