やりたいこと
GoogleSpreadSheetで下記のようなシートA,Bがあったときに、シートA'を作りたい。
- シートA
- シートB
- シートA'
※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のスプレッドシートから、第二引数で指定したシートの特定の範囲を持ってくる（参考）。
※単独で実行した結果は下記のようになる。
（シートBの内容がそのまま参照されるだけ）
SUBSTITUTE箇所
SUBSTITUTE(B2, char(10), "' or Col1 = '")
B2セル内の改行を、' or Col1 = 'という文字列に置換（参考）。
（＝改行を外し、or文で連結している）
※"char(10)"はセル内改行記号。
※単独で実行した結果は下記のようになる。
↑articlesとimagesの間の改行が消え、指定の文字列が入っている。
select箇所
,"select Col2 where Col1 = '" & SUBSTITUTE(B2, char(10), "' or Col1 = '") & "'"
&で文字列を結合し、select文を生成している。
※select文で使う列名は"Col1", "Col2"のように書かないと指定できない（"A", "B"と記載しているサイトもあったが、エラーになる。。参考）
※単独で実行した結果は下記のようになる。
↑「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）に対して、第二引数引数のクエリを実行（参考）。
※単独で実行した結果は下記のようになる。
↑結果がセルB1, B2, B3, B4に出力されている。
UNIQUE箇所
,UNIQUE(
QUERY(
〜〜〜
)
)
QUERYの結果から重複を削除している。
※単独で実行した結果は下記のようになる。
↑"BBB"が1つになっている。
JOIN箇所
JOIN(
char(10)
,UNIQUE(
〜〜〜
)
)
UNIQUEの結果を改行"char(10)"で連結し、1つのセルにまとめている。
※単独で実行した結果は下記のようになる。
↑結果が全てB1に入っている。