GASでSheets APIを使ってスプレッドシートを操作してみる ①

2022 4/17

2022年4月15日 2022年4月17日

URLをコピーする

URLをコピーしました！

Sheets APIを使ってスプレッドシートを操作する方法をご紹介

GASにはスプレッドシートを操作する際にSpreadsheetAppクラスのコードを使って処理をすることが一般的です。

SpreadsheetAppクラスはコードもわかりやすいので、すぐに使えるようになる方も少なくないと思います。

今回の記事では、Googleスプレッドシートを操作するためのもう一つの方法『Sheets API』をご紹介しようと思います。

ただし、なぜ、SpreadsheetAppでスプレッドシートが操作できるのにわざわざわかりにくいSheets APIを使うのか？そういった疑問も含めてご紹介ができたらと思っています。

本記事が役立つターゲット読者

SpreadsheetAppで開発した処理を更に高速化したいと考えている方
Advanced Google Servicesを使ってもう一段上位の開発を行いたい方
GAS以外の言語でSheets APIを使いたいと考えている方

そもそもSheets APIってなんのためにあるの？

公式ドキュメントの概要によると、

Sheets APIはスプレッドシートのデータを読み取ったり修正したりするためのRESTful APIインターフェイスである。広く使われる使用用途としては、次のようなものが挙げられる。

スプレッドシートの作成
セルの値の読み取り / 書き出し
スプレッドシートの書式設定の更新
複数のスプレッドシートの連

これだけ見るとSpreadsheetAppとあまり変わらないように見えますね。

ただ、SpreadsheetAppとの大きな違いはRESTful APIという点です。

RESTful APIとは、レストAPIと呼ばれていますが、

RESTful APIとは、Webシステムを外部から利用するためのプログラムの呼び出し規約（API）の種類の一つで、「REST」（レスト）と呼ばれる設計原則に従って策定されたもの。
出典：IT用語辞典 e-Words（https://e-words.jp/w/RESTful_API.html）

とのことで、外部のシステムから利用するための規格ということです。

詳しくは下記のリンクをご確認ください。

Sheets APIで使われる用語の定義

具体的な使い方の前に、まずは公式ドキュメントの記載を元に用語の定義とオブジェクト構造を理解していきたいと思います。

Spreadsheet（スプレッドシート）

Googleスプレッドシートで一番重要なオブジェクトとなるSpreadsheet。このオブジェクトは構造化された情報をセルに含んだ複数のシートから構成されるもので、いわゆる1つのスプレッドシートファイルのことを指します。

すべてのスプレッドシートは英数字とハイフン、アンダーバーから構成される一意（ユニーク）のIDを持っています。

IDは皆さんすでにご存知のように、URLの一部の文字列になります。（下記URLの強調部分）

https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0

{
　"spreadsheetId": string,
　"properties": {
　　object (SpreadsheetProperties)
　},
　"sheets": [
　　{
　　　object (Sheet)
　　}
　],
　"namedRanges": [
　　{
　　　object (NamedRange)
　　}
　],
　"spreadsheetUrl": string,
　"developerMetadata": [
　　{
　　　object (DeveloperMetadata)
　　}
　],
　"dataSources": [
　　{
　　　object (DataSource)
　　}
　],
　"dataSourceSchedules": [
　　{
　　　object (DataSourceRefreshSchedule)
　　}
　]
}

Sheet（シート）

Sheetとは1つのスプレッドシート内にあるシートタブのことです。Sheetオブジェクトは一意のシート名と数字で構成されたシートIDで構成されます。シートのIDもスプレッドシートURLの一部にあります。

https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId

{
　"properties": {
　　object (SheetProperties)
　},
　"data": [
　　{
　　　object (GridData)
　　}
　],
　"merges": [
　　{
　　　object (GridRange)
　　}
　],
　"conditionalFormats": [
　　{
　　　object (ConditionalFormatRule)
　　}
　],
　"filterViews": [
　　{
　　　object (FilterView)
　　}
　],
　"protectedRanges": [
　　{
　　　object (ProtectedRange)
　　}
　],
　"basicFilter": {
　　object (BasicFilter)
　},
　"charts": [
　　{
　　　object (EmbeddedChart)
　　}
　],
　"bandedRanges": [
　　{
　　　object (BandedRange)
　　}
　],
　"developerMetadata": [
　　{
　　　object (DeveloperMetadata)
　　}
　],
　"rowGroups": [
　　{
　　　object (DimensionGroup)
　　}
　],
　"columnGroups": [
　　{
　　　object (DimensionGroup)
　　}
　],
　"slicers": [
　　{
　　　object (Slicer)
　　}
　]
}

Cell（セル）

シートを構成しているデータの集合体の一つの要素のことをCellと呼びます。

エクセルなどでも馴染みが深いと思います。

Cellは行（縦方向）と列（横方向）で構成され、複数のCellをまとめてセルが結合される場合もあります。Cellオブジェクトは一意のIDは持っていませんが、その代わりに列と行の座標のようなものでCellの位置を特定します。

Cel位置の指定方法には複数方法があります。

A1 notation

A1 notation方式は一般的にスプレッドシートで関数を使う際のセルの指定方法と同じになります。セルの絶対位置を指定する際には一番頻繁に使われる方式です。

指定方法のサンプル

Sheet1!A1:B2 − Sheet1のA1、A2、B1、B2セル
Sheet1!A:A − Sheet1のA列すべて
Sheet1!1:2 − Sheet1の1行目と2行目すべて
Sheet1!A5:A − Sheet1のA5行目以降のA列すべて
A1:B2 − 表示されている一番左のシートのA1、A2、B1、B2セル
Sheet1 − Sheet1のすべて
‘My Custom Sheet’!A:A − My Custom SheetのA列すべて
‘My Custom Sheet’ − My Custom Sheetのすべて

上記の方法でCellを指定する際はできるだけ紛らわしくない方法で指定をすることが望ましいです。
たとえば、A1と指定すると、表示されている一番左のシートのA1を参照することになりますが、シングルクォーテーションで囲った‘A1’であれば、A1というシート名のすべてのCellを参照することになります。
同じように、Sheet1はSheet1というシート名のすべてのCellを参照することになりますが、もしどこかにSheet1という名前をつけた範囲があると、名前付きのCellを参照することになります。

R1C1 notation

R1C1 notation方式はRとCの部分は常にRとCになります。これはRow（行）の頭文字とColumn（列）の頭文字から来ています。シートの一番左上のセル（A1 notation方式の場合はA1を基準として、基準点をR1C1とします）
B1を指定したければ、R1C2となり、A2を指定したければ、R2C1となります。
A1 notationよりも一般的ではありませんが、A1 notationは絶対位置を参照、R1C1 notationは相対位置を参照する際に役立つ方法です。

絶対参照と相対参照については、こちらのブログに詳しく書いてありましたので紹介させていただいます。

Aprico

スプレッドシートの「絶対参照」と「相対参照」の違いや使い方、ショートカットを解説！ | Aprico Googleスプレッドシートでは、セルの参照が使えます。さらに参照には「絶対参照」と「相対参照」があります。この２つの参照を理解することでより便利になります。スプレッ…

出典：Aprico[アプリコ] | アプリ・ゲームの紹介/攻略サイト

気になる方はご確認ください。

Named range（名前をつけた範囲）

Named rangeは任意の名前をつけた1つあるいは複数のまとまったCell範囲のことを指します。Named rangeの場合は1つのスプレッドシート内で共通の名前となるため、シート名を指定する必要はありません。

Sheets APIのオブジェクトでは、FilterView resourceがNamed rangeを表しています。

{
　"filterViewId": integer,
　"title": string,
　"range": {
　　object (GridRange)
　},
　"namedRangeId": string,
　"sortSpecs": [
　　{
　　　object (SortSpec)
　　}
　],
　"criteria": {
　　integer: {
　　　object (FilterCriteria)
　　},
　　...
　},
　"filterSpecs": [
　　{
　　　object (FilterSpec)
　　}
　]
}

Protected range

Protected rangeは修正できない1つあるいは複数のまとまったCell範囲のことを指します。Sheets APIのオブジェクトでは、ProtectedRange resourceがNamed rangeを表しています。

{
　"protectedRangeId": integer,
　"range": {
　　object (GridRange)
　},
　"namedRangeId": string,
　"description": string,
　"warningOnly": boolean,
　"requestingUserCanEdit": boolean,
　"unprotectedRanges": [
　　{
　　　object (GridRange)
　　}
　],
　"editors": {
　　object (Editors)
　}
}

GASでSheets APIを使うメリットとは？

本記事の冒頭で話したように、GASにはSpreadsheetAppというクラスでスプレッドシートの操作を行えますが、なぜSheets APIを使う必要があるのか？を簡単に話してみたいと思います。

まず一つ目の理由は

SpreadsheetAppよりもSheets APIの方が処理が断然早い

これは、特にデータ量が多いスプレッドシートでその速度の違いがより顕著になっていきます。

SpreadsheetAppで処理が遅いなと思ったら一度Sheets APIを試してみる価値はあると思いますよ。

下記の記事にも書いたように、GASには実行において上限時間が決まっていたりと様々な制約が存在します。

SpreadsheetApp vs Sheets API（速度比較）

速度比較用のウェブアプリを用意しました。

速度比較用ウェブアプリ

うまくアクセスができない場合は、ブラウザのシークレットモードで開いてみてください。

参照スプレッドシート

私の環境でウェブアプリを操作したときの様子を動画に撮影しましたので、参考に置いておきます。

ボタンが4つありますが、上の2つは参照スプレッドシートのシート1のデータをすべて取得します。
データが入っているセルは52万セルありますが、こちらはそれほど大きな差はありませんが、数秒程度Sheets APIの方が早く処理ができているようです。

問題は複数シートから複数セルを取得する処理です。

Sheets API、控えめに言って爆速です

SpreadsheetAppとSheets APIの実行ログ比較 — 実行ログの比較

下から順番に、

SpreadsheetAppでシート1から全データ取得
Sheets APIでシート1から全データ取得
SpreadsheetAppで各シートから10個のセルデータを取得
Sheets APIで各シートから10個のセルデータを取得

Sheets APIの複数シート、複数セルの処理がヤバいです。

このような形で、状況によって数倍以上の処理速度を叩き出してくれますので、適材適所で使い分けをすると良いかと思います。

今回は長くなってしまったので、具体的なコードの紹介は次の投稿で執筆しようと思います。

まとめ

SpreadsheetAppとSheets APIの比較いかがでしたでしょうか。

自分でも改めて比較してみて、こんなに違いが出ることに少し驚きました。次回の投稿では、Sheets APIを具体的にどうやって使うのかをご紹介しようと思います。

次回投稿もお楽しみに。

この記事を書いた人

サイト管理人

初心者でもわかるGoogle Apps Script活用のススメの管理人。
業務でラクをするという邪な目的のためにプログラムを独学で勉強し始め、初心者向けのGoogle Apps Scriptの使い方サイトを運営するに至る。
ラクをするためにはどんな苦労も厭わないという考えから、手っ取り早くスキルアップするためにCrowdWorksやLancersなどで仕事を受注し始め、過去にはTopClass PRO Crowd workerに認定された経験を持つ。
現在では、業務効率化システムの開発を行う会社の代表を務める。