人生がたりない

~おりくらしげるのブログ~

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

time 2021/11/28

スプレッドシートで複数のグラフの表示範囲を可変にする方法(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について現在勉強中なので色々と理解できていない所がありますが、今後もっと良い方法が見つかれば記事を修正するかも知れません。
※もし、より良い方法をご存じの方がいらっしゃれば、コメントで教えて頂けるとありがたいです。

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

コメント

  • 初めまして、コメント失礼いたします。現在仕事上で非常に参考になるサイトを拝見させていただきました。
    グラフ作成部分のGASコードが見当たらないのですが、もしありましたら教えていただけますと嬉しい限りです。

    by NJ €2022年2月15日 10:50 AM

    • NJさん。コメント頂き、ありがとうございます!

      あぁ、すみません。。
      確かにグラフ作成部分の手順が抜けていますね…

      グラフはGASで作成しておらず、手動で作っていました。
      今回の場合は「グラフ」シートに札幌、東京、那覇のグラフを手動で3つ作成して、
      グラフの元となるデータに札幌、東京、那覇シートの気象情報を設定しています。

      本来この手順が「用意するもの」の一番下にあるべきなのですが、それが抜けています。。
      どこかのタイミングでそれを追加しようと思います。

      ご指摘頂き、ありがとうございました!
      他何か疑問点などありましたら、コメント頂ければと思いますm(__)m

      by shigeru-orikura €2022年2月17日 11:03 PM

  • 項目「グラフの作成」を追加しました。

    by shigeru-orikura €2022年2月19日 4:12 PM

  • はじめました。コメント失礼いたします。
    もしよろしければ2点教えて下さい。

    ①for(var i=1;i<dat.length;i++){ ですが、
     ltの意味と、i&lt<dat.length にならない理由

    ②&quotの意味

    by kokuple €2022年11月29日 10:52 AM

    • コメント頂き、ありがとうございます!
      今日明日中に回答致しますので、少々お待ちくださいませm(__)m

      by shigeru-orikura €2022年11月29日 1:19 PM

    • kokupleさん。
      コメント頂き、ありがとうございました。

      ①の件ですが、ごめんなさい。私の確認が漏れていました…
      ご指摘頂いた通り、「<」で認識合っております。

      ■原因
      このブログはWordpressを使って公開しているのですが、ソースコードのようなものの場合、エスケープ文字が自動変換されてしまうらしいです。
      そのため、ブログ公開直前のプレビューでは問題なく「<」が表示されているにもかかわらず、公開状態にすると「<」が「i&lt」に変換されてしまい、今回の事態が発生したようです。
      (それに気付かず1年放置してしまっていました…)

      ■対応
      githubにコードを公開し、そちらへのリンクを貼る方針にさせて頂きました。

      また②も同様の問題で、「"」ダブルコーテーションが「&quot」として表示されてしまう事が原因でした。

      先ほどリンクを修正致しましたので、そちらでご確認頂ければと思います。

      by shigeru-orikura €2022年11月30日 10:33 AM

  • ご丁寧にご回答いただきありがとうございます。
    GAS勉強中なので大変助かります!
    最後にもう1つ可能であればご教示ください。
    return -1はどのような意味でしょうか?
    これまでreturnはスクリプトを中止する箇所に記述していました。

    by kokuple €2022年11月30日 2:25 PM

    • あ、ごめんなさい。。
      混乱を招くようなコードになってしまっています。。。

      こちらは普通に"return"のみで問題ありません。
      私の変な癖でこんな事になっていると思って頂いて結構です。

      ※以下、言い訳
      過去にC言語での開発をしていた事があり、
      C言語では関数の戻り値として、return 0は正常終了、
      retrun -1は異常終了とする風習のようなものがあり
      その時の癖で、異常終了の発生した時は
      上流の関数に戻す値を-1にしておけばとりあえずヨシ
      みたいなノリだったので、こんな書き方をしています。
      "https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1316708918"
      とか色々と書いておいて、code.jsの一番最後の行に正常終了を表す
      return 0を書き忘れている時点で説得力が無いですねw
      後でさりげなくコードを修正するかも知れませんw

      by shigeru-orikura €2022年12月1日 8:40 PM

  • たまたま見つけたのですが、こちらに記載されているソースを丸々転載していると思われるサイトがありました。
    <a href="https://aprico-media.com/posts/10643" rel="nofollow ugc">https://aprico-media.com/posts/10643</a>

    by k €2024年1月16日 6:22 PM

    • ご連絡頂き、ありがとうございます!
      あ、本当ですね…ちょっと当該サイトに問い合わせてみます。

      by shigeru-orikura €2024年1月17日 8:11 AM

down

コメントする




おりくらしげる

個人的に自由研究をやるおじさん。やりたいことが多すぎて全てが中途半端になるけど、それもまた一興。

 

人生短いよね。500年くらいあればよいのに。嘘、そんなにはいらない。

検索

このサイトについて

プラモ・電子工作・その他についてまとめているサイトだよ。