スプレッドシートでテストデータ作成マシーンを作る技術

この記事は MicroAd Advent Calendar 2023 の 12日目の記事です。

https://qiita.com/advent-calendar/2023/microad

今回はテストデータ作成の工数を抑えるのに大活躍してくれるスプレッドシートの関数たちの紹介をしていくぜ！

（順位はいつもよく使うなあくらいの感触で適当につけたので当てにしないで下さい）

[第1位: ARRAYFORMULA](##-第1位:-ARRAYFORMULA)
[第2位: IF](##-第2位:-IF)
[第3位: TRANSPOSE](##-第3位:-TRANSPOSE)
[SUBSTITUTE](##-SUBSTITUTE)
[SPLIT と INDEX](##-SPLIT-と-INDEX)
[TO_TEXT](##-TO_TEXT)

## 第1位: ARRAYFORMULA

そこのあなた、いつまでセルの右下のポチにカーソルを合わせてドラッグし続けているんですか？
テストレコードの数が数十行くらいならまだしも、それが何百行も何千行にもなったらどうするおつもりで？

重くなったスプレッドシートで画面の下までカーソルをドラッグして左クリックしたまま30秒くらいキープしないといけないかもしれませんね。

![Screenshot 2023-12-11 at 1.05.09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/a82ec392-6c24-4fef-825f-2f1a6a597552.png)

ダブルクリックすれば下まで適用してくれるやん。
じゃあ、それが何十カラムもあったら？

そんな"カーソルをセンターに入れてクリック"の手間を省いてくれるのが`ARRAYFORMULA`関数。

https://support.google.com/docs/answer/3093275?hl=en

使い方は、例えば
```
=ARRAYFORMULA(A1:E12345)
```
みたいな感じでやるとA1~E12345の範囲の情報をそのまま持ってきてくれたり
```
=ARRAYFORMULA(A1:C20&"😭")
```
ってやればA1からC20までの内容に泣きっ面をつけてくれたりします。
![Screenshot 2023-12-11 at 1.25.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/9ea13e59-6e9d-5372-adb2-58d9c3797694.png)

もちろん関数内から関数を呼ぶこともできるので、
![Screenshot 2023-12-11 at 1.33.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/19f408b7-1178-b01a-c5e8-80d272526801.png)
これがあれば例えレコードが1万行あれど1セルしか変更しないので手間なしですね！

ただし注意点としては元から1引数に複数セルを受け取る関数（例えばCONCATENATEとかJOINとか）については同様の呼び出しができません。
![Screenshot 2023-12-11 at 1.36.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/d4c0d9f4-9d60-c7da-d7e0-7b527464fa46.png)

## 第2位: IF
紹介するまでもないのは承知ですけど、これがないと何もできないレベルで必須関数。
よくある使い方は、例えば
```
=IF(F18="JAPAN","日本国籍","外国籍")
```
みたいな感じですが、これをARRAYFORMULAと組み合わせると
![Screenshot 2023-12-11 at 2.13.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/e34fd581-2be9-0a14-cdd6-b1e3b55398f9.png)
```
=ARRAYFORMULA(
    IF(
        C2:J2="TIMESTAMP",
        "2023-12-12 00:00:"&TEXT(A5:A9,"00"),
        IF(
            C2:J2="BIGINT",
            "1234567890"&TEXT(A5:A9,"00"),
            A5:A9
        )
    )
)
```
のように多重IFでテストデータ自動作成も可能です。

## 第3位: TRANSPOSE
新しいパターンの表を作るときにはよく使わせてもらっている`TRANSPOSE`関数。
ドキュメントとかからコピーしてきたテーブル定義が別シートにあったとして
![Screenshot 2023-12-11 at 1.58.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/4ecb2566-9a23-d185-77c2-71c5d65ae208.png)
これをTRANSPOSEすると
![Screenshot 2023-12-11 at 0.59.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/05c46ef0-184c-5db4-0f85-8d0042267694.png)
いい塩梅でテストレコード記述しやすくなったりします！

## SUBSTITUTE
これはもうドキュメントの例がいっちゃんわかりやすいので使い方は下記の通り。
```
SUBSTITUTE("search for it","search for","Google")
```
https://support.google.com/docs/answer/3094215?hl=en&sjid=14614636544516594045-AP

テストデータとして用意した文字列の一部だけを消したい場合とかによくお世話になります。
![Screenshot 2023-12-11 at 2.28.49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/6c5b2742-3d47-6e4b-c1bd-f9afaaa64063.png)

ちなみに同じような名前で`REPLACE`関数がありますが、そっちは文字のインデックス番号で入れ替え対象の部分を指定しているのでちょっとやりたいこととは違います。

## SPLIT と INDEX
文字列で準備した配列データの３番目だけを取り出したい！！
そんなときにこの２つを使います。

まずは上で紹介した`SUBSTITUTE`と`SPLIT`を使って配列を分解します。
![Screenshot 2023-12-11 at 2.35.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/81b357aa-6315-b0f4-48ef-69dfd447dcab.png)
```
=SPLIT(
    SUBSTITUTE(
        SUBSTITUTE(B2,"[",""),
        "]",
        ""
    ),
    ","
)
```
`SUBSTITUTE`が重なってるのでちょっと見づらくなっちゃってますが、やってるのは
・SUBSTITUTEで括弧を外す
・","で分割
だけです。難しくないでしょ？

これの3番目の要素だけが取りたい！ってなったときに、今度は`INDEX`関数を使います。
![Screenshot 2023-12-11 at 2.40.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/96efb1a2-349d-b118-7ba1-d8944618e4e4.png)
```
=
INDEX(
    SPLIT(
        SUBSTITUTE(
            SUBSTITUTE(B2,"[",""),
            "]",
            ""
        ),
        ","
    ),
    3
)
```
これもまた階層深くなってわかりづらいですが、やっていることは
```
INDEX(SPLITで分割したやつ, 何番目)
```
の指定だけです。簡単でしょ？

余談ですがこのINDEX関数は値の取得にも使える有能くんで、
```
=INDEX(A:A,COUNTA(A:A))
```
みたいな書き方をするとA列の一番下の値を取得、みたいな働きもしてくれます。
スプレッドシートでヒストリカルデータを扱ってる時とかはこれも活躍してくれるかもしれないですね。

## TO_TEXT

テスト結果の比較をうまいことスプシでやろうとしたら
![Screenshot 2023-12-11 at 1.45.09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/eed2e6d2-9dfd-08b7-168e-ffb406b7a665.png)
いや、勝手に少数部削らんといて…とか、
![Screenshot 2023-12-11 at 1.48.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/0924d940-86a6-62ae-25fd-fadb98fd09b9.png)
なんか対数になってる…とか、スプシが数字を"いい感じに"扱ってくれちゃったからこそ不都合が起きちゃうこと、よくありますよね。

そんなお悩みも`TO_TEXT`関数があればすぐ解決。
```
=TO_TEXT(12345678901234567890.1)
```
ってやれば文字列として扱ってくれるのです。これでもうセルごとに表示形式をいじいじしなくていいですね。

![Screenshot 2023-12-11 at 1.54.12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2346145/ac323652-7d39-1371-8890-0b954718030b.png)

## おまけ: GPT
弊社は生成AIの利用に規制があり業務には利用していませんが、OpenAIのトークンさえ持っていればSpreadsheetに生成AIの力を組み込むことも可能です。

https://workspace.google.com/marketplace/app/gpt_for_sheets_and_docs/677318054654

こちらのアドオンを入れると、例えば
```
=GPT("Create an example query to insert a test record to the followiing table ...")
```
みたいにしてChatGPTからの応答をそのままセルに展開したり
```
=GPT_TABLE("Create 5 sample records", A1:E1)
```
でA1:E1のカラムを埋める5レコード分のサンプルを作ったりできちゃうんです。いい例は上のアドオンをご覧ください！


## まとめ
上で紹介した仲間たちを使えば、テストデータの作成は（初期コストはかかるけど）だいたい自動化することができちゃいます。何度も使うテーブルにデータ入れそうなときには使ってみてはどうでしょう？

ちなみにマイクロアドの一部の開発チームでは
```
=ARRAYFORMULA(
    IF(
        C2:J2="TIMESTAMP",
        "2023-12-12 00:00:"&TEXT(A5:A9,"00"),
        IF(
            C2:J2="BIGINT",
            "1234567890"&TEXT(A5:A9,"00"),
            A5:A9
        )
    )
)
```
これを10種類近くの型ぶん作って500レコード以上のテストデータを作っているらしいですよ？（すっとぼけ

何はともあれ有能なスプシ関数さんたち。
皆さんも仲良しになってテスト工数を抑えて素敵なクリスマスを迎えましょう！
メリークリスマス🎄