【1時間で出来る】GAS×LIFF×spreadsheetで作るWEB問診アプリ QUERY関数でデータ抽出

##概要
耳鼻咽喉科の開業医をしています。お子さんを何人も連れて受診される保護者の方は、子供の面倒を見ながら院内で何枚も問診表を書くのは大変そうです。院内滞在時間の短縮にもなりますので、事前に問診表を提出できるようにWEB問診アプリを作成してみました。

##実装内容
・患者さんはLINEを使って問診表を送ることが出来る。
・情報はスプレッドシートに入力されていく。
・発熱者は別待合室での対応になるため、別シートに表示する。
・医院スタッフは問診情報を簡単に検索できる。

##完成したもの
【問診表の入力フォーム】

【情報が登録されるシート1：リアルタイムで情報が追加されていきます】

【発熱者のみ表示されるシート2：シート1情報をQUERY関数で取得します】

【検索用のシート3：B2セルに氏名を入れ、検索ボタンを押すと情報が下に表示】

##作成方法

1 GASでLINE Botを動かせるようにします

[非エンジニアでもLINE Bot開発できる！ SpreadsheetをデータベースにGASで動かす]
(https://qiita.com/njn0te/items/2c08c936661a9febb3b0)

こちらの記事の通りにLINE botをまず作成します。

LINEuserIdを取得する関数と、後ほど作成するフォームとリンクさせるため確認テンプレートを追加しましたので、以下のようにGASのコードを変えています。

※チャネルアクセストークンのコピペを忘れずに

// LINE developersのメッセージ送受信設定に記載のアクセストークン
const LINE_TOKEN = 'チャネルアクセストークン（ロングターム）'; // Messaging API設定の一番下で発行できるLINE Botのアクセストークン（Channel Secretはいらないみたいです。）
const LINE_URL = 'https://api.line.me/v2/bot/message/reply';

//postリクエストを受取ったときに発火する関数
function doPost(e) {

  var event = JSON.parse(e.postData.contents).events[0];
  // WebHookで受信した応答用Token
  var replyToken = event.replyToken;
  // ユーザーのメッセージを取得
  var userMessage = event.message.text;

   // 応答メッセージの内容
  var messages = [
    {
      type: "text",
      text: "",
    },
  ];
  if (userMessage === "問診") {
    messages = confirmation();
   }else if (userMessage === "いいえ") {
    messages[0].text = "終了しました";
   }else{
  
  //メッセージを改行ごとに分割
  const all_msg = userMessage.split("\n");
  const msg_num = all_msg.length;

  // ***************************
  // スプレットシートからデータを抽出
  // ***************************
  // 1. 今開いている（紐付いている）スプレッドシートを定義
  const sheet     = SpreadsheetApp.getActiveSpreadsheet();
  // 2. ここでは、デフォルトの「シート1」の名前が書かれているシートを呼び出し
  const listSheet = sheet.getSheetByName("シート1");
  // 3. 最終列の列番号を取得
  const numColumn = listSheet.getLastColumn();
  // 4. 最終行の行番号を取得
  const numRow    = listSheet.getLastRow()-1;
  // 5. 範囲を指定（上、左、右、下）
  const topRange  = listSheet.getRange(1, 1, 1, numColumn);      // 一番上のオレンジ色の部分の範囲を指定
  const dataRange = listSheet.getRange(2, 1, numRow, numColumn); // データの部分の範囲を指定
  // 6. 値を取得
  const topData   = topRange.getValues();  // 一番上のオレンジ色の部分の範囲の値を取得
  const data      = dataRange.getValues(); // データの部分の範囲の値を取得
  const dataNum   = data.length +2;        // 新しくデータを入れたいセルの列の番号を取得

  // ***************************
  // スプレッドシートにデータを入力
  // ***************************
  //シート１のF列にuserIdを登録
  recordLineUserId(event.source.userId);
  // 最終列の番号まで、順番にスプレッドシートの左からデータを新しく入力  
  for (let i = 0; i < msg_num; i++) {
    SpreadsheetApp.getActiveSheet().getRange(dataNum, i+1).setValue(all_msg[i]);
  }
  }   

  //lineで返答する
  UrlFetchApp.fetch(LINE_URL, {
    'headers': {
      'Content-Type': 'application/json; charset=UTF-8',
      'Authorization': `Bearer ${LINE_TOKEN}`,
    },
    'method': 'post',
    'payload': JSON.stringify({
      'replyToken': replyToken,
      'messages': messages,
    }),
  });
  ContentService.createTextOutput(JSON.stringify({'content': 'post ok'})).setMimeType(ContentService.MimeType.JSON);
}

//シート１のF列にuserIdを登録する関数
function recordLineUserId(userId) {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // F列の空いているセルの行番号を取得する。（F1,F2が既に埋まっていたらnext=3となる）
  var next = activeSheet.getRange("F:F").getValues().filter(String).length + 1;
  Logger.log(next);
  // F列の空いてるセルにユーザーIDを登録する
  activeSheet.getRange(next, 6).setValue(userId);
};

//確認テンプレート
function confirmation(){
  return[
    {
          "type": "template",
          "altText": "WEB問診",
          "template": {
            "type": "confirm",
            "text": "WEB問診を行いますか？",
            "actions": [
                {
                  "type": "uri", 
                  "label": "はい",
                  "uri": "（後でここにLIFF URLをコピぺします）"
                },              
                {
                  "type": "message", 
                  "label": "いいえ",
                  "text": "いいえ"
                }
            ]
          }
}
]
};

2 LIFFで問診表の入力フォームを作成します

【全て無料】10分でLINE BotにLIFFフォームを追加してスプレッドシートにデータを保存

こちらの記事の通りに入力フォームを作成します。

GitHubにアップロードする「index.html」には以下のコードをコピペします。

※LIFF ID のコピペも忘れずに

<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>WEB問診票</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" rossorigin="anonymous">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/themes/base/jquery-ui.min.css">
</head>
<body>

    <form class="w-75 mx-auto">
        <p class="mt-3">氏名(全角ひらがな)</p>
        <div>
            <input class="form-control w-100 mt-1" name="name" placeholder="" required>
        </div>
        <p class="mt-3">診察券番号(半角数字 不明の場合は"0")</p>
        <div>
            <input class="form-control w-100 mt-1" name="bangou" required>
        </div>
        <p class="mt-3">一番お困りのことは?</p>
        <div>
            <input class="form-control w-100 mt-1" name="shushoujou" required>
        </div>
        <p class="mt-3">いつからどのような様子ですか?</p>
        <div>
            <input class="form-control w-100 mt-1" name="keika" required>
        </div>
        <p class="mt-3">37.5度以上の発熱の有無("はい" か "いいえ")</p>
        <div>
            <input class="form-control w-100 mt-1" name="hatunetu" required>
        </div>
        <input type="submit" class="mt-4 btn btn-primary" value="送信">
    </form>

    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" crossorigin="anonymous"></script>
    <script charset="utf-8" src="https://static.line-scdn.net/liff/edge/2.1/sdk.js"></script>
    <script>

        $(document).ready(function () {
            const liffId = "**********-********";
            initializeLiff(liffId);
        })

        function initializeLiff(liffId) {
            liff.init({
                liffId: liffId
            }).then(() => {
                initializeApp();
            }).catch((err) => {
                console.log('LIFF Initialization failed ', err);
            });
        }

        function sendText(text) {
            liff.sendMessages([{
                'type': 'text',
                'text': text
            }]).then(function () {
                liff.closeWindow();
            }).catch(function (error) {
                window.alert('Failed to send message ' + error);
            });
        }

        const params = (new URL(document.location)).searchParams;
        const key = params.get('key');

        $(function () {
            $('form').submit(function () {
                const name    = $('input[name="name"]').val();
                const bangou = $('input[name="bangou"]').val();
                const shushoujou  = $('input[name="shushoujou"]').val();
                const keika  = $('input[name="keika"]').val();
                const hatunetu = $('input[name="hatunetu"]').val();
                const msg = `${name}\n${bangou}\n${shushoujou}\n${keika}\n${hatunetu}`;
                sendText(msg);
                return false;
            });
        });

    </script>

</body>
</html>

3 LINE Botから問診表の入力フォームを呼び出させます

LINE Developersから「LIFF URL」をコピーし、GASの確認テンプレートの部分に張り付けます。

この時点で問診表のデータがシート1に表示されるようになっています。

※シート1の1列目にデータタイトルを、2列目サンプルデータを忘れず入れましょう。
※LIFFフォームを公開するのを忘れずに。

4 QUERY関数で発熱者のみシート2に表示させます

QUERY関数は、クエリ言語を使用してデータを抽出する関数です。

QUERY(データ, クエリ , [見出し])

「データ」は必須項目でクエリを実行するセルの範囲を指定します。
「クエリ」も必須項目でデータを抽出するクエリを指定します。二重引用符で囲みます。
「見出し」は必須項目ではなく見出しの行数を数値で指定します。省略した場合は、全ての行を抽出対象のデータとして扱われます。

今回はシート2のA1セルに以下を入力するだけです。とても簡単ですね。

=QUERY('シート1'!A:F,"SELECT A,B,C,D,E WHERE E LIKE 'はい'")
解説
・「シート1'!A:F」で、シート1のA列～F列までをクエリの範囲とします。
・「SELECT A,B,C,D,E」で、A列～E列を抽出します。
・「WHERE E LIKE 'はい'」で、E列が「はい」のデータを抽出します。

シート1はこちらです。

シート2に選択したデータ（発熱者）が抽出されました。

5 検索ボタンを押すと該当患者のデータが表示されるようにします

[【GAS】ボタンをクリックしたときにスクリプトを実行するには？]
(https://masagoroku.com/%e3%80%90gas%e3%80%91%e3%83%9c%e3%82%bf%e3%83%b3%e3%82%92%e3%82%af%e3%83%aa%e3%83%83%e3%82%af%e3%81%97%e3%81%9f%e3%81%a8%e3%81%8d%e3%81%ab%e3%82%b9%e3%82%af%e3%83%aa%e3%83%97%e3%83%88%e3%82%92)

まずこちらの記事の通りにシート3に「検索ボタン」を作成し、GASのコードに「buttonClick関数」を追加します。

次に「シート3のB2に入力した患者の情報をシート１から取得する関数」をGASのコードに追加します。ここでもQUERY関数を使用しています。

function getHitData() {

  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName('シート3');
 
  sheet.activate();
 
  let range = sheet.getRange('B2');
  let SearchChar = range.getValue();
 
  sheet.getRange('A8').setValue('=QUERY(シート1!A:E,"SELECT B,C,D,E WHERE A LIKE \'' +SearchChar+'\'")');
 
}

検索ボタンを押した時にこの関数が動くように「buttonClick関数」を変更しておしまいです。

function buttonClick() {
  getHitData();
}

##考察
患者さんの院内滞在時間を減らすために、順番取り予約システムを自作して運用しています。現在1万5000人ほどの患者さんが利用されてます。

過去の記事はこちら。
[1時間で出来る　LINE×GASで順番取り予約システムの作成]
(https://qiita.com/doikatsuyuki/items/842b675415abd463e499)
[LINE×GASで作成した順番取り予約LINE Botを改良]
(https://qiita.com/doikatsuyuki/items/d551b169b129206fc394)
[診察予約システム（LINE×GAS）にプッシュメッセージ機能を追加]
(https://qiita.com/doikatsuyuki/items/12de9078e3b5be83ef5f)
[診察中番号表示WEBアプリの作成]
(https://qiita.com/doikatsuyuki/items/2fa437eabbf778782bc7)

スタッフに使い勝手を見てもらいながらWEB問診システムを改良し、予約システムと連携してみようと思います。