GASからGoogle Sheets APIを叩く時のチートシート#1 batchGet編

# 概要

スプレッドシートのセルを読み書きする手段として、
`setValues`や`getValues`を呼ぶより断然早いと噂の`Sheets API`を使ってみました🙌

実際に色々触ってみたところ、
マジでめちゃくちゃ高速に読み書き出来るようになったので使い方をメモっておきます

## GASからSheetsAPIを利用するための準備

GASのIDEを開いて、サービスから`Google Sheets API`を追加します.

![](https://i.gyazo.com/814616fa5a535e365c8f32284cc93fc0.png)

これにより、GASのソースコード上から`Sheets`というIDを通してAPIを実行することが出来ます。お手軽ですね

## シートから値を取得する

[Spreadsheets.Values.batchGet](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet#request-body)を使います
([get](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get?authuser=0)でも良いんですが、batchGetなら複数の範囲を一括で取得出来るので基本的にこちらを推奨します🙂)

- 第１引数：取得先のスプレッドシートIDを指定します
- 第２引数：取得したい範囲を文字列の配列で指定します

```ts:main.gs
  try {
    // SheetsAPIを呼び出し
    const resp = Sheets.Spreadsheets.Values.batchGet(
      SpreadsheetApp.getActive().getId(), // スプレッドシートID
      { ranges: ['シート1!A:B', 'シート2!C1'] } // 取得したい範囲
    );
    // 値の取り出し
    const [head, ...values] = resp.valueRanges[0].values || [[]];
  } catch(e) {
    // 範囲指定に不備がある場合、例外が発生します
    console.log(e);
    return;
  }
```

### 値の取り出し方法

以下の部分でセルの値を取得しています.
`valueRanges`の配列に、指定したセル範囲の読み出し結果が二次元配列で格納されてます.
（2番目の範囲を取り出したい場合はvalueRanges[1]を参照）

```ts:main.gs
  // 値の取り出し
  const [head, ...values] = resp.valueRanges[0].values || [[]];
```

例えばシート1のA列,B列にこんな値が入っていた場合...

|number|name|
|-|-|
|101|Alice|
|102|Bob|

取り出した値はこんなデータ構造になります

```ts
// head
['number', 'name']

// values
[
  ['101', 'Alice'],
  ['102', 'Bob'],
]
```

❗️ここで注意が必要な部分が、空白セルが存在した時の挙動です.
ザックリ言うと、`batchGet`で読み出した時は**データが存在するセルまでしか**格納されません.

例えばこのようなデータに対してbatchGetを使うと

|number|name|score|
|-|-|-|
|101|Alice|100|
|102|Bob|75|
|103|Carol||

以下のように、3つ目の配列長が**2に切り詰められます**

```ts
// head
['number', 'name', 'score']

// values
[
  ['101', 'Alice', '100'],
  ['102', 'Bob', '75'],
  ['103', 'Carol'], // 配列長が2に切り詰められる
]
```

この挙動を知らないと、取得した2次元配列を操作する時に
ちょこちょこ出てくるエラーに悩まされるので注意しましょう😭

> ちなみに、この挙動は行方向に対しても同様です
> ただこれは使い方によっては便利で  
> 何も考えずに`シート1!A:C`とか指定して読み出しても
> データが存在するセルのみ引っ張ってこれます👍

#### (補足)全部のセルが空の場合

`resp.valueRanges`に`values`プロパティがそもそも存在しませんw
こんな感じで存在チェックを入れておくと良いかと思います

```ts
  // valuesがundefinedなら空の二次元配列をセット
  const [head, ...values] = resp.valueRanges[0].values || [[]];
```

## まとめ

`SheetsAPI`の具体的な使い方をまとめた記事が欲しかったので書いてみました📝

書き込み用のAPI`batchUpdate`についても解説を書いているので、
宜しければこちらもご覧ください

https://qiita.com/yosoyalumno/items/4c1f7355fb1b65d3c6b7

## 参考

https://for-dummies.net/gas-noobs/how-to-use-sheets-api-by-gas/

https://officeforest.org/wp/2022/04/16/google-apps-script%E3%81%A7sheets-api%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%A6%E3%81%BF%E3%81%9F/

https://developers.google.com/sheets/api/guides/values?authuser=0

<!--
## シートに値を書き込む

`batchUpdate`を利用

- 第１引数にリクエスト
  - `values`は文字列,数値,真偽値,日付のいずれかを指定
  - 数値型でも`NaN`だとエラーになるので注意
- 第２引数にシートID

参考 : `setValue`のドキュメントに、セット可能なデータ型の記載があります

https://developers.google.com/apps-script/reference/spreadsheet/range?hl=en#setValue(Object)

```ts
  // 書き込む値
  const values = [
    ['foo', 'bar', 'baz'],
    [0, 2, 4],
  ];
  // 書き込み
  Sheets.Spreadsheets?.Values?.batchUpdate(
    // 左上のセルを指定すればOK
    { data: [{ range: 'シート1!A1', values }], valueInputOption: 'USER_ENTERED' },
    ss.getId(),
  );
```

```ts
      // GoogleAppScript.Sheets.Schema.ValueRange
      interface ValueRange {
        majorDimension?: string | undefined;
        range?: string | undefined;
        values?: any[][] | undefined;
      }

      // GoogleAppScript.Sheets.Schema.BatchUpdateValuesRequest
      interface BatchUpdateValuesRequest {
        data?: Sheets.Schema.ValueRange[] | undefined;
        includeValuesInResponse?: boolean | undefined;
        responseDateTimeRenderOption?: string | undefined;
        responseValueRenderOption?: string | undefined;
        valueInputOption?: string | undefined;
      }

```

## シートの値をクリアする

`batchClear`を利用

- 第１引数にリクエスト
- 第２引数にシートID

```ts
  Sheets.Spreadsheets?.Values?.batchClear(
    { ranges: ['シート1!A:D'] },
    ss.getId(),
  );
```

## シートに行を追記する

`append`を利用

```ts
  // 書き込む値
  const values = [
    ['foo', 'bar', 'baz'],
    [0, 2, 4],
  ];
    Sheets.Spreadsheets?.Values?.append(
      { values },
      ss.getId(),
      'シート1!A:Z', // 追記したい範囲を指定
      { valueInputOption: 'USER_ENTERED' }
    );
```
-->