ラボ内の発注システムを構築した話

(2025年9月2日作成)

Slack, メールをGASで連携させて研究室内の物品発注を効率化するためのツールを作成した話。特にすごい技術を使ったわけでも、突出したアイデアがあるわけでもないが、問題解決やDXにおいて必要な視点/要素が詰まったものだと個人的に考えている。

背景

開発の方針

以下の改善を可能にするツールを作成することを目的とする。

• 二度手間の解消
  承認されれば自動でメールが送信されるようにすることで、二度手間を解消

• 申請履歴の一覧表示
  承認(あるいは却下)済みの申請と未承認のものを判別しやすい、一覧画面を作成する

その上で、制約として以下の条件を設ける。

• 学生以外の作業手順は極力変更しない
  すべての作業をSlackまたはメールで済ませるようにしてしまえば比較的簡単に改善が可能(Slackにも電子メールにも転送機能がある)。しかし作業手順を変更するインセンティブは学生にしか存在しないため、多忙な先生や秘書さんに新しいツール/手順を押し付けるのは避けるべき。

• 無料で維持できる
  このような小規模な効率化に公費を投じるのはコスパの観点からも合理的とは言えない上に、おそらく支出の処理が面倒。

• ある程度の保守性を担保
  研究室は、学生が数年で入れ替わる環境。あまり高度/複雑なシステムを採用すると保守できなくなる。

• システムにはどこからでもアクセス可能
  出張先にいる先生から承認をもらうようなケースも頻繁にあるため、ツールはインターネットがつながる場所であればどこからでもアクセスできる必要がある

技術選定

Slack とZapierなどの組み合わせ →ボツ

Slack上で承認のアクションが取られた際に、Zapierなどでメールを自動送信することが可能。しかし、Slackのリアクション機能などは自由度が高い(アイコンの種類を任意に選べる)ため、操作を厳密に決めておかないと必要なときにZapierが発動しなかったり、逆に誤作動が生じたりする。

例えば、承認には👍アイコンを用いているが、たまに押し間違いなどで他のアイコン(👌など)が押されることもある。人が読む分には文脈から承認の意味と捉えることができるが、Zapierは機械的な判定しかできないのでそのような場合に発動しない。また同じチャンネル内では極稀に発注の承認以外の意味で👍が用いられることもあり、これは逆に誤作動に繋がる。

したがって、操作の自由度が高いSlack上でのアクションをトリガーとすることは現実的ではないという結論に達した。Webアプリのように専用のUIを有するシステムが望ましいと思われる。

PaaS上のWebアプリ →ボツ

既存のクラウド連携ツールを使うのではなく、ツール専用のサーバーを設置してしまえば高い自由度での設計が可能になる。Webアプリを作成し、WebアプリからSlackへの通知(WebhookまたはSlack APIを使用)やメール送信を行えるようにしておけば従来の業務フローを大きく変えることなく効率化ができそう。

しかし、保守性の観点から言うとあまり望ましいとは言えない。Webアプリの仕様を変更することはあまりない(と考えられる)ためアプリのコードが複雑化すること自体は問題ないが、データベースについてはある程度の保守が必要になる可能性が考えられる(例えば古い履歴を削除して容量を確保するなど)。農学系の研究室の担当者SQLの知識を求めるのも酷な話なので、やはり避けたいところ。

さらに、永続的に無料で使える保証がない点も注意が必要。開発時点ではPaaSならRender.com、DBaaSならneon.tech あたりが実用的な無料枠を提供しているため、完全無料でシステムを構築可能だが、過去にはherokuの無料枠廃止のような事例もあるため永続性については微妙なところである。あるいはPaaSを利用せず研究室内にサーバーを建てることも可能(N100とか搭載の適当なミニPCか、なんならRaspberry Piでもできそう)だが、インターネットに公開して外部からのアクセスを可能にするには大学の情報環境機構に新しくVLANを作成してもらうなど大掛かりな手続きが必要になる(というかサーバー自体の保守がネックになるので避けたい)。

以上のような理由と、そもそも目的に大して大掛かりになりすぎるということからPaaS上のWebアプリもボツとなった。

GAS上の簡易Webアプリ →採用

上述のようなWebアプリを簡易的に実装できるのがGAS (Google Apps Script)である。データベースのかわりにGoogle Spreadsheetを用いることで、データの不整合などにも対処しやすいという点も利点となる。

さらに、ユーザーの管理が容易である点はPaaSに自作したWebアプリと比較してもメリットとなる。Googleアカウントを使用することが可能なので、ユーザーの利便性を損ねることなく承認や投稿の権限を管理することが可能になる。定数をScript propertiesで保持できる点からも、専用のGUIを用意しなくても権限管理や担当者の変更などに対応しやすく、簡便かつ保守性の高いシステムであると言える。

設計と実装

個人的な備忘録がメインなので、実装については十分な解説とは言えないことをお断りしておく。

(というかGoogle Sitesにコードブロックを配置するのが面倒なのでまともな解説を作りにくい…)

全体像

以下のような挙動のシステムを構築する。

• 承認依頼投稿フォーム
  金額、購入項目などの必要事項の入力欄を設けたフォーム。投稿されると、下記Slackへの通知が送信される。

• Slackへの通知
  投稿内容とともに、「承認」「却下」のリンクを設置したメッセージをSlackに送信。

• 承認/却下ページ
  リンクを開くと即時に承認または却下の処理が実行される。承認の場合は下記メールが自動で送信される。
  承認権限がないユーザーがクリックした場合にはエラーを返す。

• 発注依頼メール
  実際に発注を行う秘書さんに、発注依頼メールを送信する。

• 申請履歴ページ
  過去の申請と、それらの承認状態の一覧表示を提供。このページからも承認・却下の処理を行える。Slackのへの投稿などからアクセス可能。

実際のUI

UIの実装

テンプレートとなるHTMLファイルを作成し、これをGASの関数からreturn させる。getリクエストが来ると、doGet()関数が動作するようになっている。URLパラメータに応じてページを選択・構築してreturnするようにdoGet()を編集するとよい。

テンプレートの読み込み:  template = HtmlService.createTemplateFromFile("ファイル名(文字列; 拡張子不要)");

テンプレート内のパラメータの設定: template.parameter = "hogehoge"; //パラメタ名にその中身(文字列)を対応付ける

テンプレートをreturnする: template.evaluate().setTitle('発注管理システム');

• 注意点として、GASアプリでは作成したHTMLはiframe内に読み込まれる。このためアプリ内で相対参照のリンクを作成しても想定通りの挙動にはならない。絶対リンクにするため、Script parametersにデプロイのURLを保存した上で<? APP_URL ?>などとしてテンプレート内に埋め込むのがよい。また<a>の内部ではtarget="_top"とする。

• 同様の理由でjs上の"location.href='https://~~~~~'" のようなコードも(絶対参照にしたとしても)正しく動かない。window.top.location.href = を用いる。

• アイコン画像などをホストすることはできないので、UI用のアイコンはfontawesomeを使用した。自前のアイコンを使いたい場合はGitHub PagesなどをCDN代わりにしてもよいと思われる。

(GASコードの全体はページ末尾に貼り付ける)

ユーザ認証の実装

Google アカウントを用いることでユーザを識別する。Apps Scriptはユーザのアカウントで実行する設定とし、Session.getActiveUser().getEmail();でGoogle アカウントのメールアドレスを取得した。

アクセスできるユーザ、承認権限をもつユーザはScript propertiesにカンマ区切りでメールアドレスを全員分入力してホワイトリストとして使用した。

Slack通知機能の実装

SlackのIncoming webhookを使用。Webhookの設定手順は以下の通り。

• Slack APIのページ> Your Apps> Create New App >From Scratchでアプリを作成

• 左側メニューパネルのApp HomeからYour App’s Presence in Slackの欄内のdisplay nameを設定 (これをしないとwebhookを設定できない)

• 左側メニューパネルのIncoming WebhooksからAdd New Webhookをクリックし、送信先のチャンネルを指定。

GASからWebhookを叩く。以下の処理を関数とした。

function notifySlack(data, id) {

  const url = SETTINGS.slack_webhook;

  const payload = {

    text: `発注の申請がありました。( <${SETTINGS.webapp_url}?page=history|申請履歴を見る> )\n\n申請者: ${data.applicant}\nタイトル: ${data.title}\n金額: ${data.total_amount}円(税込)\n予算: ${data.budget}\n>>> ${data.details}\n\n\n<${SETTINGS.webapp_url}?page=approve&_id=${id}&action=approve|✅__承認__>　　　　　<${SETTINGS.webapp_url}?page=approve&_id=${id}&action=reject|❌__却下__>`

  };

  if (url) {

    UrlFetchApp.fetch(url, {

      method: 'post',

      contentType: 'application/json',

      payload: JSON.stringify(payload)

    });

  }

}

• Slackの記法として、<https://example.com | リンクテキスト>でHTMLで言うところの<a href="https://example.com">リンクテキスト</a>と同じようなことができる。

メール送信

GmailApp.sendEmail()を使用。

GAS にはMailApp.sendEmail()も存在するが、こちらは迷惑メールとして処理される確率が格段に高くなる。特に、GmailではないメールアドレスをGoogleアカウントに使用している場合、大学のアドレス宛に送ると迷惑メールフォルダにすら入らずブロックされてしまう。これは、GASのサーバーがfromのアドレスを指定したものに変えて(なりすまして)送信しているためらしい(Chat GPT談)。

GmailAppのほうを使うと、ユーザのGmailサーバーから直接送信されるため、そのような問題は生じない。ただし、GmailではないメールアドレスをGoogleアカウントに使用している場合には使用できない。レアケースなので一旦問題ないということにしておく。

その他GASの実装に関するTips

• GAS上の関数をフロントエンドから呼び出して、Ajaxのようなことができる。今回の開発においても何箇所かで使用した。
  google.script.run.withSuccessHandler(function(関数の返り値){
            //成功した場合の処理

        }).<Code.gs上にある実行したい関数>(引数)

その他懸案事項など

• 履歴一覧画面で過去の申請とそのステータスを一覧できるが、Slackからも承認済みか否かを確認できたほうが便利である。
  これを実装するには、Slackの投稿のIDを取得してレスポンスまたは返信をつける必要があるが、Slack にWebhookで投稿した場合には投稿IDを取得することはできない(日付などで推定することは可能だがロバストでない)。WebhookではなくSlack APIで投稿すると、投稿した際にそのIDが返ってくるらしい(Chat GPT談)ので、その方式に切り替えても良いかもしれない。(でもWebhookのほうが単純ではあるし・・・難しい)

• UIをレスポンシブデザインにしたいのだが、現状はスマートフォン偏重のデザインである。これは、HTMLがiframeで読み込まれることでviewportの制御がうまく行かないためであるが、技術次第ではなんとかなるのかもしれない。CSSよくわからん。

GASのCode.gs全文

コードが汚い部分やら無駄な部分もあるけど追々なんとかする。

const SPREADSHEET_ID = 'dummy_sheet_name'; // 例: '1A2B3C...'

const SHEET_NAME = 'history';

const SETTINGS = {

  budgets: (PropertiesService.getScriptProperties().getProperty('budgets') || '')

    .split(',').map(x => x.trim()).filter(x => x),

  approvers: (PropertiesService.getScriptProperties().getProperty('approvers') || '')

    .split(',').map(x => x.trim()).filter(x => x),

  order_manager: PropertiesService.getScriptProperties().getProperty('order_manager') || '',

  slack_webhook: PropertiesService.getScriptProperties().getProperty('slack_webhook') || '',

  webapp_url: PropertiesService.getScriptProperties().getProperty('webapp_url') || ''

};

// Webアプリのエントリーポイント（GET）

function doGet(e) {

  const page = e.parameter.page || 'form';

  if (page === 'history') {

    return HtmlService.createTemplateFromFile('history').evaluate().setTitle('履歴');

  }

  /*if (page === 'approve') {

    let id = e.parameter.id || '';

    const template = HtmlService.createTemplateFromFile('approve');

    template.id = id;

    return template.evaluate().setTitle('承認画面');

  }*/

  if (page === 'approve') {

    let id = e.parameter._id || '';

    const action = e.parameter.action || "none";

    if (action === "none"){

      const template = generateApprovePage(id);

      return template.evaluate().setTitle('承認画面');

    }else {

      order = getOrderDetailById(id);

      if (!SETTINGS.approvers.includes(getCurrentUser())){

        return generateMessagePage("承認の権限がありません");

      }

      if (!order){

        return generateMessagePage("指定したidの発注が見つかりません");

      }

      if (order.status !== "未承認"){

        return generateMessagePage(`ID: ${id} はすでに${order.status}済みです。`);

      }

      if (action === "approve"){

        status = "承認";

      }else if(action === "reject"){

        status = "却下";

      }

      let e;

      try {

        approveOrderById(id, status, "");

      }catch(error){

        e = error;

      }

      if (e){

        return generateMessagePage(e);

      }

      message = "ID: " + String(id) + " の発注を" + status + "しました.";

      return generateMessagePage(message);

    }

  }

  //ページの指定がない場合はフォームを表示する

  return HtmlService.createTemplateFromFile('form').evaluate().setTitle('発注申請フォーム');

}

function getSettings() {

  return SETTINGS;

}

function getCurrentUser() {

  return Session.getActiveUser().getEmail();

}

function generateMessagePage(message) {

  const template = HtmlService.createTemplateFromFile('message');

  template.message = message;

  template.url = SETTINGS.webapp_url;

  return template.evaluate().setTitle('発注管理システム');

}

function generateApprovePage(id) {

  order = getOrderDetailById(id);

  if (order){

    var html = `<h4>${order.title}&nbsp;&nbsp;<span style="color: lightgray;">(ID: ${order.id})</span></h4>

                <ul>

                  <li><b>申請者　：</b>${order.applicant} <span style="color: lightgray;">(${order.applicant_id})</span></li>

                  <li><b>申請日　：</b>${formatJST(order.created_at)}</li>

                  <li><b>予算費目：</b>${order.budget}</li>

                  <li><b>合計金額：</b>${order.total_amount} 円(税込)</li>

                  <li><b>発注内容：</b><br>${escapeAndBrWithLink(order.details)}</li>

                  <hr>

                  <li><b>状態　：</b>${order.status}</li>

                  <li><b>承認者：</b>${order.approver || '-'}</li>

                  <li><b>承認日：</b>${formatJST(order.approved_at)}</li>

                  <li><b>承認者コメント：</b><br>${escapeAndBrWithLink(order.approver_comment)}</li>

                </ul>

                コメント&nbsp;&nbsp;&nbsp;<span style="color:#666666">(数量などの変更は、コメントを付して「却下」とする)</span><br>

                <textarea id="approver_comment" rows="5"></textarea><br>

                <span style="color:gold;"><i class="fa-solid fa-triangle-exclamation"></i></span><span style="color:red">立替払いの物品は承認しないでください</span><br>

                <span>承認すると、発注のメールが自動で送信されます。</span><br>

                <div id="action-btns" style="display:inline-block;">`;

    //承認済みかどうか            

    if (order.status !== '未承認'){

      html = html + `<span style="color:red;">すでに${order.status}済みです。</span>`

    }else{

      userEmail = getCurrentUser();

      if (SETTINGS.approvers.includes(userEmail)){

        html = html + `<button id="btn-accept" onclick="approve('承認')">

                        <i class="fa-solid fa-circle-check" style="color:#1cc700"></i>&nbsp;承認

                      </button> ` +

                      `<button id="btn-reject" onclick="approve('却下')">

                        <i class="fa-solid fa-ban" style="color:#a31800"></i>&nbsp;却下

                      </button>`;

      }else{

        html = html + `<span style="color:red;">承認権限がありません。権限を付与されたGoogleアカウントでログインしてください。</span>`

      }

    }

    html = html + `</div>

                  <a id="link-to-history" href="${SETTINGS.webapp_url}?page=history" style="background-color:#e0e0e0; padding:5px; margin:10px; border-radius: 5px; display:inline-block; text-decoration: none; color:#0e0e0e; float:right; width:80px; text-align:center;">

                    <i class="fa-solid fa-list-ul"></i>&nbsp;履歴

                  </a>

                  `;

  }else{

    var html = "<h1>指定されたIDの申請が見つかりません</h1>"

  }

  const template = HtmlService.createTemplateFromFile('approve_ver2');

  template.html = html;

  template.id = id;

  return template;

}

function escapeAndBrWithLink(str) {

  if (!str) return '';

  // HTMLエスケープ

  let s = str.replace(/&/g, "&amp;")

            .replace(/</g, "&lt;")

            .replace(/>/g, "&gt;");

  // URL検出・リンク化（http, httpsのみ）

  s = s.replace(/(https?:\/\/[^\s<>"']+)/g, function(url) {

    return `<a href="${url}" target="_blank" rel="noopener">${url}</a>`;

  });

  // 改行

  return s.replace(/\n/g, "<br>");

}

function formatJST(isoString) {

  if (!isoString) return '-';

  const date = new Date(isoString);

  // toLocaleStringで日本ロケール・タイムゾーン指定

  return date.toLocaleString('ja-JP', { timeZone: 'Asia/Tokyo' });

}

// フォームから申請内容を受けてスプレッドシートに記録

function submitOrder(data) {

  const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);

  // 最新のid（A列）を取得して＋1（ヘッダ除外で2行目から）

  const lastRow = sheet.getLastRow();

  let newId = 1;

  if (lastRow > 1) {

    const values = sheet.getRange(2, 1, lastRow - 1, 1).getValues().flat();

    newId = Math.max(...values.map(Number)) + 1;

  }

  // データがなければ newId = 1 のまま

  const user = Session.getActiveUser().getEmail();

  const now = new Date();

  const row = [

    newId,              // id

    data.applicant,

    user,

    now.toISOString(),

    data.title,

    data.budget,

    data.total_amount,

    data.details,

    '未承認', // status

    '',       // approver

    ''        // approved_at

  ];

  sheet.insertRowAfter(1);

  sheet.getRange(2, 1, 1, row.length).setValues([row]);

  // Slack通知

  notifySlack(data, user, newId);//ここのコメントアウトを解除するとSlackに通知される！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

}

//Slackにメッセージを送信する

function notifySlack(data, user, id) {

  const url = SETTINGS.slack_webhook;

  const payload = {

    text: `発注の申請がありました。( <${SETTINGS.webapp_url}?page=history|申請履歴を見る> )\n\n申請者: ${data.applicant}\nタイトル: ${data.title}\n金額: ${data.total_amount}円(税込)\n予算: ${data.budget}\n>>> ${data.details}\n\n\n<${SETTINGS.webapp_url}?page=approve&_id=${id}&action=approve|✅__承認__>　　　　　<${SETTINGS.webapp_url}?page=approve&_id=${id}&action=reject|❌__却下__>`

  };

  if (url) {

    UrlFetchApp.fetch(url, {

      method: 'post',

      contentType: 'application/json',

      payload: JSON.stringify(payload)

    });

  }

}

// 履歴データを返す（直近1ヶ月分）・id管理で返す

function getRecentOrders() {

  const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);

  const values = sheet.getDataRange().getValues();

  const today = new Date();

  const result = [];

  for (let i = 1; i < values.length; i++) { // 1行目はヘッダ

    const created = new Date(values[i][3]); // created_at（id, applicant, applicant_id, created_at...）

    const diff = (today - created) / (1000 * 60 * 60 * 24); // 日数

    if (diff <= 31) {

      result.push({

        id: values[i][0],

        applicant: String(values[i][1]),

        applicant_id: String(values[i][2]),

        created_at: values[i][3],

        title: String(values[i][4]),

        budget: String(values[i][5]),

        total_amount: values[i][6],

        details: String(values[i][7]),

        status: String(values[i][8]),

        approver: String(values[i][9]),

        approved_at: values[i][10],

        approver_comment: String(values[i][11])

      });

    } else {

      break;

    }

  }

  return result;

}

// idで特定投稿の詳細取得

function getOrderDetailById(id) {

  const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);

  const values = sheet.getDataRange().getValues();

  for (let i = 1; i < values.length; i++) {

    if (String(values[i][0]) === String(id)) {

      return {

        id: values[i][0],

        applicant: String(values[i][1]),

        applicant_id: String(values[i][2]),

        created_at: values[i][3],

        title: String(values[i][4]),

        budget: String(values[i][5]),

        total_amount: values[i][6],

        details: String(values[i][7]),

        status: String(values[i][8]),

        approver: String(values[i][9]),

        approved_at: values[i][10],

        approver_comment: String(values[i][11])

      };

    }

  }

  return null;

}

// idで特定投稿の承認・却下

function approveOrderById(id, status, approver_comment) {

  const user = Session.getActiveUser().getEmail();

  if (!SETTINGS.approvers.includes(user)) {

    throw new Error('権限がありません');

  }

  const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);

  const values = sheet.getDataRange().getValues();

  for (let i = 1; i < values.length; i++) {

    if (String(values[i][0]) === String(id)) {

      // status, approver, approved_at を更新

      sheet.getRange(i + 1, 9).setValue(status);       // status（I列, 9）

      sheet.getRange(i + 1, 10).setValue(user);        // approver（J列, 10）

      sheet.getRange(i + 1, 11).setValue(new Date().toISOString()); // approved_at（K列, 11）

      sheet.getRange(i + 1, 12).setValue(approver_comment); // approved_at（K列, 12）

      // 承認の場合はメール送信

      if (status === '承認') {

        const data = sheet.getRange(i + 1, 1, 1, 11).getValues()[0];

        sendOrderMail(data, approver_comment);//ここをオンにするとメールが送信される！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

      }

      return;

    }

  }

  throw new Error('指定されたidの申請が見つかりません');

}

// メール送信

function sendOrderMail(data, approver_comment) {

  const to = SETTINGS.order_manager;

  const cc = data[2]; // 申請者のGoogleアカウント（メールアドレス）

  let createdAt = data[3];

  const dateObj = new Date(createdAt);

  const createdAtJST = Utilities.formatDate(dateObj, 'Asia/Tokyo', 'MM月dd日 HH:mm')

  const subject = `[発注依頼] ${data[4]} (${createdAtJST})`; // title(created_at)

  const body = `

【発注が承認されました】

以下の内容で物品の発注をお願いします。

申請ID: ${data[0]}

申請者: ${data[1]} (${data[2]})

申請日: ${data[3]}

タイトル: ${data[4]}

予算費目: ${data[5]}

合計金額: ${data[6]}

詳細:

${data[7]}

承認者: ${data[9]}

承認日: ${data[10]}

承認者コメント:

${approver_comment}

--------------------------------

このメールは昆虫生態学研究室の発注承認システムにより自動で送信されています。

--------------------------------

`;

  if (to) {

    GmailApp.sendEmail(to, subject, body, {cc: cc});

  }

}

//未承認件数を取得する関数 (メッセージ画面から呼び出す)

function countUnapproved() {

  // スプレッドシートを開く

  const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);

  // I列（9列目）の最終行までのデータを取得

  var lastRow = sheet.getLastRow();

  var values = sheet.getRange(1, 9, lastRow).getValues(); // (行, 列, 行数)

  // "未承認" のセルをカウント

  var count = 0;

  for (var i = 0; i < values.length; i++) {

    if (values[i][0] === "未承認") {

      count++;

    }

  }

  return count;

}

function test(){

  Logger.log(countUnapproved());

}