Salesforce の注文データから MoneryForward のMFクラウド請求書(以下MFクラウド) の請求書を作成する流れを見ていきます。
CData製品のインストール
まずは CData Software をインストールします。今回必要になるのは、次の2つです。
正常にインストールされると、Excel に「CData」タブが表示されるようになります。
接続設定
Salesforce および MFクラウド への接続情報を設定します。
Salesforceの接続設定
Excel を起動し、「CData」タブの「取得元Salesforce」をクリックすると、接続ウィザードが表示されるので、必要な情報を設定します。
ここでは、パスワード、セキュリティトークン、ユーザ名を設定します。
MFクラウドの接続設定
※MFクラウド請求書のAPIは2016/11現在ではβ版の提供です
MFクラウドへの接続には OAuth がサポートされています。OAuthで接続するには、MFクラウド側で「OAuthアプリケーション」を登録する必要あるので、以下の手順で登録します。
- MFクラウドへログインする
- 画面右上の？メニューから「API連携β(開発者向け)」を開く
- 「新規作成」ボタンをクリックする
- 項目を設定して作成ボタンをクリックする
アプリケーションの名前はわかりやすいものであれば何でもかまいません。
Redirect URI には、localhost のURLを設定します。ここでは、http://localhost:33333 とします。
Scopes には write を設定します。
アプリケーションを登録すると、Client ID と Client Secret が発行されるので、この値を Excel 側に設定します。
「CData」タブの「取得元Moneyforward」をクリックして接続ウィザードを表示し、必要な情報を設定します。
設定が必要な項目は Callback URL、OAuth Client ID、OAuth Client Secret です。
Callback URL にはMFクラウド側で設定した Redirect URI を、OAuth Client ID と OAuth Client Secret には、それぞれ
MFクラウド側で発行された Client ID と Client Secret を設定します。
また、MFクラウド側のデータは追加・更新を行いたいので、「有効な操作」の更新と挿入にチェックを入れておきます。
※OKボタンをクリックする前に、デフォルトのブラウザでMFクラウドにログインしておいてください。MFクラウドにログインしていない状態でOKボタンをクリックすると、「タイムアウトしました。再度試してください。」というエラーダイアログが表示されます。
これで、Excel を使用して Salesforce および MFクラウドのデータを操作する準備が整いました。
取引先情報の同期
はじめに、請求先としての取引先情報を同期してみます。
※ここでは1件づつの手動での同期方法を紹介していますが、複数件の同期方法については後述Excelマクロによる複数件データの取り扱いを参照してください。
今回出力したい請求書の注文データは、下図の注文番号 00000100 の注文です。
この注文の取引先の詳細は下図の通りです。
Salesforce の取引先情報の取得
それでは、この取引先の情報を Excel で取得します。
「CData」タブの「取得元Salesforce」をクリックします。すでに接続情報が作成されているため、今度はデータを取得するためのデータ選択ウィザードが表示されます。
データを取得する際は、SQL文を記述して行うことになります。といっても、そう難しいことはありません。取得したい項目と条件を指定するだけです。
上図にあるように、目的の取引先の取引先番号は「A-127644」なので、これを指定してデータを取得します。これは以下のようなSQL文になります。
SELECT AccountNumber, Name, Site, BillingPostalCode, BillingState, BillingCity, BillingStreet, Phone
FROM [Account] WHERE AccountNumber='A-127644'
このSQL文を指定し、シート名は「取引先」にしてOKボタンをクリックしてデータを取得します。
すると、「取引先」シートが作成され、Salesforce に登録されている取引先の情報が1行書き込まれている状態になります。
MFクラウドの取引先情報の登録
次にこの取引先の情報を、MFクラウド側に登録します。そのためにはまず、MFクラウド側の取引先のシートを作成します。
「CData」タブの「取得元Moneyforward」をクリックしてデータ選択ウィザードを表示し、「テーブル」の項目で Partners を選択します。
デフォルトでは100件取得されますが、そんなに取得する必要はないので「クエリー」の部分の LIMIT 100 を LIMIT 1 に変更してOKボタンをクリックします。
すると先程の Salesforce の取引先データを取得したときと同様、新しくシートが作成されてそこにMFクラウドの取引先データが1件表示されている状態になります。このシートを利用して、MFクラウドに新しい取引先情報を登録します。
データを追加するためには、シートの右側まで移動して、枠の右下を下にドラッグします。
すると、水色の枠で囲まれたエリアが2行になります。
そして対応する項目を「取引先」シートからコピーしてきます。MFクラウドでは、取引先の情報は「取引先」とその「部門」に分かれているため、ここでは顧客ID(Code)と取引先名(Name)だけを設定します。なおこのとき、セルを コピー→貼り付け する際は、「値で貼り付け」るようにします。
変更された部分は赤字になって表示されます。
あとは追加した行を選択し、「CData」タブの「行の挿入」を行います。確認ダイアログが表示されるのでOKボタンをクリックします。
登録が成功すると、下図のようにId等が振られ、先程赤字だった部分が黒字になります。
同様にして、取引先の部門の情報を登録します。
取引先の部門のテーブルは PartnersDepartments になるので、取引先のときと同じようにデータ選択ウィザードを使用して PartnersDepartments のシートを作成します。ただし部門は取引先の子データとなるため、先程作成した取引先のIdを指定して取得します。
WHERE PartnerId='Sq16IlKSgttOL87PW9fECg'
上記で取引先を指定しています。この PartnerId の値は、Partners シートのId列からコピーすればOKです。
MFクラウドでは、取引先を作成すると空の部門も1つ作成されるので、作成されたシートにはすでに1行分のデータが存在しています。ここに必要な情報を設定し、更新を行います。
取引先を登録したときと同じように、行を選択し、今度は「CData」タブの「行の更新」を行います。正常に更新できれば、変更したセルの赤字が黒字になります。
これで、MFクラウドにアクセスすることなく、Excelを使用して取引先の情報をMFクラウドに登録することができました。
注文データからの請求書データの作成
ここからは、注文データと請求書データの連携を行います。今回は以下のような対応で作成します。
| Salesforce | MFクラウド |
|---|---|
| 注文 (Order) | 請求書 (Billings) |
| 注文商品 (OrderItem) | 請求書-品目 (BillingsItems) |
Salesforce の注文データの取得
ここでは少しテクニックを使って、注文データと注文商品データを一括で取得してみます。
データ選択ウィザードに指定するSQL文には、SQLに慣れている方ならお馴染みのテーブル結合を使用することができます。そのため、以下のようなクエリを指定することが可能です。
SELECT t1.OrderNumber, t3.Name, t2.Quantity, t2.UnitPrice
FROM [Order] as t1
INNER JOIN [OrderItem] as t2 ON t2.OrderId=t1.Id
INNER JOIN [PricebookEntry] as t3 ON t3.Id=t2.PricebookEntryId
WHERE t1.OrderNumber='00000100'
このクエリを使用して、「注文」シートを作成します。
MFクラウドの請求書データの作成
取引先のデータを登録したときと同様、作成対象のテーブルのシートが必要となるので、Billingsのシートを作成します。シート名はデフォルトのままで、取得する件数は100から1に変更します。
Billingsシートを作成したら、枠をドラッグして行を増やします。
請求書データを作成する際に必要になるのが請求先(→取引先)なので、その情報を設定します。このとき、取引先ではなく、その取引先の部門のIdを指定する必要があるという点に注意してください。部門のIdは、PartnersDepartmentsシートを参照すればわかります。
部門の指定は DepartmentId 列なので、この列に部門のIdを指定して、行の挿入を行います。
MFクラウドの品目データの作成
最後に品目データを作成します。品目は、取引先における部門のように、請求書の子供となるデータのため、親の請求書のIdを指定してシートを作成します。クエリは以下のようになります。
SELECT * FROM [BillingsItems] WHERE BillingId='gvnyxhLm_vmi2GlLx8ySVg'
ここで指定する BillingId は、上で作成済みの請求書データのIdです。このクエリを使用して BillingsItems シートを作成します。
請求書を作成すると、デフォルトで空の品目が作成されるため、BillingsItems シートにはすでに1行のデータが存在しています。
今回の注文データの注文商品データは2件のため、枠をドラッグして行を2行にします。
あとは、あらかじめ作成しておいた「注文」シートから、品目名、数量、単価をコピーしてきます。
品目は請求書の子データとなるため、追加した行については「親はどれか」を指定するための BillingId の指定が必要になります。
これは単に1行目のものをコピーすればOKです。
1行目はすでに存在する品目のため行の更新、2行目は新しく追加した行なので行の挿入を行います。
これでMFクラウドの請求書データが作成されました。
MFクラウドの方で見てみると、上図の通り請求書が作成されていることを確認できます。
請求書の出力
MFクラウドには請求書をPDFで出力する機能がありますが、簡単なマクロを記述をすることで、Excel から出力することも可能です。
標準モジュールを追加して、以下のプロシージャを記述します。
<CallbackUrl>、<OAuthClientId>、<OAuthClientSecret> には、MFクラウドの接続設定 で接続ウィザードに設定したものを指定します。
"C:\temp\billing" はPDFファイルの出力先になります。任意のフォルダを指定できます。
Sub 請求書出力()
Dim module
Set module = CreateObject("CData.ExcelAddIn.ExcelComModule")
module.SetProviderName ("Moneyforward")
module.SetConnectionString ("CallbackUrl=<CallbackUrl>;OAuthClientId=<OAuthClientId>;OAuthClientSecret=<OAuthClientSecret>")
Dim nameArray
nameArray = Array("BillId", "LocalPath")
Dim valueArray
valueArray = Array(Selection.Value, "C:\temp\billing")
Call module.CallSP("DownloadBill", nameArray, valueArray)
End Sub
使用方法は、Billingsシートで出力したい請求書のIdを選択した状態で、追加した「請求書出力」マクロを実行するだけです。
成功すると、指定したフォルダにファイル名が請求書IdになっているPDFファイルが作成されています。
Excelマクロによる複数件データの取り扱い
ここまでの手順では、1件の請求情報についてExcel上で操作する方法をご紹介しましたが、Excelマクロを使用すると、関連性を持つ複数件のデータの取り扱いを自動化することができます。
取引先情報の同期
ここでは、マクロを使用してすべての取引先データを同期してみたいと思います。
マクロで取引先情報を同期する際も、行うことは手動で行ったことと本質的には変わりありません。
まずはSalesforceの取引先情報を取得します。
module.SetProviderName ("Salesforce")
module.SetConnectionString ("User=<User>;Password=<Password>;Security Token=<Token>;")
Dim nameArray
nameArray = Array()
Dim valueArray
valueArray = Array()
If module.Select("SELECT AccountNumber, Name, Site, BillingPostalCode, BillingState, BillingCity, BillingStreet, Phone FROM Account", nameArray, valueArray) Then
Dim recordSet()
Dim recordCount
recordCount = 0
While (Not module.EOF)
ReDim Preserve recordSet(recordCount)
Dim record()
ReDim record(module.GetColumnCount())
For i = 0 To module.GetColumnCount() - 1
record(i) = module.GetValue(i)
Next
recordSet(recordCount) = record
recordCount = recordCount + 1
module.MoveNext
Wend
End If
上記により、変数 recordSet にはSalesforce側の取引先情報が格納されます。これを使用して、次にMFクラウド側の取引先情報および部門情報を登録します。
module.SetProviderName ("Moneyforward") ' 接続先をMFクラウドに変更
module.SetConnectionString ("CallbackUrl=<CallbackUrl>;OAuthClientId=<OAuthClientId>;OAuthClientSecret=<OAuthClientSecret>")
Dim partnerCodeMap ' 同じ取引先を重複して登録しないために使用するマップ
Set partnerCodeMap = CreateObject("Scripting.Dictionary")
If module.Select("SELECT Code FROM Partners", nameArray, valueArray) Then
While (Not module.EOF)
partnerCodeMap(module.GetValue(0)) = 1
module.MoveNext
Wend
End If
For i = 0 To UBound(recordSet) - 1
record = recordSet(i)
If Not partnerCodeMap.Exists(record(0)) Then
nameArray = Array("code", "name")
valueArray = Array(record(0), record(1))
Call module.Insert("INSERT INTO Partners(Code, Name) VALUES (@code, @name)", nameArray, valueArray)
Call module.Select("SELECT Id FROM Partners WHERE Code=@code", nameArray, valueArray)
Dim partnerId
partnerId = module.GetValue(0)
nameArray = Array("partnerId")
valueArray = Array(partnerId)
Call module.Select("SELECT Id FROM PartnersDepartments WHERE PartnerId=@partnerId", nameArray, valueArray)
Dim id
id = module.GetValue(0)
nameArray = Array("id", "partnerId", "name", "zip", "tel", "prefecture", "addr1", "addr2")
valueArray = Array(id, partnerId, record(2), record(3), record(7), record(4), record(5), record(6))
Call module.Update("UPDATE PartnersDepartments SET Name=@name, Zip=@zip, Tel=@tel, Prefecture=ISNULL(@prefecture, ''), Address1=@addr1, Address2=@addr2 WHERE Id=@id AND PartnerId=@partnerId", nameArray, valueArray)
End If
Next
これを実行すると、Salesforce側に登録されている取引先がすべてMFクラウド側に登録されます。
マクロ実行前
マクロ実行後
複数件の取引先が、部門情報も込みで登録されていることが確認できます。
請求書データの作成
今度は、請求書データをマクロを使って同期してみます。請求書に関するデータは、注文<->請求書 と 注文商品<->品目 に分かれており、Excelシート上で操作するのは少し複雑ですが、マクロを使用するとこれも一括で同期させることができます。
ここでは、手動でSalesforce側の注文データをExcelシートに読み込み、その中から請求書データを作成したいものを選択して、マクロで請求書データを作成するということを行ってみます。
以下のSQL文を使用して、ベースとなる注文情報シートを作成します。シート名は「注文」にします。
SELECT 0 AS 選択, t1.OrderNumber, t2.AccountNumber, t2.Name, t1.EffectiveDate, t1.TotalAmount
FROM [Order] as t1
INNER JOIN [Account] as t2 ON t2.Id=t1.AccountId
このA列に「1」を設定した注文について、請求データを作成するマクロを組みます。
CDataモジュールを生成します。Saleforce用とMFクラウド用と2つ作ってしまいます。
Dim moduleSF
Set moduleSF = CreateObject("CData.ExcelAddIn.ExcelComModule")
moduleSF.SetProviderName ("Salesforce")
moduleSF.SetConnectionString ("User=<User>;Password=<Password>;Security Token=<Token>;")
Dim moduleMF
Set moduleMF = CreateObject("CData.ExcelAddIn.ExcelComModule")
moduleMF.SetProviderName ("Moneyforward")
moduleMF.SetConnectionString ("CallbackUrl=<CallbackUrl>;OAuthClientId=<OAuthClientId>;OAuthClientSecret=<OAuthClientSecret>")
パラメタ名とパラメタ値を格納するための配列、それからデータの行数を取得しておきます。
Dim nameArray
Dim valueArray
Dim lastRow
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
データの開始行である2行目から最終行までループして、「選択」列に 1 がたっているものを探し、請求書データを作成します。
For i = 2 To lastRow
If Cells(i, 1).Value = 1 Then
nameArray = Array("accountNumber")
valueArray = Array(Cells(i, 3).Value)
Call moduleMF.Select("SELECT Id FROM Partners WHERE Code=@accountNumber", nameArray, valueArray)
Dim partnerId
partnerId = moduleMF.GetValue(0)
nameArray = Array("partnerId")
valueArray = Array(partnerId)
Call moduleMF.Select("SELECT Id FROM PartnersDepartments WHERE PartnerId=@partnerId", nameArray, valueArray)
Dim deptId
deptId = moduleMF.GetValue(0)
nameArray = Array("deptId")
valueArray = Array(deptId)
Call moduleMF.Insert("INSERT INTO Billings(DepartmentId) VALUES (@deptId)", nameArray, valueArray)
Call moduleMF.Select("SELECT Id, BillingNumber FROM Billings ORDER BY CreatedAt DESC LIMIT 1", nameArray, valueArray)
Dim billingId
billingId = moduleMF.GetValue(0)
nameArray = Array("billingId")
valueArray = Array(billingId)
Call moduleMF.Select("SELECT Id FROM BillingsItems WHERE BillingId=@billingId", nameArray, valueArray)
Dim billingsItemId
billingsItemId = moduleMF.GetValue(0)
' デフォルトで作成されている品目は削除してしまう
nameArray = Array("id", "billingId")
valueArray = Array(billingsItemId, billingId)
Call moduleMF.Delete("DELETE FROM BillingsItems WHERE Id=@id AND BillingId=@billingId", nameArray, valueArray)
nameArray = Array("orderNumber")
valueArray = Array(Cells(i, 2).Value)
Call moduleSF.Select("SELECT t3.Name, t2.Quantity, t2.UnitPrice, t1.OrderNumber FROM [Order] as t1 INNER JOIN [OrderItem] as t2 ON t2.OrderId=t1.Id INNER JOIN [PricebookEntry] as t3 ON t3.Id=t2.PricebookEntryId WHERE t1.OrderNumber=@orderNumber", nameArray, valueArray)
While Not moduleSF.EOF
nameArray = Array("billingId", "name", "quantity", "unit")
valueArray = Array(billingId, moduleSF.GetValue(0), moduleSF.GetValue(1), moduleSF.GetValue(2))
Call moduleMF.Insert("INSERT INTO BillingsItems(BillingId, Name, Quantity, Unit_Price) VALUES(@billingId, @name, @quantity, @unit)", nameArray, valueArray)
moduleSF.MoveNext
Wend
End If
Next
コードは以上です。下図の3件のデータを選択して、このマクロを実行してみます。
実行後、MFクラウド側で請求書を確認すると、
選択した3件の請求書が作成されていることが確認できます。
品目も追加され、単価と数量が正しく設定されていることを確認できます。