Google スプレッドシートの情報を GAS で WebAPI にしてXML で取得できるようにして、それを Excel の PowerQuery で取得する
悩み
経理ネタです。Perl 出てきません(往年の YAPC みたいになってきた)
- 請求書ベースの振込先の管理に Excel シートを利用している
- 自社サービスの一部の振込先の管理に Google スプレッドシートを使っている
これをどっちかにまとめたい。というか、マスタ的なものが複数あるのは面倒。仕事(というか作業)に IF 文は少ないに限る。
振込のデータ作成と、振込先のデータは分けておきたい。そして、振込先のデータは編集へのハードルが高い状態にしておきたい。
一意になっていないレコードがあったりして、こういうのを何とかしていきたい(企業の名前はよく変わる。口座番号そのままに・・・)
考えた
- ローカル(社内共有サーバ)においてある Excel ファイルの情報をインターネット上の Google スプレッドシートに反映させるのは手間
- 手動?
- 絶対やだ
- 手動?
逆に、Google スプレッドシートの情報を正として、Excel に反映させるのはどうか
Google Fusion Tables とかいうのがあって、JSON とかで吐き出せるらしい
- 提供終了していた
- Google、お前はいつもそうだ
- gsuiteupdates-ja.googleblog.com
- 提供終了していた
Google スプレッドシートは Google Apps Script 使って WebAPI にできるから、それを利用できないか?
- API を呼び出す Excel 側は Excel2013 以降(かつWindows版)で利用できる WEBSERVICE 関数を使おう
- で、とってきた情報は FILTERXML でパースする・・・XML?JSONは? FILTERJSON関数は?
- そんな関数ない
- API作って、出力の時にXMLにすれば良い
作った
- 手始めに WebAPI として JSON で吐き出してみる
- これはok
XML での出力も何とかなった、というかちゃんと関数あった
- developers.google.com
- しかし、エレメント名(タグ名とでもいうか)に半角カタカナを使えないことを知らずに2時間くらいハマってた
- 半角カタカナ現役の世界があるんすよ・・・
- この辺りは Perl で RSS をいじったりなんだりした経験が生きた
- 主に欲望方面での RSS
Excel の WEBSERVICE 関数からのアクセス・・・エラー「ログインしてね」
項目(カラム)1個ごとに1回、WebAPI のアクセスが発生しちゃう!非効率!
- 今のところ呼び出す項目の順番は固定だから、配列にしちゃうかー
- ところで、 XML って配列サポートしてるの・・・?
- いやちょっと待って、1件1件データとってくる必要ある?
- ない
- 今のところ呼び出す項目の順番は固定だから、配列にしちゃうかー
全件取得して、そのシートを参照すれば良いのでは
この辺りで全てがめんどくさくなってくる
そもそも、なぜこんな苦労を・・・普通にDB立てたら?
- 担当者がいなくなった後、誰がメンテするんだ
-
- 担当者がいなくなった後、誰がメンテするんだ(2回目
- 15年くらい前に Accessの資格とった気がするが、全然覚えてない
Excel と Google スプレッドシートがぱっと見分かりやすすぎるんだよな・・・
ということで結論的な
- まぁ、うちだけでも使えるようにすればいいか
- 欲しかったものをすぐ使えないのは残念だけど、まぁ、それはそれで
- 経験積めたしー
- とはいえ悔しい
2020年7月のオリンピック開催にあわせて国が用意した4連休ですが、コロナ自粛で遠出もせず、こういうものを作ってました
- 個人的には失敗なのだけど、こういうのを作ってました、って残しておこう
Googleスプレッドシート
XML取得のURL
Googleスプレッドシートの1行目の列名をXMLのタグ名とする
Excel の Power Query で取り込む(Windows版のみ)
- メニューから 「データ」 を選ぶ
- [データの取得] をクリックし、「その他のデータソースから」->「Webから」を選択
- ラジオボタンは [基本] のままで、URLに上記のURLを入力し [OK] ボタンをクリックする
- 「Webコンテンツへのアクセス」の画面が出るので、そのまま(匿名のまま)、右下の [接続] ボタンをクリックする
- ナビゲーター の画面が出るので、左側メニューの row を選択して、右側のプレビューが出ることを確認する
- 確認したら、右下の [データの変換] ボタンをクリックする
- Power Query エディターの画面が出るので、右下の 「適用したステップ」のところで「変更された型」をクリックし、❌マークが赤くなったら❌マーククリックして削除する
- これを行わないと、金融機関コードと支店コードで左側が0埋めされない
- 左上の [閉じて読み込む] をクリックして、「閉じて読み込む」 を選択
GAS
手元にコピペして試す時は、メニューの [公開] から 「ウェブアプリケーションとして導入」しておくのを忘れずに
"use strict" const SHEETNAME = 'シート1'; function getValues(){ const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEETNAME); const sourceRange = sourceSheet.getDataRange(); const sourceValues = sourceRange.getValues(); return sourceValues; } //function searchId(id = 1) { // const sourceValues = getValues(); // const row = sourceValues.filter( row => id === row[0] ); // //// console.log(row); // // return row; //} function searchAll(){ const sourceValues = getValues(); sourceValues.shift(); // console.log(sourceValues); return sourceValues; } function getColNames(){ const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEETNAME); const lastColumn = sourceSheet.getLastColumn(); const colNames = sourceSheet.getRange(1,1,1,lastColumn).getValues(); return colNames[0]; } function makeXml(rows = searchId(3) ){ const colNames = getColNames(); const root = XmlService.createElement('rows'); rows.forEach( row => { const child = XmlService.createElement('row'); for (let i =0;i<row.length;i++){ child.addContent(XmlService.createElement(colNames[i]).setText(row[i])); } root.addContent(child); }); const document = XmlService.createDocument(root); const xml = XmlService.getPrettyFormat().format(document); // console.log(xml); return xml; } function doGet(e) { // const id = e.parameter.id; const rows = searchAll(); const xml = makeXml(rows); return ContentService .createTextOutput(xml) .setMimeType(ContentService.MimeType.XML); } function doPost(e) { doGet(e); }