Google Cloud Functions で作った源泉所得税算出API（＋社会保険料API）をXML対応してEXCEL関数(WEBSERVICE, FILTERXML)で値を取得してみた

更新履歴

• 2019/6/21 Googleスプレッドシートの場合の例を追加しました。

要約

• Google Cloud Functions に源泉所得税算出用APIを作成、XMLで値を返せるようにした
• EXCEL関数で源泉所得税算出用API からXMLを取得(WEBSERVICE関数)して、XMLから抽出(FILTERXML)した値をカラムに設定
• 給与計算が楽＆確実になったぜ！やりぃ

やったこと

以前、Google Cloud Functions で源泉所得税と社会保険料（協会けんぽ、東京）をJSONで取得するAPIを作ってみた(gistリンク付き)
という記事を書きました。イマドキのREST APIっぽくjsonで値を返していましたが、EXCELやgoogle spreadsheetの標準関数では json がパース出来ないことが判明。（というか意識してなかった）
EXCEL上で気軽に値を取得できるようになれば、給与計算（チェック）が楽になるなーと思ってたところ、EXCEL関数に WEBSERVICE / FILTERXML があることを知り、レスポンスの型を指定してXMLも返せるようにして、それらの関数を使って値を取得してみました。

上記の文は、要約で書いたことから何一つ情報が増えてない気がするけど、気を取り直して説明してみます。

源泉所得税APIの修正(XMLを返せるようにする)

jsonしか返せなかった悲しき REST API に、formatを与えると xml も返せるようにしました。

修正内容はこんな感じです（gistのrevisionを1つだけ表示する方法が分からず画像で失礼します。。）

なんてことはなく、単に

• format パラメータを新たに受け付ける。
• format=xml ならxmlを返す/ それ以外は json で返す（ライブラリ入れるのが面倒なので文字列でXML編集する荒業を使いました）

としただけです。

例として以下のようなリクエストを実行すると

https://us-central1-tsunagi-192005.cloudfunctions.net/getIncomeTax2018?salary=471012&numberOfDependents=0&type=a&format=xml

以下のようなレスポンスが取得できました。

<?xml version="1.0" encoding="UTF-8"?>
<data>
    <tax>
        <basic>24990</basic>
        <additional>0</additional>
        <deduction>0</deduction>
        <total>24990</total>
    </tax>
</data>

EXCELで REST APIを叩いてXMLを取得する

EXCEL 2003から WEBSERVICE関数というものが標準で用意されるようになったそうです。
こちらを使って、以下のような関数をEXCELのカラムに設定します。

=WEBSERVICE("https://us-central1-tsunagi-192005.cloudfunctions.net/getIncomeTax2018?salary=471012&numberOfDependents=0&type=a&format=xml")

すると、関数を設定したカラム上に、上記のXMLが表示することが確認できます。

EXCEL で XMLをパースして値を取得する

EXCEL 2003から？、FILTERXMLという関数が標準で用意されるようになったそうです。
こちらを使って、以下のような関数をEXCELのカラムに設定します。

=FILTERXML(【WEBSERVICE関数を設定したカラム番号】, "//total")

第二引数はXPath形式で書く必要があるそうです。
結果として、関数を定義したカラムには total の値である 24990 が設定されます。

蛇足：組み合わせて１カラムで

ここまで来たら説明する必要もあまりない気がしますが、特段 XML を鑑賞する趣味がない方は、以下のように二つの関数を組み合わせて、値だけを取得してあげてください。

=FILTERXML(WEBSERVICE("https://us-central1-tsunagi-192005.cloudfunctions.net/getIncomeTax2018?salary=471012&numberOfDependents=0&type=a&format=xml"), "//total")

追加：Google スプレッドシートの場合

Googleスプレッドシートで同じことをやる場合、 IMPORTXML という１つの関数で表現できる。

=IMPORTXML("https://us-central1-tsunagi-192005.cloudfunctions.net/getIncomeTax2018?salary=471012&numberOfDependents=0&type=a&format=xml","//total")

やったこと（追加）：社会保険料（協会けんぽ）APIのXML対応

同様に社会保険料API（協会けんぽ用）のXML対応もしてみた。

以下のように format=xml をパラメータに追加することで、XMLのレスポンスを得られます。

GET https://us-central1-tsunagi-192005.cloudfunctions.net/getSocialInsurance201804?salary=300000&kaigo=0&format=xml

<?xml version="1.0" encoding="UTF-8"?>
<data>
    <standardRemuneration>300000</standardRemuneration>
    <insurance>
        <grade>22</grade>
        <payment>14850</payment>
    </insurance>
    <pension>
        <grade>19</grade>
        <payment>27450</payment>
    </pension>
</data>

注意：こちら、平成31年の更新は行っていません。介護保険料のみ変わっており、自社に対象がいないからという、完全な自社都合なのですが、ご使用いただく際はご承知おきください。

まとめ

• イマドキ XML？いやいやそんなの必要ないでしょjsonで十分でしょ、って日頃考えがちな自分の戒めになりました（懲りてないけど）
• 流行り廃りではなく、ユースケースに応じて必要なフォーマットや技術を選択してあげようと思います（できればXMLは扱いたくないけど）
• WEBで完結することに拘らないで、必要な技術を必要なところにうまく使っていきたい