前回、Google Apps ScriptからExcel上のデータを取得する事ができました。今回はそれに引き続き、データの書込をしてみたいと思います。Google Apps Scriptの場合、データの書込はかなりメソッドが充実しているので容易且つ堅牢ですが、Excelの場合はREST APIで都度投げる必要があるので、注意が必要です。

また、通常のExcelへの書込の他に、テーブル機能を利用した書込方法があるので、今回はこの2種類について実装をしてみたいと思います。テーブル機能を利用した書込については、Google Apps Scriptよりも楽な面があります。

※GAS側に排他処理の機能があるので、GASをゲートにしてExcelファイルの読み書きをさせると安全に処理が可能です。

今回使用するファイル等

今回もOneDriveルート直下のファイルにアクセスします。データは、テストというシートに架空の食中植物売上データを入れてあります。

事前準備

今回はデータの書込ですが、いくつかのデータ書込パターンを実装します。前回の項目同様に書込先ファイルのIDを調査すると共に、今回はExcelのテーブル機能も利用する為、シートに配置した「テーブル」の名前も調べる必要があります。アプリケーションから読み書きする場合には、このテーブル機能は非常に優れているので、是非活用しましょう。

ファイルのIDを取得する手順

  1. Graph Explorerサイトを開き、自分のアカウントでサインインする。
  2. GETにて、「https://graph.microsoft.com/v1.0/me/drive/root/children」を実行。今回は、OneDriveルート直下のxlsxを調べます。対象のファイルは、サンプルファイルだよ.xlsxを見つけて調べました。
  3. 検索結果が応答のプレビューに出てくるので、対象のファイルのidを控えておく。
  4. 特定のフォルダ内のファイルを調べる場合には、rootの部分にフォルダのIDを入れて調べる。

図:まずは、ファイルのIDを調べる

対象ファイルのシート上にあるテーブルの名前を取得する手順

  1. Graph Explorerサイトを開き、自分のアカウントでサインインする。
  2. GETにて、「https://graph.microsoft.com/v1.0/me/drive/items/ファイルのID/workbook/worksheets(‘シート名‘)/tables」を実行。対象のシート上にあるテーブルに付けられているテーブル名を調べます。
  3. 検索結果が応答のプレビューに出てくるので、対象のシート名を控えておく。

図:テーブル名が重要です。

ちなみにですが、このテーブル名、Excel Online上では確認する手段がありませんが、ローカルのExcel上では変更や確認することが可能です。こちらのほうが素直でしょう。

  1. 対象のファイルを開く
  2. デザインタブを開く
  3. 左上にある「テーブル名」を開く
  4. ここのテーブル名がGraphエクスプローラで表示されるものと同じとなるので、控えておく。

図:テーブル名を調べる方法はこちらが素直

ソースコードと実行結果

範囲を指定して書込

こちらは、Google Apps Scriptでもおなじみの書込方法と同じやり方です。相手先のシートの範囲を正確に指定して、作成してある配列データを書き込みます。配列データの縦横の大きさと相手先シートの範囲指定の縦横は必ず一致していなければなりません。

  • 書込はPATCHメソッドにて送り込みます。
  • 送り込むデータは通常の配列データです。
  • rangeにて、書込先シートの範囲をしていしています。
  • 相手先の範囲が「テーブル」であっても普通に書込が可能です。
  • 別のAPIメソッドである「UsedRange」を利用すれば、Excel VBAのUsedRange.Rows.Countと同じく、最終行を特定する事が可能です。
  • 書込が成功するとJSONで結果が返ってきます。
  • 実際にAPIを叩く処理は、graphInsert関数が引き受けています。

テーブルに対して追加

テーブルは、ExcelでありながらAccessのテーブルと同じような挙動をする事の出来る優れた機能です。見た目は通常のセルもテーブルも同じですが、テーブルという形で利用できる利点は、追記が楽である点です。Google Apps Scriptの場合には、appendRowにて配列を1行一番下に追記が可能ですが、Graph APIの場合には2次元配列もレコード追加として対応しているので、GASよりも面倒が少ないです。

  • レコードの追加は同じくPATCHメソッドにて送ります。
  • 送り込むデータは通常の配列データそのままなので、とても楽です。
  • 相手先にテーブルがないとエラーになりますので要注意です。
  • 範囲指定がない為、データはそのテーブルの一番下の行に追加されます。
  • 書込が成功するとJSONで結果が返ってきます。
  • 実際にAPIを叩く処理は、graphInsert関数が引き受けています。

書込の実行

スプレッドシートのメニューよりまずは、 認証の実行をしましょう。実行後に書込ができるようになります。

認証を実行する

図:OAuth認証画面

その後、メニューより「Excelデータ」⇒「データの書込」にて、範囲指定でデータを書込、「データレコードの追加」にてレコード挿入を実行する事ができます。コードの中にファイルのIDを指定する部分があるので、

関連リンク

共有してみる: