Google Spreadsheet のimporthtml, importdataが重い・遅いときの対処法

以前の記事で,Google Spreadsheetで10年分の財務情報をインポートして解析する方法をお伝えしましたが,ふと気がつくと,Google Spreadsheetの処理がめちゃくちゃ遅くなっていることに気付きました。

特に遅いのがimporthtml/importdataなどの外部URLを叩いてデータをインポートするAPIです。

たとえば,importhtml(‘http://financials.morningstar.com/ajax/exportKR2CSV.html?&callback=?&t=’+ticker+’&region=usa&culture=UTF-8&cur=&order=asc&r=283305’)みないな計算をさせていると,「Loading data…」とかになって固まっていることもしばしば。

現在,私はテンプルトン卿にならって約1500銘柄をウォッチ対象としており,約50の指標でスクリーニングしています。この計算が滞るというのは命取りです。

GoogleヘルプフォーラムのImporthtml and importdata commands not working を見てもクッキーを削除せよとかブラウザーを再起動せよと言った役に立たない(失礼!)アドバイスしか掲載されていません。かなり昔の方までフォーラムを読みあさったものの収穫なし。

 

頭に来ました。

完全に。

 

ということで,対策を紹介します。

 

作業方針

1.Google Spreadsheetは表”計算”以外させない

動作が非常に遅いSpreadsheetですが,単なる計算だけであれば200行×20列ぐらいの計算をさせてもほとんど時間が気になりません。

そのため,「urlからのデータの取得とセルへの代入」という重いAPI処理(importhtml/importdata/importxml)をSpreadsheetから省いてしまえば,Spreadsheetは軽快に使うことが出来ます。

2.Google Apps Scriptでデータをインポートする

では,「urlからのデータの取得とセルへの代入」をどうやるかですが,Apps Scriptでやります。

処理の手順としては,

  1. 指定したurlからcvsデータをfetchする
  2. fetchしたcvsデータを二次元配列[][]に格納する
  3. 格納したデータを「1行ずつ」シートに代入する
  4. 以上

特に注意点が3の「1行ずつ」という点です。普通に考えれば,2次元配列なのだからまとめてsetValues(Object[][])で書き込んでしまいたくなるところです。

しかし,qiitaさんのところで紹介されているとおり,書き込みセル数上限(251)の縛りがありどうもうまくいかないようです。素直に一行ずつ代入するのであきらめましょう。

2.のcvsデータを配列Arrayに変換するコードは,Googleの提供するチュートリアルのCVSToArrayを使いましょう。

効果

importdata/importhtmlは異常処理が不十分なのに対し,Apps Scriptではダウンロード先のデータが正常かどうかを事前チェックして,エラーなら異常処理できる点が高速化に大きく効いています。

体感では100倍ぐらい速いのではないでしょうか?(エラーで固まると今までは全く先に進まなかったので。)

私見

アルファベット(GOOGL)の株を買いましょう。

私も,2週間ほど前には完全に頭に来ていましたが,いじくっているうちにGoogleの強みは開発者の層の厚みであることに気付きました。これはマイクロソフト(MSFT)やオラクル(ORCL)にも言えることですが。

困ったことがあったら自力で解決できるMakers(#クリス・アンダーソン)時代において,Googleは社会インフラであり,簡単には他のサービスの乗り換えることが出来ませんでした。たしか,YutaさんもGoogle Apps scriptを使ってJSONでグラフ描画していらっしゃったと思います。

※最初はOffice 365 Onlineに乗り換えようしましたが,チュートリアル等で情報入手が容易なGoogleに残ってしまいました。結果的にGoogleの「乗り換えコストの高さ」というワイドモートが確認できました。

サンプルコード

以下にサンプルコードを掲載します。汎用的に使えそうな部分のみ掲載しますので,あとはアイデア次第で様々な自動化が出来ると思います。

 

 

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です