の続き。

機能の記事のキモはこのとおり。

JavaScript使ってピボットしたり集計したり、あんまり得意じゃないらしいし、コードいじれない人がスプレッドシートから動的に集計の変数いじれるようにしてあげてほしいよね～。

って。

よろしい、ならばやろう。

スプレッドシートにクエリ関数を同的に運用する仕掛けは可能です。

たとえばこんなシチュエーション。
BOSSからの緊急指令。

かしこまりっ！

じゃあ以下の手順でやってみう。

これでA1セルのNASDAQ上場銘柄の終値を一日ずつとってきてくれます。

株価や銘柄の指定方法は公式のヘルプをみてくださいな。

GASでとってこなくても、基本関数でここまでの動的な疑似スクレイピングは可能です。

んじゃあ、もうちょい長期で2010から取得して、年平均に慣らしてみましょっか。

まずは、D列の横に年と月をarrayformula関数で出します。
死ぬほど便利なarrayformula関数の威力が炸裂。

E2セルにこの式入れたらC列の年をダダダっと出してくれます。

=arrayformula(year((C2:C)))

同様に月も出すよ。

=arrayformula(month((C2:C)))

んだらあとは、まずはクエリ関数のベタ打ちで年集計してみます。

=query(C:F,"select E, avg(D) where C is not null group by (E) ")

月平均だとこんな感じ。

=query(C:F,"select F, avg(D) where C is not null group by F ")

年月平均だとこれ。

=query(C:F,"select E,F, avg(D) where C is not null group by E,F ")

じゃあ、これをGASに書くには？になるよね。

GASで書く時には、

銘柄のコード
開始日
平均グループ化の単位（年、月、年月）

これをスプレッドシートに入力した値から取得して
GASで書き込む関数に読み込ませてあれげればよろしい。

すると、スプレッドシートのセルでプルダウンで開始年を選ぶ、
銘柄を選ぶといった動的な株価チャートが描画されるようになる。

適当なセルで年、月、年月をプルダウン選択して、適当なセルにifs関数で
それぞれに合った列を表示させます。

今回なら、こんな感じ。
A3セルにプルダウンを指定して、

A6セルに関数書いてます。

=iferror(ifs(A3="年","E",A3="月","F",A3="年月","E,F"),"")

A2セルにはデータの入力規則で日付型のみを入力するようにしておきます。

で、最後の関数はこうなる。

=query(C:F,"select "&A6&", avg(D) where C is not null group by "&A6&" ")

クエリ関数でセル参照するときはselect句が始まるダブルクォーテーションで一度閉じて、&でセル参照先を指定して、さらに&のあとにダブルクォーテーションで囲う式に続きを書きます。

このあたり、慣れないうちはA6セルを&で囲うように見えてしまって、式を作る法則性が分からず混乱するから気を付けましょう。

あとは、GASでこれをいい感じにコントロールする。

昨日の記事でお伝えしたように、取得したデータの集計用に、このクエリ関数を文字列としてGASエディタに書けばオッケーです。

この例でいう、A6セルや平均化する列としてD列などを変数としてあれこれいじることもできますし、頭の体操代わりに書いてみてください。

つづく。回答編はまた後日。

GAS書かんのかい！