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

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

先日の記事

あわせて読みたい

GASでSheets APIを使ってスプレッドシートを操作してみる ① 【Sheets APIを使ってスプレッドシートを操作する方法をご紹介】 GASにはスプレッドシートを操作する際にSpreadsheetAppクラスのコードを使って処理をすることが一般的…

に引き続きSheets APIに関する記事を書いてみたいと思います。

Twitterでの反応や他のブログの方にも記事でご紹介を頂いたりと反響がありましたので、やはり皆さんGASの処理速度や⑥分間の処理制限に関しては関心が高いのだと改めて認識しました。

先日の記事では、コードなどは共有していませんでしたので、サンプルコードなどを含めての共有です。

Sheets APIは処理が早いということが前回の記事ではおわかりいただけたと思いますので、今回はSheets APIをどうやって利用するのかを中心に記事にしたいと思います。

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

Sheets APIを使うための事前準備

Sheets APIはAdvanced Google Servicesに属するサービスの一つになります。

SpreadsheetAppでできないことを補ったりすることができるとイメージしてもらえればいいかと思います。

よく使われるAPIでは、Drive APIというものがありますが、こちらはご存じの方も少なくないかもしれません。

本ブログでも、以前Googleドライブでファイルの新バージョンをアップロードする方法をご紹介しましたが、こちらもDrive APIでないとできない処理の一つです。

あわせて読みたい

GASでGoogleドライブのファイルに新しい版をアップロードする方法 今回の記事では、Googleドライブ上でファイルの新しいバージョンを追加する方法について書いていきたいと思います。このページをご覧頂いている方々はもうご存知の方が…

さて、話題がそれてしまいましたが、Sheets APIやDrive APIを含むAdvanced Google Servicesを使うためには、事前にサービスを有効化する必要があります。

Sheets APIを有効化する

Sheets APIを有効化する方法は2種類あります。

エディタ上のサービスから追加する

GASのスクリプトエディタ上からクリック操作だけで有効化できるので、比較的簡単な方法です。

STEP

スクリプトエディタを開く

まずは、対象となるスクリプトエディタを開きます。

STEP

サービスの横にある「＋」をクリックする

エディタ上にファイル、ライブラリ、サービスと並んていますので、マウスカーソルをサービス上に持っていき、表示される「＋」をクリックします。

STEP

サービスを追加ダイアログからGoogle Sheets APIを選択する

サービス一覧の中からGoogle Sheets API探し、クリックしてから右下の追加ボタンをクリックします。

バージョンはドロップダウンメニューになっていますが、GASで利用できる最新版しか選択ができませんので、特に意識しなくても良いです。

IDにSheetsと入力されていると思いますが、これはGASの上でサービスを呼び出すときの名称となりますので、Sheets以外が良ければ任意のIDに変更すれば良いと思います。

※当ブログでは初期値Sheetsのままコードを書いていますので、IDを変更した場合は当ブログのサンプルコードのSheetsをIDに設定した文字列と同じに変更してご利用ください。

STEP

サービス配下にSheetsが表示されたかどうかを確認

無事Sheets APIが追加されると上記画像のようにSheetsと表示されます。

※IDを任意の文字列に変更している場合は、変更後の文字列がここに表示されます。

appsscript.jsonを編集して追加する

GASの設定ファイルであるappsscript.jsonを直接編集して追加する方法で、あまり一般的ではないとは思いますが、Apps Script APIを使って外部からGASの設定ファイルを変更する場合などに使う手法ですので、上級者であれば知っておいて損はありません。

※設定ファイルをイジるのに慣れていない方にはあまりオススメしません。

STEP

スクリプトエディタを開く

先程の手順と同じで、まずは、対象となるスクリプトエディタを開きます。

STEP

プロジェクトの設定を開く

サイドメニューにあるプロジェクトの設定をクリックします。

STEP

マニフェストファイルをエディタに表示するにチェックする

「appsscript.json」マニフェストファイルをエディタで表示するにチェックを入れます。

STEP

appsscript.jsonを開く

エディタに戻り、appsscript.jsonを開きます。

STEP

appsscript.jsonを書き換える

{
　"timeZone": "Asia/Tokyo",
　//ここから追加
　"dependencies": {
　　"enabledAdvancedServices": [
　　　{
　　　　"userSymbol": "Sheets",
　　　　"version": "v4",
　　　　"serviceId": "sheets"
　　　}
　　]
　},
　//ここまでを追加
　"exceptionLogging": "STACKDRIVER",
　"runtimeVersion": "V8"
}

コメントに囲まれている部分を追加します。

userSymbolがIDに該当しますので、任意の名称でSheets APIを呼び出したい場合はこちらを変更します。

STEP

サービス配下にSheetsが表示されたかどうかを確認

無事Sheets APIが追加されると上記画像のようにSheetsと表示されます。

※IDを任意の文字列に変更している場合は、変更後の文字列がここに表示されます。

これで事前準備は完了しました。

サンプルコード

スプレッドシートからデータを取得する

const BULK_DATA_SHEET_URL = "https://docs.google.com/spreadsheets/d/1hlsCwCZCCzDAiLQmaIMaLkW_GUZ2lPsc61OBDcr6F88/"; // 参照元スプレッドシート

//シート1から全データを取得する
function getBulkDataFromSingleSheetWithSheetsApi() {
　let sheetId = BULK_DATA_SHEET_URL.match(/\/([\w\-_]+)\/$/)[1]; //Sheets APIではスプレッドシートIDが必要なので、URLからIDを抽出
　let request = {
　　ranges: "シート1", //この文字列を任意の文字列に変更すれば取得対象のシートを変更できる
　　majorDimension: "ROWS",
　}
　let data = getValuesBySheetsApi(sheetId, request);
　let end = Date.parse(new Date()) / 1000;
　let object = {
　　data: data
　}
　console.log(object);
}

//シート1からシート10それぞれのシートからrangesに格納されているセルのデータを取得してくる
function getBulkDataFromMultipleSheetsWithSheetsApi() {
　let sheetId = BULK_DATA_SHEET_URL.match(/\/([\w\-_]+)\/$/)[1]; //Sheets APIではスプレッドシートIDが必要なので、URLからIDを抽出
　let ranges = ["A1", "B2", "C3", "D4", "E5", "F6", "G7", "H8", "I9", "J10"];
　let array = [];
　let i, j;
　for (i = 1; i <= 10; i++) {
　　let sheetName = "シート" + i;
　　for (j in ranges) {
　　　let range = sheetName + "!" + ranges[j];
　　　array.push(range);
　　}
　}
　let request = {
　　ranges: array, //ここには対象となるシート or セルのデータが配列で設定します
　　majorDimension: "ROWS",
　}
　let data = getValuesBySheetsApi(sheetId, request); 
　let end = Date.parse(new Date()) / 1000;
　let object = {
　　data: data
　}
　console.log(object);
}

function getValuesBySheetsApi(sheetId, request) {
　let values = Sheets.Spreadsheets
　　　　　　　　　　 .Values
　　　　　　　　　　 .batchGet(sheetId, request)
　　　　　　　　　　 .valueRanges; //ここでスプレッドシートから値を取得するリクエストを送る
　let object = {};
　for (var i in values) {
　　let range = values[i]["range"].replace(/'/g, "");
　　object[range] = values[i]["values"];
　}
　return object;
}

サンプルコードにアクセスして、各スクリプトを実行することで取得できるデータを確認できます。

Sheets APIの返り値としては、配列が返されます。ループ処理をしてvalues[i][“values”]から取得したデータを取り出して処理に使うようにしましょう。

上記サンプルでは、values[i][“range”]から取得したシート＆セル名を取得してオブジェクトのキーとしています。

スプレッドシートにデータを出力する

function setValueToSpreadsheetBySheetsApi() {
　let sheetId = ""; //対象のシートIDを指定してください。
　var resource = {
　　data: [
　　　{
　　　　values: [[""]], //二次元配列で値を入れる
　　　　range: "", //出力セルを入れる
　　　},
　　　//一括で複数セルに対して出力したい場合は配列にオブジェクトを追加していく
　　　{
　　　　values: [[""]],
　　　　range: "",
　　　}
　　　//ここまでが一つのオブジェクトの塊
　　],
　　valueInputOption: "USER_ENTERED",
　}
　updateValuesBySheetsApi(sheetId, resource);
}

function updateValuesBySheetsApi(sheetId, resource) {
  Sheets.Spreadsheets.Values.batchUpdate(resource, sheetId);
}

サンプルスクリプトでは、シートIDや対象セル、値などを設定していませんので、このまま実行しても使えませんが、正しくIDや対象セルなどを入れればそのまま使うことができるようになっています。

データを取得するときと違い、出力する場合はオブジェクト形式で出力セルと値を1つずつ指定することになります。

このとき、セル範囲を指定しているrangeですが、単数形になっていることに注目ください。データを取得する際は一度にセル範囲を指定していたので、rangesと複数形になっていましたが、この点が2つのリクエスト方法での違いとなります。

Sheets APIを使うには、オブジェクトの使い方を理解する必要がありますが、使用するのは実はそれほど難しくはありません。

Sheets APIを使う上での留意点

先日の記事から、Sheets APIを使った方が処理速度が早くなるとお伝えしてきました。

これは、私の経験上からも間違いはありませんし、前回の検証の結果をご確認頂いても異論はないのではないかと思います。

ただし、Sheets APIも全てにおいてSpreadsheetAppに勝るものではないということは覚えておいてほしいと思っています。

ここからはあくまでも私の経験則ですので、異なる見解を持っている方もいると思いますから、一意見として見ていただければと思います。

具体的に何を危惧しているかというと、

Sheets APIで扱えるデータ上限はSpreadsheetAppよりも少ない

ということです。

データ量が多いスプレッドシートを扱うにはSheets APIの方が早いと言っておきながら、今さら何を言っているんだ？と言われると思います。

ですのでもう少し掘り下げて説明します。

例えば、スプレッドシートで50万件のデータを持っているファイルがあったとます。

Sheets APIでもSpreadsheetAppでもこの程度のデータ件数であれば問題なく処理することができ、この条件下ではSheets APIの方が処理は数倍早くなります。

これは、どちらも扱うことができるデータ上限を超えていないからです。

ただし、データ件数の上限はSheets APIの方が早く到達してしまう傾向があると私は感じています。

以前、行数20万行、列数50列程度のデータをオブジェクト形式でスプレッドシートに保存したスプレッドシートで両方のパターンで検証を行いました。

行数20万✕50列のデータというと、1000万個のデータになりますので、通常のデータの持ち方ではスプレッドシートにデータをもたせることができません。（スプレッドシートは500万セルが上限）

このような膨大なデータを処理させたとき、SpreadsheetAppでは20秒から30秒ですべてのデータを取得できましたが、Sheets APIではデータ量が多いというエラーが返され、データを取得することができませんでした。

このような運用は通常の利用ではあまり考えられませんが、状況によってはSheets APIではなく、SpreadsheetAppを使わざるを得ないこともあるということは覚えておく必要があると思います。

ただ、このくらいデータ量が多くなる場合は、すでにGASで処理ができる範囲を超えていて別のデータベースを考える必要がある時期なのでしょうね。

GASは手軽なので何でもすぐに作ってしまおうと思ってしまうのですが、このくらい大規模なデータを取り扱う場合は別のアプローチを考えるべきかなと思います。

Sheets APIの使用制限について

Sheets APIはGoogleが提供している他のAPIと同じように、APIのリクエスト制限があります。

Google for Developers

Usage limits  |  Google Sheets  |  Google for Developers

上記のページは英語のページですが、下記に日本語訳を記載します。

上記上限に引っかからないようにシステム設計を考える必要があります。

まとめ

2回にわたってSheets APIを題材に記事を書いてみました。

意外と知られていないことだったようで書いた甲斐があったなぁと思っております。

GASでは使っていくうちに様々な壁にぶち当たることもあると思いますが、解消方法も無数にあると思います。

開発者で情報共有をしながらGAS界隈を更に盛り上げて行ければと思います。