【Google Spread Sheet】Query関数とIMPORTRANGE関数の使い方

GASをセットするのはめんどくさいので、ほんとに簡単なものなら関数ですませたい。

# やりたいこと

**別のGoogle Spread SheetファイルからSheetを転記して特定のデータを抽出したい!!**

# この記事の一番大事なポイント

▶ **`QUERY`と`IMPORTRANGE`を組み合わせて使う場合は、列がexampleで紹介されてる`A`等の様な指定ではなくて`Col[列番号]`に代わる。**

# IMPORTRANGE

▶ [IMPORTRANGE - ドキュメント](https://support.google.com/docs/answer/3093340?hl=ja)

> 指定したスプレッドシートからセルの範囲を読み込みます。

IMPORTRANGEを使うことで、別のGoogle Spread SheetファイルからSheetを読み込むことができる。

## 使用例

*注意:例で示してる`[]`は置換の印です。`[]`含んで置き換えてください。*

```GoogleSpreadSheet
IMPORTRANGE("[読み込むSheetファイルのURL]", "[シート名]![開始セル]:[終了セル]")
```

例えばこう使う

> `=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "シート1!A1:C10")`

その他、開始セルと終了セルは`シート1!A:X`みたいに列のみの指定もできる。

# QUERY

▶ [QUERY - ドキュメント](https://support.google.com/docs/answer/3093343)

> Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。

つまり、SQLライクな[Google Visualization API のクエリ言語](https://developers.google.com/chart/interactive/docs/querylanguage)とやらを使って欲しい行列を指定したり抽出したりできる。

## 使用例(IMPORTRANGEと組み合わせて)

### `select`で抽出する列を指定する。

とりあえず全部表示するには

```GoogleSpreadSheet
QUERY(IMPORTRANGE("[読み込むSheetファイルのURL]", "[シート名]![開始セル]:[終了セル]"),"select * ")
```

`select Col1, Col2`等とすることで列を指定して抽出できるが、自分は、`*`で全部抽出して非表示にした方が楽で好き。

### `where`で抽出する行の条件を指定する。

例えば、A列が`1`の行のみを抽出する

SQLライクな言語なので等価が`=`一つなのはだいぶ感覚とズレる。そして、**IMPORTRANGEで読み込んだシートに対して列を指定するときは`A`、`B`は`Col1`、`Col2`等`Col[列番号]`に置き換えて指定しなければいけない。**

```GoogleSpreadSheet
QUERY(IMPORTRANGE("[読み込むSheetファイルのURL]", "[シート名]![開始セル]:[終了セル]"),"select * where Col1 = '○' ")
```

### 特定の列の値が文字列の場合等

文字列のデータは完全等価なものは少ないので、`match`か`like %[文字列]%`を使うときが多い

```GoogleSpreadSheet
QUERY(IMPORTRANGE("[読み込むSheetファイルのURL]", "[シート名]![開始セル]:[終了セル]"),"select * where Col1 like '%完了%' ")
```

#### 文字列検索の色々

公式ドキュメントを適当に日本語訳したのを置いとく

> contains - 部分文字列のマッチです。 whole contains partが全体の中のどこかに含まれている場合はtrueになります。例：name が 'John' を含む場合、'John'、'John Adams'、'Long John Silver' にマッチしますが、'john adams' にはマッチしません。

> `starts with` 文字列が値の先頭にある場合に真です。例: `where dept start with engineering` は 'engineering' と 'engineering managers' にマッチします。

> `end with` 文字列が値の最後にある場合は真です。例: `where role ends with `y`は'cowboy'、'boy'、および 'y' にマッチします。

> `matches` - 正規表現を使う。 例: `where country matches '.*ia'` は `India` と `Nigeria` にマッチします。 これはグローバルな検索ではないので、 `where country matches 'an'` は 'Canada' にはマッチしないことに注意してください。

> `like` - 2つのワイルドカードをサポートするテキスト検索です: % (0文字以上の任意の文字にマッチします) と _ (アンダースコア) です。これは SQL の LIKE 演算子に似ています。例: `where name like fre%` は `fre`、`fred`、`freddy`にマッチします。

### `order by`で列を指定してソートする。

注意 -> **`select`と`where`はセットなので、`order by`はwhereの後ろに書く**

```GoogleSpreadSheet
QUERY(IMPORTRANGE("[読み込むSheetファイルのURL]", "[シート名]![開始セル]:[終了セル]"),"select * where Col1 = '○' order by Col2 asc ")
```

# Google Visualization APIの参考文献

- [QUERY - ドキュメント エディタ ヘルプ](https://support.google.com/docs/answer/3093343?hl=ja&ref_topic=9054531)
- [IMPORTRANGE - ドキュメント エディタ ヘルプ](https://support.google.com/docs/answer/3093340)
- [Query Language Reference (Version 0.7)  |  Charts  |  Google Developers](https://developers.google.com/chart/interactive/docs/querylanguage)
- [Start querying data with this simple Query Language](https://www.freecodecamp.org/news/start-querying-data-with-google-query-language/)