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リンク付き)
](https://qiita.com/tbtbt/items/0d0af258dbfbcc6bfe66) という記事を書きました。イマドキのREST APIっぽくjsonで値を返していましたが、EXCELやgoogle spreadsheetの標準関数では json がパース出来ないことが判明。（というか意識してなかった）
EXCEL上で気軽に値を取得できるようになれば、給与計算（チェック）が楽になるなーと思ってたところ、EXCEL関数に WEBSERVICE / FILTERXML があることを知り、レスポンスの型を指定してXMLも返せるようにして、それらの関数を使って値を取得してみました。

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

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

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

修正内容はこんな感じです（gistのrevisionを1つだけ表示する方法が分からず画像で失礼します。。）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/127179/055a18d6-e0da-7f42-00c9-3a0f334158c0.png)

なんてことはなく、単に 

- 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で完結することに拘らないで、必要な技術を必要なところにうまく使っていきたい