【GAS】スクリプトでVlookupを実現する方法を教えてください

Question

前提・実現したいこと

以下の表をvlookup関数を使わずに、スクリプトで実現したいのですがわかりません。
何卒ご教示の程お願いいたします。

※シート１のB列には、以下のような関数が入っています。
B2には、=vlookup(A2,'シート2'!A:B,2,0)
B3には、=vlookup(A3,'シート2'!A:B,2,0)
B4には、=vlookup(A4,'シート2'!A:B,2,0)

以上です

Comments

[papinianus]

使い方を教えてください。シート1のB列には今後はどのように入力するのですか？

[oneuskmt]

２行目から３行目の関数同様に番号をキーにシート２を参照して、一致した番号の値を持って来るようにしたいです。

[papinianus]

じゃなくて、`=myvlook…`をどういう感じで書くのかです。正直、vlookupと同じパラメータをかくなら、再実装する意味が分からない(というかvlookupを使ったほうがバグが少なくて安全)と思ったので。

[macaron_xxx]

教えてくださいではなく、どう試して、どうダメで、何が困っているかを書いてください。

[oneuskmt]

このシートの行数はシート１も２も10,000行あります。列は20列。このようなシートにvlookupを使うと、処理時間が５分もかかります。GASを使えば処理速度が上がるかなと思い投稿させてもらいました。いい案があるようでしたらご教示いただきたくお願いします。

Answer 1

他の方の回答もありますが、私だったらこうするを載せておきますね。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 一覧を連想配列化
  var sheet1 = ss.getSheetByName('シート2');
  var kvRow = 1;        // 開始する行数を定義（ヘッダは省く） 
  
  var keyColumn = 1;    // キーの列を定義
  var valueColumn = 2;  // 値の列を定義（ただしキー列＜値列）
  var kvData = sheet1.getRange(kvRow, keyColumn, sheet1.getLastRow(), valueColumn).getValues();
  var kvDic = {};
  
  for each(var kv in kvData) {
    kvDic[kv[keyColumn - 1]] = kv[valueColumn - 1];
  }
  
  // 値をセットする
  var sheet2 = ss.getSheetByName('シート1');
  
  var startRow = 1;     // 開始する行数を定義（ヘッダは省く） 
  var targetColumn = 1; // キーになるコードの列を指定
  var targetData = sheet2.getRange(startRow, targetColumn, sheet2.getLastRow(), 1).getValues();
  
  var setData = [];
  
  for each(var targetRow in targetData) {
    setData.push([kvDic[targetRow[0]]]);
  }
  Logger.log(setData);
  var setColumn = 3; // 挿入したい列数
  sheet2.getRange(startRow, setColumn, sheet2.getLastRow(), 1).setValues(setData);
}

Comments

[oneuskmt]

ありがとうございます。教えていただいた内容を参考に動かしてみます。後ほど報告させていただきます。

[oneuskmt]

だいぶ処理速度が上がって、EXCEL関数と同じくらい早くなりました。ありがとうございます。

Answer 2

function fillIn() {
  var from = "シート2!A:B"; // vlookupする先
  var to = "シート1!A:B"; //埋める元
  var fromSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(from.split("!")[0]).getRange(from.split("!")[1]).getValues();
  var map = fromSheet.reduce(function (previous, current, index, array){ previous[current[0]] = current[1]; return previous;},{});
  var toSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(to.split("!")[0]).getRange(to.split("!")[1]);
  var vals = toSheet.getValues();
  for(var i = 0; i < vals.length; i++) {
    vals[i][1] = map[vals[i][0]];
  }
  toSheet.setValues(vals);
}

Comments

[papinianus]

A:Bのように2列であることを前提にしています。そこを可変にするの面倒。要件が確定したら追記する

[oneuskmt]

ありがとうございます。教えていただいた内容を参考に動かしてみます。後ほど報告させていただきます。

Answer 3

下記のスクリプトでシート1とシート2のデータが取れるので、あとはループ回しながら値を入れれば行けると思います。
適当に書いてるのでエラー出るかもですが。。
あと、data2はfor文回さずにindexOfとかで検索かけた方が早いかも。

var ss = SpreadsheetApp.getActiveSpreadsheet();
// シート1のデータを取得
var sheet1 = ss.getSheetByName('シート1');
var data1 = sheet1.getRange(1, 1, sheet1.getLastRow(), 2).getValues();

// シート2のデータを取得
var sheet2 = ss.getSheetByName('シート2');
var data2 = sheet2.getRange(1, 1, sheet2.getLastRow(), sheet2.getLastColumn()).getValues();

for(var i=0; i<data1.length; i++) {
  for(var j=0; j<data2.length; j++) {
    if(data1[i][0] == data2[j][0]) {
      data1[i][1] = data2[j][1];
    }
  }
}
sheet1.setValues(vals);

Comments

[oneuskmt]

ありがとうございます。教えていただいた内容を参考に動かしてみます。後ほど報告させていただきます。

Answer 4

こう書いて↓
B2セルに=pseudoVlookup("A2", "シート2!A:B")とやればできます

function pseudoVlookup(key, range) {
  var vals = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(range).getValues();
  var keyVal = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(key).getValue();
  for(var i = 0; i < vals.length ; i++ ) {
    if(keyVal == vals[i][0]) {
      return vals[i][1];
    }
  }
}

Comments

[papinianus]

リロードしたら追記に違うことが書いてました。↑は忘れてください。

[oneuskmt]

ありがとうございます。教えていただいた内容を参考に動かしてみます。後ほど報告させていただきます。