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

how_to_use_sheets_api_by_gas
URLをコピーする
URLをコピーしました!
目次

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のスクリプトエディタ上からクリック操作だけで有効化できるので、比較的簡単な方法です。

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

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

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

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

Advanced Google Services追加ボタン
STEP
サービスを追加ダイアログからGoogle Sheets APIを選択する

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

Sheets APIの有効化

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

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

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

STEP
サービス配下にSheetsが表示されたかどうかを確認
Sheets API追加後のサービス

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

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

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

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

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

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

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

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

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

プロジェクトの設定
STEP
マニフェストファイルをエディタに表示するにチェックする
マニュフェストファイルを表示する

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

STEP
appsscript.jsonを開く

エディタに戻り、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 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界隈を更に盛り上げて行ければと思います。

how_to_use_sheets_api_by_gas

この記事が気に入ったら
いいね または フォローしてね!

よかったらシェアしてね!
URLをコピーする
URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

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

コメント

コメント一覧 (7件)

  • 初めまして。高橋と申します。
    わかりやすい記事をありがとうございます。
    一点操作していく中で疑問があり、解消できませんでしたので、もし解決方法をご存知でしたら教えていただきたくコメントさせていただきました。

    コピー元データに「2022/9/6」のような日付データがある場合なのですが、
    貼り付けると「Thu Sep 01 00:00:00 GMT+09:00 2022」のような表記に書き換わってしまいます。
    書式設定等は試してみたのですが、効果がなく・・。
    もし解決方法をご存知でしたらご教授いただけますと幸いです!よろしくお願いいたします。

    • 高橋さん

      コメントありがとうございます。
      おそらく、日付オブジェクトのまま出力しようとしているのではないかと思います。

      出力する前に、Utilities.formatDateを使って任意の日付フォーマットの文字列として変換してから出力してみると結果は変わりますでしょうか?

      Utilities.formatDate(日付オブジェクト, “JST”, ,yyyy/M/d”)

      このような形です。
      もし、日付データが「Thu Sep 01 00:00:00 GMT+09:00 2022」という文字列の場合は、対象のデータをnew Dat(日付データの値)で日付オブジェクトにしてからUtilities.formatDateを使ってみてください。

      Utilities.formatDate(new Date(日付文字列), “JST”, ,yyyy/M/d”)

      というような形です。

      スマホで書いているので、記載ミスがあったらすいません。
      試して頂いたら簡単で良いので結果を教えていただけたら嬉しいです。

      • 丁寧なご返信をいただき、ありがとうございます!
        また、私の理解や、説明が足りておらず大変恐縮です・・。

        コピーしたいデータは、
        A列:ID(数字)
        B列:法人名(文字列)
        C列:契約開始日(日付)
        D列:アカウント数(数字)
        E列:最終ログイン日(日付)
        のように日付や、文字列等が入り混じったデータで、これが20列×1万行みたいな構成となっております。
        このような入り混じったデータの場合に、取得したデータ全体に「Utilities.formatDate」は使えないように見受けられました。取得したデータ全体に対して、「日付データが有る場合はこのフォーマットにする」のようなざっくりとした処理というのはやはり難しいのでしょうか・・?何度も申し訳ございませんが、ご確認のほどよろしくお願いいたします。

        • 管理人様

          すみません。。
          昨日データの書式については試したつもりでいたのですが、
          今、元データを全て書式なしテキストに変更してみたところ問題なくコピーできました。
          丁寧な返信まで頂きましたのに、私の確認ミスで大変失礼いたしました・・。
          そして、本当にありがとうございました!

          • 高橋さん

            ご返信ありがとうございます。
            表示書式の変更で対応できたとのことで良かったです。
            スプレッドシートの場合は、セルの表示書式のせいで想定通りの処理ができないことがありますので、今後に活かせる経験になったかと思います。

            さて、ご質問を頂いていた点についても回答いたします。

            文字列や日付が入り交じる配列をループ処理で日付オブジェクトにのみフォーマット変更の処理を行う場合は、値のデータ型を確認して処理をすればよいと思います。

            typeofを使えば、変数のデータ型を確認することができます。
            参考ページ:https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Operators/typeof

            例えば、
            let data = [[対象の配列]];
            for (let i in data) {
            for (let j in data[i]) {
            if (typeof data[i][j] === “object”) {
            //日付オブジェクトの場合の処理
            data[i][j] = Utilities.formatDate(data[i][j], “JST”, “yyyyM/d”);
            }
            }
            }

            このようにすれば日付オブジェクトの場合だけ日付フォーマットを変えることができると思います。
            文字列の場合はstring
            数値の場合はnumber
            真偽値の場合はboolean

            objectが返ってくるのは日付オブジェクトだけではありませんが、スプレッドシートに格納される値でオブジェクト型として取得できてしまうのは日付くらいかなという想定で上記コードを書いています。

            日付が文字列として取得されてしまう場合は、少し難しいかもしれませんが正規表現などでパターンに一致した場合にフォーマットを変更する処理を入れたらいいと思います。

          • だいぶ苦戦しましたが、いい経験になりました!また関数(eomonth関数など)で引っ張ってきた日付に関しては、書式なしテキストにするだけですと不十分でした。しかし、管理人様がgasでの考え方を教えてくださっていたおかげで、関数自体をtext関数などでくくってあげた上で書式なしテキストにすればいいという着眼点を持てました!

            なるほど・・こんな方法論があるのですね!
            forの使い方を私がいまいちわかっていないことや、この処理で時間がかかりすぎやしないかだけ少し不安ですが、今後のためにもテスト環境で試してみたいと思います。親切丁寧なご返信本当にありがとうございます!!

          • 昨日、教えていただいた方法を社内のエンジニアにも教えてもらいながら試したところうまくいきました!
            懸念しておりました該当部の実行時間も、数十万セルであろうと1秒程度で終わることがわかり、運用に取り入れていくことができそうです。重ね重ねありがとうございました!

コメントする

目次
トップへ
目次
閉じる