【実践GAS１】実務で使うVLOOKUP関数代替2次元配列スクリプト
概要
作業でスプレッドシート関数VLOOKUP関数の機能は結構使うんだんけど、
スプレッドシート関数をそのままコード化して文字列としてはめ込んでしまうと、以下の問題が出てくるので、代替スクリプトを紹介ーーーー✨
問題点
対象の行数が多い＝データ量が重いと、APIの問題かファイルの再構成の問題か途中で関数が頑張るのを諦める場合がある。
GASが途中で諦めるので、抽出できないデータが出てくる。
ファイル全体のデータ量が多すぎると、シートへのアクセスエラーで実行が途中で中断される。
じゃあ手作業でそこだけやれば良いじゃん
て思う人も多いと思うけど、手作業でやると、
せっかく途中まで自動化できてるのに、そこだけ手作業が入ると完全自動化ができない。
手作業は、必ずミスが起きるし、データ量が多いと手作業でもVLOOKUPで全て検出できるとは限らない。
なんかもある。
書こうと思った理由
ネットとかで
GAS VLOOKUP
みたいな感じで調べたんだけど、ほとんど１次元配列の
getValue();
setValue();
でしかやってなかったり、２次元配列でも小難しく解説が薄く、応用が効きそうになかったりで困ってそうな人がいそうだなあ。
データ量が少なかったら、１次元配列でやればいいんだけど、、、
の前に、そんなデータ量ならばそもそもスプレッドシート関数でやれば良いんでない？
て感じ。
２次元配列でやるってことは、そんだけ
データ量が重い
ってことで、そんなデータ量を１次元配列でやると、必ず６分（か30分）のタイムアウトになるんだよねえ。
と思ったので。
例えば、
みたいな感じで会員番号と名前、郵便番号の列があるとして、
郵便番号列が空白な検索元のシートの郵便番号列に
「検索対象シートの２列目にある郵便番号を書き出したい」
場合で、後述のコードを実行すると、
てな感じで、３列目の郵便番号に対象のデータが入る。
処理時間は、
「今回のデータが10行しかないからじゃない？」
って思うかもしれないけど、
検索元のデータが2500行以上で、検索対象のデータが21000行以上でやっても、10秒くらいしか普段かかってない。
VLOOKUP関数でやっても上記の問題点でコケる時とかによく使ってるんだよねえ。
コード
function 代替VLOOKUP(){
//アクティブなスプレッドシートを指定
let ss = SpreadsheetApp.getActiveSpreadsheet();
//検索元シートを指定
let shMoto = ss.getSheetByName('検索元シート');
//検索するシートを指定
let shTaisho = ss.getSheetByName('検索対象シート');
//配列
let array = [];
//郵便番号の引数
let postNum;
//検索するシートの最終行を取得
let lastRow = shTaisho.getLastRow();
//検索対象列のデータを取得してテーブル化
let table = shTaisho.getRange(2,1,lastRow - 1).getValues();
//取得したデータを一次元配列に一旦変換（対象の行数取得）
table = Array.prototype.concat.apply([],table);
//検索対象シートの有効データを取得
let dataTaisho = shTaisho.getDataRange();
//見出しを削除して検索対象シートの値を取得
let valuesTaisho = dataTaisho.getValues().slice(1);
//検索元シートの有効データを取得
let dataMoto = shMoto.getDataRange();
//見出しを削除して対象元シートの値を取得
let valuesMoto = dataMoto.getValues().slice(1);
//検索元シートのデータ分だけ処理を繰り返す
for(let i = 0; i < valuesMoto.length;i++){
let names = valuesMoto[i][1];
//テーブル化したキーから行数を取得
let rows = table.indexOf(names);
if(rows > -1 && names != ''){
postNum = valuesTaisho[rows][1];
array.push([postNum]);
} else {
postNum = '';
array.push([postNum]);
}
}
//検索元シートへ書き出し
shMoto.getRange(2,3,array.length,array[0].length).setValues(array);
//配列を空欄
array.splice(0);
table.splice(0);
//処理後の休止
Utilities.sleep(2000);
}ひらめきポイント
VLOOKUP関数って要は、
キーになる値を列を基準に、縦向きに見てるだけ
👉検索対象の行番号さえわかれば、欲しいものは抜き出せるんじゃね？
って感じで閃いただけ〜〜〜🕺
使い回しもできるし、それで自動化もできるならそれで良いんじゃない？って感じ。
もしデータ量が多い処理でVLOOKUP関数でやってもダメで自動化で困ってる人がいたら使ってみてね〜〜〜〜
現在
出したかった開発哲学系の記事とか一旦、6/15に全て上げ終わったので、ゆっくり休憩中💃
久しぶりにGASでよく使う実践テクニックでも書いてみようかと思いついて今日は書いただけ🕺
今回のコードで他にわからないことがあったら、自分で調べてね〜〜〜〜。電話番号列とか増やして、間に空白セルを作ったりして、コード応用しても練習になるかもーーー！
GASの基本から学びたい人向け
去年の9月から（かな？）GASど初心者から、実務である程度作れるようになるまでの勉強の記録。
この順番どおりにしか勉強してません（言い切り）！！！
後は、現場でうんうん唸りながら進んできました💦💦
興味がある人は覗いて見てねー！
今回のコードは初発だから
ベタ打ちで書いてるけど、これをさらに変更可能性や可読性を高めたい人は、
で書いてる諸々の記事を参考に書き換えてみてね〜〜〜！
要は、
「コードを変えても動きは変えない。
バックアップを取って、
コードを１文字でも変更したら、実行して、
動きが変わってないかを確認して
次の変更に移る！！！」
だけ。
今回のコードをリファクタリングする例を見たい人とか、この記事の内容が為になったて人など
💃スキをよろしく🕺
この記事が気に入ったらサポートをしてみませんか？