Google Apps ScriptとMicrosoft Graph APIの連携 - データ取得編【GAS】

前回、Microsoft Graph APIのOAuth2.0認証を取得する所まで用意することが出来ました。今回はそれに引き続き、OneDriveに配置してあるExcelファイルからデータを取得して、自身のスプレッドシートに書き込みまでを実装してみました。

ただし、Googleのそれとは文化がだいぶ異なるのと、REST APIを叩くことで操作をする方式なので、GASの流儀で操作は出来ません。単純にデータを取得して貼り付けてるだけなので、より発展的な操作をするためには何度もAPIを叩く必要があるので、少し面倒ですね。

今回使用するファイルとサイト

• Graph APIにアクセスするスプレッドシート
• アクセスするExcelファイル on OneDrive
• Microsoft Graph エクスプローラ
• Microsoft Graph API リファレンス
• eStat - 第２１回医療経済実態調査医療機関等調査 統計データ

今回はOneDriveルート直下のファイルにアクセスします。データはeStatの統計データを入れてあります。

事前準備

OneDriveやSharePointはローカルのファイルサーバと同じく、同じディレクトリ内に同じファイル名が存在できないルールになっています。また、Microsoft365のExcelのURLにはファイルIDが含まれておらず、事前にMicrosoft Graph Explorerからクエリを実行して、対象のファイルのIDを取得しておく必要があります。(GASのようなメソッドチェーンでURLからID取得するようなことが出来ないのが不便)

ファイルのIDを取得する手順は以下の通り。

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

図：ちょっと手間の掛かる手順が必要。

{@odata.context=https://graph.microsoft.com/v1.0/$metadata#users('c9322986-6a70-4c29-b6f4-63e8e46f866f')/drive/items('01AAJRRAG5LIQKDSXKDVF2WHCRJMJ5LTT4')/workbook/worksheets, 
value=[
  {@odata.id=/users('c9322986-6a70-4c29-b6f4-63e8e46f866f')/drive/items('01AAJRRAG5LIQKDSXKDVF2WHCRJMJ5LTT4')/workbook/worksheets(%27%7B00000000-0001-0000-0000-000000000000%7D%27), visibility=Visible, name=統計データ, id={00000000-0001-0000-0000-000000000000}, position=0}, 
  {@odata.id=/users('c9322986-6a70-4c29-b6f4-63e8e46f866f')/drive/items('01AAJRRAG5LIQKDSXKDVF2WHCRJMJ5LTT4')/workbook/worksheets(%27%7B467CE256-F0F4-43BD-95CA-372E4A759B39%7D%27), visibility=Visible, name=テスト, id={467CE256-F0F4-43BD-95CA-372E4A759B39}, position=1}
  ]
}

結果：試しにシート一覧を取得してみた。

コードと実行結果

GAS側コード

//ターゲットファイルの設定
var fileid = "ここにファイルのIDをいれてください。";

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

//Excelファイルからデータを取得する
function getsheetdata() {
  //書き込み先シートを取得する
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1")
  var ui = SpreadsheetApp.getUi();
  //Graph APIサービスを取得する
  var service = checkOAuth();
  
  if (service.hasAccess()) {
    //Excelのワークシートを取得する
    var sheetname = "統計データ";
    var range = "a3:e12";
    var excelfile = endpoint + "/me/drive/items/" + fileid + "/workbook/worksheets('" + sheetname + "')/range(address='" + range + "')?$select=values ";
    var xlsx = graphapicall("GET", excelfile);
    
    //ワークシートデータを配列で取得する
    var sdata = xlsx.values
    
    //配列の縦横の数を取得する
    var row = sdata.length;  　　　　　　　　　　　//レコード数を取得
    var column = sdata[0].length;　　　//カラムの数を取得
    ss.getRange(3,1,row,column).setValues(sdata);
    
    //終了処理
    ui.alert("データを取得しました。")
    
  } else {
    ui.alert("認証が実行されていませんよ。");
  }
}

• データは通常の配列データで返ってきますので、扱いは簡単です。
• endpointに様々なURL情報を付け加えるのですが、ワークシートはシート名指定で行けます。レンジの範囲も通常のExcel上の範囲指定と同じ形式が利用できます。
• worksheets(シート名)/usedrangeにて、データのある最終行を取得出来ます。

実行結果

メニューより、「Excelデータ」⇒「データの取得」を実行します。実行すると、OneDrive上のExcelからデータをひっこぬいて、Googleスプレッドシート側に書き込まれます。

図：そっくりそのままコピーしてきました。

関連リンク

• How to access files stored in OneDrive or in a SharePoint site with Microsoft Graph and the Excel API
• Microsoft Graph を使用して、Python アプリの Excel にアクセスする
• Microsoft Graph を使ってみよう : ブックやグラフリソース (Excel)
• [Python] Excel OnlineをAPI経由で更新する方法
• OneDrive file picker for JavaScript v7.2 overview