皆さんこんにちは。 去年から花粉症が発症し、憂鬱な時期を過ごしているSHUです。
今回は番外編となります。 たまたま業務でGoogle Apps Scriptを勉強する機会があったため、 社内でのGAS活用方法をご紹介したいと思います。
なお、Alibaba Cloudは関係しない内容のため、ご興味がある方は読み進めていただければ幸いです。
なぜGAS?
当社はフリーアドレスで、毎朝手入力でシートマップに当日の座席位置を入力する必要がありましたが、手間だったのもあり、なかなか習慣付いておりませんでした。そこで、シートマップ自体がGoogleスプレッドシートで作成されていたので、Google Apps Scriptを使って下記2つの機能を実装してみました。
今回は、サンプルスクリプトとともに、下記2つの機能をご紹介いたします。
- Gmailの自動メール(リマインダー)
- QRコード入力(簡易入力インターフェース)
Google Apps Script(GAS)とは
Google Apps Script(通称GAS)は、Googleが提供する11のサービスをクラウド上でスクリプトを実行することで操作できるサービスです。スクリプトの言語は、JavaScriptをベースとしています。
「Excelのマクロと同じ」と言われることもありますが、これは正しくありません。スプレッドシートだけを操作する場合は、Excelのマクロと似ていますが、Google Apps Scriptはより幅広い用途に使用できます。
- カレンダー
- コンタクト
- ドライブ
- ドキュメント
- スプレッドシート
- フォーム
- Gmail
- グループ
- マップ
- サイト
スクリプトを使って操作できる内容を例えると、 - 毎朝8時にGmailを自動送信する - Googleカレンダーに登録されている特定期間のスケジュールを一括でスプレッドシートに転記する - スプレッドシートで作成した見積もりを自動的にPDF化させ、Googleドライブに保存する
など、目的を持って使えば、かなり便利な機能となります。
メール自動送信機能
シートマップ向けに作成した機能その①です。 例えば、下図のような座席表への入力があったかどうかを判定するシートを作成します。
D列の関数には、A列が空白でない場合"入力済み"未入力の場合”未入力”と表示されるようにしています。(これはExcelの関数レベルの話ですね)
このシートの中で、未入力者だけに対してGmailを起動し 定形テキストメールを送信するスクリプトを作ってみます。
Google Apps Scriptのエディタは、スプレッドシートの ツールバーのツール -> スクリプトエディタから開けます。
サンプルコードを下記に記載いたします。
function SendEmailSeldomToSpecificPerson(){ // 関数名 適当に決めます // シートを名前で指定 var ss = SpreadsheetApp.getActive().getSheetByName('シート1'); // メールの送信時間 var today = Utilities.formatDate(new Date(), 'JST', 'yyyy年M月d日 H時'); // タイトル var MailTitle = "恐れ入りますが、座席表登録をお願いいたします!" var lastRow = ss.getLastRow(); // ステータスが未入力の場合、未入力セルと同行の情報を取りメールを送信 for(var i=2; i<=lastRow; i++){ if (ss.getRange(i,1).getValue() == "未入力"){ var rangeB = ss.getRange("B" + i).getValue(); var rangeC = ss.getRange("C" + i).getValue(); var MailText = rangeB+"さん"+"\n\nお疲れ様です。\n本日"+today+"時点で座席表が未入力です。\n恐れ入りますが、シートマップにご入力いただきますよう、お願いします。"; GmailApp.sendEmail(rangeC, MailTitle, MailText, { from: '送信元アドレス', // GmailAppのoption 送信元アドレス変更可能(例えば、グループアドレスにできたりする) name: '送信元アドレスネーム' // 受け取り側の表示名を指定 }); } }
メソッドの機能を下図に記載していますので、ご確認ください。
では、上記スクリプトを実行してみましょう。
エディタツールバーに実行ボタンがあるので、ポチっとしてみます。
ちなみに時計マークみたいなアイコンから、トリガーを設定することができます。
設定できるトリガーの種類としては、
- タイムトリガー
- イベントトリガー(スプレッドが更新されたらなど)
- Googleカレンダートリガー
などありますので、用途によって使い分ができますね。
では実行してみます!
ステータスが”未入力”となっているメールアドレスだけに、定形テキストが送信されました。
タイムトリガーで毎週火曜日の13時 〜 14時の間に 自動実行するようにしてあげたりするといいでしょう。
QRコード入力機能
Google Apps Scriptには、ウェブアプリケーションを公開したりするために"doGet"という特別な関数があります。パラメータを加えて渡して上げることで、スプレッドシートのデータなどをJSONで受け取ったり、ウェブページを生成して、ウェブアプリケーションを表示したりなど、重要な機能の1つです。この仕組を使うことで、自作のREST APIもどきを作ることも可能です。
今回は、この「doGet」でスプレッドシートへの入力をおこないます。
function doGet(e){ var no = e.parameter.no; var datetime = new Date(); var UserInfo = Session.getActiveUser().getEmail(); var ss = SpreadsheetApp.getActive().getSheetByName('シート1'); var LogSheet = SpreadsheetApp.getActive().getSheetByName('Log'); var lastRow = ss.getLastRow(); var flg = false; for (var i=2; i<=lastRow; i++) { if (ss.getRange(i, 7).getValue() == UserInfo) { ss.getRange(i, 1).setValue(no); flg = true; break; } } // 実行日時&メールアドレスセット LogSheet.appendRow(['登録完了', datetime, no, UserInfo]); if (flg == false) { LogSheet.appendRow(['対象外', datetime, no, UserInfo]); return ContentService.createTextOutput("対象外"); } else { return ContentService.createTextOutput("User:"+UserInfo+"\nNo. "+no+" シートへの登録が完了しました"); } }
GETで渡されたパラメータ値のログも取得するために、 スプレッドシートでLogシートを作成しておきます。
併せて取得したユーザー情報(メールアドレス)を照合するためのメールアドレス情報をシート1のG列セルに入力しておきます。
続いて、外部ユーザがアクセスできるようにウェブアプリケーションとして導入します。 ツールバー -> 公開 -> ウェブアプリケーションとして導入から実行可能です。
なお、自分をユーザーとしてアプリケーションを実行することによって、同一GmailドメインのユーザーごとにPermissionの許可をすることなくユーザー側から実行させることが可能です。 バージョンの名前は適当でおkです。
完了するとURIが払い出されます。 /exec?no=110 のように、箱として指定したURIのnoに任意の座席番号を入れておきましょう。
では、ブラウザからURIにアクセスして実行してみましょう。 ・ ・ ・ G列とUserInfoを照合して、対象ユーザーのA列のセルに、登録ができましたね。 ブラウザ側へのレスポンスも問題なく返りました。
Logシート側にも受け取った情報がセットされました。 ※D列はアドレス情報なのでマスクしてあります
肝心のQRコードですが、URIをQR生成してくれるサイトがいくつかあるので、noにパラメータを入れた状態のURIでQRを必要数分作成しています。 スマートフォンのカメラ等でQRを読み取れば、GETした瞬間にセルのユーザー情報を照合し適切なセルへnoが書き込まれるといった仕組みです。
このブログのURIをQRコード生成しておきました。 いつでもアクセスできるようにMacの背面に張っておきましょう。
いかがでしたでしょうか?
実は上記機能以外にも
- 当日のシートマップの自動保管
- 入力された座席番号の自動削除
などもGASで実装していたりしますので、機会があればまたご紹介させていただきます。
まとめ
グループウェア単体としても便利なG-suiteですが、 Google Apps Scriptと組み合わせることで、より利活用できるかと思います。
ネット上に様々なリファレンスが掲載されているので、是非皆さまもお試しください。