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件の請求書が作成されていることが確認できます。

品目も追加され、単価と数量が正しく設定されていることを確認できます。