Salesforce の注文データで MFクラウド請求書(MoneyForward) から請求書を出力する方法 (詳細)

Salesforce の注文データから MoneryForward のMFクラウド請求書(以下MFクラウド) の請求書を作成する流れを見ていきます。

# CData製品のインストール
まずは CData Software をインストールします。今回必要になるのは、次の2つです。

* [Excel Add-In for Salesforce](http://www.cdata.com/jp/drivers/salesforce/excel/ "Excel Add-In for Salesforce")
* [Excel Add-In for Money Forward](http://www.cdata.com/jp/drivers/moneyforward/excel/ "Excel Add-In for Money Forward")

正常にインストールされると、Excel に「CData」タブが表示されるようになります。

![CDataタブ.png](https://qiita-image-store.s3.amazonaws.com/0/141193/16e805f0-0f95-bcb4-db6a-5b2d7257288c.png)

# 接続設定

Salesforce および MFクラウド への接続情報を設定します。

## Salesforceの接続設定

Excel を起動し、「CData」タブの「取得元Salesforce」をクリックすると、接続ウィザードが表示されるので、必要な情報を設定します。
ここでは、パスワード、セキュリティトークン、ユーザ名を設定します。

![SF接続設定.png](https://qiita-image-store.s3.amazonaws.com/0/141193/732f3458-d989-d2fe-c4bb-8177292cc13f.png)

## MFクラウドの接続設定
_※MFクラウド請求書のAPIは2016/11現在ではβ版の提供です_

MFクラウドへの接続には OAuth がサポートされています。OAuthで接続するには、MFクラウド側で「OAuthアプリケーション」を登録する必要あるので、以下の手順で登録します。

1. MFクラウドへログインする
2. 画面右上の？メニューから「API連携β(開発者向け)」を開く
3. 「新規作成」ボタンをクリックする
4. 項目を設定して作成ボタンをクリックする

アプリケーションの名前はわかりやすいものであれば何でもかまいません。
Redirect URI には、localhost のURLを設定します。ここでは、http://localhost:33333 とします。
Scopes には write を設定します。

![OAuth登録.png](https://qiita-image-store.s3.amazonaws.com/0/141193/f5637977-e398-3801-8cee-7886f27f580f.png)

アプリケーションを登録すると、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クラウド側のデータは追加・更新を行いたいので、「有効な操作」の更新と挿入にチェックを入れておきます。

![MF接続設定.png](https://qiita-image-store.s3.amazonaws.com/0/141193/2a98b14e-81f7-afbe-05ae-764258f56749.png)
※OKボタンをクリックする前に、デフォルトのブラウザでMFクラウドにログインしておいてください。MFクラウドにログインしていない状態でOKボタンをクリックすると、「タイムアウトしました。再度試してください。」というエラーダイアログが表示されます。

これで、Excel を使用して Salesforce および MFクラウドのデータを操作する準備が整いました。

# 取引先情報の同期

はじめに、請求先としての取引先情報を同期してみます。
※ここでは1件づつの手動での同期方法を紹介していますが、複数件の同期方法については後述[Excelマクロによる複数件データの取り扱い](#excelマクロによる複数件データの取り扱い)を参照してください。

今回出力したい請求書の注文データは、下図の注文番号 00000100 の注文です。

![注文.png](https://qiita-image-store.s3.amazonaws.com/0/141193/9ee13763-98ac-7a31-cd06-35d510344be8.png)

この注文の取引先の詳細は下図の通りです。

![SF取引先.png](https://qiita-image-store.s3.amazonaws.com/0/141193/51f30470-7cfe-1bac-ab1b-8021fc1bd221.png)

## Salesforce の取引先情報の取得
それでは、この取引先の情報を Excel で取得します。

「CData」タブの「取得元Salesforce」をクリックします。すでに接続情報が作成されているため、今度はデータを取得するためのデータ選択ウィザードが表示されます。

データを取得する際は、SQL文を記述して行うことになります。といっても、そう難しいことはありません。取得したい項目と条件を指定するだけです。
上図にあるように、目的の取引先の取引先番号は「A-127644」なので、これを指定してデータを取得します。これは以下のようなSQL文になります。

```sql
SELECT AccountNumber, Name, Site, BillingPostalCode, BillingState, BillingCity, BillingStreet, Phone
FROM [Account] WHERE AccountNumber='A-127644'
```

このSQL文を指定し、シート名は「取引先」にしてOKボタンをクリックしてデータを取得します。

![SF取引先取得.png](https://qiita-image-store.s3.amazonaws.com/0/141193/b59c5151-11e1-1cca-e9c4-8c94e57e6f87.png)

すると、「取引先」シートが作成され、Salesforce に登録されている取引先の情報が1行書き込まれている状態になります。

![SF取引先シート.png](https://qiita-image-store.s3.amazonaws.com/0/141193/a115b934-d048-e6bc-ef40-df5459e7baf4.png)

## MFクラウドの取引先情報の登録

次にこの取引先の情報を、MFクラウド側に登録します。そのためにはまず、MFクラウド側の取引先のシートを作成します。
「CData」タブの「取得元Moneyforward」をクリックしてデータ選択ウィザードを表示し、「テーブル」の項目で Partners を選択します。
デフォルトでは100件取得されますが、そんなに取得する必要はないので「クエリー」の部分の LIMIT 100 を LIMIT 1 に変更してOKボタンをクリックします。

![MF取引先取得.png](https://qiita-image-store.s3.amazonaws.com/0/141193/d90d6331-8df8-595f-7912-5158a72a6043.png)

すると先程の Salesforce の取引先データを取得したときと同様、新しくシートが作成されてそこにMFクラウドの取引先データが1件表示されている状態になります。このシートを利用して、MFクラウドに新しい取引先情報を登録します。

データを追加するためには、シートの右側まで移動して、枠の右下を下にドラッグします。

![MF取引先シートドラッグ.png](https://qiita-image-store.s3.amazonaws.com/0/141193/e880b191-2bb4-130d-1408-8b247eaf2e09.png)

すると、水色の枠で囲まれたエリアが2行になります。

![MF取引先行追加後.png](https://qiita-image-store.s3.amazonaws.com/0/141193/34f48041-e9a5-d699-ac4d-b6b7bae39789.png)

そして対応する項目を「取引先」シートからコピーしてきます。MFクラウドでは、取引先の情報は「取引先」とその「部門」に分かれているため、ここでは顧客ID(Code)と取引先名(Name)だけを設定します。なおこのとき、セルを コピー→貼り付け する際は、「値で貼り付け」るようにします。
変更された部分は赤字になって表示されます。

![MF取引先追加1.png](https://qiita-image-store.s3.amazonaws.com/0/141193/623a244e-f72b-8a79-11ac-0694151cc43c.png)

あとは追加した行を選択し、「CData」タブの「行の挿入」を行います。確認ダイアログが表示されるのでOKボタンをクリックします。
登録が成功すると、下図のようにId等が振られ、先程赤字だった部分が黒字になります。

![MF取引先追加2.png](https://qiita-image-store.s3.amazonaws.com/0/141193/598a8523-ec45-ca4a-feaa-762c8e11e1c3.png)

同様にして、取引先の部門の情報を登録します。
取引先の部門のテーブルは PartnersDepartments になるので、取引先のときと同じようにデータ選択ウィザードを使用して PartnersDepartments のシートを作成します。ただし部門は取引先の子データとなるため、先程作成した取引先のIdを指定して取得します。

![MF取引先部門取得.png](https://qiita-image-store.s3.amazonaws.com/0/141193/03fdc257-78ec-331c-b2f3-6450814b3de8.png)

```sql
WHERE PartnerId='Sq16IlKSgttOL87PW9fECg'
```
上記で取引先を指定しています。この PartnerId の値は、Partners シートのId列からコピーすればOKです。

MFクラウドでは、取引先を作成すると空の部門も1つ作成されるので、作成されたシートにはすでに1行分のデータが存在しています。ここに必要な情報を設定し、更新を行います。

![MF取引先部門追加1.png](https://qiita-image-store.s3.amazonaws.com/0/141193/6f59e9ca-5dda-4e1a-73dd-2755da78f700.png)

取引先を登録したときと同じように、行を選択し、今度は「CData」タブの「行の更新」を行います。正常に更新できれば、変更したセルの赤字が黒字になります。

これで、MFクラウドにアクセスすることなく、Excelを使用して取引先の情報をMFクラウドに登録することができました。

# 注文データからの請求書データの作成

ここからは、注文データと請求書データの連携を行います。今回は以下のような対応で作成します。

|Salesforce|MFクラウド|
|:---------|:--------|
| 注文 (Order)| 請求書 (Billings)|
| 注文商品 (OrderItem)|請求書-品目 (BillingsItems)|

## Salesforce の注文データの取得

ここでは少しテクニックを使って、注文データと注文商品データを一括で取得してみます。
データ選択ウィザードに指定するSQL文には、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'
```

このクエリを使用して、「注文」シートを作成します。

![SF注文シート.png](https://qiita-image-store.s3.amazonaws.com/0/141193/b00308e8-0f3f-34a4-5db5-736cf9477366.png)

## MFクラウドの請求書データの作成

取引先のデータを登録したときと同様、作成対象のテーブルのシートが必要となるので、Billingsのシートを作成します。シート名はデフォルトのままで、取得する件数は100から1に変更します。

Billingsシートを作成したら、枠をドラッグして行を増やします。

![MF請求書追加1.png](https://qiita-image-store.s3.amazonaws.com/0/141193/331d4ea4-13df-967c-d65d-3031019c4d9b.png)

請求書データを作成する際に必要になるのが請求先(→取引先)なので、その情報を設定します。このとき、取引先ではなく、その取引先の部門のIdを指定する必要があるという点に注意してください。部門のIdは、PartnersDepartmentsシートを参照すればわかります。
部門の指定は DepartmentId 列なので、この列に部門のIdを指定して、行の挿入を行います。

![MF請求書追加2.png](https://qiita-image-store.s3.amazonaws.com/0/141193/cffbf173-c5ba-9666-03c9-ec89776389c6.png)

## MFクラウドの品目データの作成

最後に品目データを作成します。品目は、取引先における部門のように、請求書の子供となるデータのため、親の請求書のIdを指定してシートを作成します。クエリは以下のようになります。

```sql
SELECT * FROM [BillingsItems] WHERE BillingId='gvnyxhLm_vmi2GlLx8ySVg'
```

ここで指定する BillingId は、上で作成済みの請求書データのIdです。このクエリを使用して BillingsItems シートを作成します。
請求書を作成すると、デフォルトで空の品目が作成されるため、BillingsItems シートにはすでに1行のデータが存在しています。

今回の注文データの注文商品データは2件のため、枠をドラッグして行を2行にします。

![MF請求書品目追加1.png](https://qiita-image-store.s3.amazonaws.com/0/141193/4ddbc707-44d7-2c89-a567-8445e2ec7ddf.png)

あとは、あらかじめ作成しておいた「注文」シートから、品目名、数量、単価をコピーしてきます。
品目は請求書の子データとなるため、追加した行については「親はどれか」を指定するための BillingId の指定が必要になります。
これは単に1行目のものをコピーすればOKです。

![MF請求書品目追加2.png](https://qiita-image-store.s3.amazonaws.com/0/141193/7b806031-bcb9-769e-4a6b-66270e7ca204.png)

1行目はすでに存在する品目のため行の更新、2行目は新しく追加した行なので行の挿入を行います。

これでMFクラウドの請求書データが作成されました。

![MF請求書画面.png](https://qiita-image-store.s3.amazonaws.com/0/141193/caf50ace-2531-f202-7c49-9d48304d643b.png)

MFクラウドの方で見てみると、上図の通り請求書が作成されていることを確認できます。

# 請求書の出力

MFクラウドには請求書をPDFで出力する機能がありますが、簡単なマクロを記述をすることで、Excel から出力することも可能です。

標準モジュールを追加して、以下のプロシージャを記述します。
\<CallbackUrl>、\<OAuthClientId>、\<OAuthClientSecret> には、[MFクラウドの接続設定](#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ファイルが作成されています。

![請求書出力.png](https://qiita-image-store.s3.amazonaws.com/0/141193/d09a3dd4-4651-5710-b6d8-d411839613ee.png)


![請求書出力後.png](https://qiita-image-store.s3.amazonaws.com/0/141193/239f96ca-0bea-9c02-8376-ae1f791641d9.png)

# 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クラウド側に登録されます。

マクロ実行前
![MF取引先マクロ登録前.png](https://qiita-image-store.s3.amazonaws.com/0/141193/e762c8c2-0d82-78c1-30c4-67eee3accb22.png)

マクロ実行後
![MF取引先マクロ登録後.png](https://qiita-image-store.s3.amazonaws.com/0/141193/47b08c27-eaeb-8111-2fcb-a47a52568d9b.png)

複数件の取引先が、部門情報も込みで登録されていることが確認できます。

## 請求書データの作成
今度は、請求書データをマクロを使って同期してみます。請求書に関するデータは、注文<->請求書 と 注文商品<->品目 に分かれており、Excelシート上で操作するのは少し複雑ですが、マクロを使用するとこれも一括で同期させることができます。

ここでは、手動でSalesforce側の注文データをExcelシートに読み込み、その中から請求書データを作成したいものを選択して、マクロで請求書データを作成するということを行ってみます。

以下のSQL文を使用して、ベースとなる注文情報シートを作成します。シート名は「注文」にします。

```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
```

![SFベース注文シート.png](https://qiita-image-store.s3.amazonaws.com/0/141193/e8074c47-4729-f027-c187-f267ad793908.png)

この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件のデータを選択して、このマクロを実行してみます。

![SFベース注文シート選択中.png](https://qiita-image-store.s3.amazonaws.com/0/141193/9e2bc61e-1b8b-915f-1db0-3c0188a76241.png)

実行後、MFクラウド側で請求書を確認すると、

![MFマクロ請求書作成後1.png](https://qiita-image-store.s3.amazonaws.com/0/141193/b1203d2b-7d02-7cd3-ded9-b179ec42eeb5.png)

選択した3件の請求書が作成されていることが確認できます。

![MFマクロ請求書作成後2.png](https://qiita-image-store.s3.amazonaws.com/0/141193/e835bc37-68d0-3b5a-7fd7-d4f764d112c8.png)

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