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

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

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

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

1 今回使用するファイルとサイト
2 事前準備
3 コードと実行結果
- 3.1 GAS側コード
- 3.2 実行結果
4 関連リンク

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

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

事前準備

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

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

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

{@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("認証が実行されていませんよ。");
  }
}

//ターゲットファイルの設定

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にて、データのある最終行を取得出来ます。