0.完成はこちら

GASを使って、システム権限申請をエクセルからWEBに変更してみました。

みなさんこんにちは。
某小売業でECシステムに関わっています。
私の管轄するECは全国各地に事業拠点があり、数多くの社員（ユーザ）が働いております。今回はECシステムの管理画面に社員（ユーザ）がアクセスする権限の申請について、試行錯誤してみました。

1.システムの権限や管理って？

システムの権限管理について知っていますか。

システムの権限管理は、情報セキュリティの基礎です。特に、認証（ユーザが本人であることの確認）と認可（ユーザが特定のリソースにアクセスする権限を持っているかの確認）は、セキュリティの中核を成します。

権限は最小権限の原則を適用し、ユーザには必要最低限の権限のみを付与することが重要です。また、誤った権限付与は、不正アクセスやデータの漏洩など、重大なセキュリティインシデントに繋がる可能性があります。不要な権限を削除することで、システムの安全性を保つことができます。権限管理の徹底は、組織の情報資産を守る上で避けて通れない道です。

2.権限申請はどのように実施しているのか

ユーザから依頼があれば、私の所属する部内にてシステムに権限登録します。申請はエクセルファイルに記入し、メール送付となります。

みなさんのチームでは以下のようなことありますか？
　・申請はメールでのエクセルファイル添付が主あり、
　　ユーザとの余計なメールが増えているかもしれない
　・人事異動があり、着任者の登録依頼はされるが、
　　退任者の消去依頼は棚卸の際になっているかもしれない
　・ユーザ側のまとめ役が、ユーザ棚卸を試みようとしても、
　　データベースから最新の情報を取得する手段しかないため、
　　簡単に棚卸できないかもしれない

ユーザがシステムを使えるようになるだけならば、上記でも良いですが、
利便性や安全性のあるパーフェクトなシステムとは言えないかもしれません。

3.申請はWEBで完結すると良いかもしれない

エクセルの方が慣れている方もいると思います。しかし、WEBであれば、メールを送付する手間が減り、権限ユーザの一覧も随時把握できるかもしれません。また、ユーザに権限が不要になった際、申請していただけるかもしれません。現状に満足せず、利便性向上、システムの安全性向上を目指してWEB申請にできるようしてみました。

4.使用したツール

・ChatGPT
・ChatGPTの企業向けサービスexaBase
　ChatGPTが使用可能であれば、exaBaseは必須ではありません。
・Google Apps Script
・Google Spreadsheet

5.開発

5-1.全体構造

全体構造は以下になっております。ブラウザからリクエストを受けると申請データに対してアクセスして応答を返します。

5-2.主な機能

・ユーザ登録されている情報が表示される
・ユーザ新規登録、更新、消去が行える
・申請時は必須項目のチェックがされる
・更新時は一覧情報から選択すると、入力フォームに反映される
・申請者が複数件登録する可能性はあるので、ボタン押下後も申請者情報をフォームに残す

5-3.ChatGPTが大活躍

コードを作成しますが、その際は生成AIを使用します。使用方法として、「GASで申請するWEBを作りたい」というような入力になります。今回の使い方として、１回の出力で終わりではなく、生成AIと会話しながら、コードを修正していくステップを踏んでいます。私の使用した１回目やその後の入力を共有します。

警告
※1 後ほど後述しますが、１回の出力だけではうまくいきません。
※2 修正の過程で、下記プロンプトの内容と最終デプロイの内容は若干異なりました。認識齟齬がある可能性もありますので、ご注意ください。

１回目プロンプト

GASを使って、WEBを作成したい。
画面のタイトルは、「ECシステムユーザー権限申請」とする。
また、上部に、「こちらのWEB申請でシステムのユーザー登録を実施します。※作業完了までの目安として、１週間頂いております。」と大きく表示する。※以降は赤字で表示したい。

ボタンは、登録依頼ボタン、更新依頼ボタン、消去依頼ボタンを表示する。ボタンが押下された時は、「はい」と「いいえ」の確認メッセージを出して、「はい」の場合は実行するようにしたい。

表示項目と入力項目は、申請種別、社員番号、氏名、所属、備考、申請者氏名、申請者社員番号、申請者電話番号とする。

受付Noは、登録依頼ボタンが押下されたら附番される番号にしたい。編集はできない。
申請種別は、入力必須で、WEBからプルダウンメニューで選ばせるようにする。
プルダウンメニューは、新規登録、変更、パスワード変更、消去とする。新規登録が選択された場合は、登録依頼ボタンだけが押せるようにし、更新依頼ボタン、消去依頼ボタンを押してもエラーにする。変更またはパスワード変更が選択された場合は、更新依頼ボタンだけ押せるようにする。消去が選択された場合は、消去依頼ボタンだけ押せるようにする。

社員番号は、入力必須で、７桁である。桁違いはエラーメッセージが出るようにしたい。
氏名は、入力必須とする。
所属、入力必須とする。
備考、入力任意とする。
申請者氏名、入力必須とする。
申請者社員番号、入力必須とする。
申請者電話番号、入力必須とする。
申請日は、登録依頼ボタンが押下された日を自動で書き込まれるようにしたい。編集はできない。
更新日は、更新依頼ボタンが押下された日を自動で書き込まれるようにしたい。編集はできない。

エクセルのデータは以下のように変更する。
B2のセルに受付Noのラベルがあり、B3より下の列に、値が入るようにしたい。
C2のセルに申請種別のラベルがあり、C3より下の列に、値が入るようにしたい。
D2のセルに社員番号のラベルがあり、D3より下の列に、値が入るようにしたい。
E2のセルに氏名のラベルがあり、E3より下の列に、値が入るようにしたい。
F2のセルに所属のラベルがあり、F3より下の列に、値が入るようにしたい。
G2のセルに備考のラベルがあり、G3より下の列に、値が入るようにしたい。
H2のセルに申請者氏名のラベルがあり、H3より下の列に、値が入るようにしたい。
I2のセルに申請者社員番号のラベルがあり、I3より下の列に、値が入るようにしたい。
J2のセルに申請者電話番号のラベルがあり、J3より下の列に、値が入るようにしたい。
K2のセルに申請日のラベルがあり、K3より下の列に、値が入るようにしたい。
L2のセルに更新日のラベルがあり、L3より下の列に、値が入るようにしたい。

途中のプロンプト

スプレッドシートのデータを取得して、ボタンの下に社員番号、氏名、所属の一覧を表示したい。コードを修正してください。

5-4.Google Apps Script（GAS）

今回は以下の記事も参考にしています。

上記記事にGASの基本的な使い方がありますので、今回は省略します。
GASファイルには、「.gs」と「.html」と「.css.html」を用意します。

5-4-1.gsファイル

データベース（Google Spreadsheet）へのデータ書き込みなどの処理を書いております。

スプレッドシートのファイルIDは適宜変更してください。

gsコード

// スプレッドシートの設定
const fileId = 'OOOOOOOOOOOOOOOOOOOOOOOOOOOO';  // ←ココにスプレッドシートのファイルIDを指定します。
const spreadSheet = SpreadsheetApp.openById(fileId);  
const sheet       = spreadSheet.getSheetByName('data'); 

function doGet() {  
  const html = HtmlService.createTemplateFromFile('index');  
  return html.evaluate();  
}

function submitForm(request) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('data'); // 申請を管理するシート名を指定
  // 受付Noを生成（最後の受付Noに1を加える）
  const lastRow = sheet.getLastRow();
  const lastReceiptNoCell = sheet.getRange(lastRow, 1).getValue(); // 受付Noが最初の列にあると仮定
  const receiptNo = lastReceiptNoCell ? (parseInt(lastReceiptNoCell) + 1) : 1; // 最初の申請の場合は1から開始

  // フォームデータから取得した情報をスプレッドシートに追加
  sheet.appendRow([
    receiptNo,
    request.formData.applicationType,
    request.formData.employeeNumber,
    request.formData.name,
    request.formData.department,
    request.formData.remarks,
    request.formData.applicantName,
    request.formData.applicantEmployeeNumber,
    request.formData.applicantPhoneNumber,
    new Date(),  // 現在の日付
    request.action, // 申請のアクションタイプ（登録、更新、消去）
    // 他の必要なデータ
  ]);
}

function getEmployeeList() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data'); // 'data'はシート名
  const lastRow = sheet.getLastRow();
  const range = sheet.getRange('B3:E' + lastRow); // 申請種別、社員番号、氏名、所属がある範囲
  let values = range.getValues();
  
  let deletionSet = new Set(); // 「消去」申請があった社員番号を記録
  let latestEntries = {}; // 最新のデータのみを保持するためのオブジェクト

  // 最初に全ての行を見て、「消去」申請があった社員番号を記録
  values.forEach(row => {
    const applicationType = row[0]; // 申請種別がB列（配列の最初の要素）にある
    const employeeNumber = row[1];
    if (applicationType === '消去') {
      deletionSet.add(employeeNumber); // 「消去」申請の社員番号を記録
    }
  });

  // 次に、消去リストにない社員番号のデータのみを処理
  values.forEach(row => {
    const applicationType = row[0];
    const employeeNumber = row[1];

    // 「消去」リストに含まれていない、または申請種別が「消去」でないデータのみを保持
    if (!deletionSet.has(employeeNumber) && applicationType !== '消去') {
      latestEntries[employeeNumber] = {
        applicationType: applicationType,
        employeeNumber: row[1], // 社員番号
        name: row[2], // 氏名
        department: row[3], // 所属
      };
    }
  });
  
  const latestData = Object.values(latestEntries); // オブジェクトの値のみを配列として抽出
  
  return latestData; // 最終的なデータ配列をそのまま返す
}

5-4-2.htmlファイル

ボタンや項目の表示はもちろん、必須項目のチェックや申請時の確認メッセージ表示、修正時にフォームへ情報を反映させる処理なども書いております。

htmlコード

<!DOCTYPE html>
<html lang="ja">
<html>
  <head>
    <title>ECシステムユーザー権限申請</title>
    <?!= HtmlService.createHtmlOutputFromFile("style.css").getContent(); ?>
    <script>
      function onApplicationTypeChange() {
        var applicationType = document.getElementById('applicationType').value;
        // ボタンの有効/無効化を管理
        document.getElementById('registerRequestButton').disabled = applicationType !== '新規登録';
        document.getElementById('updateRequestButton').disabled = applicationType !== '変更' && applicationType !== 'パスワード変更';
        document.getElementById('deleteRequestButton').disabled = applicationType !== '消去';
      }

      // フォーム送信前のバリデーション
      function validateForm() {
        // 必須項目のチェック
        const requiredFields = ['employeeNumber', 'name', 'department', 'applicantName', 'applicantEmployeeNumber', 'applicantPhoneNumber'];
        for (let i = 0; i < requiredFields.length; i++) {
          const fieldValue = document.getElementById(requiredFields[i]).value.trim();
          if (fieldValue === '') {
            alert('必須項目が入力されていません。');
            return false; // バリデーション失敗
          }
        }
        return true; // バリデーション成功
      }

      // ボタンクリック時の処理
      function onSubmitButtonClick(action) {
        // フォームのバリデーション
        if (!validateForm()) {
          return; // バリデーション失敗時は処理を中断
        }

        // 確認メッセージを表示
        var confirmMessage = 'この操作を実行しますか？';
        if (confirm(confirmMessage)) {
          // 「はい」が選択された場合
          google.script.run
            .withSuccessHandler(function() {
              alert('申請が完了しました。'); // サーバー側の処理が成功した場合のメッセージ
              // ここで社員リストを再表示
              google.script.run
                .withSuccessHandler(displayEmployeeList)
                .getEmployeeList();
              // 申請者情報を除いてフォームをリセット
              resetFormExceptApplicantInfo();
            })
            .withFailureHandler(function(error) {
              alert('エラーが発生しました: ' + error.message); // エラーが発生した場合の処理
            })
            .submitForm({
              action: action,
              formData: Object.fromEntries(new FormData(document.getElementById('requestForm')))
            });
        } else {
          // 「いいえ」が選択された場合は何もしない
        }
      }

      // 申請者情報を残してフォームをリセットする関数
      function resetFormExceptApplicantInfo() {
        const applicantName = document.getElementById('applicantName').value;
        const applicantEmployeeNumber = document.getElementById('applicantEmployeeNumber').value;
        const applicantPhoneNumber = document.getElementById('applicantPhoneNumber').value;

        document.getElementById('requestForm').reset();
        
        // 申請者情報を再設定
        document.getElementById('applicantName').value = applicantName;
        document.getElementById('applicantEmployeeNumber').value = applicantEmployeeNumber;
        document.getElementById('applicantPhoneNumber').value = applicantPhoneNumber;

        // applicationTypeの変更イベントを再度トリガー
        document.getElementById('applicationType').dispatchEvent(new Event('change'));
      }
    </script>
  </head>
  <body>
    <div class="container">
        <div class="sidebar">
            <!-- メニュー項目 -->
            <ul>
                <li><a href="#">MTS</a></li>
                <li><a href="#">ユーザー権限申請</a></li>
                <li><a href="#">設定</a></li>
            </ul>
        </div>
    <div class="main-content">
     <h2>ECシステムユーザー権限申請</h2>
    <div>こちらのWEB申請でシステムのユーザー登録を実施します。<span style="color: red;">※作業完了までの目安として、１週間頂いております。</span></div><br>
    <div>内容に応じて申請種別の切り替えをしてください。</div><br>

    <form id="requestForm">
      <!-- フォームの内容 -->
      <div class="form-group">
      <label for="applicationType">申請種別 　　　:</label>
      <select id="applicationType" name="applicationType" required onchange="onApplicationTypeChange()"><br>
        <option value="新規登録">新規登録</option>
        <option value="変更">変更</option>
        <option value="パスワード変更">パスワード変更</option>
        <option value="消去">消去</option>
      </select><br>
      <label for="employeeNumber">社員番号*　　　:</label>
      <input type="text" id="employeeNumber" name="employeeNumber" required><br>
      <label for="name">氏名*　　　　　:</label>
      <input type="text" id="name" name="name" required><br>
      <label for="department">所属*　　　　　:</label>
      <input type="text" id="department" name="department" required><br>
      <label for="remarks">備考 　　　　　:</label>
      <textarea id="remarks" name="remarks"></textarea><br><br>
      
      <!-- 申請者情報 -->
      <label for="applicantName">申請者氏名*　　:</label>
      <input type="text" id="applicantName" name="applicantName" required><br>
      <label for="applicantEmployeeNumber">申請者社員番号*:</label>
      <input type="text" id="applicantEmployeeNumber" name="applicantEmployeeNumber" required><br>
      <label for="applicantPhoneNumber">申請者電話番号*:</label>
      <input type="text" id="applicantPhoneNumber" name="applicantPhoneNumber" required><br><br>

      <!-- ボタン（前の説明を参照） -->
      <input type="button" id="registerRequestButton" value="登録依頼ボタン" onclick="onSubmitButtonClick('register')" disabled>
      <input type="button" id="updateRequestButton" value="更新依頼ボタン" onclick="onSubmitButtonClick('update')" disabled>
      <input type="button" id="deleteRequestButton" value="消去依頼ボタン" onclick="onSubmitButtonClick('delete')" disabled><br>
    </div>
    </form>
    
    <br><div>以下は現在のユーザになります。社員番号の左丸をクリックすると入力フォームに反映されます</div>

    <div id="employeeListContainer"></div>
     </div>
    <script>
      // ページ読み込み時に社員リストを表示
      document.addEventListener('DOMContentLoaded', function() {
        google.script.run
          .withSuccessHandler(displayEmployeeList)
          .getEmployeeList();
      });
      // 取得した社員リストをラジオボタンとして表示する関数
      function displayEmployeeList(list) {
          const container = document.getElementById('employeeListContainer');
          container.innerHTML = ''; // 既存の内容をクリア
          list.forEach((item, index) => {
              const label = document.createElement('label');
              const radioButton = document.createElement('input');
              radioButton.setAttribute('type', 'radio');
              radioButton.setAttribute('name', 'employee');
              radioButton.setAttribute('value', index); // ラジオボタンのvalueにはindexを設定
              radioButton.setAttribute('id', 'employee' + index);
              radioButton.setAttribute('onclick', 'onEmployeeSelect(this)'); // ラジオボタンをクリックした時の関数を設定
              
              label.appendChild(radioButton);
              label.appendChild(document.createTextNode(`社員番号: ${item.employeeNumber}, 氏名: ${item.name}, 所属: ${item.department}`));
              container.appendChild(label);
              container.appendChild(document.createElement('br')); // 改行を追加
          });
      }


      // ラジオボタン選択時にフォームに情報を反映させる関数
      function onEmployeeSelect(radioButton) {
          const index = radioButton.value; // 選択されたラジオボタンのvalueからindexを取得
          google.script.run
              .withSuccessHandler(function(list) {
                  const selectedEmployee = list[index];
                  // 各フォームフィールドに適切な値を設定
                  document.getElementById('employeeNumber').value = selectedEmployee.employeeNumber; // 社員番号をフォームに設定
                  document.getElementById('name').value = selectedEmployee.name; // 氏名をフォームに設定
                  document.getElementById('department').value = selectedEmployee.department; // 部署をフォームに設定
              })
              .getEmployeeList(); 
      }



      document.getElementById('applicationType').dispatchEvent(new Event('change')); // 初期状態の設定
    </script>
  <div id="employeeListContainer"></div>
  </div>
  </body>
</html>

5-4-3.css.htmlファイル

画面のデザインを整えるための処理などを書いております。

cssコード

<style>
* {
    box-sizing: border-box;
    margin: 0;
    padding: 0;
}

body, html {
    height: 100%; /* bodyとhtmlの高さを100%に設定 */
}

body {
    font-family: Arial, sans-serif;
    display: flex; /* bodyをflexコンテナに */
    flex-direction: column; /* 子要素を縦方向に並べる */
}

.container {
    display: flex;
    flex: 1; /* containerをflexアイテムにし、可能な限りのスペースを占めるように設定 */
}

.sidebar {
    width: 200px; /* サイドバーの幅 */
    background-color: #6495ED; /* サイドバーの背景色 */
    min-height: 100vh; /* ビューポートの高さいっぱいに、またはそれ以上に */
    padding: 20px;
}

.sidebar ul {
    list-style: none;
}

.sidebar ul li {
    padding: 10px 0;
}

.sidebar ul li a {
    text-decoration: none;
    color: #333;
}

.main-content {
    flex-grow: 1; /* 残りのスペースいっぱいに広がる */
    padding: 20px;
}

.main-content h2 {
    margin-bottom: 20px;
}

.form-group {
    margin-bottom: 50px; /* 各フォームグループの下の余白 */
    flex-flow: column; 
    align-items: center; /* ラベルとフォーム要素を中央揃え */
}


.form-group input,
.form-group select,
.form-group textarea {
    width: 20%; /* フォーム要素を親要素の幅いっぱいに */
    padding: 8px 12px; /* フォーム要素の内側の余白 */
    border: 1px solid #ccc; /* ボーダーのスタイル */
    border-radius: 4px; /* 角の丸み */
}

/* フォーム送信ボタンのスタイリング */
.form-group button {
    padding: 10px 15px; /* ボタンの内側の余白 */
    background-color: #007bff; /* ボタンの背景色 */
    color: white; /* ボタンの文字色 */
    border: none; /* ボーダーを非表示 */
    border-radius: 4px; /* 角の丸み */
    cursor: pointer; /* カーソルをポインターに */
}

.form-group button:hover {
    background-color: #0056b3; /* ホバー時の背景色 */
}

/* リンクにホバー効果を追加 */
.sidebar ul li a:hover {
    text-decoration: underline;
}

</style>

5-5.Google Spreadsheet

データベースとしてGoogle Spreadsheetを用います。

6.開発での問題点

ChatGPTにコード作成を依頼しても、意のままに作れないというのが開発の課題でした。対話をしながらコードを修正していくステップがとても重要であるということになります。

6-1.事例：コードが生成できてない

コードの生成依頼の仕方が悪いのもありますが、必須項目チェック機能が当初の生成されたプログラムにはありませんでした。そのため、必須入力項目に値なしでも登録されました。

6-2.事例：コードが勝手に省略されている

プログラムのコメントを読んで頂けるとわかりますが、社員番号のみ反映させるプログラムのようです。私はそのような指示をしていなく、省略されたプログラムになっていました。

したがって、根気よく会話して１回目のプロンプトで生成されたコードに付け足し、最終的な成果とする必要があります。

7.最終デプロイ

デプロイは以下になります。デプロイの公開範囲（アクセスできるユーザ）は自分だけにすることもできます。

WEBは以下となりました。リンクは2週間程度公開予定です。

MTSシステム

8.部署からのフィードバック

「データを入れた際の速度はどれくらい？」「運用コストはどれくらい？」といった質問が来ました。400件くらいは入れて遅くはないという確認をしておりましたので、速度については安心材料があると回答をしております。運用コストはどれくらいになるのか、今後調査が必要かもしれないです。

9.次への一歩

今回は、GASでシステム権限申請のWEBを作成しました。
ChatGPTを使ったため、開発の時間は短縮できました。そのため、工数に余剰時間が生まれ、GASのデプロイを複数回実施することや部内のフィードバックを貰うことまで行うことができました。

システム権限申請をWEB化するかはまだ確定しておりませんが、また一つWEB開発する経験が増えました。申請された際、管理者へのメール通知機能もあれば実用に耐えられるかもしれません。また、登録のダブりやバリデーションチェックも実用を考えるうえで必要かもしれません。今後も色々開発していきたいと思います。

最後まで読んでいただき、ありがとうございました！

2024年7月24日　作成
2024年7月29日　修正しています