スプレッドシートで複数のグラフの表示範囲を可変にする方法(Google Apps Script)

今回の記事は、自分への備忘録として書いた物です。自分はスプレッドシート初心者(VBAすらロクに触っていない人)なので色々と間違っている可能性があります。「こんなやり方もある」くらいのノリで見て頂けるとありがたいです！

やりたい事

訳あって、各地域の気象情報(気温/降水量/日照時間)を比較する事があったのですが、一つのグラフにまとめて表示してしまうと見え辛くなってしまうため、地域別にグラフを作成して比較する事になりました。

このようにスプレッドシート上にグラフを並べて見比べる感じです。

各地域のリストを作成し、その情報を元にしてグラフを作ったのは良いのですが、グラフの表示範囲を指定したい時(例えば、2020年4月1日~2020年4月2日の情報だけが見たい時とか)に「グラフエディタ」を使って、全てのグラフの範囲を設定するのが地味に大変です(今回のグラフは3つしかないので手間は少ないですが、グラフの数が増えるとかなり大変です)。なので、全てのグラフの表示範囲をいっぺんに変える良い方法が無いか検討していました。

で、結果どうしたのかと言いますと、日時範囲の入力領域とグラフ更新ボタンを作成して、元のリストから見たくない日時範囲を「非表示状態」にするGAS(Google Apps Script)を書いて対応しました。

この方法が最良とは思えないのですが、一応やりたい事は実現できたので記載します。

用意するもの

まず、元になるデータです。今回のデータは気象庁の「過去の気象データ・ダウンロード」から気象情報のcsvファイルをダウンロードして使っています。今回は2020年1月~5月までの札幌、東京、那覇の1時間毎の気象情報をダウンロードしています。

グラフの作成

ではスプレッドシート上にフラフを作成して行きます。

スプレッドシートの左上の「ファイル」から「インポート」を選択します。

「ファイルをインポート」ウィンドウが開くため、「アップロード」タブを選択し「デバイスファイルを選択」ボタンを選択します。先ほどダウンロードしたcsvファイルを選択すると、スプレッドシートへインポートする事ができます。

csvファイルをインポートすると、このような形になります。シート名は地域に合わせて変更しました。

次はグラフを作成します。左下の「＋」を押すと新しいシートが追加されます。今回はそこにグラフを追加していきます。「挿入」から「グラフ」を選択します。

画面上にグラフ領域が表示されます。また右側のペインには「グラフエディタ」という見出しの領域が表示されます。このままでは何も表示されていないままなので、右側の「データ範囲」項目にある「田んぼの田」みたいなマークのボタンを選択します。

「データ範囲の選択」ウィンドウが表示されます。候補内に「”札幌”!A1:D2929」という物があるため、そちらを選択してグラフ領域を設定してOKボタンを押します(「別の範囲を追加」ボタンで、グラフ表示範囲を手動選択する事も出来ます)。

そうすると、このような形でグラフが表示されました。デフォルトの状態だと見辛いので「折れ線グラフ」を選択します。

このような形になりました。縦軸と横軸の情報が設定されていないので、棒状のグラフになってしまっています。今回は横軸を時間軸に、縦軸を降水量や気温等の気象データにしたいので、①「X軸を追加」領域に「年月日時」を選択し、②「系列」項目に入ってしまっている「年月日時」を③削除します。

それっぽいグラフになりました！この辺の設定は見たい情報によって異なると思いますが、参考までに私の環境での設定を以下に記載します。
・グラフエディタ
　・設定
　　・「行1を見出しとして使用」にチェック
　　・「列Aをラベルとして使用」にチェック
　・カスタマイズ
　　・グラフと軸のタイトル
　　　・タイトルテキスト：札幌
　　・縦軸
　　　・最小値：-15(氷点下15℃くらいまで表示できれば良いだろうの精神)
　　　・最大値：35(最大35℃くらいまで表示できれば良いだろうの精神)
　　　・「範囲外のデータを使用しない」にチェック
　　・右の縦軸
　　　・最小値：0
　　　・最大値：3(日照時間の最大値設定。とりあえずで3にしてみた)
　　　・「範囲外のデータを使用しない」にチェック
あとは同じように「東京」「那覇」でもシートとグラフを作成していくだけです！

最終的にはこのような形になりました！

Apps Scriptの登録

Apps Scriptの登録を行いますが、この辺の情報は、調べると大量に出てくるためそちらを参照するという事で割愛させて頂きます。

登録が完了すると、スプレッドシートの上タブから”Apps Script”が選択できるようになります。

スクリプトの作成

Apps Scriptの設定が完了すると、「コード.gs」にソースコードを入力する事ができるようになります。ここにコードの実装を行えば、スプレッドシートからスクリプトを実行できるようになります。

今回書いたGASコードを以下に記載します(githubに公開しています)。

やっている事は、開始日の値と終了日の値を取得して、地域シートの「年月日時」一覧と文字列比較し、マッチした列の範囲以外を非表示にしているだけです。

入力範囲を作成

今回見たいのは、[xxxx年xx月xx日]～[xxxx年xx月xx日]の範囲のグラフなので、その日付を入力する欄を作成します。

こんな感じで、「開始日」と「終了日」を入力するセルを作りました。今回の入力範囲は日付なので、カレンダーを表示させる事にしました。

カレンダー入力を有効にするためには、カレンダー入力にしたいセルを選択した状態で、上タブの「データ」から「データの入力規則」を選択します。

「データの入力規則」ウィンドウが表示されるので、条件の値を「日時」と「有効な日時」に設定して、保存ボタンを押下すれば良いです。

入力規則を設定したセルをダブルクリックすると、カレンダーが表示されるようになりました。これでカレンダーの日付が選択できるようになったので、入力の手間が省けます。

ボタンを作成

先ほど「スクリプトの作成」で、GASのコードを作成しましたが、そのままではスクリプトが実行されないため、スクリプトを実行するボタンを作成しようと思います。

ボタンは「図形描画」を用いて作成します(おそらく画像とかでも大丈夫です)。

別ウィンドウが出てくるので、適当なボタンっぽい図形を作成します。ボタンっぽい物なら何でも良いです。私は正方形で中央に「グラフ更新」と書いたシンプルな図形を作成しました。

図形を作成し、図形をクリックすると、右側に「・・・」という設定ボタンが出てきます。その中の選択肢から「スクリプトを割り当て」を選択します。

「どのスクリプトを割り当てますか？」と表示されるため、先ほど作成した”makeGraphData”を入力してOKボタンを押下します。

これで追加した図形がボタンとして機能するようになりました。

※後から図形の修正や関数名の変更をする場合は、右クリックで図形を選択してください(左クリックするとスクリプトが実行されてしまいます)。

スクリプトを実行する

先ほど作成したボタンをクリックします。

そうすると「スクリプトを実行しています」というポップアップメッセージが表示されるため、スクリプトが終了するまで待ちます(データの量に比例して実行時間が変わります)。

一連の動きをgifアニメーションにしてみました。グラフの情報を更新するのに少し時間がかかってしまっていますが、すべてのグラフが更新できています。

ちなみにグラフの元にデータの方は、選んだ期間以外の行が折り畳まれています。元のデータを非表示にするとグラフ側も非表示になるため、それを利用してグラフの表示範囲を可変にしている訳です。

小ネタ

今回のグラフは時刻指定する事も出来ます。

[2020/01/01 6:00:00]のようなフォーマットで入力すれば時刻単位でグラフを更新可能です。

ただし、時刻指定する際にはカレンダー入力できません。カレンダーみたいに時計の時刻をクリック入力できるインターフェースがあれば良いのですが、今のところは無さそうです。

さいごに

今回の記事は「やり方がわからないので、こんな方法でやってみた」という物でした。

GASについて現在勉強中なので色々と理解できていない所がありますが、今後もっと良い方法が見つかれば記事を修正するかも知れません。
※もし、より良い方法をご存じの方がいらっしゃれば、コメントで教えて頂けるとありがたいです。

という訳で今回の記事はこれで終わりです！ではでは～