検温のフォームと見るシートを作った話 - Query関数を初めて使いました -

# つくったもの
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/39f7ec04-5930-5ca0-d444-f6aa8ae7150f.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/7136367e-dc9d-4e3d-ccdc-aa3a568a9e2d.png)

# はじめに
これは、GASにかまけて、表計算の関数やらQUERY関数やら使ってこなかったプログラミング初心者が、3タブくらい開きながら作ったモノになります。

コンセプトは「簡単に作る」です。

# 動作環境
googleフォーム、googleスプレッドシート。

# つくりかた
## フォームを作る。
名前と日にちと体温の項目を設定する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/833a338e-73b2-89b8-8ab3-6152b9478f45.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/aa66e071-16ce-b2a8-6b44-9ca42d84857b.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/60d733e6-0644-0e5c-8408-4d52cf76a666.png)
検温対象の母数が多ければ、名前ではなくメールアドレスを記入してもらったり、
フォームを開く際にログインする設定にして、googleアカウントで識別してもよい。

また、日にちはフォーム送信時のタイムスタンプから取得してもよい。
今回は、結果の信頼性より、結果が歯抜けになることや、送信忘れ等の対応を嫌ったために、
日にちを記入してもらっています。

## スプレッドシートの作成。
フォームの回答のスプレッドシートを作成。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/6e4150b9-e8a2-5140-73f5-c547fae6ee26.png)
作成したスプレッドシートを開き、新しいタブを作成。
縦軸に日付、横軸に名前を設定する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/f8f5547c-a24e-0eac-3087-92d07010574e.png)

## 関数を設定する。
結果だけ知りたい人は、[最後まで](#さいごに)とばしてください。

### 別タブのセルを参照する。
```
='<タブの名前>!<セルの座標>'
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/8a2200c0-79ec-5446-2ae0-140dd5132aed.png)

### Query関数を使用する。
[関数リファレンス](https://support.google.com/docs/answer/3093343?hl=ja)
のサンプルコードから真似てみる。

とりあえず全部取得してみる。

```
=QUERY('フォームの回答 1'!$A$2:$D$100,"select *")
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/2b49f2e1-d2c5-d166-e3a3-83357884c90f.png)

5つの回答のデータ全てが取得出来たが、複数行と列に渡って出力されている。

### Query関数の出力範囲を限定する。
[googleのQueryのリファレンス](https://developers.google.com/chart/interactive/docs/querylanguage#language-clauses)
から、LIMIT句が使えるっぽいので、これで出力を1行に絞る。

```
=QUERY('フォームの回答 1'!$A$2:$D$100,"select * limit 1")
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/1cdbe189-3b12-605c-abee-1309a352395a.png)

更に、QueryでD列を指定して、体温の列だけ取得する。

```
=QUERY('フォームの回答 1'!$A$2:$D$100,"select D limit 1")
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/1a38a8b4-966a-09a7-9985-2a51cc60ca52.png)

これで、何があっても出力が1セルに収まる。

### WHERE句を使う。
まず、名前が「Alpha」であるものだけを取得する。

```
=QUERY('フォームの回答 1'!$A$2:$D$100,"select D where (B='Alpha') limit 1")
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/c197dc1e-bb3a-d457-d4b5-c8c60b6272d2.png)

これだと使い勝手が悪いので、
各列1行目にある名前と一致するものを取得する。

```
=QUERY('フォームの回答 1'!$A$2:$D$100,"select D where (B='"&B$1&"') limit 1")
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/713d00d8-9ef7-c622-da11-97abb2fc6c2e.png)
「&」は、たいていの言語における「+」。

次に、日付が一致するものを取得する。

日付の一致は、文字列の一致とは異なり、`date'YYYY-MM-DD'`の形式に沿う必要がある。

```
=QUERY('フォームの回答 1'!$A$2:$D$100,"select D where (B='"&B$1&"' and C=date'2020-08-01') limit 1")
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/91fccc6d-da2d-ece2-2b86-3fc85dc7130f.png)
標準の日付の形式である「YYYY/MM/DD」から「YYYY-MM-DD」に変換するために、[text関数](https://support.google.com/docs/answer/3094139)を用いる。

```
=QUERY('フォームの回答 1'!$A$2:$D$100,"select D where (B='"&B$1&"' and C=date'"&text($A2,"YYYY-MM-DD")&"') limit 1")
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/4725635e-e984-adf8-6309-22918d899841.png)

### N/Aを出力しないようにする。
[ifna関数](https://support.google.com/docs/answer/9365944)で全体を囲う。

```
=IFNA(QUERY('フォームの回答 1'!$A$2:$D$100,"select D where (B='"&B$1&"' and C=date'"&text($A2,"YYYY-MM-DD")&"') limit 1"),"")
```

この関数を全部のセルに[オートフィル](https://spread-sheets.com/useful/data/autofill/)で適用すると完成。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/500333/bf841ee7-1a5a-56c2-38cf-4cfdea308bb4.png)

# さいごに
Query関数便利ですね。GASを使うか関数を使うか、上手く判断できるようになりたいですね。