More than 3 years have passed since last update.

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

Last updated at 2021-09-29Posted at 2021-09-29

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

なぜ作ったのか

誰でも短時間で簡単に在庫場所を検索できるようにするためです。

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

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

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

どんなものか

検索したい薬品名を入れると、、、

在庫場所（棚番）とかが一覧表示されます。
これで在庫場所がすぐわかる！

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

という感じです。

作り方

1. スプレッドシートを準備する

まずはGoogleのドライブからでもスプレッドシートを作成して、名前はお好みで「棚表」等と付けてください。
使うシートの名前は
・search
・data
とします。

こんな感じでシートを作っておいてください。

「search」シートにて、こんな感じで検索画面を作ります。

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

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

2. データを準備する

「data」シートにて、検索の元となるデータを用意します。
（薬局さんによってはこういうデータ出力ができないかもしれません＞＜）
今回はこんな感じの列で作成しています。

列名は下記の通り。

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

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

3. GASをコピペする

次に検索処理の部分を作成します。
上の「ツール」から「スクリプトエディタ」を選択。

すると、GAS（Google Apps Script）を書ける画面が出てくるので、

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

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;
  }
}

こんな感じ。

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

4. test関数を実行し、認証する

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

ということで、認証のためにtest関数を実行します。
test関数を選択して

「実行」を押します。

「権限を確認」をクリック。

こんな画面になったら、左下の「詳細」をクリック。

1番下の「無題のプロジェクト（安全ではないページ）に移動」をクリック（プロジェクト名を変えていた場合表示名が異なります）。

「許可」を選択。

これで認証できました！