【番外編】Google Apps Script(GAS)のAPIでスプレッドシートにデータを書き込む

皆さんこんにちは。
去年から花粉症が発症し、憂鬱な時期を過ごしているSHUです。

今回は番外編となります。
たまたま業務でGoogle Apps Scriptを勉強する機会があったため、
社内でのGAS活用方法をご紹介したいと思います。

なお、Alibaba Cloudは関係しない内容のため、
ご興味がある方は読み進めていただければ幸いです。

 

1. なぜGAS?

当社はフリーアドレスで、毎朝手入力でシートマップに当日の座席位置を入力する必要がありましたが、手間だったのもあり、なかなか習慣付いておりませんでした。

そこで、シートマップ自体がGoogleスプレッドシートで作成されていたので、Google Apps Scriptを使って下記2つの機能を実装してみました。

今回は、サンプルスクリプトとともに、機能をご紹介いたします。

1. Gmailの自動メール
-> リマインダー

2. QRコード入力
-> 簡易入力インターフェース

 

2. Google Apps Script(GAS)とは

Google Apps Script(通称GAS)は、Googleが提供する11のサービスをクラウド上でスクリプトを実行することで操作できるサービスです。スクリプトの言語は、JavaScriptをベースとしています。

「Excelのマクロと同じ」と言われることもありますが、これは正しくありません。スプレッドシートだけを操作する場合は、Excelのマクロと似ていますが、Google Apps Scriptはより幅広い用途に使用できます。

現在は、以下の11のサービスを操作することができます。

  • カレンダー
  • コンタクト
  • ドライブ
  • ドキュメント
  • スプレッドシート
  • フォーム
  • Gmail
  • グループ
  • マップ
  • サイト
  • Languages

スクリプトを使って操作できる内容を例えると、

  • 毎朝8時にGmailを自動送信する
  • Googleカレンダーに登録されている特定期間のスケジュールを一括でスプレッドシートに転記する
  • スプレッドシートで作成した見積もりを自動的にPDF化させ、Googleドライブに保存する

など、目的を持って使えば、かなり便利な機能となります。

 

3. メール自動送信機能

シートマップ向けに作成した機能その①です。

例えば、下図のような座席表への入力があったかどうかを判定するシートを作成します。

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時の間に
自動実行するようにしてあげたりするといいでしょう。

 

4. QRコード入力機能

Google Apps Scriptには、ウェブアプリケーションを公開したりするために「doGet」「doPost」という特別な関数があります。パラメータを加えて渡して上げることで、スプレッドシートのデータなどを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で実装していたりしますので、機会があればまたご紹介させていただきます。

5. まとめ

グループウェア単体としても便利なG-suiteですが、
Google Apps Scriptと組み合わせることで、より利活用できるかと思います。

ネット上に様々なリファレンスが掲載されているので、是非皆さまもお試しください。

 

 

この記事をシェアする
© 2018 SB Cloud Corp. All rights reserved.