【GAS】列移動をしやすく＋定期的にステータス変更＋シートからのBacklog操作を誰でもできるように

はじめに

こんにちは！Relicの北川です！
今回も以前投稿しました GAS をさらにパワーアップしてみましたのでご紹介します！

vol.1

vol.2

今回の結論

1. 列の移動をしやすくする
2. 定期的に Backlog チケットのステータスを見て、シート側のステータスを更新する
   2-1. シートに記載している作成済みのチケットを抽出する
   2-2. Backlog チケットのステータスが「処理中」のものがある場合、シート上もステータス「処理中」に変更する
   2-3. 定期的に Backlog ステータスを見る
3. シート側からのチケット操作を誰でもできるようにする

実施したこと

1. 列の移動をしやすくする

これまで改良を続けていると、列を追加することが多くなってきて、そのたびに GAS 側の定数の部分の修正が必要で、ちょっと面倒になってきていました。

// ファイル、シートに応じて変更してください
const NUMBER_COL = 1;             // No.の列
const TICKET_NAME_COL = 2;        // チケット名の列
const SKIPFLAG_COL = 4;           // 空欄だったらチケット作成対象にするための列
const TICKET_NO_COL = 4;          // チケット番号の列
const STATUS_COL = 7;             // ステータスの列

それに、見栄えを意識して列の移動をする場合も、GAS 側のコード変更が必要で、ちょっと使い勝手が良くなかったのです。

これを解決するために、「列名」から「列番号」を取得するように変更することにしました。

const NUMBER_COL_NAME = "No";                // No.の列名
const TICKET_NAME_COL_NAME = "タイトル";        // チケット名の列名
const SKIPFLAG_COL_NAME = "リンク";            // 空欄だったらチケット作成対象にするための列名
const TICKET_NO_COL_NAME = "リンク";            // チケット番号の列名
const STATUS_COL_NAME = "ステータス";           // ステータスの列名

const NUMBER_COL = getColumnFromName(NUMBER_COL_NAME);
const TICKET_NAME_COL = getColumnFromName(TICKET_NAME_COL_NAME);
const SKIPFLAG_COL = getColumnFromName(SKIPFLAG_COL_NAME);
const TICKET_NO_COL = getColumnFromName(TICKET_NO_COL_NAME);
const STATUS_COL = getColumnFromName(STATUS_COL_NAME);

こんな感じで「列名」を指定して、getColumnFromName() で「列番号」を取得するように変更しました。

getColumnFromName() の詳細はこちらです。

// 列名から列番号を返す
function getColumnFromName(columnName) {
  let result = null;
  for (let i=0; i<MENU_VALUE.length; i++) {
    if (MENU_VALUE[i] == columnName) {
      result = i+1; // 0スタートなので
      break;
    }
  }
  return result;
}

見出し部分がテキストの配列になっている MENU_VALUE と比較して一致していれば、その列番号を返す関数ですね。
配列は0スタートなので、「+1」しています。

これで列移動しても、見出しのテキスト情報から列番号を取得するので、コードを変更することなく自由に列移動ができるようになりました！

2. 定期的に Backlog チケットのステータスを見て、シート側のステータスを更新する

シート上だけで対応中の項目を見れるようにしたいと思いましたが、開発メンバーは Backlog チケット操作がメインとしているので、シート側のステータス変更は自動の方が良いですよね。

今回は、

1. 定期的に、
2. シートに記載している作成済みのチケットを抽出して、
3. ステータス「処理中」のものがある場合、シート上もステータス「処理中」に変更

します。

2-1. シートに記載している作成済みのチケットを抽出する

以前までに作成していた関数 getCreateTicketNo() と反対に、条件を「チケット作成対象列が空欄でない」場合を「チケット作成済み」として扱います。

// 作成済みのチケットリストをシートから取得
function getTicketList() {
  let array = {};

  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;
  }
  for (let key in array) {
    Logger.log(key + "=" + array[key]);
  }
  return array;
}

if (skipFlag == "") continue;

この判定が逆なのと、max_number 保存しない、ぐらいで以前作成した getCreateTicketNo() との違いはありません。
シート上の行番号も使うので保存しておきます。

これで作成済みのチケットリストは抽出できました。

2-2. Backlog チケットのステータスが「処理中」のものがある場合、シート上もステータス「処理中」に変更する

まずは、特定の Backlog チケットの情報取得ができるか試してみました。

// 課題情報の取得
function getKadai(issueId){
  let ui = SpreadsheetApp.getUi();

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

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

    //リクエストヘッダ
    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);
    if (result['errors'] == null) return result;
    return null;

  }else{
    return null;
  }
}

公式ドキュメントに合わせてリクエストするだけなので割と簡単にできましたね。
引数にチケット番号を取るようにしています。
一応存在しないチケットの場合は null を返すようにしておきました。

次に取得した Backlog チケット側のステータスが「処理中」のものがあれば、シートのステータスも「処理中」にセットします。

const STATUS_ID_DOING = "2";        // Backlog上のステータス「処理中」のID

//　定期的にシートのステータスをBacklogのステータスに合わせる（処理中のみ）
function updateStatus() {
  // 作成済みのチケットリストをシートから取得
  let ticketList = getTicketList();

  // 作成済みのうち1行ずつ処理する
  for (let key in ticketList) {
    let ticketArray = ticketList[key];

    // シートにあるチケットNoからBacklogに問い合わせて情報を取得する
    let ticketNumber = ticketArray[TICKET_NO_COL-START_COL];
    let ticketInfo = getKadai(ticketNumber);

    // BacklogのステータスIDが「処理中」ならシートのスタータスも「処理中」に変更する
    if (ticketInfo != null) {
      let status = ticketInfo['status'];
      if (status['id'] == STATUS_ID_DOING) {
        let sheet_row = ticketArray[ticketArray.length-1];  // 保存しておいたシート上の行番号を使う
        SHEET.getRange(sheet_row,STATUS_COL,1,1).setValue(status['name']);
      }
    }
  }
}

Backlog ステータス「処理中」は、Backlog 側で初めからあるステータスなので、ID は「2」のようですね。

これでシート上のチケット作成済みリストから、Backlog ステータスを見て「処理中」のものは、シート上も「処理中」に変更することができました。

2-3. 定期的に Backlog ステータスを見る

定期的に実行するために、GAS のトリガーで設定しました。

さっき作成した、updateStatus() を登録します。
ひとまず1時間おきで良いかなと思っています。

これで定期的に Backlog ステータスを見てシート上のステータスを変更することができました。

3. シート側からのチケット操作を誰でもできるようにする

今まで Backlog API を GAS で使用するために、API キーとなる、ClientId と ClientSecret を GAS コードにベタ書きしていました。

var appid = 'ここにクライアントIDを入れる';
var appsecret='ここにクライアントシークレットを入れる';

困ったこととしては、

• API キーを発行した私しか、シート上からチケット作成などの Backlog 操作ができない
• シート自体は社内であれば誰でも見れる場所においてあるので、GAS コードを見れば API キーが見れてしまうのであまりよろしくない

ということがあります。

これに対して、

• 実行者には API キーを発行していただくが、その情報は実行者以外は見れない場所に置いておく
• GAS の実行時に実行者情報を取得して、対象のファイルを開き、API キーを取得する

ということをやってみようと思います。

まず、GAS の実行時、実行者情報を取得してみたいと思います。

  let mailAddress = Session.getActiveUser().getEmail();

これだけで実行者のメールアドレスが取れました。簡単ですね。

Session.getActiveUser()について
実行者情報の取得方法を検索すると、下記のようなものを見かけました。

let user = Session.getActiveUser();
let email = user.getUserLoginId();

公式ドキュメントを読むと、getUserLoginId() メソッドはサポートが終了しているようですね。
https://developers.google.com/apps-script/reference/base/user?hl=ja#getUserLoginId()

let user = Session.getActiveUser();
let contact = ContactsApp.getContact(user); 
let name = contact.getFullName();
let email = contact.getPrimaryEmail();

こちらも公式ドキュメントを読むと、ContactsApp クラスは使用が非推奨で、getContact() メソッドはサポートが終了していました。
https://developers.google.com/apps-script/reference/contacts/contacts-app?hl=ja

次にシートの管理方法を検討してみます。

こんな感じで専用のシートを用意して、ファイル名にあたるスプレッドシートキーを、ユーザーごとに置いておくことにします。
それぞれのスプレッドシートファイルは実行者本人しか開くことができないようにしておきます。

スプレッドシートキーとは？
URLで言うと、"/spreadsheets/d/" の後から "/" までの部分のことを指します。

指定しているスプレッドシート側はこんな感じです。

こんな感じで ClientId と ClientSecret を置いておきます。
何度も言いますが、このファイルは実行者本人しか開けないようにしておきます。

Backlog の ClientId と ClientSecret の取得方法
vol.1 でもご紹介しました、下記記事が分かりやすいです。
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/#Backlog

次に GAS から別スプレッドシートを開く処理を作ります。
別シートを開く処理に関して参考にしたのは下記記事です。

この一連の、シートに指定している別スプレッドシートを読み込んで、ClientId と ClientSecret を抽出する処理はこんな感じです。

//認証用の各種変数
var appid = '';
var appsecret='';

// ファイル、シートに応じて変更してください
const BACKLOG_API_SHEET_NAME = "BacklogInfo"; // メールアドレスと別ファイルキーをまとめたシート名
const BACKLOG_API_ROW_START = 2;              // そのシートの開始行
const BACKLOG_API_COL_START = 1;              // そのシートの開始列
const BACKLOG_API_COLS = 2;                   // そのシートの列数（行数は動的に取得する）
const BACKLOG_API_ROW_MAILADDRESS = 0;        // そのシートのメールアドレスの列番号
const BACKLOG_API_COL_SHEETKEY = 1;           // そのシートの別ファイルキーの列番号
const USER_SHEET_NAME = "BacklogAPIKey"; //　別ファイル側のシート名
const USER_SHEET_ROW_CLIENT_ID = 0;      // そのシートのClientIdの列番号
const USER_SHEET_ROW_CLIENT_SECRET = 1;  // そのシートのClientoSecretの列番号

// BacklogのClientIdとClientSecretを別ファイルから読み取る
function checkClientIdSecret() {
  // 実行者のメールアドレスを取得する
  let mailAddress = Session.getActiveUser().getEmail();

  // デバッグ
  // mailAddress = "[e-mail]";
  
  // メールアドレスと別ファイルキーをまとめたシートの値を取得
  let backlogApiSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(BACKLOG_API_SHEET_NAME);
  let backlogApsValue = backlogApiSheet.getRange(BACKLOG_API_ROW_START, BACKLOG_API_COL_START, backlogApiSheet.getLastRow(), BACKLOG_API_COLS).getValues();

  // 実行者と一致する別ファイルを開いて、ClientIdとClientSecretを取得する
  for (let i=0; i<backlogApsValue.length; i++) {
    if (backlogApsValue[i][BACKLOG_API_ROW_MAILADDRESS] == mailAddress) {
      let userSheet = SpreadsheetApp.openById(backlogApsValue[i][BACKLOG_API_COL_SHEETKEY]).getSheetByName(USER_SHEET_NAME);
      let userValue = userSheet.getRange(1, 1, 2, 2).getValues();

      appid = userValue[USER_SHEET_ROW_CLIENT_ID][1];
      appsecret = userValue[USER_SHEET_ROW_CLIENT_SECRET][1];
      return true;
    }
  }
  Browser.msgBox("ユーザー登録がありません。\n" + BACKLOG_API_SHEET_NAME + "シートに登録してください。", Browser.Buttons.OK);
  return false;
}

戻り値は True/False にしました。
この関数が呼ばれるタイミングは Oauth 関連の認証時にする必要があります。

//認証チェック
function checkOAuth() {
  if (checkClientIdSecret()) {
    return OAuth2.createService("backlog")
      .setAuthorizationBaseUrl(authurl)
      .setTokenUrl(tokenurl)
      .setClientId (appid)
      .setClientSecret(appsecret)
      .setCallbackFunction ('authCallback')
      .setPropertyStore (PropertiesService.getUserProperties ());
  } else {
    return null;
  }
}

これで GAS コード側にある ClientId と ClientSecret のベタ書きをなくして、事前準備をしておけば誰でも、シート上から Backlog 操作ができるようになりました。

まとめ

前回のまとめでも言及しましたが、シートとチケットの役割分担が明確になっているので、改修もやりやすいですね。

• 開発者はチケット操作をメインにすることでシートのステータス変更ができるように
• マネジメント側は見やすくするために列移動がしやすくなり、誰でもチケット操作ができるように

なりましたね！

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

お知らせ

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

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

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

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

参考文献

• https://developer.nulab.com/ja/docs/backlog/api/2/get-issue/#
• https://developers.google.com/apps-script?hl=ja
• 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/#Backlog
• https://excel-ubara.com/apps_script1/GAS015.html