1. 入力内容をGoogle Spreadsheetで操作
フォームに入力した日時指定送信メールの内容をGoogle Spreadsheetで操作する。時間指定メール送信システムを構築するにあたり，いろいろアルゴリズムは考えられるが，メール送信プログラムはタイマーによりフォーム入力とは非同期で動作させることにする。
フォームで入力されたSpreadsheetを非同期で開いて各行にあるデータの送信時間を確認する方法では，徐々にデータが溜まってしまい処理が遅くなっていくことが予想される。そこで，フォームに入力され次第，メール送信用の別Spreadsheetを特定のフォルダ（例えばmail）内に作成し，そのファイル名を送信日時にしておく。
非同期で起動したプログラムで，そのフォルダ内にファイルがあるファイルを確認し，送信日時に現在日時が到達していれば，送信作業を行う。送信後はそのファイルは削除する。
フォーム入力があると，そのSpreadsheetにはフォームデータ送信と同時に最下行に付け加えられていく仕様となっている。よって，そのSpreadsheetに紐付けられたGASプログラムで，mailフォルダに作ったSpreadsheetにフォームで作成されたSpreadsheetの最下行だけをコピーしておき，メール送信に備えておく。
- フォームで送信が行われた段階で，二重起動を防ぐためのロック等の処理をした後，フォームで作成されたSpreadsheetの最下行のデータを取得する。
- 取得したデータを専用フォルダ「mail」内に用意した新規Spreadsheetに書き込む。
- 一定時間（たとえば１分）ごとに実行するGASプログラムで，現在日時を取得し，保存されたメールファイルの中から，送信日時が現在時刻より古いものを抽出し送信する。
- 送信したファイルは削除する。
今日は2までを行う。下の図では⑤，⑥を実行するプログラムを書く。図には「最下行だけを1行目にコピー」となっているが，間違いである。「インデックスを1行目，最下行を2行目にコピー」が正しいので，注意する。課題の説明の通りにとくこｔ
1.1. フォーム入力データが格納されたGoogle Spreadsheetの作成
- 時間指定メール送信システムフォームに，データを5件程度入力せよ。過去と未来の両方の送信日時を含むものとする。送信メール内容は適当で構わないが，メールの宛先は必ず自分の公式メールアドレスにすること。
- フォーム編集画面から「回答」をクリック。
- スプレッドシートの作成，をクリック。新しいスプレッドシートを作成する。
- 新しいSpreadsheetが作成され，自動で開かれる。
-
Googleのバグ？
ファイルSpreadsheetの，ファイル，設定，をクリックし，言語と地域が，日本，Tokyo，になっているか確認せよ。なっていない場合は修正せよ。（タイムゾーンが，Delhi，になっている場合がある。どうもGASを開発している拠点のタイムゾーンになっている可能性が高い。2022年は問題なかったので，Googleの開発においてバグが発生したものと思われる）
-
Google Driveを見るとSpreadsheetが確認できる。
- 新規にフォルダ「mail」を作成する。
- SpreadsheetとmailフォルダのIDを確認しておく。mailフォルダのIDは，そのフォルダに入ってURLで確認する。folders/の後ろの部分がID。
- Spreadsheetのツールメニューからスクリプトエディタを起動し，プロジェクト名を変更。
- 最初から書かれているGASプログラム，「コード.gs」の4行は全削除する。
- 次の内容をコピーペーストする。「**********」の部分は，それぞれ適切なIDに置き換える。logファイルはGoogle Formと供用しない。
- 課題の実行テストの方法は，プログラムリストの下に示す。
//ここから，mailフォルダに作成したSpreadsheetの１行目に送信するプログラムを書き，そのSpreadsheetのファイル名を
//getDateAndTimeOfNowShort()と同じ形式に名前を変更する。
＜課題8＞
テンプレートを元に次のプログラムを完成させよ。全てプログラムで実行すること。
- フォームにより作成されたスプレッドシートの1行目（インデックス）をmailフォルダに新規作成されたSpreadsheetの1行目にコピーする。
- フォームにより作成されたスプレッドシートの最下行のデータをmailフォルダに新規作成したSpreadsheetの2行目にコピーする。
- 新規作成したSpreadsheetのファイル名をフォーム入力された送信予定日時にする。そのフォーマットはgetDateAndTimeOfNowShort()と同じとする。
- フォーム入力して「mailフォルダに作成された送信日時がファイル名となっているSpreadsheet」を提出フォルダにアップロードせよ。
テンプレート
var logID = "**********";//手動でDocument，「log2」を新規作成しIDを調べておく
var ssID1 = "**********";//フォーム入力で作成されたSpreadsheetのID
var ssID2 = "";//mailフォルダに作成されたSpreadsheetのID
var folderID = "**********";//mailフォルダのID
function submitForm(){//フォームが送信されたら呼び出される関数，重複処理を避ける
log = new Doc(logID);
log.print('\n'+getDateAndTimeOfNowShort()+" スクリプト開始\n");
var lock = LockService.getScriptLock();//ロックサービスのオブジェクトを生成
try{
lock.waitLock(30000);//複数のフォーム送信がほぼ同時にあった時，遅い方に最大30秒待ってもらう
log.print("他のスクリプト実行要求をロック完了，最大30秒\n");
main(log);
}catch(err){
log.print("発生したエラー："+err+'\n');
}finally{
lock.releaseLock();//次の送信のためにロック解除
log.print(getDateAndTimeOfNowShort()+" ロック解除，次のスクリプト要求を受け付け開始\n");
}
}
function main(log){
var ssSrc= new Ssheet(ssID1);//フォーム入力されたデータが入っているSpreadsheet
var todayNow =getDateAndTimeOfNowShort();//現在日時の取得（Shortバージョン）
ssID2 = createSpreadsheetInFolder(folderID, "temp");//新規作成，ファイル名はとりあえずtemp後で送信日時に変更する
var ssDst = new Ssheet(ssID2); //最後にフォーム入力されたデータだけを記録するSpreadsheet
var maxRow = ssSrc.getLastRow(0);
var maxColumn = ssSrc.getLastColumn(0);
log.print("最後の行"+maxRow+" 最後の列"+maxColumn+"\n");
//ここから先を完成させる
}
//-----------------------日付や時間のための関数群
function getSerial(date,time){//日付と時間からシリアル値をゲット
var serial = new Date(date.toString().slice(0,16)+time.toString().slice(16));
return serial;
}
function getDateAndTimeOfNow(){// 現在日時を文字列でゲット
var now = new Date();
var year = now.getFullYear();//getYear()+1900
var month = now.getMonth() + 1;
var day = now.getDate();
var hour = now.getHours();
var min = now.getMinutes();
var sec = now.getSeconds();
return year +'_'+ ("0"+month).slice(-2) +'_'+ ("0"+day).slice(-2) +' '+
("0"+hour).slice(-2) +'-'+ ("0"+min).slice(-2) +'-'+ ("0"+sec).slice(-2);
}
function getDateAndTimeOfNowShort(){// 現在日時を文字列でゲット
var now = new Date();
var year = now.getFullYear();//getYear()+1900
var month = now.getMonth() + 1;
var day = now.getDate();
var hour = now.getHours();
var min = now.getMinutes();
var sec = now.getSeconds();
return (""+year).slice(0,4) + ("0"+month).slice(-2) + ("0"+day).slice(-2) +'_'+
("0"+hour).slice(-2) + ("0"+min).slice(-2) + ("0"+sec).slice(-2);
}
function getTime(serialValue){//シリアル値から時間を取り出す，シリアル値は「1899年12月30日午前0時」からの経過ミリ秒のはず
var str = serialValue.toString();//（例：'Sat Oct 29 2016 16:05:42 GMT+0900 (JST)'）
var strings = str.split(' ');
return strings[4];
}
function getDate(serialValue){//シリアル値から年月日を取り出す
var str = serialValue.toString();//（例：'Sat Oct 29 2016 16:05:42 GMT+0900 (JST)'）
var strings = str.split(' ');
return strings[0] +' '+ strings[1] +' '+ strings[2] +' '+ strings[3];
}
//-----------------------ファイルの新規作成等の関数群
//フォルダID，ファイルID，ファイル名を受け取り，ファイルIDのコピーをフォルダ内に作成して，ファイルのIDを返す
function copyFileInFolder(folderID, srcID, fileName) {
var originalFile = DriveApp.getFileById(srcID);
var folder = DriveApp.getFolderById(folderID);
var copiedFile = originalFile.makeCopy(fileName, folder);
copiedFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);//リンクからアクセスできる人は編集可能にする
var copiedFileId = copiedFile.getId();//コピーのファイルIDをゲット
return copiedFileId;
}
//フォルダID，ファイル名を受け取り，スプレッドシートを指定フォルダ内に新規作成しそのファイルIDを返す
function createSpreadsheetInFolder(folderID, fileName) {
var folder = DriveApp.getFolderById(folderID);
var newSS=SpreadsheetApp.create(fileName);
var originalFile=DriveApp.getFileById(newSS.getId());
var copiedFile = originalFile.makeCopy(fileName, folder);
//DriveApp.getRootFolder().removeFile(originalFile);//2020/10にサポート終了した様子
originalFile.setTrashed(true);
var copiedFileId = copiedFile.getId();//コピーのファイルIDをゲット
return copiedFileId;
}
//////////Ssheetクラスの定義開始（コンストラクタとメンバ関数で構成）
//Ssheetクラスのコンストラクタの記述
Ssheet = function(id){
this.ssFile = SpreadsheetApp.openById(id);
this.ssFileName = this.ssFile.getName();
SpreadsheetApp.setActiveSpreadsheet(this.ssFile);//値を返さない
this.activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
}
//Ssheetクラスのメンバ関数（メソッド）の定義開始
//spreadsheetのファイル名を返すメソッド
Ssheet.prototype.getFileName = function(){
return this.ssFileName;
}
//spreadsheetのファイル名を変更するメソッド
Ssheet.prototype.rename = function(newName){
this.ssFile.rename(newName);
}
//spreadsheetのsheetでrow行cal列にデータを入れるメソッド
Ssheet.prototype.setValue = function(sheet,row,col,value){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
var cell = this.activeSheet.getRange(row,col);
cell.setValue(value);
}
//spreadsheetのsheetでrow行cal列をクリアするメソッド
Ssheet.prototype.clear = function(sheet,row,col,value){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
var cell = this.activeSheet.getRange(row,col);
cell.clear(value);
}
//spreadsheetのsheetからrow行のcal列のデータをもらってくるメソッド
Ssheet.prototype.getValue = function(sheet,row,col) {
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
var value = this.activeSheet.getRange(row, col).getValue();
return value;
}
//背景の色を設定するメソッド
Ssheet.prototype.setBackgroundColor = function(sheet,row,col, r,g,b) {
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
var cell = this.activeSheet.getRange(row,col);
cell.setBackgroundRGB(r,g,b);
}
//spreadsheetのsheet数を指定の数増やすメソッド
Ssheet.prototype.insertSheet = function(num){
var sheetNum = this.activeSpreadsheet.getNumSheets();
while(num>sheetNum){
this.activeSpreadsheet.insertSheet();
sheetNum++;
}
}
//spreadsheetの指定sheetを削除
Ssheet.prototype.deleteSheet = function(sheet){
this.activeSpreadsheet.deleteSheet(this.activeSpreadsheet.getSheets()[sheet])
}
//spreadsheetのsheetの名前をセットするメソッド
Ssheet.prototype.renameSheet = function(sheet,newName){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
this.activeSheet.setName(newName);
}
//spreadsheetの指定sheetの全データを取得
Ssheet.prototype.getValues = function(sheet){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
return this.activeSheet.getDataRange().getValues();//シートの全データを取得
}
//spreadsheetの指定sheetを取得
Ssheet.prototype.getSheet = function(sheet){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
return this.activeSheet;//シートを返す
}
//spreadsheetの指定sheetの最後の行番号を取得
Ssheet.prototype.getLastRow = function(sheet){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
return this.activeSheet.getLastRow();//最後の行番号を取得
}
//spreadsheetの指定sheetの最後の列番号を取得
Ssheet.prototype.getLastColumn = function(sheet){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
return this.activeSheet.getLastColumn();//最後の列番号を取得
}
//spreadsheetの指定行rowを削除
Ssheet.prototype.deleteRow = function(sheet,row){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
this.activeSheet.deleteRow(row);//行を削除
}
//spreadsheetの指定行rowを挿入
Ssheet.prototype.insertRow = function(sheet,row){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
this.activeSheet.insertRows(row);//行を挿入
}
//spreadsheetの指定列colを削除
Ssheet.prototype.deleteColumn = function(sheet,col){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
this.activeSheet.deleteColumn(col);//列を削除
}
//spreadsheetの指定列colを挿入
Ssheet.prototype.insertColumn = function(sheet,col){
this.activeSheet = this.activeSpreadsheet.getSheets()[sheet];
this.activeSheet.insertColumns(col);//列を挿入
}
//////////Ssheetクラスの定義終了
//////////Docクラスの定義開始（コンストラクタとメンバ関数で構成）
//Docクラスのコンストラクタの記述
Doc = function(id){
this.ID = id;
this.doc = DocumentApp.openById(this.ID);
this.body = this.doc.getBody();
this.docText = this.body.editAsText();
}
//Docクラスのメンバ関数の定義開始
//メソッドprintの定義，テキスト追加
Doc.prototype.print = function(str){
this.docText.appendText(str);
}
//メソッドreplaceの定義，文字列置き換え
Doc.prototype.replace = function(src,dst){
this.body.replaceText(src,dst);
}
//メソッドclearの定義，全消去
Doc.prototype.clear = function(){
this.body.clear();
}
//メソッドgetIDの定義，ファイルIDを返す
Doc.prototype.getID = function(){
return this.ID;
}
//指定秒数のウェイト，表示動作を遅らせたい時などに使用
Doc.prototype.waitSec = function(sec){
var start = new Date().getSeconds();
while((new Date().getSeconds()-start) < sec);
}
//指定ミリ秒のウェイト，表示動作を遅らせたい時などに使用
Doc.prototype.waitMiliSec = function(msec){
var start = new Date(); //new Date()は，「1970年1月1日午前0時」からの通算ミリ秒を返す
while((new Date()-start) < msec);
}
//今現在の日時を表示
Doc.prototype.printTodayNow = function(){
var now = new Date();
var year = now.getYear();
var month = now.getMonth() + 1;
var day = now.getDate();
var hour = now.getHours();
var min = now.getMinutes();
var sec = now.getSeconds();
this.docText.appendText(year +'_'+ ("0"+month).slice(-2) +'_'+ ("0"+day).slice(-2) +' '+
("0"+hour).slice(-2) +'-'+ ("0"+min).slice(-2) +'-'+ ("0"+sec).slice(-2));
}
/////////Docクラスの定義終了
- 実行テストは次のように行う。submitFormを実行テストするのではないことに注意。今回はフォーム入力直後のプログラムの動作を確認したいので，mainをテスト実行する。
