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

## はじめに

この記事はGoogle Apps Script Advent Calendar2022の14日目の記事です🎅

https://qiita.com/advent-calendar/2022/google-apps-script

## 概要

前回書いた、Google Sheets APIを使って爆速でシートを読み書きする記事の続きです。
今回は主に書き込みに使うAPIを紹介します

:::note warn
GASでSheetsAPIを利用する事前準備については
前回の記事をご参照下さい
:::

https://qiita.com/yosoyalumno/items/f28f6e90a0cf9d9ff1d9

## シートに値を書き込む

[Sheets.Spreadsheets.Values.batchUpdate](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate)を使用します

```typescript
Sheets.Spreadsheets.Values.batchUpdate(
    resource: Sheets_v4.Sheets.V4.Schema.BatchUpdateValuesRequest,
    spreadsheetId: string)
```

### 第1引数 : resource

APIへのリクエスト内容を指定するオブジェクトです

```javascript
{
  valueInputOption: 'USER_ENTERED',
  data: [
    { 
        range: 'main!A1:B2',
        values: [[1, 2], [3, 4]]
    },
  ]
}
```

#### `valueInputOption`

入力値をどのように解釈するかのオプションです
以下２種類のいずれかをstringで指定します

| 名前 | 概要 |
| - | - |
|**RAW** | 入力した値をそのままセットします<br>※数値なら数値、文字列なら文字列として解釈 |
|**USER_ENTERED** | 入力した文字列を解釈可能な場合は<br>数値や日付などに変換してセットします |

:::note info
基本的に**USER_ENTERED**で問題ありません
:::

#### `data`

書き込みたいセル範囲とデータ、書き込みオプションを指定出来ます
以下のプロパティを持つオブジェクトの配列で指定します

| 名前 | データ型 | 概要 |
| - | - | - |
|**range** | string | 書き込み先のセル範囲をA1形式の文字列で指定します |
|**values**| any[][] | 入力値を２次元配列で指定します |
|**majorDimension** | string | `values`の値を行方向(ROWS)に展開するか、<br>列方向(COLUMNS)に展開するかを指定します<br>※プロパティの指定が無い場合、行方向になります |


### 第2引数 : spreadsheetId

書き込み先のスプレッドシートIDをstringで指定します
アクセス権があれば、現在開いているものとは別のスプレッドシートを指定することも可能です


### 書き込み例

例えばこんなパラメータを指定すると

```javascript
Sheets.Spreadsheets.Values.batchUpdate(
  {
    valueInputOption: 'USER_ENTERED',
    data: [
      {
        range: 'シート1!A1:B2',
        values: [[101, 'Alice'], [102, 'Bob']]
      },
    ],
  },
  SpreadsheetApp.getActive().getId(),
);
```

こんな出力結果が得られます

**シート1**
||列A|列B|
|-|-|-|
|行1|101|Alice|
|行2|102|Bob|

続いて、入力値を指定する際のコツについて説明していきます

### 入力値の指定方法

#### サポートしている入力データの型

`bool`,`string`,`double`をサポートしています

セルの値をクリアしたい場合は空文字列`""`を指定すると
該当のセルが空の値で上書きされます

特定のセルを上書きしたくない場合、
Null値を指定することで書き込みの対象外とすることも可能です

:::note warn
`Null値`・・・`null`や`undefined`。書き込みの対象外として扱われます

注意として **`NaN`だけはサポートされていないようで例外が発生してしまいます**
`NaN`が想定されるロジックの場合、`null`や`undefined`に置換してから書き込みを行いましょう
:::

#### 数式の入力

文字列としてそのまま数式を書けば、数式がセットされます
(`valueInputOption`を`USER_ENTERED`に設定しておく必要あり)

```javascript
Sheets.Spreadsheets.Values.batchUpdate(
  {
    valueInputOption: 'USER_ENTERED',
    data: [
      { range: 'シート1!A1', values: [['=TODAY()']] },
    ],
  },
  SpreadsheetApp.getActive().getId(),
);
```

**シート1**
||列A|
|-|-|
|行1|2022/12/14|


#### 複数リクエストの一括指定

同じスプレッドシート内であれば、**複数の書き込みリクエスト**を一度に送信することが出来ます

例えば同じ入力データを複数のシートに書き込みたい場合
セル範囲だけを変えたリクエストを`data`配列に指定すればOKです。

```javascript
const values = [[101, 'Alice'], [102, 'Bob']];
Sheets.Spreadsheets.Values.batchUpdate(
  {
    valueInputOption: 'USER_ENTERED',
    data: [
      { range: 'シート1!A1:B2', values },
      { range: 'シート2!A1:B2', values },
      { range: 'シート3!A1:B2', values },
      { range: 'シート4!A1:B2', values },
      { range: 'シート5!A1:B2', values },
    ],
  },
  SpreadsheetApp.getActive().getId(),
);
```

`setValues`だと離れた複数のセルを変更することは出来ないので、
こういった使い方が出来るのは何気に便利ですね。

#### rangeの範囲外への書き込み

`range`で指定された範囲を超えるようなデータを`values`に指定していると、
例外が発生して書き込みに失敗してしまいます。

:::note alert
`GoogleJsonResponseException`が発生します
:::

そのため、`range`が2行 x 2列なら、`values`も2行 x 2列にするか
2行 x 1列のように範囲を超えないような配列サイズで指定する必要があります

```javascript
const values32 = [[101, 'Alice'], [102, 'Bob'],[103, 'Carol']];
const values21 = [[101], [102]];
Sheets.Spreadsheets.Values.batchUpdate(
  {
    valueInputOption: 'USER_ENTERED',
    data: [
      { range: 'シート1!A1:B2', values: values32 }, // <- 書き込み不可
      { range: 'シート1!C1:D2', values: values21 }, // <- 書き込み可
    ],
  },
  SpreadsheetApp.getActive().getId(),
);
```

#### 書き込み先の先頭セル指定

👆で書いた通り、例外の発生に配慮しようとすると
毎回`range`のセル範囲を意識して`values`を指定する必要があります。

単純なシステムであればあまり問題にならないのですが、
それなりに巨大なデータを扱うようになると、
いちいち`values`のサイズを考慮してセルを指定するのは大変です。

実は`range`には**書き込み先の先頭セル**だけを指定することも可能で
`values`の長さに関係なく、指定したデータをそのままのサイズで書き込むことが出来ます

なので、こんな指定をすると

```javascript
const values = [
    [101, 'Alice'],
    [102],
    [103, 'Carol', '2022-12-11']
];
Sheets.Spreadsheets.Values.batchUpdate(
  {
    valueInputOption: 'USER_ENTERED',
    data: [
      { range: 'シート1!A1', values },
    ],
  },
  SpreadsheetApp.getActive().getId(),
);
```

A1を先頭に`values`のデータが展開されます。とても助かる

**シート1**
||列A|列B|列C|
|-|-|-|-|
|行1|101|Alice||
|行2|102||
|行3|103|Carol|2022-12-11|



## まとめ

SheetsAPIを使用して書き込みを行う方法について解説してみました。

個人的に以下の点が非常に使いやすいので気に入ってます
- 複数のセル範囲を一括で書き込み出来る
- 先頭の1セルだけ指定して書き込みが出来る
- Null値を指定すると、現在の値をそのまま据え置くことが出来る

前回の`batchGet`と合わせて、シートの読み書きはこれでかなりの範囲がカバー出来るかと思います