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 関数を使おう
  • support.microsoft.com
• で、とってきた情報は FILTERXML でパースする・・・XML？JSONは？ FILTERJSON関数は？
  • そんな関数ない
• API作って、出力の時にXMLにすれば良い
  • ちょっとググったけど、XMLで出力するってのはあんまり作例ない
    • JSONで出力するってのが多かった
    • XMLを取得して、Googleスプレッドシートに反映させる、ってのも多かった
  • いざとなれば温かみのある手作りXMLも辞さない
    • フラグ

作った

• 手始めに WebAPI として JSON で吐き出してみる
  • これはok

• XML での出力も何とかなった、というかちゃんと関数あった

  • developers.google.com
  • しかし、エレメント名（タグ名とでもいうか）に半角カタカナを使えないことを知らずに2時間くらいハマってた
    • 半角カタカナ現役の世界があるんすよ・・・
  • この辺りは Perl で RSS をいじったりなんだりした経験が生きた
    • 主に欲望方面での RSS

• Excel の WEBSERVICE 関数からのアクセス・・・エラー「ログインしてね」

  • あー、Web ブラウザから URL を直接叩けば、Cookie なり SAML なり SSO で権限の問題は解消されるけど、Excel が URL にアクセスしにいく時はそうじゃないもんなー
  • WebAPI を全公開にしたらちゃんと反映出来た
    • 現行よりセキュリティ要件甘くなってるやん
    • WebAPI の URL に BASIC 認証をかけるとかどうよ
      • なさそう
    • WebAPI のリクエストパラメータに認証情報を設けて、そこで区別するというのはどうか？
      • うーん、出来なくはないけどー、せっかく社で使ってるのに、それとは別の認証を設けるのはちょっとなんか

• 項目（カラム）1個ごとに1回、WebAPI のアクセスが発生しちゃう！非効率！

  • 今のところ呼び出す項目の順番は固定だから、配列にしちゃうかー
    • ところで、 XML って配列サポートしてるの・・・？
  • いやちょっと待って、1件1件データとってくる必要ある？
    • ない

• 全件取得して、そのシートを参照すれば良いのでは

  • Excel の PowerQuery で XML を取り込むことでうまくいった
    • これ、結局手動では？シート開くたびに更新とかできるのかな？（未確認

この辺りで全てがめんどくさくなってくる

• そもそも、なぜこんな苦労を・・・普通にDB立てたら？

  • 担当者がいなくなった後、誰がメンテするんだ

• Microsoft 製品同士 Accessを使ってみては？

  • 担当者がいなくなった後、誰がメンテするんだ（2回目
  • 15年くらい前に Accessの資格とった気がするが、全然覚えてない

• Excel と Google スプレッドシートがぱっと見分かりやすすぎるんだよな・・・

  • 利用者が Excel 側だけ更新して、正のデータである GAS を更新しないということはあり得そう
  • だって、それが現行の使い方で、1ステップで終わるけど、GAS 更新=> Excel で取り込み って2ステップを強いてもうまく行かないよな
    • 得られる結果は同じで、手数は増える
    • 野良マスタが増えるわけよなー
    • データの一元化、正規化のメリットは大きいんだけどなー

ということで結論的な

• まぁ、うちだけでも使えるようにすればいいか
• 欲しかったものをすぐ使えないのは残念だけど、まぁ、それはそれで
• 経験積めたしー
• とはいえ悔しい

2020年7月のオリンピック開催にあわせて国が用意した4連休ですが、コロナ自粛で遠出もせず、こういうものを作ってました

• 個人的には失敗なのだけど、こういうのを作ってました、って残しておこう

Googleスプレッドシート

docs.google.com

XML取得のURL

Googleスプレッドシートの1行目の列名をXMLのタグ名とする

なお、列名に半角カタカナ入れるとコケて面白い（面白くない https://script.googleusercontent.com/a/macros/sironekotoro.com/echo?user_content_key=dax6BTzkq7IeQQ3h-NknDD2IBTMIbIL_6PslC5LmBLAEa4hlkdRClhKqoKUnM3q8_8qO4thL0iVD3vHg_hOR4pFpYRdKinrym5_BxDlH2jW0nuo2oDemN9CCS2h10ox_nRPgeZU6HP93n8xsgl2lv7M3QUtYsNJpyc5IGh1q_XKB3dFIzDdUtB5Qdgb8Zy7FHv7ZCHVgFop9_MCr1SAs-DMeGy7bcuKtdIQwAH1Q462S69gbKJ2_-9z9Jw9Md8uu&lib=Mi5FVeXc-rscpK5HLaNixMyOoJz7hZlKj

Excel の Power Query で取り込む（Windows版のみ）

1. メニューから 「データ」 を選ぶ
2. [データの取得] をクリックし、「その他のデータソースから」->「Webから」を選択
3. ラジオボタンは [基本] のままで、URLに上記のURLを入力し [OK] ボタンをクリックする
4. 「Webコンテンツへのアクセス」の画面が出るので、そのまま（匿名のまま）、右下の [接続] ボタンをクリックする
5. ナビゲーター の画面が出るので、左側メニューの row を選択して、右側のプレビューが出ることを確認する
6. 確認したら、右下の [データの変換] ボタンをクリックする
7. Power Query　エディターの画面が出るので、右下の 「適用したステップ」のところで「変更された型」をクリックし、❌マークが赤くなったら❌マーククリックして削除する
   • これを行わないと、金融機関コードと支店コードで左側が0埋めされない
8. 左上の [閉じて読み込む] をクリックして、「閉じて読み込む」 を選択

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);
}