[spreadsheet / gas] スプシのimportdata関数が重すぎる時の対処法

# 1. はじめに
この記事では、
①スプレッドシートの便利な`importdata`関数のご紹介
②使いこなす上でのペイン
③ペインの解決方法
について書いていきます。


# 2. importdata関数のご紹介
Googleスプレッドシートには、`importdata`という関数があります。
Googleが提供する公式ドキュメントはこちら。

https://support.google.com/docs/answer/3093335?hl=ja

`importdata`関数は、非常に便利な関数で、データ分析などをする上で非常に有用です。

## importdata関数の使用例
①まず、表示したいデータを取得できるCSVリンクを用意します。
私は仕事でよくRedashというBIツールを使いますが、Redashであれば、`Show API Key` から出力されたCSV取得リンクを活用すると良いです。

②次に、①で取得したリンクを、スプレッドシートのセルに書きます。
どこでも良いのですが、試しにD1セルに入れてみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41481/defa0ac5-7bad-869f-f758-b2eac8276a15.png)

③最後に、A2セルに、`=IMPORTDATA(D1)`という関数を入れます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41481/3c48e7d1-ce79-acbe-50a4-bfc69d9c3152.png)

たったこれだけで、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セルに記載します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41481/2ad49c6f-5f5b-c10f-841c-4128d97f009b.png)

②次に、`拡張機能`->`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`というボタンが現れます（スクショ右上）。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41481/3b064658-8593-51b3-7351-76aab85d7233.png)
`test_btn`->`更新`を押下し、データをスプレッドシート上に出力することができます。

初めての実行時には、権限の承認が求められますので、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41481/038efe8c-a9f6-e915-940e-ecbda41dd8fd.png)
`権限を確認`ボタンを押下していきましょう。

⑤定期実行したい場合は、トリガー設定をしましょう。
例えば、毎朝6〜7時に更新したい場合、GASの実行トリガーを
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/41481/877652e8-01be-2d6a-8cb8-b2a6f081b7c8.png)
のように設定します。

上記により、毎朝自動更新をすることができ、個別に手動実行したいときは`test_btn`->`更新`を押下すればOKです。
誰かの一助になれば幸いです。