【徒然GAS32】スプレッドシートへの書き込み高速化でやらかしていた勘違い〜備忘録〜

M_Kaku堂

2021年11月3日 17:34

概要

やってみてコツを掴みかけるまで、自分も勘違いしかけていたので、備忘録

※すでにそんなん知っとるわい！な方が多いとは思うけど、、、💦

参考にしたリンク

勘違いしかけたこと

よくあるサンプルだと、

あるスプレッドシートにすでにあるものすごく大きなデータとかを何かの列に書き込む処理

などを引き合いに説明しているものが多いので、

①データの有効な範囲を、getDataRange()で取得する。

↓

②有効な範囲のデータを、getValues()で一気に取得する。

↓

③二次元配列として、取得したデータをセットする。

↓

④書き込む範囲を指定して、setValues()で一気に書き込む。

で説明をしていることが多いから、

スプレッドシート上にすでにあるデータの操作かあ😶

と思っていたんだけど、

GASって、WEBスクレイピングでも使うじゃん、

WEBスクレイピングでは使えないのかあ、、、💦

と思い込んでた。

ひらめき

でもGASでWEBスクレイピングで高速書き込むができないって不便どころの話じゃないやん、、、

待てよ？もしかして、、、

あ、そうか！

スクレイピングで取得したデータを二次元配列にハメ込みさえすれば、

できるはず！

試したコード

単純書き込みのコード（加工済み）

function メール全件をシート2に単純書き込み(){
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sh = ss.getSheetByName('シート2');
 //送信元と検索期間を指定
 const threads = GmailApp.search(
   'after:2001/10/10 before: 2021/11/3');
let mailAry = [];
 //シートへの書き込み用変数
 let row = 1;
 for(const thread of threads){
   const messages = thread.getMessages();
   for(const message of messages){
     const date = message.getDate();
     const subject = message.getSubject();
     const sender = message.getFrom(); 
     const textPlain = message.getPlainBody();　
     const id = message.getId();
         
     //入力
     sh.getRange(row,1).setValue(date);
     sh.getRange(row,2).setValue(subject);
     sh.getRange(row,3).setValue(sender);
     sh.getRange(row,4).setValue(textPlain);
     sh.getRange(row,5).setValue(id);
     row++;
   }
 }
}

配列を使って

function メール全件を配列でシート3に一気にプッシュ(){
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sh = ss.getSheetByName('シート3');
 //送信元と検索期間を指定
 const threads = GmailApp.search(
   'after:2001/10/10 before: 2021/11/3');
let mailAry = [];
 //シートへの書き込み用変数
 let row = 1;
 for(const thread of threads){
   const messages = thread.getMessages();
   for(const message of messages){
     const date = message.getDate();
     const subject = message.getSubject();
     const sender = message.getFrom(); 
     const textPlain = message.getPlainBody();　
     const id = message.getId();
         
     //入力
     mailAry.push([date,subject,sender,textPlain,id]);
     row++;
   }
   sh.getRange(1,1,mailAry.length,mailAry[0].length).setValues(mailAry);
 }
}

実現できました🕺

個人的には、Gmailをほぼ使っていなかったので、100件程度しかメールがなかったから差はあまりなかったんだけど、それが1000件とか行っていると、おそらく圧倒的に速くなる（はず🙇‍♂️）。

まとめ

これで、JSONやXMLから取得した情報も取得さえできれば、高速な書き込みができるだろうな。

あくまでも大量のデータを一気に処理した場合の話だから

数件のデータならば、setValue()で単純書き込みの方が早いかもしれないけど💦

✨setValues()メソッドは、getValues()とセットでなくても使える。

✨取得元の情報がスプレッドシート上じゃなくても使える。

の方が当たり前すぎて書いてないだけかもしれないけど、結構重要かも✨

参考にした本（今、勉強中）

この記事が気に入ったらサポートをしてみませんか？