Google apps script とスプレッドシートでスプレッドシート比較ツールを作ってみる

Google apps scriptというのをたんなるエクセルのマクロのような存在と思いこんでいたりすると、大きなまちがいだなと最近すごく感じています。

実際に、これを使うと、簡単なウェブアプリケーションが作れたりします。例えば、Slackの会話を添付ファイル込みでエクスポートするWebアプリケーションも作れたもします。

 また、Google apps scriptとスプレッドシートをの組み合わせもいい基盤です。入力と出力を必要とするようなツール基盤としていろいろ便利な存在です。

以下の具体例を交えて説明しておきます。

Google apps scriptを使うとカスタムメニューを追加することができます。

コードは簡単です。こんな風に書くだけです。

function onOpen() {

  SpreadsheetApp.getUi()

      .createMenu('比較ツール')

      .addItem('比較実行', 'executeCompare')

      .addToUi();

}

Spreadsheetのうち一つのシートをパラメータ設定用のシートにして、結果を出力するシートをもう一つ作ると、

パラメータをセットした後に、ツールを実行ということができるようになります。

イメージとして、実際のサンプルツールを作ってみました。

こちらは、２つのスプレッドシートを比較するツールになります。

https://docs.google.com/spreadsheets/d/1zaLJSTasXGTilJ3B_9L3AbrgZVZQVHJTLHRAhstfpyw/edit?usp=sharing

こちらのスプレッドシートをコピーして実行してみてください。

使い方も簡単に書いておきます。

上の図のようにvarsのシートに

1. 比較したいスプレッドシートのURLを記載します。
2. 比較したいシートの名称を記載します。
3. キーとなるカラムを指定します。(複数時には、カンマ区切り)
4. 比較したいカラムを指定します。(複数時には、カンマ区切り)

これらをセットして、メニューの比較ツール→比較実行から実行すると、Resultタブに結果が出てきます。

それ以外のシートは作業用です。

結果の例）

比較に使えるサンプルも用意してみましたのでそのまま実行できるのではないかと思います。

https://docs.google.com/spreadsheets/d/1dCF-3lBFXSmNFZaPGOJV9AMWPNYN9tXhibAiTWwuoRM/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1nYw--oBuCfsoQX9NUzmH32KBTm0LzUputbK4mmnOp2g/edit?usp=sharing

30分くらいでぱっと作ったものなので、バグってたらすいません。

２つのファイルを比較するって比較的ニーズはありそうなんですが、もしも、こんなこともやれるようにしたいなどのご要望があったら、

コメント欄からいただければ、ちょっとここから改善してみるかもしれません。

声がなくても、自分用にいろいろ改善していこうかと思っています。

Googleスプレッドシートのフィルタに別シートのListを使ってフィルタしたい

 フィルタするときのフィルタに指定する値を別表に書いているものを使ってフィルタしたいという例があると思います。

例えば、

表1：

A	1
B	2
C	3
D	4
B	5
A	6
B	7
C	8
C	9
D	10

表2：

A

B

があり、表1をA,Bでフィルタをかけたいというようなイメージです。

ＳＱＬのイメージ的にいうと

SELECT * from 表1 where ID in (SELECT ID from 表2)

みたいなイメージのことをSpreadSheetを使ってやりたいなというところです。

これどうやってやるのが一番簡単なのかちょっといろいろ探ってみたのですが、

おそらく、FILTER関数とMATCH関数を使って実現するのがよさそうです。

書き方は下記の感じです。

=FILTER(フィルタしたい票の範囲,MATCH(フィルタしたい範囲の条件部分,フィルタを掛けたい値の別表,0))

実例はこちらになります。

=FILTER(A1:B10,MATCH(A1:A10,'別表2'!A1:A2,0))

実際のサンプルスプレッドシートはこちらです。

https://docs.google.com/spreadsheets/d/1tSr8mukLFhREQCYxy6gsp2QUz9-Ie6liPV7uHt3fOh0/edit?usp=sharing

Spreadsheetにて正規表現にマッチした値を取り出す。

例えば、Google spread sheetで数字の部分だけを取り出したいとかいうニーズがあったりします。

そんな時に利用できるのが、REGEXTRACT関数です。

正規表現を使って文字列を抽出することができます。

例えば、「10円」というセルから10を取り出そうと思うと、

=REGEXEXTRACT(A2,"[0-9]+")

と記述することにより、10の部分を取り出すことができます。

他の例だと、例えば、メールのアドレス部分が、

「サンプル太郎 <spreadsheet@example.com>」のような形式で入っていた場合。

下記のように記述すると、名前の部分が取り出せます。

=REGEXEXTRACT(E2,"(.+)<")

また、下のように書くとメールアドレスの部分を取り出せますね。

=REGEXEXTRACT(E2,"<(.+)>")

このように、文字列を自由自在に分解できると、また、spreadsheetの活用範囲が広がるのではないでしょうか。 

本日の関数の実サンプルはこちらのリンクを参照ください。

Google Spread Sheetですべての値の一覧化をしたいときに使う関数(UNIQUE)

ある列に入っている値の一覧を取得したいと思うことがあると思います。

そんな時に使う関数が、UNIQUE関数です。

利用方法は簡単です。A列に入っている値の一覧が取得したいときには、下記のように記述します。

=UNIQUE(A2:A10)

 このように書くと、ここから下に、一意な値が並びます。

それをさらに並べ替えたい場合には、SORT関数を使います。

=SORT(UNIQUE(C2:C10))

これで、一覧を作成したうえで並び替えることができます。

実際のSpreadsheetのサンプルのURLはこちら。

Google spreadsheetを使って自動応答メールの作成

 Google Apps scriptを使ってSpreadSheetを開いたらメールを送る例は、前回の記事でも紹介しました。

似たようなユースケースですが、今回は、フォームを使って自動送信メールを実現する方法を紹介します。メール送信プログラムは前回の記事で紹介したので本日は割愛します。

また、フォームの作成は、以前の記事で紹介しているのでこれも割愛します。

メールのプログラムを書いた後に、スクリプトエディタ上の「編集」→「現在のプロジェクトのトリガー」より、トリガを作成のボタンを押していただき、イベントの種類を選択から「フォーム送信時」を選ぶとフォームを提出したときに自動的にメールを送信することができます。

前回の記事のあて先部分を下記のようにすると、提出者に対してメールを送信することができますね。

    var toAdr = Session.getActiveUser().getEmail() ;

これを使うと例えば下記のようなことが実現できます。

• フォーム回答時にお礼のメールを送信する
• 下記の内容で受け付けました的なお知らせメール
• 申込者に対して秘密のURLなどを送付する。アンケート回答のお礼のクーポンとかもそのたぐいですね。

便利ですね。日々の仕事とかちょっとした部分に色々使えそうです。

コメント欄に、こういう使い方がしてみたとか、こういう使い方ってできるかなどの質問なども気軽にいただけるとありがたいです。

Google App Script でメールを送信する

 例えば、下記のようなことをしたくないでしょうか。

例えば、鍵が開錠されたら通知をしたいけど、鍵は、単なる物理鍵なので、通知は難しい。

そんな時に、下記のような構成にすることによって、疑似的に実現可能かなと思っています。

・暗証番号などのカギをGoogle spreadsheetに書いておく。

・Google spreadsheetが開かれたら、誰が開いたかを管理者にメールする

このようなことが、Google spreadsheetとGoogle apps scriptを使うと簡単にできます。

暗証番号型のカギで鍵をかけてその暗証番号などの秘密のファイルをどこかにおいておき、

そのファイルが開封されたら、通知してほしい。

まず、Google spreadsheetから「ツール」→「スクリプトエディタ」を開きます。

その後下記のようなコードを書きます。

<pre>

function send_mail(){

    var toAdr = "xxxxxxx9999@gmail.com";

    var ccAdr = "";

    var bccAdr = "";

    var subject = "";

    var name = "";

    var files = new Array();

    var body = Session.getActiveUser().getEmail() +

      "さんから訪問がありました。\n";

  

    MailApp.sendEmail({to:toAdr, cc:ccAdr, bcc:bccAdr, subject:subject, name:name, body:body, attachments:files});

}

</pre>

Session.getActiveUser().getEmail() は接続者のメールアドレス情報を取得します。

MailApp.sendEmailはメールの送信をする関数です。

toAdrに送信したいあｔメールアドレスをいれてください。

その後、スクリプトエディタ上で、実行→関数を実行→send_mailを選択すると許可を求める画面が出てきます。今ログインしているユーザの代わりにメールを送信する許可を求められるので、問題がなければ許可をします。

すると、メールが送信されると思います。

次に、スクリプトエディタ上の「編集」→「現在のプロジェクトのトリガー」を選択すると、この関数をいつ発火するかを選択できます。「トリガーを追加」を押して起動時に発火するように下記の通り、選択します。

イベントの種類を「起動時」とすることでファイルを開いたときにメール送信関数を呼び出すようにすることができます。

以上のステップのみで、冒頭に述べたことが実現できます。

とても簡単ですね。。。

Google spreadsheetの関数を使ってみる(QUERY)

 前回の記事では、集計の方法として、SUMIFなどの関数を紹介しました。

今回の集計に使える関数の紹介です。SQLなどになじみのある方は、この関数が便利に感じると思います。

前回の記事と同じように、支払い種別、用途、金額という表があった時に、支払種別と用途ごとに集計したいというような場合に、SQLだと、

select 支払い,用途,sum(金額) from xxx group by 支払い、用途  

というようなSQLで集計するとおもいます。これはSpreadsheetでも同様に描くことができます。

支払い	用途	金額
現金	食費	¥1,000
現金	交通費	¥1,000
現金	食費	¥600
現金	娯楽費	¥1,500
現金	食費	¥500
クレジットカード	医療費	¥30,000
現金	薬代	¥1,500
現金	医療費	¥3,000
クレジットカード	医療費	¥18,000

セルに下記のように書いてみましょう。

=QUERY(A2:C10,"select A,B,sum(C) group by A,B",-1)

SQLの集計関数と同じ文法が書けます。最初の引数は、範囲。次がQuery文字。

これを書くと下記のように表示することができます。

クレジットカード	医療費	48000
現金	交通費	1000
現金	医療費	3000
現金	娯楽費	1500
現金	薬代	1500
現金	食費	2100

Joinなどはできませんが、それでもSQLに慣れている人にとっては非常に便利な関数です。

SQLに慣れ親しんでいる人は、いろいろ関数を組んで書くよりも楽かもしれませんね。

今回のスプレッドシートの例は、こちら。