初心者でもわかるGoogle Apps Script活用のススメ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

RESTful APIとは、Webシステムを外部から利用するためのプログラムの呼び出し規約(API)の種類の一つで、「REST」(レスト)と呼ばれる設計原則に従って策定されたもの。

出典:IT用語辞典 e-Words(https://e-words.jp/w/RESTful_API.html)

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

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

これが何を意味しているかというと、一言で表すと、SpreadsheetAppはGASでのみ利用できるコードSheets APIは様々な言語からスプレッドシートの操作に使われるAPIということです。

そのため、Googleの公式ドキュメントにも色々な言語で利用するためのサンプルコードが記載されています。

我らがGASももちろんありますが、その他にも色々な言語で利用できることが確認できます

Sheets APIがなんのためにあるのかがだいたいわかりましたね。

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[アプリコ] | アプリ・ゲームの紹介/攻略サイト

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

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をSheets APIに置き換えるだけでその遅さを解消することができるので、この点は大きなメリットだと思います。

そして、二つ目の理由

処理速度を更に早くする秘訣がある

APIリクエストの回数が増えると処理が遅くなるということは他のブログでもよく言われていることと思いますので、ご存じの方も多いですね。

SpreadsheetAppは比較的使うのが容易ですが、Sheets APIと比較すると実は大きな欠点があります

それは、

  • 複数のシートから値を取得する際、1シートずつ処理をしなければならない
  • 位置の離れたセルから値を取得したり、出力したりすることができない

という仕様です。

例えば、ファイルの中に10個のシートがあり、それぞれのシートから値を取得しなければならないとき、SpreadsheetAppでは、

シートを取得 → セルから値を取得 ✕ 10回が必要

1シートを取得するだけでもコンマ数秒かかってしまいますので、数が多くなればそれだけ多くの時間がかかってしまうことは想像に難しくありません。

また、1つのシートから値を取得する場合でも、隣接するセル(例えば、A1:D5という範囲)から値を取得するのは、範囲指定をして1回のAPIリクエストで値を取得することができるのですが、隣接していないセル(A1、D2、F10など)から値を取得する場合は、対象のセルの数の個数分セルを取得しなければならないということがあります。

これも量が多くなればそれだけ時間がかかってしまいます。

Sheets APIならなんと!1回の処理でどちらの場合も値を取得可能

Sheets APIが内部的にどのような処理をしているのかはよくわかりませんが、

  • データ量の多いファイルの処理
  • 複数シートに対する処理を行う場合
  • 隣接しないセルに対する処理を行う場合

少なくとも、この3つの条件下において、Sheets APIはSpreadsheetAppの 数倍 処理が 早い です。

このようなメリットがありますので、Sheets API覚えておいて損はないと思います。

SpreadsheetApp vs Sheets API(速度比較)

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

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

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

https://for-dummies.net/gas-noobs/wp-content/uploads/2022/04/Screen-recording-2022-04-15-22.31.33.webm

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

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

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

実行ログの比較

下から順番に、

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

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

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

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

まとめ

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

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

次回投稿もお楽しみに。

Exit mobile version