1. はじめに
この記事では、
①スプレッドシートの便利なimportdata
関数のご紹介
②使いこなす上でのペイン
③ペインの解決方法
について書いていきます。
2. importdata関数のご紹介
Googleスプレッドシートには、importdata
という関数があります。
Googleが提供する公式ドキュメントはこちら。
importdata
関数は、非常に便利な関数で、データ分析などをする上で非常に有用です。
importdata関数の使用例
①まず、表示したいデータを取得できるCSVリンクを用意します。
私は仕事でよくRedashというBIツールを使いますが、Redashであれば、Show API Key
から出力されたCSV取得リンクを活用すると良いです。
②次に、①で取得したリンクを、スプレッドシートのセルに書きます。
どこでも良いのですが、試しにD1セルに入れてみます。
③最後に、A2セルに、=IMPORTDATA(D1)
という関数を入れます。
たったこれだけで、A2セル以下にデータを出力することができます👏
3. 使いこなす上でのペイン
importdata
関数を呼び出すデータ量や回数が少なければ、特にペインなどなく、非常に快適に活用することができます。
一方で、取り扱うデータ量が増えたり、使う箇所が増えてくると、importdata
関数の限界を迎え、重くて動作が不安定になります。
importdata
関数が重くて使えなくなる理由は、基本的に
- 呼び出し回数が多い
- 呼び出し時のデータ量が多い
のいずれかとなります。
4. ペインの解決方法
4-1. 解決方法A:importdataの呼び出し回数を減らす
呼び出し回数を減らすことで、ペインを解消できることがあります。
不必要にimportdataを使いすぎず、値貼り付けで良い部分はそうすると良いです。
また、経験則から、「同一ドメインでの呼び出し回数が多い」と落ちやすいことがわかりました。
ローデータのimportを1枚のシートに集約をし、そこからimportrange関数でデータを取得してくることが有効です。
また、別ドメインにもローデータを用意しておくことで、ドメインあたりの呼び出し回数を減らす、というテクニックもあるので、よかったらご活用ください。
4-2. 解決方法B:importdataの呼び出しデータ量を減らす
解決方法Bよりは、解決方法Aの方がペイン解消に役立つ頻度は多いように思います。
解決方法Aで解消されない場合、この解決方法Bも試してみてください。
過去データは値貼り付けにするなどして、importdataで呼び出すデータ量を減らすことで、軽くすることができます。
4-3. 解決方法C:importdataによる読み込み失敗時に、データの値貼り付けをする
これは非常に原始的な方法ですが、有用です。
解決方法ABの方法で解決できない場合、importdataで呼び出すことを諦め、データを値貼付けする、ということも有効です。
4-4. 解決方法D:GAS活用により、importdata関数を使わずにデータを自動取得する
①まず、呼び出したいCSVダウンロードリンクを、シートに記載します。
スクショの例だと、D1セルに記載します。
②次に、拡張機能
->Apps Script
をクリックし、Google Apps Scriptのエディタを開きます。
③エディタに、下記のソースコードを書きます。
var ROW_HEADER = 2; //データをシートの何行目から出力させたいかを書きましょう
var CELL_CSV_LINK = 'D1'; //CSVダウンロードリンクを記載するセル番号を書きましょう
var TAB_NAME = 'GAS活用'; //スプレッドシートのタブ名を書きましょう
function updateTestData(){
clearContent(); //今入っているデータを削除し、新しいデータを入れる準備(関数定義は下に記載)
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName(TAB_NAME); //csvファイルを読み込むシート名をセット
var charset = "utf-8"; // 読み込むcsvファイルの文字コードをセット
var url = sh.getRange(CELL_CSV_LINK).getValue(); //読み込むCSV URLをセット
var response = UrlFetchApp.fetch(url);
var csvData = response.getContentText(charset); //データ取得
var ary = Utilities.parseCsv(csvData); //2次元配列に転換
sh.getRange(ROW_HEADER,1,ary.length,ary[0].length).setValues(ary); //シートに反映
}
function clearContent(){ //現在表示されているデータをクリア
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName(TAB_NAME);
var range = sh.getRange('A' + ROW_HEADER + ':E'); //データがA列〜E列に入っている場合の記述となっている。適宜読み替えてください
range.clearContent();
}
function onOpen(){ //GAS実行ボタンをスプシ上に作成
SpreadsheetApp.getUi()
.createMenu('test_btn')
.addItem('セルの値を削除', 'clearContent')
.addItem('更新', 'updateTestData')
.addToUi();
return;
}
④onOpen関数を実行
これにより、test_btn
というボタンが現れます(スクショ右上)。
test_btn
->更新
を押下し、データをスプレッドシート上に出力することができます。
初めての実行時には、権限の承認が求められますので、
権限を確認
ボタンを押下していきましょう。
⑤定期実行したい場合は、トリガー設定をしましょう。
例えば、毎朝6〜7時に更新したい場合、GASの実行トリガーを
のように設定します。
上記により、毎朝自動更新をすることができ、個別に手動実行したいときはtest_btn
->更新
を押下すればOKです。
誰かの一助になれば幸いです。