マクロって言ったり、関数って言ったり、Functionだったりする何か。~Google Spread Sheet~

f:id:astamuse:20170425195524j:plain

お久しぶりでございます。scalaでバックエンドを開発しているaxtstar(@axtstart)でございます。

今回は、弊社の勉強会で話したネタを少しアレンジして記載します。

Google Spread Sheet

前々回のエントリーでExcelマクロには○○とか話をしていてナンですが、業務では結構G Suite上のGoogle Spread Sheetなどを通して、仕様やデータを検証することも多く、ローカルでのExcelでの作業より、G Suite上での作業の方が徐々に多くなってきています。

GoogleSpreadSheetを使った業務改善の話はこちらのエントリーでも取り上げていますが、ここではもっとプリミティブなtips的なSpreadSheetの関数を紹介してみたいと思います。

こちらは他のSpreadSheetのデータを参照することができる関数です。

元のSheetを汚したくない場合などによく利用しています。データのレンジ範囲をセル上に書けば、その範囲を全部拾って来ます。 Excelにはこのような、記載したセル以外に値を展開する関数というのはみたことがありませんでした。

例えば、ターゲットのスプレッドシートが下記のようなURLの場合、

https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxx/

f:id:astamuse:20170423112752p:plain

対象スプレッドシートのスプレッドキー（上記の場合：xxxxxxxxxxxxxxxxx）とシート・レンジを指定します。

=ImportRange("xxxxxxxxxxxxxxxxx","シート6!A1:F12")

１度だけ、アクセスの許可をする必要があります。

f:id:astamuse:20170422234819p:plain

なお、範囲上に展開する関数は、他のデータが展開先に存在する場合、エラーになります。

f:id:astamuse:20170422235031p:plain

すると、下記のように、C4:H15の範囲にシート6!A1:F12の内容が展開されます。

f:id:astamuse:20170423121306p:plain

上記を日直の持ち回り表というようにみていただくと、追加でExcelライクな関数を設定することで次の日直を自動で算出することが可能です。（この場合はGさんが次の日直）まぁ少し例としては微妙ですが。。。

f:id:astamuse:20170423130055p:plain

またGoogleスプレッドシートはAPIからアクセスすることができますので、メッセージに応じて、slack上に日直を表示するというような、botを作ることもできます。

f:id:astamuse:20170423235020p:plain

こちらにslack botの scala で実装したサンプルを上げておきました。

また、ほんのちょっとした処理をスクリプトエディタで記載するのもいい手です。

例えば、文字列をURLエンコードする関数は、存在しないのですが、スクリプトエディタだと↓簡単に記述できます。

下記のようにスクリプトエディタを開き、

f:id:astamuse:20170425185221p:plain

こちらに

f:id:astamuse:20170425185442p:plain

下記を張り付ければOK。

function encode(value) {
  return encodeURIComponent(value);
}

function decode(value) {
  return decodeURIComponent(value);
}

スクリプトエディタ*1は、JavaScript1.6をBaseにつくられており、 Array、Date、RegExp、MathといったObjectが利用可能です。ただし、Googleのサーバで動作するため、Window APIは利用不可。

URLを指定することでXMLをパースして取得することができます。実際にはHTMLでもかなり取得してくれます。ただ、認証など入力することはできないため、ログインが必要なデータは取得できないようです。

例えば下記で、本ブログのキーワードタグを取得できます。

=ImportXML("http://lab.astamuse.co.jp/","//ul[@class='hatena-urllist']")

f:id:astamuse:20170423181907p:plain

こちらを先ほど作成したencode関数と下記関数で、キーワードタグに対して

関数	用途例
Transpose	転置 =Transpose(ImportXML("http://lab.astamuse.co.jp/","//ul[@class='hatena-urllist']"))
REGEXEXTRACT	正規表現によるデータの取得 =REGEXEXTRACT(A2,"( \([1-9]+\))")
Substitute	文字列置換 =Substitute(A2,B2,"")
encode	URLエンコード。スクリプトエディタで作成した関数 =encode(C3)