概要
スプレッドシートのセルを読み書きする手段として、
setValuesやgetValuesを呼ぶより断然早いと噂のSheets APIを使ってみました🙌
実際に色々触ってみたところ、
マジでめちゃくちゃ高速に読み書き出来るようになったので使い方をメモっておきます
GASからSheetsAPIを利用するための準備
GASのIDEを開いて、サービスからGoogle Sheets APIを追加します.
これにより、GASのソースコード上からSheetsというIDを通してAPIを実行することが出来ます。お手軽ですね
シートから値を取得する
Spreadsheets.Values.batchGetを使います
(getでも良いんですが、batchGetなら複数の範囲を一括で取得出来るので基本的にこちらを推奨します🙂)
- 第１引数：取得先のスプレッドシートIDを指定します
- 第２引数：取得したい範囲を文字列の配列で指定します
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]を参照）
// 値の取り出し
const [head, ...values] = resp.valueRanges[0].values || [[]];
例えばシート1のA列,B列にこんな値が入っていた場合...
| number | name |
|---|---|
| 101 | Alice |
| 102 | Bob |
取り出した値はこんなデータ構造になります
// head
['number', 'name']
// values
[
['101', 'Alice'],
['102', 'Bob'],
]
❗️ここで注意が必要な部分が、空白セルが存在した時の挙動です.
ザックリ言うと、batchGetで読み出した時はデータが存在するセルまでしか格納されません.
例えばこのようなデータに対してbatchGetを使うと
| number | name | score |
|---|---|---|
| 101 | Alice | 100 |
| 102 | Bob | 75 |
| 103 | Carol |
以下のように、3つ目の配列長が2に切り詰められます
// head
['number', 'name', 'score']
// values
[
['101', 'Alice', '100'],
['102', 'Bob', '75'],
['103', 'Carol'], // 配列長が2に切り詰められる
]
この挙動を知らないと、取得した2次元配列を操作する時に
ちょこちょこ出てくるエラーに悩まされるので注意しましょう😭
ちなみに、この挙動は行方向に対しても同様です
ただこれは使い方によっては便利で
何も考えずにシート1!A:Cとか指定して読み出しても
データが存在するセルのみ引っ張ってこれます👍
(補足)全部のセルが空の場合
resp.valueRangesにvaluesプロパティがそもそも存在しませんw
こんな感じで存在チェックを入れておくと良いかと思います
// valuesがundefinedなら空の二次元配列をセット
const [head, ...values] = resp.valueRanges[0].values || [[]];
まとめ
SheetsAPIの具体的な使い方をまとめた記事が欲しかったので書いてみました📝
書き込み用のAPIbatchUpdateについても解説を書いているので、
宜しければこちらもご覧ください
参考