Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

CData製品のインストール

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

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

CDataタブ.png

接続設定

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

Salesforceの接続設定

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

SF接続設定.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

アプリケーションを登録すると、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
※OKボタンをクリックする前に、デフォルトのブラウザでMFクラウドにログインしておいてください。MFクラウドにログインしていない状態でOKボタンをクリックすると、「タイムアウトしました。再度試してください。」というエラーダイアログが表示されます。

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

取引先情報の同期

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

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

注文.png

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

SF取引先.png

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ボタンをクリックしてデータを取得します。

SF取引先取得.png

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

SF取引先シート.png

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

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

MF取引先取得.png

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

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

MF取引先シートドラッグ.png

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

MF取引先行追加後.png

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

MF取引先追加1.png

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

MF取引先追加2.png

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

MF取引先部門取得.png

WHERE PartnerId='Sq16IlKSgttOL87PW9fECg'

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

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

MF取引先部門追加1.png

取引先を登録したときと同じように、行を選択し、今度は「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'

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

SF注文シート.png

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

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

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

MF請求書追加1.png

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

MF請求書追加2.png

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

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

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

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

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

MF請求書品目追加1.png

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

MF請求書品目追加2.png

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

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

MF請求書画面.png

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ファイルが作成されています。

請求書出力.png

請求書出力後.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

マクロ実行後
MF取引先マクロ登録後.png

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

請求書データの作成

今度は、請求書データをマクロを使って同期してみます。請求書に関するデータは、注文<->請求書 と 注文商品<->品目 に分かれており、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

SFベース注文シート.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

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

MFマクロ請求書作成後1.png

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

MFマクロ請求書作成後2.png

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした