Google Apps Scriptでスプレッドシートの膨大な量のデータを高速で処理する方法
GoogleスプレッドシートはExcelの競合とはいえExcelの機能に全く追いついていません。そこはやはりWebアプリの限界なのでしょうけど、その中でも困るのはGASで膨大な量のデータを処理できないという点です。処理に時間がかかるとタイムエラーが発生するからです。Excelマクロでは時間がかかりながらも最後まで完了してくれます。
なので、Google Apps Scriptではプログラムを工夫しないといけないわけですね。気楽にループ処理は使えないのです。
今回はスプレッドシート上にある膨大な量のデータをタイムエラーを発生させることなく高速に処理する方法について紹介します。
まずはどんなデータを使うか
郵便局の郵便番号データをダウンロードしたものを使いました。膨大なデータでテストするといえばこれです。
全国の郵便番号データなので全部で12万行以上のデータがあります。これだけのデータを扱えるプログラムを作ることが今回の目的です。
単純にループ処理してみよう（ダメな例）
何も考えずに１行目から舐めていって指定の郵便番号にヒットしたら住所を表示するというプログラムを作ってみました。Excelマクロでこんなロジック組んじゃう人多いと思うんですよね。GASでも同じことやってみようと思います。
郵便番号6050017は京都のとある場所のものですがそれなりの行数のところで無作為に選びました。郵便番号は3列目にあるので３列目の値が6050017とイコールだったら住所を表示しストップさせるというプログラムです。これをまず実行してみましょう。ちなみにconsole.log("-")は途中の経過時間を見たいためにダミー文字（-）を出力させています。
function getAdress(){
var sheet = SpreadsheetApp.getActive().getSheetByName('KEN_ALL');
var zip = "6050017"
console.log("-");
r=2
while(sheet.getRange(r, 1).getValue()!=""){
if(zip==sheet.getRange(r, 3).getValue()){
console.log("-");
ad = sheet.getRange(r, 7).getValue()+sheet.getRange(r, 8).getValue()+sheet.getRange(r, 9).getValue();
console.log(ad);
break;
}
r++;
}
}
結果はこちらです。残念ながらタイムエラー。GASでは最大実行時間が設けられていて、それを超えたらエラーが出ます。
高速で処理する方法（解決策）
次に解決策です。
どうするかというと、全データを一括で配列化して、その配列をループで探しに行くというプログラムにしました。配列化の時間を測るためにconsole.logで表示させています。
function getAdress(){
var sheet = SpreadsheetApp.getActive().getSheetByName('KEN_ALL');
var address = sheet.getRange("A1:I124508").getValues();
console.log("-");
var zip = "6050017"
address.some(function(value) {
if(zip==value[2]){
ad = value[6]+value[7]+value[8];
console.log(ad);
return true;
}
});
}
結果はご覧のとおり。ちゃんと検索ヒットできました。とは言え、やはり配列化に12秒と時間かかりましたね。ループ処理は一瞬で終わっています。
Excelマクロでも同じようなロジックを組めば高速で処理してくれます。ExcelマクロでもGASでもセルへの参照に時間を要するので、いかにセル参照の回数を減らすかがポイントなんですよね。
スポンサーリンク