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 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 ドキュメントで章番号・節番号を付与

Google ドキュメントで見出しに章番号を付与しようといろいろ調べてみたけど標準ではそういう機能がなさそうな感じ。

ということで、スクリプト使ってできそうだったのでちょっと作成してみました。

基本的に、必要な概念は、

var pars = DocumentApp.getActiveDocument().getBody().getParagraphs();

でパラグラフを取得してループを回していきます。

  var hdg = par.getHeading();
    if (hdg == DocumentApp.ParagraphHeading.HEADING1

ここでヘッダ１なら章番号を加算して出力するという感じでやってます。

par.setText(counterh1+'.\t'+chunks[0]);

初めて書いてみましたが、なるほどなかなか便利ですね。

コード例：