2018年12月18日 / 最終更新日時 : 2022年7月12日 officeの杜 Google

Google Apps ScriptとMicrosoft Graph APIの連携 – データ書き込み編【GAS】

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

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

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

1 今回使用するファイル等
2 事前準備
- 2.1 ファイルのIDを取得する手順
- 2.2 対象ファイルのシート上にあるテーブルの名前を取得する手順
3 ソースコードと実行結果
4 関連リンク

今回使用するファイル等

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

事前準備

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

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

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

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

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

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

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

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

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

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

ソースコードと実行結果

範囲を指定して書込

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

//Excelファイルからデータを取得する
function setsheetdata() {
  //書き込み先シートを取得する
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data")
  var sdata = ss.getRange("A2:E").getValues();
  var slength = sdata.length + Number(1);　　　//range指定の為に1を加算しておく
  var ui = SpreadsheetApp.getUi();
  var fileid = "ここに書き込み先ExcelファイルのIDを入れる";  //xlsxのファイルID
  
  //Graph APIサービスを取得する
  var service = checkOAuth();
  
  if (service.hasAccess()) {
    //Excelのワークシートへデータを送り込む
    var sheetname = "テスト";
    var range = "a2:e" + slength;
    var excelfile = endpoint + "/me/drive/items/" + fileid + "/workbook/worksheets('" + sheetname + "')/range(address='" + range + "')";
    var xlsx = graphInsert("PATCH", excelfile, sdata);

    //終了処理
    ui.alert("データの送り込みに成功しました。")
    
  } else {
    ui.alert("認証が実行されていませんよ。");
  }
}

//実際にREST APIを叩く関数
function graphInsert(method, eUrl, sdata) {
  //Graph APIサービスを取得する
  var service = checkOAuth();
  
  var payloadData = {
    values:sdata
  }
  
  if (service.hasAccess()) {
    //HTTP通信
    var response = UrlFetchApp.fetch(eUrl, {
      headers: {
        Authorization: "Bearer " + service.getAccessToken()
      },
      method: method,
      contentType: "application/json",
      payload:JSON.stringify(payloadData)
    });
    
    //取得した値を返す
    return JSON.parse(response.getContentText());
  }else{
    //エラーを返す（認証が実行されていない場合）
    return "error";
  }
}

//Excelファイルからデータを取得する

function setsheetdata() {

//書き込み先シートを取得する

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data")

var sdata = ss.getRange("A2:E").getValues();

var slength = sdata.length + Number(1);　　　//range指定の為に1を加算しておく

var ui = SpreadsheetApp.getUi();

var fileid = "ここに書き込み先ExcelファイルのIDを入れる"; //xlsxのファイルID

//Graph APIサービスを取得する

var service = checkOAuth();

if (service.hasAccess()) {

//Excelのワークシートへデータを送り込む

var sheetname = "テスト";

var range = "a2:e" + slength;

var excelfile = endpoint + "/me/drive/items/" + fileid + "/workbook/worksheets('" + sheetname + "')/range(address='" + range + "')";

var xlsx = graphInsert("PATCH", excelfile, sdata);

//終了処理

ui.alert("データの送り込みに成功しました。")

} else {

ui.alert("認証が実行されていませんよ。");

}

//実際にREST APIを叩く関数

function graphInsert(method, eUrl, sdata) {

//Graph APIサービスを取得する

var service = checkOAuth();

var payloadData = {

values:sdata

}

if (service.hasAccess()) {

//HTTP通信

var response = UrlFetchApp.fetch(eUrl, {

headers: {

Authorization: "Bearer " + service.getAccessToken()

method: method,

contentType: "application/json",

payload:JSON.stringify(payloadData)

});

//取得した値を返す

return JSON.parse(response.getContentText());

}else{

//エラーを返す（認証が実行されていない場合）

return "error";

}

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

テーブルに対して追加

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

//テーブルにレコードを追加する
function InsertTableRow(){
  //UIと書込先ExcelファイルのIDを取得する
  var ui = SpreadsheetApp.getUi();
  var fileid = "ここに書き込み先ExcelのIDを入力する";  //xlsxのファイルID
  
  //Graph APIサービスを取得する
  var service = checkOAuth();

  if (service.hasAccess()) {
    //Excelのワークシートへデータを送り込む
    var sheetname = "テスト";
    var tablename = "テーブル1"
    var excelfile = endpoint + "/me/drive/items/" + fileid + "/workbook/worksheets('" + sheetname + "')/tables('" + tablename + "')/rows/add";
    
    //追記するデータ（配列形式）
    var sdata = [[1,2,3,4,5],[6,7,8,9,10]];
    var xlsx = graphInsert("POST", excelfile, sdata);
    
    //現在のレコード数を取得する
    Logger.log(xlsx.index)

    //終了処理
    ui.alert("データの送り込みに成功しました。")
    
  } else {
    ui.alert("認証が実行されていませんよ。");
  }
}

//テーブルにレコードを追加する

function InsertTableRow(){

//UIと書込先ExcelファイルのIDを取得する

var ui = SpreadsheetApp.getUi();

var fileid = "ここに書き込み先ExcelのIDを入力する"; //xlsxのファイルID

//Graph APIサービスを取得する

var service = checkOAuth();

if (service.hasAccess()) {

//Excelのワークシートへデータを送り込む

var sheetname = "テスト";

var tablename = "テーブル1"

var excelfile = endpoint + "/me/drive/items/" + fileid + "/workbook/worksheets('" + sheetname + "')/tables('" + tablename + "')/rows/add";

//追記するデータ（配列形式）

var sdata = [[1,2,3,4,5],[6,7,8,9,10]];

var xlsx = graphInsert("POST", excelfile, sdata);

//現在のレコード数を取得する

Logger.log(xlsx.index)

//終了処理

ui.alert("データの送り込みに成功しました。")

} else {

ui.alert("認証が実行されていませんよ。");

}

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

書込の実行

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

認証を実行する

図：OAuth認証画面

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

Google Apps ScriptとMicrosoft Graph APIの連携 – データ書き込み編【GAS】

今回使用するファイル等