GoogleSpreadSheet

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

やりたいこと

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

  • シートA
    Screen Shot 2017-09-12 at 9.44.21.png

  • シートB
    Screen Shot 2017-09-12 at 9.45.54.png

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

  • シートA'
    Screen Shot 2017-09-12 at 9.46.43.png

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

結論

下記の記述で解決。

= 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のスプレッドシートから、第二引数で指定したシートの特定の範囲を持ってくる(参考)。

※単独で実行した結果は下記のようになる。
Screen Shot 2017-09-11 at 19.24.53.png
(シートBの内容がそのまま参照されるだけ)

SUBSTITUTE箇所

       SUBSTITUTE(B2, char(10), "' or Col1 = '")

B2セル内の改行を、' or Col1 = 'という文字列に置換(参考)。
(=改行を外し、or文で連結している)

※"char(10)"はセル内改行記号。
※単独で実行した結果は下記のようになる。
Screen Shot 2017-09-11 at 19.41.31.png
↑articlesとimagesの間の改行が消え、指定の文字列が入っている。

select箇所

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

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

※select文で使う列名は"Col1", "Col2"のように書かないと指定できない("A", "B"と記載しているサイトもあったが、エラーになる。。参考
※単独で実行した結果は下記のようになる。
Screen Shot 2017-09-11 at 19.46.24.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)に対して、第二引数引数のクエリを実行(参考)。

※単独で実行した結果は下記のようになる。
Screen Shot 2017-09-11 at 19.59.57.png
↑結果がセルB1, B2, B3, B4に出力されている。

UNIQUE箇所

,UNIQUE(
    QUERY(
        〜〜〜
    )
)

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

※単独で実行した結果は下記のようになる。
Screen Shot 2017-09-11 at 20.03.45.png
↑"BBB"が1つになっている。

JOIN箇所

JOIN(
    char(10)
    ,UNIQUE(
        〜〜〜
    )
)

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

※単独で実行した結果は下記のようになる。
Screen Shot 2017-09-11 at 20.06.28.png
↑結果が全てB1に入っている。

参考にしたサイト