GoogleSpreadSheetで読み込み、置換、検索等の関数を駆使して別シートの値を取得する

# やりたいこと
GoogleSpreadSheetで下記のようなシートA,Bがあったときに、シートA'を作りたい。


* シートA
<img width="276" alt="Screen Shot 2017-09-12 at 9.44.21.png" src="https://qiita-image-store.s3.amazonaws.com/0/57617/db8c96df-88b0-3cc4-c50d-94cae1537144.png">

* シートB
<img width="259" alt="Screen Shot 2017-09-12 at 9.45.54.png" src="https://qiita-image-store.s3.amazonaws.com/0/57617/cfde15ae-59d8-c815-5895-bcc0002a6872.png">


:arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down: :arrow_down:

* シートA'  
<img width="354" alt="Screen Shot 2017-09-12 at 9.46.43.png" src="https://qiita-image-store.s3.amazonaws.com/0/57617/61384128-0a9f-6455-d2e4-eee2ef7c5863.png">

※C列（関連テーブル）のセル内はユニークにするという要件。


# 結論
下記の記述で解決。

```sql:
= JOIN(
    char(10)
    ,UNIQUE(
        QUERY(
            IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXX/edit", "シートB!A1:B5")
            ,"select Col2 where Col1 = '" & SUBSTITUTE(B2, char(10), "' or Col1 = '") & "'"
            ,-1
        )
    )
)
```



# 解説
## IMPORTRANGE箇所
>            IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXX/edit", "シートB!A1:B5")

第一引数に指定したURLのスプレッドシートから、第二引数で指定したシートの特定の範囲を持ってくる（[参考](https://support.google.com/docs/answer/3093340?hl=ja)）。

※単独で実行した結果は下記のようになる。
![Screen Shot 2017-09-11 at 19.24.53.png](https://qiita-image-store.s3.amazonaws.com/0/57617/8234861c-3c13-98ba-f44d-6411fc45bb46.png)
（シートBの内容がそのまま参照されるだけ）


## SUBSTITUTE箇所
>            SUBSTITUTE(B2, char(10), "' or Col1 = '")

B2セル内の改行を、`' or Col1 = '`という文字列に置換（[参考](https://support.google.com/docs/answer/3094215)）。
（＝改行を外し、or文で連結している）

※"`char(10)`"はセル内改行記号。
※単独で実行した結果は下記のようになる。
![Screen Shot 2017-09-11 at 19.41.31.png](https://qiita-image-store.s3.amazonaws.com/0/57617/eaf29a68-8c7e-c072-59f5-fba9fb84e025.png)
↑articlesとimagesの間の改行が消え、指定の文字列が入っている。

## select箇所
>            ,"select Col2 where Col1 = '" & SUBSTITUTE(B2, char(10), "' or Col1 = '") & "'"

&で文字列を結合し、select文を生成している。

※select文で使う列名は"Col1", "Col2"のように書かないと指定できない（"A", "B"と記載しているサイトもあったが、エラーになる。。[参考](https://productforums.google.com/forum/#!topic/docs/lkesz_heTOg)）
※単独で実行した結果は下記のようになる。
![Screen Shot 2017-09-11 at 19.46.24.png](https://qiita-image-store.s3.amazonaws.com/0/57617/816ae587-c1a2-6316-140f-e2c9f41e99c2.png)
↑「Col1が"articles" or "images"である行のCol2を取得」というselect文が生成されている。


## QUERY箇所
>        QUERY(
            IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXX/edit", "シートB!A1:B5")
            ,"select Col2 where Col1 = '" & SUBSTITUTE(B2, char(10), "' or Col1 = '") & "'"
            ,-1
        )

IMPORTRANGEで取得したデータ（＝シートB）に対して、第二引数引数のクエリを実行（[参考](https://support.google.com/docs/answer/3093343?hl=ja&ref_topic=3105411)）。

※単独で実行した結果は下記のようになる。
![Screen Shot 2017-09-11 at 19.59.57.png](https://qiita-image-store.s3.amazonaws.com/0/57617/64bf9c35-c734-9891-abc7-927235dabaae.png)
↑結果がセルB1, B2, B3, B4に出力されている。

## UNIQUE箇所
>     ,UNIQUE(
        QUERY(
            〜〜〜
        )
    )

QUERYの結果から重複を削除している。

※単独で実行した結果は下記のようになる。
![Screen Shot 2017-09-11 at 20.03.45.png](https://qiita-image-store.s3.amazonaws.com/0/57617/22ba3ca6-c1d8-a71d-5b41-d5b6c1f60126.png)
↑"BBB"が1つになっている。

## JOIN箇所
>     JOIN(
        char(10)
        ,UNIQUE(
            〜〜〜
        )
    )

UNIQUEの結果を改行"`char(10)`"で連結し、1つのセルにまとめている。

※単独で実行した結果は下記のようになる。
![Screen Shot 2017-09-11 at 20.06.28.png](https://qiita-image-store.s3.amazonaws.com/0/57617/ebac6d91-5945-6aba-b74a-43db225f0952.png)
↑結果が全てB1に入っている。


# 参考にしたサイト
* http://chaika.hatenablog.com/entry/2015/11/26/143853
* https://blog.codecamp.jp/google_spreadsheet_function