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

皆さんこんにちは。去年から花粉症が発症し、憂鬱な時期を過ごしている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で受け取ったり、ウェブページを生成して、ウェブアプリケーションを表示したりなど、重要な機能の１つです。この仕組を使うことで、自作の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&lt;=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シートを作成しておきます。