Google Spreadsheetで自分用の簡単なメール送信予約スクリプトを作る。

Google SpreadsheetではExcelで使える数式でガツガツ作りこむことも可能ですが、その場合シートがどうしても重くなりがち。

Query関数で引っ張ったほうがサクッといくことがあります。
今回はQuery関数を使って、前から作っておきたかった簡単なメール送信予約シートを作りました。

今までChromeの拡張を使ったりしてたのですが、有料ですとかフリーミアムモデルで途中から有料ですとか言われたものの、そんなものSpreadsheetで作れるのに有料とかいい商売してるなーと感じてました。

Spreadsheetに簡単なフォームを生成して、あとは1分おきとか5分おきにScriptを回しておくだけ。

1時間くらいで作ったというのもあって、変数定義の部分の変更くらいはいれようかなぁ。

ま、こんなスクリプトを書いてみました。

var sheetNumber = 1
function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[sheetNumber - 1];
  var now = new Date();
  var nDate = now.getYear() + "/" + parseInt(now.getMonth() + 1) + "/" + now.getDate();
  var nTime = now.getHours() + ":" + now.getMinutes() + ":00";
  sheet.getRange("J1").setValue(nDate);
  sheet.getRange("K1").setValue(nTime);
  sheet.getRange("A1").setFormula('QUERY(\'Form Responses 1\'!A:F;\"select * where E = date \"&text(J1,"\'\'YYYY-MM-DD\'\'\")&\" and F = timeofday \'\"&K1&\"\'\")');
  var rows = sheet.getLastRow();
  var dataCount = rows - 1;
  var emailAddress,subject,message,dataCount2;
  if(dataCount > 0){
  for(var i = 0 ; i < rows-1 ; i++){
    emailAddress = sheet.getRange(i+2,2).getValue();
    subject = sheet.getRange(i+2,3).getValue();
    message = sheet.getRange(i+2,4).getValue();
    MailApp.sendEmail(emailAddress, subject, message);
  }
    
    var latestDate = sheet.getRange(rows,5).getValue();
    var latestTime = sheet.getRange(rows,6).getValue();
    sheet.getRange("M1").setValue(latestDate);
    sheet.getRange("N1").setValue(latestTime);
    sheet.getRange("A1").setFormula('QUERY(\'Form Responses 1\'!A:F;\"select * where E = date \"&text(J1,"\'\'YYYY-MM-DD\'\'\")&\" and F > timeofday \'\"&K1&\"\'\")');
    rows = sheet.getLastRow();
    dataCount = rows - 1;
    sheet.getRange("A1").setFormula('QUERY(\'Form Responses 1\'!A:F;\"select * where E > date \"&text(J1,"\'\'YYYY-MM-DD\'\'\"))');
    rows = sheet.getLastRow();
    dataCount2 = rows - 1;
    if(dataCount == 0 && dataCount2 == 0){
      DeleteSheet();
    }
  }
}
function DeleteSheet() {
    SpreadsheetApp.flush();
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[sheetNumber];
    var datarange = sheet.getDataRange();
    var lastrow = datarange.getLastRow();
    var values = datarange.getValues();
    for (i=lastrow;i>=2;i--) {
        var tempdate = values[i-1][2];
        {
            sheet.deleteRow(i);
            function deleteAllResponses() {}
        }
    }
}
function deleteAllResponses() {
  var form, urlForm = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  if (urlForm) {
    form = FormApp.openByUrl(urlForm);
    if (form) form.deleteAllResponses();
  }
}

追記1)シートが重くならないように一定条件でデータを削除するフローを追加と、それに伴う一部ソースを修正しました。