【GAS】スプレッドシートで商品名・一般名から医薬品在庫の場所を検索できるようにする

普段の薬局での薬剤師業務で「商品名・一般名から在庫場所を検索できるスプレッドシート」を作成したので、誰でもコピペで作れる方法を共有します。
# なぜ作ったのか
誰でも短時間で簡単に在庫場所を検索できるようにするためです。

薬局としては規模が大きく、扱う品目が多いため在庫場所を覚えるのでも一苦労。

薬剤師ではない調剤スタッフさんが、処方せんを見て「商品名→一般名」「一般名→商品名」を瞬時に変換して調剤するのもかなり慣れが必要になります。

忙しいときは数秒も無駄にできないので、「とりあえずiPadで検索すればすぐに在庫場所がわかる」という環境はとても重要です。

# どんなものか
検索したい薬品名を入れると、、、
![検索.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/546ee71e-0670-9106-969b-1be13cd3088f.png)
在庫場所（棚番）とかが一覧表示されます。
これで在庫場所がすぐわかる！
![検索結果.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/9aa9b572-1015-6701-ab1e-af3dfa4b15a0.png)

 - 一般名（成分名）が分からなくても商品名検索で一般名がわかる！
 - 商品名（先発品、後発品）が分からなくても一般名フリガナ検索で先発品、後発品を一覧表示できる！

という感じです。

# 作り方
## 1. スプレッドシートを準備する
まずはGoogleのドライブからでもスプレッドシートを作成して、名前はお好みで「棚表」等と付けてください。
使うシートの名前は
・search
・data
とします。
![棚表シート.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/b6b18171-26e0-eeaf-74ec-e869b158ba83.png)
こんな感じでシートを作っておいてください。

「search」シートにて、こんな感じで検索画面を作ります。
![棚表検索.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/30f5f908-3c32-2df3-a0db-bd6799a8c3ef.png)

コピペできるようにこちらも記載しておきます。

|  商品名 検索  |  一般名フリガナ 検索  |  備考  |
| ---- | ---- | ---- |
|    |    |  ・両方ひらがなで検索可能<br>・商品名検索では漢字検索でヒットする（漢方名等）<br>・漢字の文字列はかな検索でヒットしない"  |
|  ※ 入力後、他のセルを選択すると検索が実行されます  |


## 2. データを準備する
「data」シートにて、検索の元となるデータを用意します。
（薬局さんによってはこういうデータ出力ができないかもしれません＞＜）
今回はこんな感じの列で作成しています。
![棚表data.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/166af859-7c3c-4b4c-ca3c-842146adbe44.png)

列名は下記の通り。

|  棚番  |  商品名  |  一般名称  | 一般名フリガナ |販売元|更新日|
| ---- | ---- | ---- | ---- | ---- | ---- |

検索で表示させる部分やデータ元の表はアレンジ可能なので、まずこれで動作確認をしていただければと思います。


## 3. GASをコピペする
次に検索処理の部分を作成します。
上の「ツール」から「スクリプトエディタ」を選択。
![gas.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/b852a2ab-b84d-4e82-550d-c26c8a71db05.png)
すると、GAS（Google Apps Script）を書ける画面が出てくるので、
![gas2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/f545695e-2825-c9fb-4cce-6ac596a1582d.png)

中のコードを全て下記に書き換えてください。

```javascript:app.gs
function test(){
  var sheet = SpreadsheetApp.getActiveSheet();
}

// 編集時、商品名検索、一般名フリガナ検索を実行、検索結果表示
function onEdit(e){
  // このスプレッドシートを取得
  var spread = SpreadsheetApp.getActiveSpreadsheet();
  // 「search」シートを取得
  var sheetSearch = spread.getSheetByName('search');
  // 編集した範囲を取得
  var range = e.range;
  // 商品名検索（商品名検索部分を入力した場合）
  if(range.getColumn() === 1){
    // 商品名検索欄に入力されている場合（入力なしで全て表示させる処理は無駄なためこうしている）
    if(!sheetSearch.getRange(2, 1).isBlank()){
      // 入力した検索文字列を取得
      var keyword = sheetSearch.getRange(2, 1).getValue();
      // 検索文字列が「文字列」タイプだった場合
      if (typeof keyword == "string") {
        // 検索ワードをひらがな→カタカナに変換
        keyword = phonetic(keyword);
        // 前後に%を付けて検索文字列を含む場合にヒットさせる
        var queryWord = "'%"+keyword+"%'";
        // QUERY関数で商品名検索結果を表示
        sheetSearch.getRange(4, 1).setValue('=QUERY(data!A:F, "SELECT A,B,C,E WHERE B like '+queryWord+'", 1)');
      }
    }
    // 一般名フリガナ検索（一般名フリガナ検索部分を入力した場合）
  }else if(range.getColumn() === 2){
    // 商品名検索欄に入力されている場合（入力なしで全て表示させる処理は無駄なためこうしている）
    if(!sheetSearch.getRange(2, 2).isBlank()){
      // 入力した検索文字列を取得
      var keyword = sheetSearch.getRange(2, 2).getValue();
      // 検索文字列が「文字列」タイプだった場合
      if (typeof keyword == "string") {
        // 検索ワードをひらがな→カタカナに変換
        keyword = phonetic(keyword);
        // 前後に%を付けて検索文字列を含む場合にヒットさせる
        var queryWord = "'%"+keyword+"%'";
        // QUERY関数で一般名フリガナ名検索結果を表示
        sheetSearch.getRange(4, 1).setValue('=QUERY(data!A:F, "SELECT A,B,C,E WHERE D like '+queryWord+'", 1)');
      }
    }
  }
}
// ひらがな⇒全角カタカナ, 全角英数⇒半角英数
function phonetic(args) {
  if (typeof args == "string") {
    // ひらがな⇒カタカナ    
    var s = [];
    for (var i = 0; i < args.length; i++) {
      c = args[i].charCodeAt();
      s[i] = (0x3041 <= c && c <= 0x3096) ? c + 0x0060 : c;
    }
    var katakana = String.fromCharCode.apply(null, s);
    // 全角英数⇒半角英数
    return katakana.replace(/[Ａ-Ｚａ-ｚ０-９]/g, function(s) {
      return String.fromCharCode(s.charCodeAt(0) - 0xFEE0);
    });
  } else {
    var s = "";
    for (var i = 0; i < args.length; i++) {
      s += args[i];
    }
    return s;
  }
}
```

こんな感じ。
![gas3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/0bf97d24-9714-2c2e-c679-1af9f67e6965.png)

中身の処理はこれで全てです。

## 4. test関数を実行し、認証する
これで完成はしているのですが、検索範囲に文字を打っても検索が実行されないと思います。
というのも、「このスプレッドシートでデータ操作等実行して良いか」の認証ができていないからです。

ということで、認証のためにtest関数を実行します。
test関数を選択して
![gas4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/21ac24ce-9e5d-c6a5-ad6d-3307a8a3e31d.png)
「実行」を押します。
![gas5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/ce4ae759-74cb-327b-2592-9091ff75d855.png)
「権限を確認」をクリック。
![gas6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/0d5e8f2a-0ac3-341c-3ba7-383c8fe41018.png)
こんな画面になったら、左下の「詳細」をクリック。
![gas7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/677507cd-3cac-bd8e-29fd-b5d23b75df26.png)
1番下の「無題のプロジェクト（安全ではないページ）に移動」をクリック（プロジェクト名を変えていた場合表示名が異なります）。
![gas8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/0697432b-dfc0-160f-eef8-adcf670c11c2.png)
「許可」を選択。
![gas9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/82c66b08-aa87-3c67-4b60-1b61d01241b2.png)
これで認証できました！

## 5. 動作確認
検索してみます。
![検索.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/c88bf86e-741d-d3e3-ceb6-9444dd90d080.png)
約２秒で結果が表示されました。
![検索結果.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1604715/71e690bc-8e4f-caac-7a0c-aaedf053ce74.png)

商品名検索でも検索できます。

# 開発のポイント
最後にはなりますが、開発のポイントをまとめておきます。

 - iPadでGASを起動させるには、「編集時」のみであるため検索ボタン等の実装はできない
 - GAS（スプレッドシート）で検索をかけるにはQUERY関数が最速（データが4000行以上でもある程度のPC環境なら約２秒）
 - 余計な処理は実行させず、処理を最速化（商品名検索と一般名フリガナ検索を分けた理由もこれ）
 - ひらがなでの検索も可能にし、検索時の時短を図る
 - 商品名→一般名、一般名→商品名がわからなくても、両方検索することで解決可能（一般名フリガナ検索で先発・後発品リストを得られる）

# アレンジのポイント
このスプレッドシートをもとに、いろいろアレンジを加えたい、というのもあるかと思います。
そんな時はとりあえず下記を把握していじれば対応できると思いますので、確認してみてはいかがでしょうか。

 - getRange()内でセルの取得場所を変えられる
 - QUERY()内でデータ元の場所、抽出する列、検索対象の列を変えられる

# 最後に
このスプレッドシートが少しでも参考になれば幸いです！
改良点等あればお気軽にご教授くださいませ。。