Sheets APIを使ってスプレッドシートを操作する方法をご紹介
先日の記事
に引き続きSheets APIに関する記事を書いてみたいと思います。
Twitterでの反応や他のブログの方にも記事でご紹介を頂いたりと反響がありましたので、やはり皆さんGASの処理速度や⑥分間の処理制限に関しては関心が高いのだと改めて認識しました。
先日の記事では、コードなどは共有していませんでしたので、サンプルコードなどを含めての共有です。
Sheets APIは処理が早いということが前回の記事ではおわかりいただけたと思いますので、今回はSheets APIをどうやって利用するのかを中心に記事にしたいと思います。
本記事が役立つターゲット読者
- Sheets APIを使いたいけど使い方がわからない方
- 使い方は知っているけど、Sheets APIの留意点を知りたい方
Sheets APIを使うための事前準備
Sheets APIはAdvanced Google Servicesに属するサービスの一つになります。
SpreadsheetAppでできないことを補ったりすることができるとイメージしてもらえればいいかと思います。
よく使われるAPIでは、Drive APIというものがありますが、こちらはご存じの方も少なくないかもしれません。
本ブログでも、以前Googleドライブでファイルの新バージョンをアップロードする方法をご紹介しましたが、こちらもDrive APIでないとできない処理の一つです。
さて、話題がそれてしまいましたが、Sheets APIやDrive APIを含むAdvanced Google Servicesを使うためには、事前にサービスを有効化する必要があります。
Sheets APIを有効化する
Sheets APIを有効化する方法は2種類あります。
エディタ上のサービスから追加する
GASのスクリプトエディタ上からクリック操作だけで有効化できるので、比較的簡単な方法です。
まずは、対象となるスクリプトエディタを開きます。
エディタ上にファイル、ライブラリ、サービスと並んていますので、マウスカーソルをサービス上に持っていき、表示される「＋」をクリックします。
サービス一覧の中からGoogle Sheets API探し、クリックしてから右下の追加ボタンをクリックします。
バージョンはドロップダウンメニューになっていますが、GASで利用できる最新版しか選択ができませんので、特に意識しなくても良いです。
IDにSheetsと入力されていると思いますが、これはGASの上でサービスを呼び出すときの名称となりますので、Sheets以外が良ければ任意のIDに変更すれば良いと思います。
※当ブログでは初期値Sheetsのままコードを書いていますので、IDを変更した場合は当ブログのサンプルコードのSheetsをIDに設定した文字列と同じに変更してご利用ください。
無事Sheets APIが追加されると上記画像のようにSheetsと表示されます。
※IDを任意の文字列に変更している場合は、変更後の文字列がここに表示されます。
appsscript.jsonを編集して追加する
GASの設定ファイルであるappsscript.jsonを直接編集して追加する方法で、あまり一般的ではないとは思いますが、Apps Script APIを使って外部からGASの設定ファイルを変更する場合などに使う手法ですので、上級者であれば知っておいて損はありません。
※設定ファイルをイジるのに慣れていない方にはあまりオススメしません。
先程の手順と同じで、まずは、対象となるスクリプトエディタを開きます。
サイドメニューにあるプロジェクトの設定をクリックします。
「appsscript.json」マニフェストファイルをエディタで表示するにチェックを入れます。
エディタに戻り、appsscript.jsonを開きます。
{
"timeZone": "Asia/Tokyo",
//ここから追加
"dependencies": {
"enabledAdvancedServices": [
{
"userSymbol": "Sheets",
"version": "v4",
"serviceId": "sheets"
}
]
},
//ここまでを追加
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"
}コメントに囲まれている部分を追加します。
userSymbolがIDに該当しますので、任意の名称でSheets APIを呼び出したい場合はこちらを変更します。
無事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のリクエスト制限があります。
上記のページは英語のページですが、下記に日本語訳を記載します。
| リクエストの種類 | リクエストの条件 | リクエスト制限数 |
|---|---|---|
| 読み取りリクエスト | プロジェクトごとの1日の制限 | 無制限 |
| プロジェクトごとの1分間の制限 | 300回 | |
| 1プロジェクトでユーザーごとの1分間の制限 | 60回 | |
| 書き込みリクエスト | プロジェクトごとの1日の制限 | 無制限 |
| プロジェクトごとの1分間の制限 | 300回 | |
| 1プロジェクトでユーザーごとの1分間の制限 | 60回 |
上記上限に引っかからないようにシステム設計を考える必要があります。
まとめ
2回にわたってSheets APIを題材に記事を書いてみました。
意外と知られていないことだったようで書いた甲斐があったなぁと思っております。
GASでは使っていくうちに様々な壁にぶち当たることもあると思いますが、解消方法も無数にあると思います。
開発者で情報共有をしながらGAS界隈を更に盛り上げて行ければと思います。