【GAS】他部署からの依頼をGoogleフォームで受けてTrelloに自動連携し、管理工数削減した話（前編）〜外部JSONを読み込んでスクリプトのプロパティを自動設定する〜

どうも、ずみーです。

Trello。使ってますか。私は大好きです。

今日はTrelloをGoogleフォームと連携して業務効率改善しましたというお話。

ことの発端

私は現職で情シス×企画×業務効率改善（DX推進）みたいな部署にいるのですが、とにかく他部署からいろんな依頼を受けます。そこでよくある失敗パターンが、

画面や機能を作ってみたものの、依頼元がイメージしていたものと食い違っている
そもそも口頭での依頼だったので、確認しようが無い
っていうか依頼した方も自分が何を言っていたかあんまり覚えていない
っていうかそもそも誰が言い出しっぺなのかはっきりしない
せっかく作ったものがお蔵入り（！）

大きな組織で個人の責任範囲が見えづらい会社にいた経験のある方は共感していただけるんじゃないでしょうか。

そんなわけで、上司に言われました。

「これからは開発依頼書ベースで案件を管理していくから、フォーマットを作っといて」

そこで、いくつかのツールを検討検討しました。

ツール	利点	欠点
Excel	みんな使ってる。みんな大好き。	DX推進してるのにまたExcel？って感じ →没
Power Point	システムの仕様書ですらパワポで作る会社なので、これもみんな使ってる。	DX推進してるのに（以下略 →没
Salesforceのケース	管理・共有しやすい	一部の社員しかアカウントを持っていませんでした→没
Trello	チームでの内容共有やステータス管理がしやすい	ボードが部署内限定公開でした →没
Googleフォーム	URL一つで簡単に共有でき、入力が楽！	チームでの内容共有やステータス管理がしづらい

結果、閃いたのです。

Googleフォームの送信をトリガにしてTrelloにカード作れれば万事解決じゃね？

Trelloカードを直接起票してもらうことはできません。しかし、Googleアカウントなら社員全員持っているので、Googleフォームを記入してもらうことはどうやらできそうでした。

これが開発依頼書だ！

そんなこんなで上司とのディスカッションの末、フォームが完成しました。

f:id:nshzm:20201105214156p:plain — 開発依頼書

背景やら見込まれる効果やらを書いた後に、印鑑画像や補足資料を添付して送信できるようになっています。（信じられないかもしれませんが、私の職場では誰でもコピーできる印鑑の画像が責任者の電子印として成立しているのです）

今回は、フォームを送信した時に生成されるスプレッドシートの方でGoogle Apps Script（以下GAS）を使って回答内容を収集し、Trelloにデータ連携しました。

で、肝心のGAS

ソースコード全文はGit Hubのリポジトリに上げてます。ローカル開発環境でclaspを使って書きましたが、インストール必須ではありません。GASはブラウザ上のエディタで開発できます。

動きは以下のようになります。

誰かがフォームを送信する
スプレッドシートにその内容が書き込まれる（フォームの標準機能）
スプレッドシートのスクリプトに仕込んだトリガによって、最後の行（最新の回答）を取得
Trelloにカードを作成
作成が終わったら、スプレッドシートにTrelloカードのURLを書き込む

それでは、もう少し詳しくご説明していきますね。

まずはフォルダ構成から

フォームとその回答（スプレッドシート）は以下のようなフォルダ構成にしています。スプレッドシートと同じ階層に添付ファイル格納用フォルダが自動生成されています。

開発依頼書（フォルダ）
    ┣store.json
    ┣開発依頼書.gform
    ┣開発依頼書（回答）.gsheet
    ┗開発依頼書 (File responses)（フォルダ）
        ┣依頼元部門責任者の印鑑画像をアップロードしてください。 (File responses)（フォルダ）
        ┗補足資料があればアップロードしてください。 (File responses)（フォルダ）

store.jsonというファイルを用意していますが、これはTrelloのAPIキーや各項目の列番号なんかを外出しするために用意しています。後でスプレッドシートのスクリプトから読み込んで、プロパティストアに格納していきます。以下のように書いておきます。

store.json

{
    "user_name": "[Trelloのユーザ名]",
    "api_key": "[TrelloのAPIキー（後述）]",
    "api_token": "[TrelloのAPIトークン（後述）]",
    "team_name": "自分が参加しているチーム名",
    "request_list_name": "依頼",
    "selected_system_column_no": "3",
    "client_name_column_no": "4",
    "title_column_no": "5",
    "background_column_no": "6",
    "required_spec_column_no": "7",
    "expected_effect_column_no": "8",
    "delivery_date_column_no": "9",
    "client_team_column_no": "10",
    "stamp_image_column_no": "11",
    "attachment_file_column_no": "12",
    "card_link_column_no": "13",
    "attachment_error_column_no": "14"
}

次に、スプレッドシートに仕込んだスクリプトを見ていきましょう。

スプレッドシート側のスクリプト

今回（前編）では、store.jsonの内容を読み込んで、スクリプトのプロパティを設定する部分を詳しく見ていきます。

以下の部分です。この関数を実行することで、スプレッドシートと同階層においてあるstore.jsonを読み込んで、一つ一つの値をプロパティストアにセットしてくれます。

function logProperties() {
    const fileIT = DriveApp.getFilesByName('store.json').next();
    const textdata = fileIT.getBlob().getDataAsString('utf8');
    const prop_json = JSON.parse(textdata);

    const properties = PropertiesService.getScriptProperties();

    for (let key in prop_json) {
        properties.setProperty(key, prop_json[key]);
        Logger.log('key:' + key + ' value:' + properties.getProperty(key));
    }
}

最初の数行で、getFilesByName()によってファイルのイテレータを取得し、UTF-8で文字列化した後、JSON化しています。（→prop_jsonに格納）
次に、スクリプトのプロパティを取得してpropertiesに格納しています
最後に、prop_jsonの内容をfor文で回し、キーと値をプロパティストアに格納していきます

スクリプト全文（2020/11/05時点）

次回は、いよいよTrello APIを叩いてカードを作る部分をご説明していきます。

// DevRequest To Trello(公開版)
// プロパティを初期化するときは以下のコメントアウトを外して、logProperties()を実行
// logProperties();

function logProperties() {
    const fileIT = DriveApp.getFilesByName('store.json').next();
    const textdata = fileIT.getBlob().getDataAsString('utf8');
    const prop_json = JSON.parse(textdata);

    const properties = PropertiesService.getScriptProperties();

    for (let key in prop_json) {
        properties.setProperty(key, prop_json[key]);
        Logger.log('key:' + key + ' value:' + properties.getProperty(key));
    }
}

//始めに、プロジェクトのプロパティを読み込む
const properties = PropertiesService.getScriptProperties();

// ユーザー名
const user_name = properties.getProperty('user_name');

// APIキーとトークン
const api_key = properties.getProperty('api_key');
const api_token = properties.getProperty('api_token');

const team_name = properties.getProperty('team_name');
const request_list_name = properties.getProperty('request_list_name');

const request_lists = JSON.parse(setProperties());

// ---ここから列番号の取得---
const selected_system_column_no = properties.getProperty('selected_system_column_no');    // 最初の質問の答え（開発を依頼するシステムの選択）が入っている列の番号
const client_name_column_no = properties.getProperty('client_name_column_no');            // 依頼人氏名の列番号
const title_column_no = properties.getProperty('title_column_no');                        // カード名の列番号
const background_column_no = properties.getProperty('background_column_no');              // 背景の記述の列番号
const required_spec_column_no = properties.getProperty('required_spec_column_no')         //やりたいことの記述の列番号
const expected_effect_column_no = properties.getProperty('expected_effect_column_no')     //期待される効果の列番号
const delivery_date_column_no = properties.getProperty('delivery_date_column_no')         //希望納期の列番号
const client_team_column_no = properties.getProperty('client_team_column_no')             //依頼元担当者（複数可）の列番号
const stamp_image_column_no = properties.getProperty('stamp_image_column_no');            // 印鑑画像の列番号
const attachment_file_column_no = properties.getProperty('attachment_file_column_no');    // 添付ファイルの列番号
const card_link_column_no = properties.getProperty('card_link_column_no');                // カードへのリンクを挿入する列の番号
const attachment_error_column_no = properties.getProperty('attachment_error_column_no');  //添付ファイルのエラー文言を挿入する列の番号
// ---ここまで列番号の取得---

function setProperties() {
  const boards = getTeamBoards(team_name);
  const request_lists_json = getRequestLists(boards, request_list_name);
  const request_lists = JSON.stringify(request_lists_json);
  properties.setProperty('request_lists', request_lists);
  return request_lists;
}

// チーム名を渡すと、そのチームの全ボードIDを取得してくれる関数
function getTeamBoards(team_name) {
  const url = `https://api.trello.com/1/organizations/${team_name}/boards?key=${api_key}&token=${api_token}&fields=id,name`;
  const response = UrlFetchApp.fetch(url, {'method':'get'});
  const response_data = JSON.parse(response.getContentText());
  return response_data;
}

// ボードのリストとリスト名を渡すと、それぞれのボードの中で指定したリスト名をもつリストのIDを取得してくれる関数
// 返り値は{ボード名：指定したリストのID}の配列になる
function getRequestLists(boards, request_list_name) {
  const retMap = boards.map(board => {
    const url = `https://api.trello.com/1/boards/${board.id}/lists?key=${api_key}&token=${api_token}&filter=open&fields=id,name`
    const response = UrlFetchApp.fetch(url, {'method':'get'});
    const response_data = JSON.parse(response.getContentText());
    // 名前にlist_nameが含まれているリストだけ抜き出します
    const request_lists = response_data.filter(list => list.name.indexOf(request_list_name) >= 0 );
    if (request_lists[0]) {
      return {
        'board_id': board.id,
        'board_name': board.name,
        'list_id': request_lists[0].id,
        'list_name': request_lists[0].name
      };
    }
  })
  return retMap.filter(e => e);
}

function addTrelloCard() {
  // 最後の行を取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const last_row = sheet.getLastRow();

  const selected_system = sheet.getRange(last_row, selected_system_column_no).getValue();
  const list_id = request_lists.find(list => list.board_name == selected_system).list_id;

  //リストIDが取得できなければ強制終了
  if (!list_id) {
    sheet.getRange(last_row, card_link_column_no).setValue('Could not fetch list_id.');
    return null;
  }

  const card_title = sheet.getRange(last_row, title_column_no).getValue();
  const client_name = sheet.getRange(last_row, client_name_column_no).getValue();

  //ここでdescriptionの記述内容を取得
  const background = sheet.getRange(last_row, background_column_no).getValue();
  const required_spec = sheet.getRange(last_row, required_spec_column_no).getValue();
  const expected_effect = sheet.getRange(last_row, expected_effect_column_no).getValue();
  const delivery_date = sheet.getRange(last_row, delivery_date_column_no).getValue();
  const client_team = sheet.getRange(last_row, client_team_column_no).getValue();

  // Markdownのdescriptionを生成
  const card_description = generateDescription(background, required_spec, expected_effect, delivery_date, client_team);

  const url = 'https://api.trello.com/1/cards/?key=' + api_key + '&token=' + api_token;
  const options = {
    'method' : 'post',
    'muteHttpExceptions' : true,
    'payload' : {
      'name'      : card_title + ' #' + client_name,
      'desc'      : card_description,
      'due'       : '',
      'idList'    : list_id,
      // 'idLabels'  : 'ラベルを使用したい場合はgetLabelsを実行してよしなに',
      'urlSource' : ''
    }
  }
  const response = UrlFetchApp.fetch(url, options);
  const response_data = JSON.parse(response.getContentText());
  sheet.getRange(last_row, card_link_column_no).setValue(response_data['shortUrl']);
  Logger.log(response_data);

  // 印鑑画像及び添付ファイルがあるかどうかをチェックし、あれば添付する
  const card_id = response_data['id'];
  const stamp_image = sheet.getRange(last_row, stamp_image_column_no).getValue();
  const attachment_file = sheet.getRange(last_row, attachment_file_column_no).getValue();
  if (stamp_image) attachFile(card_id, stamp_image, '印鑑画像');
  if (attachment_file) attachFile(card_id, attachment_file, '補足資料');
}

// フォームの内容を元に、カードのdescriptionにあたるマークダウンの文字列を生成する関数
function generateDescription(background, required_spec, expected_effect, delivery_date, client_team) {
  const descriptions = [];
  descriptions.push('### 背景・現状・課題\n' + background);
  descriptions.push('### やりたいこと\n' + required_spec);
  descriptions.push('### 開発によって見込まれる効果\n' + expected_effect);
  descriptions.push('### 希望納期\n' + delivery_date);
  descriptions.push('### 依頼元の担当者\n' + client_team);
  return descriptions.join('\n***\n\n');
}

// カードIDとファイルURLを渡すと、カードに指定したファイル名で添付ファイルをつけてくれる関数
function attachFile(card_id, file_url, file_name) {
  const url = `https://api.trello.com/1/cards/${card_id}/attachments?key=${api_key}&token=${api_token}&name=${file_name}&url=${file_url}`;
  const options = {
    'method' : 'post',
    'muteHttpExceptions' : true,
    'pauload' : {
      'name' : file_name,
      'file' : file_url
    }
  }
  const response = UrlFetchApp.fetch(url, options);
  const response_data = JSON.parse(response.getContentText());
  Logger.log(response_data);
}

ずみーBlog

元クラゲ研究者（見習い）の92年生まれがエンジニアを目指しながら日々寄り道するブログです。