読者です 読者をやめる 読者になる 読者になる

astamuse Lab

astamuse Labとは、アスタミューゼのエンジニアとデザイナーのブログです。アスタミューゼの事業・サービスを支えている知識と舞台裏の今を発信しています。

マクロって言ったり、関数って言ったり、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の関数を紹介してみたいと思います。

ImportRange

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

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

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

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

f:id:astamuse:20170423112752p:plain

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

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

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

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

SpreadSheet API

また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は利用不可。

ImportXML

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)

URLを↓下記のように生成できます。

f:id:astamuse:20170423190011p:plain

さらに生成したURLにImportXmlを適用し、タイトルを取得してみました↓

f:id:astamuse:20170423193443p:plain

ただあまり処理数が多いと以下のようにエラーになります。

f:id:astamuse:20170423231554p:plain

今回はこの辺で。

最後に

いかがでしたか?ローカルで動作するExcelとは違った機能を紹介してみました。

またここでは紹介はしなかったですが、Gmail等のGoogleのサービスへの連携も比較的簡単にできるため重宝しています。

アスタミューゼでは現在、エンジニア・デザイナーを募集中です。 興味のある方はぜひ 採用サイト からご応募ください。

参考にしたサイトなど

Google スプレッドシートの関数リスト

Google Apps Script ドキュメント

Google API Client Libraries JAVA編

*1:正確にはGoogle Apps Script

Copyright © astamuse company, ltd. all rights reserved.