✨
SalesforceからSharepoint にExcel 帳票を生成する
以前「SalesforceからMicrosoft Graph API を利用してみました」という記事を投稿しましたが、今回はそのつづきで、Graph API を使って Salesforce から Sharepoint上に Excel の帳票を作成する方法をご紹介します。
Graph APIにアクセスできれば、帳票作成は実装だけかなと思っていたのですが、ちょこちょこ Salesforce/Apex/GraphAPI のエッセンスがあったので、部分的にでもご参考にしていただければと思い記事にしてみました。Graph API を利用した方法なので、今回の帳票作成は Sharepoint 上の Excel ファイルを操作する形となります。テンプレートとなる Excel ファイルを Sharepoint に配置して、それを特定のSharepoint フォルダにコピーした後、Salesforce のデータを流し込みます。
大きな流れとしては次のような感じです:
- テンプレートと出力フォルダの設定
- テンプレートのコピー
- セッションの作成
- データの転記
- 作成した帳票の表示
1. テンプレートと出力フォルダの設定
-
帳票の元となるテンプレート(Excel)を Sharepoint 上に配置し、帳票を作成する出力フォルダもSharepoint に用意します。開発やテストもあるので、サンドボックス(スクラッチ組織)と本番環境で2系統作ると便利かと思います。私のところでは「Salesforce管理者」という Teams のチャンネルで管理チームを運営しているので、そのチャンネルのファイル(実際は Sharepoint)の下に、作業用の出力フォルダ("Sandbox/帳票出力")とテンプレートファイルを入れるフォルダ("_template")を用意しました
-
Graph API からフォルダやファイルにアクセスするには、当該 Sharepoint サイトのドライブ ID と、テンプレートやフォルダのアイテムID を知る必要があります。これがちょっと手間なのですが、Qiita にもいくつか良い記事が投稿されていますのでご参照ください
-
ドライブ ID とアイテム ID 分かったらプログラム中にハードコードしてもいいんですが、保守性としてどうなんかというのと、先に述べましたようにサンドボックス(スクラッチ組織)と本番環境でIDが異なるので、Salesforce のカスタムメタデータを利用して管理するようにしました。各 ID を Salesforce 組織に保存して、プログラム中から参照します。私の場合、項目数を減らすため、ドライブ ID とアイテム ID を ‘,’ でつなげた文字列を1項目としています('<drive id>, <item id>' の形の文字列をメタデータの1項目とする)
SFDX では次のようになります。Commitするときは、サンドボックス用の設定を主として本番用をコメントにしています。スクラッチ組織は頻繁に作るので、スクラッチ組織用の設定をデフォルトにしたほうが便利かと思います。
デプロイ用のパッケージは次のような感じです。
<types>
<members>XXX_Setting.XXX_Setting</members>
<name>CustomMetadata</name>
</types>
<types>
<members>XXX_Setting__mdt</members>
<name>CustomObject</name>
</types>
- 下は Apex からカスタムメタデータを読み出すコードです
//カスタムメタデータを利用
private static String targetFolderDriveId = null;
private static String targetFolderItemId = null;
private static void getSettings(){
XXX_Setting__mdt md2 = XXX_Setting__mdt.getInstance('XXX_Setting');
String targetFolderInfo = md2. FormOutputFolder__c;
targetFolderDriveId = targetFolderInfo.split(',')[0];
targetFolderItemId = targetFolderInfo.split(',')[1];
…
}
2. テンプレートのコピー
- 準備が完了したら帳票出力用のApexコードに移ります。手順としては帳票テンプレートを出力フォルダにコピーして、そのファイルの中身を書き換えます。以下コピー用のAPIを呼び出すコード例です (callGraphApi 関数については以前の記事をご参照ください)
// ファイルコピー
@AuraEnabled(cacheable=false)
public static String copyFile(String fromDriveId, String fromFileId,
String toDriveId, String toFolderId, String newName) {
String api =
'/drives/' + fromDriveId +
'/items/' + fromFileId +
'/copy?@microsoft.graph.conflictBehavior=replace';
Map<String,Object> body = new Map<String,Object>();
if (toFolderId != null){
Map<String,Object> parentReference = new Map<String,Object>{
'id' => toFolderId
};
if (toDriveId != null){
parentReference.put('driveId', toDriveId);
}
body.put('parentReference', parentReference);
}
if (newName != null){
body.put('name', newName);
}
HttpResponse res = callGraphApi('POST', api, null, JSON.serialize(body, False));
- ファイルコピーは簡単なのですが、実際のコピーは API の戻りとは非同期に行われるので注意が必要です。コピー先のファイルに対して操作するので、コピー完了を待たないといけません。Graph API の long running actions という処理が必要になります。
先のレスポンスヘッダに Location が含まれていますので、こちらの URL に GET リクエストして、レスポンスボディの ‘status’ が ‘completed’ になればコピー終了です。その時、’resourceId’ にコピーしたファイルのアイテムIDも取得することができます。下はサンプルコードです。無限ループが心配になりますが、100 回で Apex エラーで終了しますのでリトライカウントは省略しています。あと、リトライ毎に Sleep を入れたいところですが、Apex には Sleep 機能がないようなのでそちらも割愛です。callout はそれなりに待ち時間のある処理ですし。１ファイルのコピーなので、私の環境ではリトライ回数は無し、あるいはごく少数でした。あと下にコメントがありますが、 ヘッダーに Accept を入れないとエラーになるようですのでご注意ください (私はこれで結構ハマりました。。)
String location = res.getHeader('Location');
String id = null;
while (True){
Http h = new Http();
HttpRequest req = new HttpRequest();
req.setEndpoint(location);
req.setMethod('GET');
req.setHeader('Accept', 'application/json'); //これがないと 400 エラーになる
res = h.send(req);
if (res.getStatusCode() != 200){
break;
}
Map<String,Object> resMap = (Map<String,Object>)JSON.deserializeUntyped(res.getBody());
if ((String)resMap.get('status') == 'completed'){
id = (String)resMap.get('resourceId');
break;
}
// コールアウト制限で 100回以上繰り返すとエラー終了となる
}
return id;
｝
- ステータス確認用の URL は Graph API の仕様として特に定められていないみたいですが、私の場合 Graph API ではなく https://XXXX.sharepoint.com/ というファイルを配置・出力する Sharepoint サイトのリソースでした。こちらも Salesforce のリモートサイトに登録しないとエラーになるので設定の追加が必要です
3. セッションの作成
- Sharepoint にはファイルの変更履歴をみる機能があります。セッションを利用すると一連の Graph API による変更をまとめて1履歴とすることができます。 今回は必要なかったのですが、変更作業の前後をセッションを適用しました
- セッションの作成は次のようなコードになります
String itemPath = '/drives/' + poTemplateDriveId
+ '/items/' + itemId;
String bookPath = itemPath + '/workbook';
res = GraphAPI.callGraphApi('POST', bookPath + '/createSession', null, '{\"persistChanges\": true}');
if (res.getStatusCode() != 201){ // created
return null;
}
resMap = (Map<String,Object>)JSON.deserializeUntyped(res.getBody());
sessionId = (String)resMap.get('id');
- 反対のセッションのクローズは次のようなコードになります。Excelブックの変更後呼び出します
res = GraphAPI.callGraphApi('POST', bookPath + '/closeSession', getHeader(), '{\"persistChanges\": true}'); // Body つけないと何故か 411 エラーになる
if (res.getStatusCode() == 204){ // no content
sessionId = null;
}
- セッション中の Graph API 呼び出しにはヘッダーにセッションIDを指定しますので、そのための関数を用意しておきます
private static Map<String, String> getHeader(){
if (sessionId != null){
return new Map<String, String>{
'workbook-session-id' => sessionId
};
} else {
return null;
}
}
4. データの転記
- この部分はそれぞれだと思いますし、あまり語ることはありませんが、いくつかエッセンスを紹介したします。実はテンプレートの Excel シートに ‘=”{objectName.fieldName}” みたいに、参照したい Salesforce の情報を記入する方法も導入しているのですが、記事が長くなるのでこちらは機会があれば別の回でご紹介させていただければと思います
- 今回ご紹介するのは、テンプレートのデータを一度かたまり(Range)で取得して、書き換える項目を変更した後、同じかたまり(Range)で書き込む方法です。これだと１つ１つのセルを書き換えるよりも API コールの回数を減らすことができますし、セルの配置が想定とあっているかどうかの Validation もできるので便利です
- まずはテンプレートで利用されているデータのレンジを取得するコード例です。 戻り値は ‘sheet1!A1:F20’ のような文字列になります
Sring sheetPath = bookPath + '/worksheets/' + EncodingUtil.urlEncode('sheet1', 'UTF-8');
String usedRange = null;
res = GraphAPI.callGraphApi('GET', sheetPath + '/usedRange', getHeader(), null);
if (res.getStatusCode() != 200){
// エラー処理
}
resMap = (Map<String,Object>)JSON.deserializeUntyped(res.getBody());
usedRange = ((String)resMap.get('address')).split('!')[1];
- 次に usedRange のデータを取得します
res = GraphAPI.callGraphApi('GET', sheetPath + '/' + EncodingUtil.urlEncode('range(address=\'' + usedRange + '\')', 'UTF-8'), getHeader(), null);
if (res.getStatusCode() != 200){
// エラー処理
}
resMap = (Map<String,Object>)JSON.deserializeUntyped(res.getBody());
usedRange_formulas = (List<Object>)resMap.get('formulasR1C1');
- 取得した Excel レンジは data[row][column] のようにアクセスしたいのですが、下のように記述するとランタイム時に conversion エラーとなってしまいます
List<List<Object>> data = (List<List<Object>>)resMap.get('formulasR1C1');
もっといい方法あるかもしれませんが、次のように2次元リストに再構築することにしました
List<Object> data_tmp = (List<Object>)resMap.get('formulasR1C1');
List<List<Object>> data = new List<List<Object>>();
for (Integer i = 0 ; i < data_tmp.size(); i++){
data.add((List<Object>)data_tmp[i]);
}
// 次のようにアクセスできる
data[4][1] = ‘HELLO’
- レンジデータの必要な個所を更新後、シートに書き込みします
String payload = JSON.serialize(new Map<String, Object>{
'formulasR1C1' => data
}, False);
res = GraphAPI.callGraphApi('PATCH', sheetPath + '/' + EncodingUtil.urlEncode('range(address=\'' + usedRange + '\')', 'UTF-8'), getHeader(), payload);
5. 作成した帳票の表示
-
データの更新を行ったら、セッションのクローズを行い、帳票作成終了です。この後の処理もいろいろだと思いますが、私のところでは作成した帳票をブラウザの別タグで開くようにしています
-
ここまでの処理は Apex で実装していますが、UI は LWC で実装しています。ので、Apex としては作成した帳票の Url を返すところで終了です
res = GraphAPI.callGraphApi('GET', itemPath, null, null);
if (res.getStatusCode() == 200){
resMap = (Map<String,Object>)JSON.deserializeUntyped(res.getBody());
return (String)resMap.get('webUrl');
}
- LWC側のサンプルコードです。createForm() が Apex側の関数で、戻り値の Url に対して window.open() するだけです。実際の挙動はブラウザや Windows の設定などにもよるかと思いますが、私の環境ではこれで前述のように別タグに Excel が開きます
async onFormCreationClick(event){
this.showSpinner = true;
createForm({
'oppId': this.recordId
})
.then(url => {
this.showSpinner = false;
if (url != null){
window.open(url);
}
});
return;
本日の記事は以上となります。ご意見ご感想アドバイスなど、コメントお待ちしております。
Discussion