sironekotoroの日記

Perl と Mac の初心者の備忘録

Google スプレッドシートの情報を GAS で WebAPI にしてXML で取得できるようにして、それを Excel の PowerQuery で取得する

悩み

経理ネタです。Perl 出てきません(往年の YAPC みたいになってきた)

  • 請求書ベースの振込先の管理に Excel シートを利用している
  • 自社サービスの一部の振込先の管理に Google スプレッドシートを使っている

これをどっちかにまとめたい。というか、マスタ的なものが複数あるのは面倒。仕事(というか作業)に IF 文は少ないに限る。

振込のデータ作成と、振込先のデータは分けておきたい。そして、振込先のデータは編集へのハードルが高い状態にしておきたい。

一意になっていないレコードがあったりして、こういうのを何とかしていきたい(企業の名前はよく変わる。口座番号そのままに・・・)

考えた

作った

  • 手始めに WebAPI として JSON で吐き出してみる
    • これはok
  • XML での出力も何とかなった、というかちゃんと関数あった

    • developers.google.com
    • しかし、エレメント名(タグ名とでもいうか)に半角カタカナを使えないことを知らずに2時間くらいハマってた
      • 半角カタカナ現役の世界があるんすよ・・・
    • この辺りは PerlRSS をいじったりなんだりした経験が生きた
      • 主に欲望方面での RSS
  • ExcelWEBSERVICE 関数からのアクセス・・・エラー「ログインしてね」

    • あー、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の資格とった気がするが、全然覚えてない
  • ExcelGoogle スプレッドシートがぱっと見分かりやすすぎるんだよな・・・

    • 利用者が 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 エディターの画面が出るので、右下の 「適用したステップ」のところで「変更された型」をクリックし、❌マークが赤くなったら❌マーククリックして削除する
  8. 左上の [閉じて読み込む] をクリックして、「閉じて読み込む」 を選択

f:id:sironekotoro:20200726111614p:plain

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