質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.59%

GASを利用したVlookupのような検索と書き出し

解決済

回答 3

投稿

  • 評価
  • クリップ 1
  • VIEW 899

gloom

score 11

GASを利用し、簡易な顧客の管理システムを作成しています。

【今から作成したいもの】
スプレッドシート内にGASを利用して検索・登録用のシートを設置して
問い合わせいただいた内容や顧客情報を検索・登録できるようにしたいです。

①検索機能
登録・検索シートから「名前」「電話番号」などを入力した際に
顧客情報から一致する情報を書き出す。

②登録機能
登録・検索シートに情報を入力することで
顧客情報の一番下へ反映される。
(情報が重複する場合わかるようにできればと考えています。)

イメージ説明
イメージ説明

GASを利用してメール内容の取得などコードを組んでいたのですが、知識不足な点が多くお力添えいただきたいです。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • papinianus

    2019/05/07 07:34

    検索時におそらく名前、日付、フリガナのどれかで検索したいのだろうな、電話番号では検索しないのだろうな、という風に見えるのですが、そのあたり検索仕様が分かりません。例えばヤマダさんとヤマモトさんがいるとき、フリガナにヤマといれて検索したらどうなるのでしょうか。
    また、2件以上ヒットしたらどうなるのでしょうか
    システムっぽくしたいのは分かりますが、フィルタで目的を達成できるのではないですか?

    キャンセル

  • gloom

    2019/05/07 10:11

    説明不足で申し訳ございません。
    検索は名前、フリガナ、電話番号のいずれかでしようかと考えています。
    基本的にはいずれかの完全一致で検索をかける予定です。

    キャンセル

回答 3

checkベストアンサー

+2

質問がうまく伝わらなかったみたいですみません。
少なくともこれまでに与えられた状況からはフィルタ一択です。
フィルタ(エクセルのフィルタのイメージで語ってるのでもしかしたら合ってないかもですが)で、絞り込みが多段階にできる機能が存在するのになぜ中途半端な劣化コピーのコードを書かなければならないのか分かりません。

まあ、自己満足な例を考えてみたんで、(将来の訪問者さんの)参考までに。
こういうデータを用意しました。
雑データ

で、こういう結果をだしたいと思います。
雑検索と結果
枠線とか引いてないですが2行目が検索です。結果は5行目以下に出てきます。
質問のような検索窓を作ると、全部の項目で検索したくなるのが人情。
複数項目がからむとandとかorとかのインターフェイスと制御はなかなかに直感的でなくて分かりにくいので、類似度の高いものから(類似度0を除き)リストアップして表示することにしました。
コードはこんな感じ

function filterSimilarity() {
  const dataSheetName = '顧客情報';
  const editingSheetName = '登録・検索';
  const activeSheet = SpreadsheetApp.getActiveSheet();
  if(activeSheet.getName() !== editingSheetName) return;
  activeSheet.getRange(5,1,Math.max(1, activeSheet.getLastRow() - 5), 7).clear();
  const key = activeSheet.getRange(2,1,1,7).getValues()[0];
  const data = SpreadsheetApp.getActive().getSheetByName(dataSheetName).getDataRange().getValues();
  const similars = data.map(function(e){ return e.concat([calcScore(key,e)]); }).filter(function(e){ return e[e.length - 1] > 0});
  if(similars.length < 1) return;
  similars.sort(function(a,b){ return b[a.length - 1] - a[a.length - 1];});
  activeSheet.getRange(5,1,similars.length,similars[0].length).setValues(similars);
}

function calcScore(key, haystack) {
  const len = Math.min(key.length, haystack.length);
  var sum = 0;
  for(var i = 0; i < len; i++) {
    if(key[i] === "") continue;
    if(key[i] === haystack[i]) { sum++; continue;}
    if(haystack[i].toString().indexOf(key[i].toString()) !== -1) { sum += (key[i].toString().length / haystack[i].toString().length); continue; }
  }
  return sum;
}


calcScoreが雑すぎて実用に耐えないとは思いますが、逆に言えばここの評価関数だけ必要に応じて直せば(例えば日付は無視するとか、フリガナや名前は完全一致にするとか、前方一致にするとか、全角半角をちゃんと考慮するとか)実用できるんじゃないかと思います。このところは要件が明確になっていれば実装は難しくないですが、なんでもかんでも考えつくすっていうのは無理があるところなので、このレベルになってしまいます。
実際のところ、質問の例だと日付の完全一致で探すパターンがあり得るっぽいので、1日に複数の取引先を登録しちゃうだけで、質問のような美しい見た目のマスにはうめられない(同日登録された一部の取引先は日付検索ができない)ことになって破綻するのが目に見えているので、それにくらべりゃだいぶマシかと思います。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+1

以前、Vlookupみたいなのを作る演習を勝手にやってみたときのサンプルがあるので、共有します。
checkdata:検索のためのキーワード
sheetname:検索用リストのあるシート名
targetcolumn:リスト上の検索ワード格納列数
getcolumn:リスト上の取り出したい情報の格納列数
です。

/*最もシンプルな発想での記述*/

function mylookup1(checkdata,sheetname,targetcolumn,getcolumn) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName(sheetname);

  var lastrow = sh.getLastRow();

  for(var i = 1;i<=lastrow;i++){
    var data = sh.getRange(i,targetcolumn).getValue();
    if(data == checkdata){
      var anser = sh.getRange(i,getcolumn).getValue();
      return anser;
      break;
    }else{}
  }
}

//****************************************************************************************************************
/*データを配列でまとめて取得して処理する記述*/

function mylookup2(checkdata,sheetname,targetcolumn,getcolumn) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName(sheetname);

  var datarange = sh.getDataRange().getValues();

  for(var i = 0;i<datarange.length;i++){
    var data = datarange[i][targetcolumn-1];
    if(data == checkdata){
      var anser = datarange[i][getcolumn-1];
      return anser;
      break;
    }else{}
  }
}

//****************************************************************************************************************
/*データを配列でまとめて取得してindexOfを活用して処理する記述*/

function mylookup3(checkdata,sheetname,targetcolumn,getcolumn) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName(sheetname);

  var datarange = sh.getDataRange().getValues();
  var lastrow = sh.getLastRow();
  var dataarry = sh.getRange(1,targetcolumn,lastrow,1).getValues();
  var data =Array.prototype.concat.apply([], dataarry);

  var Rownumber = data.indexOf(checkdata);
  if(Rownumber<=-1){}else{
      var anser = datarange[Rownumber][getcolumn-1];
      return anser;
  }
}

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

GoogleスプレッドシートにはVLOOKUP関数が存在します。
https://support.google.com/docs/answer/3093318

データの登録(追加)はコチラをご参照下さい。
https://www.atmarkit.co.jp/ait/articles/1706/09/news018.html

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/06 02:29

    回答ありがとうございます。

    データの追加について参考にさせていただきます。

    VLOOKUPに関して、例えば名前を入力して検索する場合と電話番号を入力して検索する場合など複数の場合が考えられるので、GASの利用を考えておりました。
    もしVLOOKUPでそのような場合でも有効に活用できるのであればご教授いただきたいです。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.59%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る