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

はじめに

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

概要

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

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

シートに値を書き込む

Sheets.Spreadsheets.Values.batchUpdateを使用します

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

第1引数 : resource

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

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

valueInputOption

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

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

基本的にUSER_ENTEREDで問題ありません

data

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

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

第2引数 : spreadsheetId

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

書き込み例

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

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値を指定することで書き込みの対象外とすることも可能です

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

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

数式の入力

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

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

シート1

	列A
行1	2022/12/14

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

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

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

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に指定していると、
例外が発生して書き込みに失敗してしまいます。

GoogleJsonResponseExceptionが発生します

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

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の長さに関係なく、指定したデータをそのままのサイズで書き込むことが出来ます

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

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と合わせて、シートの読み書きはこれでかなりの範囲がカバー出来るかと思います