個人開発で「Excel専用のWebAPI」を作りました
どういうもの？
このように関数を使ってデータを取り込めます。
背景
元々は「インターネット上のWebAPIのデータをExcelに取り込みたい」と思ったのがきっかけです。
VBAマクロを使えば簡単ではありますが、マクロは使いたくないという方も多いのではないでしょうか。かくいう私も勤務先が属人化を防ぐためにマクロの使用を推奨していません。
そこで、関数を使用する方法を選択しました。
ExcelにはWEBSERVICE 関数という指定したURLの応答データを取り込む関数があります。
しかし、この関数は大変便利な関数ですが、ExcelはHTMLやJSONのパーサーを用意していません。結局取り込んだHTMLやJSONの処理にはVBAマクロが必要になります。
また、欲しいWebAPIが存在しない問題もありました。
そこで、Excelでデータを編集するのではなく、元々のWebAPIをExcel用に作ればいいと考えて開発しました。
サービス概要
サービス内容としては「WEBSERVICE関数でExcelが処理できる形式のデータをインターネット経由で提供するWebAPI」です。長い。
サイト上で様々なWebAPIを提供し、それをWEBSERVICE関数でExcelに取り込むことを目指しています。
サイト名は「ExcelAPI」と名付けました。
タイトルで「Excel専用」と書きましたが、GoogleSpreadseetなどExcel以外からでも使用できます。技術的には平文のWebAPIのためです。しかし、入力パラメーターがExcelの形式だったりするのでExcel向けではあります。
提供しているWebAPIを見て頂いた方が分かりやすいと思いますので、ここからは具体的な提供サービスの紹介をします。
提供サービス
実は検索などから流入した早い段階から使用してくださっているユーザーの方々がいます。
その方々がどういった機能を主に使用しているか、アクセスランキング上位をご紹介します。
JSON形式のWebAPIをExcel形式に変換する機能
メイン機能です。
ですがアクセスランキングは6位であまり使われていません。（悲しい）
サーバーでJSONを解析し、指定された項目だけを返します。
Excel形式とは平文のことで、平文と言っても分かりにくいので、あえてこのような表現を使用しています。
少々分かりにくいかもしれませんが、A列がJSONを返すWebAPIで、B列でパースするターゲットを指定しています。
※ A列に入力されているURLは国土交通省が提供している「都道府県内市区町村一覧取得API」になります。
技術的にはJSONをパースしているだけですので特筆することは無いのですが、この機能はJSONを返すWebAPIなら全て使用できるため、最も汎用性が高い機能です。
例えば、Googleのルート検索APIと連携して駅間の所要時間をまとめたりもできます。（不動産価値の潜在性調査に使用しました）
また、デフォルトではキャッシュを使用するため、有料のWebAPIを何度も叩いても最低限の費用しかかからないようにできるメリットもあります。
1項目を取得するたびに有料APIを使用していたら料金が跳ね上がる懸念があるためです。
とりあえず、これだけで大抵のWebAPIが使えるようになります。
推しの機能ですので、ぜひ使ってみてください。
郵便番号 ⇔ 住所を変換する機能
アクセスランキング1位です。
郵便番号と住所の変換というのは実はかなり面倒です。
いわゆるKEN_ALL問題というものがあり、これを考慮していないと品質が低くなります。
巷に溢れているExcelの手順では、品質の低い外部アドインやマクロを紹介していることが多いため、そのまま使用すると問題になりかねません。
KEN_ALL問題の例：
※日本郵政からは、このように複数の候補が酷いフォーマットで提供されています
この問題を解決した優秀なサービスもありますが、それらは有料であり仕様もExcelでの使用が考慮されていません。
そのため、ExcelAPIではKEN_ALL問題を解決したデータを作成し、Excelの仕様に合わせた形式で提供しています。
※ Excel以外からでも無料で品質の良いAPIとしても使用できると思います。
郵便番号から住所：
住所から郵便番号：
※ 「霞が関2」で郵便番号を導き出している点がポイント
技術的には、郵便番号から住所の場合は地道な整形作業を行っています。（大変でした）
住所から郵便番号の場合は、住所を分解して全国の住所に一致する地域が無いかを調べています。（びっくりするくらい大変でした）
会社情報を取得する機能
アクセスランキング2位です。
会社の情報というものはホームページを見に行けば書いているものですが、統一されたフォーマットというものは無く、行政が公開している資料も一部だけだったり、縦割りでデータが分散していたりします。
これらを集約したデータを提供しています。
特に法人名から法人番号を検索する機能のアクセスが非常に多いです。
この機能だけで1日に30万アクセスあった日もありました。業者さんが使っているんでしょうか。よくわかりませんが。
法人名から法人番号：
法人番号から法人の基本情報：
法人番号から法人の財務情報：
技術的には様々な公共サービスの提供データをデータベースに集約したものを返しています(経済産業省のgBizINFOをベースとしています)。収集先は国税庁、厚労省、EDINET(有価証券報告書)等です。
デイリーでデータを更新しています。
電話番号を統一する機能
アクセスランキング3位です。
電話番号に関するよくある課題として、電話番号をハイフン付きにしたり、国際電話番号に変換したいという課題があります。
しかし、これは容易ではありません。電話番号の桁数は固定桁数ではないため、ハイフン無しの電話番号のどこが区切りかを判別するのは困難なためです。
この問題を解決し電話番号の統一を行えます。
国内電話番号形式に変換：
国際電話番号形式に変換：
祝日を取得する機能
アクセスランキング4位です。
Excelで祝日の処理という作業は、多くの人が行ったことのある作業だと思います。
しかしながら、一般的には別表に祝日テーブルを作成して、それをVLOOKUPする手順が用いられるため、祝日テーブルを更新する手間がかかりメンテナンスフリーとはいきません。
この問題を解決できます。
また、この祝日機能は一部の海外の祝日にも対応しています。
実は海外の祝日を取得するWebAPIはHolidayAPI(年間4万円)など有料サービスが多く、無料のAPIというものはほとんどありません。
しかし、当サイトなら対応国数が少ない代わりに無料です。
祝日テーブルの作成：
なお、パラメーターはExcelの日付形式(シリアル値と呼ばれている)としているため、日付データを編集することなく使用できます。（Excel以外からは使いにくいかもしれませんが）
Wikipediaの記事の概要を取得する機能
アクセスランキング5位です。
Wikipediaの記事の冒頭部分を「記事の概要」として返します。
昔、同様のWebAPIがあったようですが停止していたため当サイトで作成しました。
技術的には、wikipediaが公開しているデータベースを編集して利用しています。
※ スクレイピングは行っていません
他
他、比較的使われている機能をさらっと紹介します。
住所の分割
通貨・仮想通貨の価格
翻訳
正規表現で置換
姓名が混ざった名前を姓と名に分割
パスワード文字列の作成
テキストを指定文字で分割
などなど、あれ欲しいなと思って作っていたら、ここまで膨らんでしまいました。
刺さった機能ありましたでしょうかね。
ぜひ一度使ってみてください。
技術スタック
ここからは技術的な話をします。
フロントエンド：docusaurus (React) / Infima
バックエンド: express (node.js), python
データベース: SQLite
サーバー: nginx / ConoHa VPS
CI/CD: GitHub Actions
見ての通り、わりと古風というか、枯れた技術をセレクトしていると思います。
このサービスはキャッシュ等々で大容量のデータベースを使用しており、特にwikipedaについてはデータ量が膨大で20GBを超えます。また、大量アクセスが発生することも想定されました（実際にそうなっている）。
そのため、クラウドを使うとあまりに費用が嵩んでしまうことから、流行りに逆行してVPS/SQLiteを採用しています。料金は月額800円くらいです。
また、個人情報を扱っていないことや、データを自動生成している部分が多いため、ローカルのデータが消えても大きな問題にならないことも、マネージドデータベースを使っていない理由です。
フロントエンドのAPIドキュメントにはdocusaurusというMarkdownから静的サイトを生成するプロダクトを使用しています。あまり使っている方をみかけませんが、なかなか良いプロダクトです。類似としてVuePressも良いプロダクトでしたので紹介しておきます。
expressをバックエンドに採用した理由はnpmパッケージを使用したかったからです。
また、expressはC10K問題を想定して作られたこともあって、大量リクエストの発生するWebAPIに向いていることも理由です。まぁそこまでリクエストは発生していませんが。
正直、バックエンドは同期処理が多いのでnode.jsで書きたくなかったのですが致し方なくといったところです。個人的にはFlaskやLaravelの方が好きですね。特にLaravelは認証系が楽で良い。
pythonは各種データの生成に使用しています。
nodeやpythonのバージョン管理はasdfを採用しました。
工夫したポイント
とにかくシンプルにすることにこだわりました。
利用者の想定はExcelを使用している方々ですので、技術に明るくない可能性があるためです。
- URLの入力パラメーターを最大2つまでにする
- 単機能にする
- マニュアルはExcelにコピペできるように作る
- 技術用語を可能な限り使わない
などです。
この記事では技術用語を多少使ったりしていますが、公式サイトでは技術用語を可能な限り排除しています。
大変だったポイント
データベースの生成がとにかく大変でした。
データを返す部分はいいんです。リクエストを受けてフォーマットチェックしてデータベースを読みに行って返すだけなんですから。
しかし、データベースの作成だけはそうはいかないわけです。
郵便番号と住所の例なんて分かりやすいもので、「漢数字」と「算用数」が混ざっていたり、「大字」や「字」が使われていたり、「〇〇町」を繰り返す住所があったりします。
ひたすら目視チェックの繰り返しでした。
会社情報も有価証券報告書なんて各社でフォーマットが違う始末です。
社長名に「代表取締役」が入ったり「代 表 取 締 役」とかスペースを入れていたり、もういっそのこと役職名を削除して「お前の名前は平社員だよ！」としてやろうかと、、
ああ、社長名は大変だから対応しないことにしたんだった。
wikipediaも2015年頃からデータが破損しているらしくデータの生成に手間取りました。
10GBのXMLなんてパースできるわけがないので、パーサー部分は自作です。
あいまい回避ページは優先度を付けたり、リダイレクトページはリダイレクト先の記事を返すようにしたりしています。
wikipediaの記事もある程度のフォーマットはあっても、人間が編集しているので統一されていませんしね。
今考えても、よくこれを余暇に作っていたなと思うくらいに見上げた根性です。
そう、根性です。やれば終わる。
ありそうなツッコミへの回答
- スクレイピング禁止しているサイトを使ってないか？
スクレイピング禁止のサイトは使用していません。
スクレイピングすれば楽だったポイントはいくつもありました。特にGoogleの検索結果が優秀で、何度Googleからデータ取ってこようと思ったか分かりません。
しかし、基本的に公開しているデータベース・資料・WebAPIを使用しており、ルールに沿った収集方法を行うようにしています。
- こういうサービスは終了リスクが怖いよな
わかります。
私が外野ならこのコメントをしていると思います
このサービスは2017年頃から2021年まで3～4年間も個人用途で運用していた実績があり、実は運営5年目のサービスだったりします。公開してからは2年目です。
そのため、早晩終了することは無いだろうと思います。
- データがどこまで正確なのかわからんなぁ
これに関しては難しい問題です。
私もテスト・確認はしているものの、自分の使用している範囲以上となると気づいていない問題点もありそうです。
バグ・情報の誤りの報告は歓迎しております。むしろ欲しいです。
- Power QueryでJSONをパースできるよ
これはありそうというよりあった質問です。
確かにPower QueryでもJSONはパースできます。しかしPower QueryはJSONから表を作成することが目的になっていて、各JSONの一項目だけを取得したいという要望に応えにくいのが現状です。
例えば以下の表をPower Queryで簡単に作成できるでしょうか。いいえ、とんでもなく大変な手間がかかります。
SQL Server等との連携ならPower Queryは大変便利ですが、ことJSONに至っては向いていないと私は思います。
今後
当初欲しいと考えていた機能は一通り実装できたので、今後はブラッシュアップに取り組む予定です。
作ったはいいものの非公開にしている機能（HTMLをパースする機能や、先物、株価、地価、Twitter、Youtubeのデータ取得など）もいずれ公開できるレベルになれば公開したいと考えています。
他の方からの要望などがあれば、それを実装してみるのも面白いかもしれません。
データ生成系のシステムも細かな問題点があるので、そこも手を入れないと。
まぁ、とにかくまだやることは山積みですので、今後も余暇で続けていきたいと思います。
良かったら使ってください。アクセス数が励みになります。
Discussion