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

Question

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

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

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

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

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

Comments

[papinianus]

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

[gloom]

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

Answer 1

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

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

で、こういう結果をだしたいと思います。

枠線とか引いてないですが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日に複数の取引先を登録しちゃうだけで、質問のような美しい見た目のマスにはうめられない(同日登録された一部の取引先は日付検索ができない)ことになって破綻するのが目に見えているので、それにくらべりゃだいぶマシかと思います。

Answer 2

以前、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;
  }
}

Answer 3

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

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

Comments

[gloom]

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

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

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