Google Apps Script でスプレッドシートの文字を利用する

Question

前提・実現したいこと

Googleフォームからお申込みがあったら、自動返信をするスクリプトをGoogleフォーム側で設定をしておりますが、毎月日にちを修正するのに手間がかかるため、スプレッドシート内にある日付を自動取得して利用したいと思っております。
動作としては、該当申込日が定員に達しているかを判定し、返信メール内で「〇月〇日に受付いたしました」「〇月〇日は定員に達していますので受付できません」と記載して返信を行います。
定員に達しているかどうかの判定は正しく動作しておりますが、日付を代入して利用する方法がわかりません。
コード内　if (answer == '5月16日'){　部分の6ヶ所を毎月手入力しております。
ご教授いただけると助かります。よろしくお願い致します。

該当のソースコード

function submitForm(e){
  var itemResponses = e.response.getItemResponses();
  var message = '';
  var username = '';
  var h1 = '';
  var h2 = '';
  var K = '';
  var mail = '';
  for (var i = 0; i < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    var question = itemResponse.getItem().getTitle();
    var answer = itemResponse.getResponse();
    if (question == 'お名前'){
      username = answer;
    }
    if (question == 'メールアドレス'){
      mail = answer;
    }
    k='';
    h1='';
    if (question == '申込日'){
        if (answer == '5月16日'){
          h1=nsu1()
          if ( h1 <= 0 ){
            k='は定員に達していますので受付できません。';
            } else {
            k='に受付いたしました';
          }
        }
        if (answer == '5月21日'){
         h1=nsu2()
         if (h1 <= 0 ){
            k='は定員に達していますので受付できません。';
            } else {
            k='に受付いたしました';
            }
        }
       if (answer == '5月30日'){
         h1=nsu3()
        if (h1 <= 0 ){
           k='は定員に達していますので受付できません。';
            } else {

             k='に受付いたしました';
           }
         } 
    }
    
   if (question == '申込日2'){
       if (answer == '5月16日'){
          h1=nsu1()
          if ( h1 <= 0 ){
           k='は定員に達していますので受付できません。';
            } else {
           k='に受付いたしました';
          }
        }
        if (answer == '5月21日'){
         h1=nsu2()
        if (h1 <= 0 ){
           k='は定員に達していますので受付できません。';
           } else {
            k='に受付いたしました';
           }
         }
        if (answer == '5月30日'){
        h1=nsu3()
         if (h1 <= 0 ){
            k='は定員に達していますので受付できません。';
            } else {
           k='に受付いたしました';
            }
         } 
    }
    
  
    message = message + '. ' + question + ': ' + answer + k + '\n';
  }
  
  var title2 = 'お申込みありがとうございます';
  var content2 = username + '様\n\nお申込ありがとうございます。\n'
    + '※このメールはお申込みをいただいた方に自動送信されます。\n\n'
    + message;
  MailApp.sendEmail(mail, title2, content2);
}
 
function nsu1(){
  var ur1="https://docs.google.com/spreadsheets/d/XXXXXXXXX/edit#gid=0";
  var spreadsheet = SpreadsheetApp.openByUrl(ur1);
  var sheets = spreadsheet.getSheets();
  for ( var i in sheets ){
    if ( sheets[i].getSheetName() == "申込人数" ) {
      var data1 = sheets[i].getSheetValues(2, 5, 1, 1);
    }
   }
      return data1;
 }
 
 function nsu2(){
  var ur1="https://docs.google.com/spreadsheets/d/XXXXXXXXX/edit#gid=0";
  var spreadsheet = SpreadsheetApp.openByUrl(ur1);
  var sheets = spreadsheet.getSheets();
  for ( var i in sheets ){
    if ( sheets[i].getSheetName() == "申込人数" ) {
      var data2 = sheets[i].getSheetValues(3, 5, 1, 1);
    }
   }
      return data2;
 }

 function nsu3(){
  var ur1="https://docs.google.com/spreadsheets/d/XXXXXXXXX/edit#gid=0";
  var spreadsheet = SpreadsheetApp.openByUrl(ur1);
  var sheets = spreadsheet.getSheets();
  for ( var i in sheets ){
    if ( sheets[i].getSheetName() == "申込人数" ) {
      var data3 = sheets[i].getSheetValues(4, 5, 1, 1);
    }
   }
      return data3;
 }
 

スプレッドシート

左から3番目の「申込人数」シート A列の日にちを自動取得したい
|日にち|申込人数|定員||満席判定|
|:--|:--:|--:|
|2019/05/16|5|50||45|
|2019/05/21|4|50||46|
|2019/05/30|5|50||45|

Answer 1

他所様のコメントについたコードですが、直すとこう。

function submitForm(e){
    var itemResponses = e.response.getItemResponses();
    var message = '';
    var username = '';
    var h1 = '';
    var h2 = '';
    var K = '';
    var mail = '';
    for (var i = 0; i < itemResponses.length; i++) {
        var itemResponse = itemResponses[i];
        var question = itemResponse.getItem().getTitle();
        var answer = itemResponse.getResponse();
        if (question == 'お名前'){
            username = answer;
        }
        if (question == 'メールアドレス'){
            mail = answer;
        }
        message = message + '. ' + question + ': ' + answer;
        if (question == '申込日'){
            var k = nsu(answer);
            message = message + k;
        }
        if (question == '申込日2'){
            var k2 = nsu(answer);
            message = message + k2;
        }
        message = message + '\n';
    }
    var title2 = 'お申込みありがとうございます';
    var content2 = username + '様\n\nお申込ありがとうございます。\n'
        + '※このメールはお申込みをいただいた方に自動送信されます。\n\n'
        + message;
    MailApp.sendEmail(mail, title2, content2);
}
function nsu(answer) {
    //変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
    var ur1="https://docs.google.com/spreadsheets/d/XXXXXXXXX/edit#gid=0";
    var spreadsheet = SpreadsheetApp.openByUrl(ur1);
    var sheet = spreadsheet.getSheetByName('申込人数');
    var range = sheet.getRange(2,1,3,5);
    k='';
    h1='';
    for(i=2;i<=4;i++){
    
        //i行目の日にちを取得する
        var date = sheet.getSheetValues(i, 1, 1, 1);
        
        //2行目の日にちと入力された日にちが一緒なら
        if(date == answer ){
        
            //i行目の満席判定を取得する
            h1 = sheet.getSheetValues(i, 5, 1, 1);
            
            //i行目の満席判定がマイナスなら
            if ( h1 <= 0 ){
                k='は定員に達していますので受付できません。';
            
            //i行目の満席判定がプラスなら
            } else {
                k='に受付いたしました';
            }
        }
    }
    return k;
}

---

original

function submitForm(e){
    var itemResponses = e.response.getItemResponses();
    var message = '';
    var username = '';
    var h1 = '';
    var h2 = '';
    var K = '';
    var mail = '';
    for (var i = 0; i < itemResponses.length; i++) {
        var itemResponse = itemResponses[i];
        var question = itemResponse.getItem().getTitle();
        var answer = itemResponse.getResponse();
        if (question == 'お名前'){
            username = answer;
        }
        if (question == 'メールアドレス'){
            mail = answer;
        }
        
        function nsu(){
            //変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
            var ur1="https://docs.google.com/spreadsheets/d/XXXXXXXXX/edit#gid=0";
            var spreadsheet = SpreadsheetApp.openByUrl(ur1);
            var sheet = spreadsheet.getSheetByName('申込人数');
            var range = sheet.getRange(2,1,3,5);
            return data;
            k='';
            h1='';
            for(i=2;i<=4;i++){
            
                //i行目の日にちを取得する
                var date = sheets.getSheetValues(i, 1, 1, 1);
                
                //2行目の日にちと入力された日にちが一緒なら
                if(date == answer ){
                
                    //i行目の満席判定を取得する
                    h1 = sheets.getSheetValues(i, 5, 1, 1);
                    
                    //i行目の満席判定がマイナスなら
                    if ( h1 <= 0 ){
                    k='は定員に達していますので受付できません。';
                    
                    //i行目の満席判定がプラスなら
                    } else {
                        k='に受付いたしました';
                    }
                }
            }
            
            message = message + '. ' + question + ': ' + answer + k + '\n';
        }
        
        var title2 = 'お申込みありがとうございます';
        var content2 = username + '様\n\nお申込ありがとうございます。\n'
        + '※このメールはお申込みをいただいた方に自動送信されます。\n\n'
        + message;
        MailApp.sendEmail(mail, title2, content2);
    }
}

Comments

[papinianus]

> 説明不足で申し訳なかったのですが、申込日と申込日2があり、それぞれ同じ日付の3日間がフォームに登録されており、応募者は申込日と申込日2の2日間を選択できます。
申込日と申込日2のそれぞれに対してメールで回答を自動返信したいので、
if (question == '申込日')...以下
部分をコピーし、'申込日'→'申込日2'に　申込日はnsu1(answer)に申込日2はnsu2(answer)にし、
function nsu(answer) {..以下もコピーしてnsu1、nsu2としてみましたが、submitFormも実行されていない状態になってしまいました。
そんな単純な事ではできなかったようです。

dett様の回答からの展開を見る限り、質問者様がコピーをすると関数のブロック({と}の範囲)がおかしくなるようです。
formの送信時に、「アンケート」という設問があり、その回答を埋めているのに「受信者が指定されていません」となるのは、このためです。

申し込み2に対応した改訂を行いましたので、こちらでご確認ください。

[papinianus]

ちがうと思いますが、やりたいのは「2通のメール」を送信することですか？
また、動作検証をしたのであれば「申込日」だけについて正常な応答ができているのか(その上で「2」にトライしたのか)、できていないのか、「申込日」だけではそもそも足りないからいきなり「2」を入れたのか、など、何をしてどうなったを書いていただきたいです。

[sazami]

失礼しました。いえ、2通のメールではなく1通のメールに2日分の解答を記載する事です。
動作検証は「申込日」だけでやった時には「受信者が指定されていません at submitForm(コード:28)」となり、2を追加してトライしたらsubmitFormも実行されずの状態になりました。
申し込み2に対応したコードの改訂ありがとうございます。
試してみましたが「受信者が指定されていません at submitForm(コード:32)」になってしまいました。
関数のブロック({と}の範囲)がおかしくなるようです。←のコメントについてですが、目視で確認しても記載したいただいたコードとの相違はありませんでした。

[papinianus]

申込2を入れる前の時点で、そのエラーになるなら、質問に「メールアドレス」という設問があって、そこの欄にメールアドレスが書いてあるか、が疑われるのですが、ありますか？

[sazami]

はい、メールアドレスの項目は必須なので、間違いなく入っております。
申込2を入れる前の状態でもsubmitFormエラーでしたので、

var title2 = 'お申込みありがとうございます';
var content2 = username + '様\n\nお申込ありがとうございます。\n'
+ '※このメールはお申込みをいただいた方に自動送信されます。\n\n'
+ message;
MailApp.sendEmail(mail, title2, content2);
}
}
↑部分の }1つをvar title2 = の上に移動してみたところ、メールは届きましたが、内容が下記のように
------------------------------------------------------------------------
〇〇様

お申込ありがとうございます。
※このメールはお申込みをいただいた方に自動送信されます。

. 申込日: 5月16日
------------------------------------------------------------------------
のみで、満席か否かのコメントはなく、また他にフォームでの入力項目の
.お名前. ふりがな. 住所. 電話番号. メールアドレス. 性別. 生年月日
の記載もない状態です。

コードを変更する前は、入力項目が全て入った状態で届いておりました。

[sazami]

追加でコメントさせて頂きます。
if (question == '申込日'){
var k = nsu(answer);
}
message = message + '. ' + question + ': ' + answer + k + '\n';
}
var title2 = 'お申込みありがとうございます';
var content2 = username + '様\n\nお申込ありがとうございます。\n'
+ '※このメールはお申込みをいただいた方に自動送信されます。\n\n'
+ message;
MailApp.sendEmail(mail, title2, content2);

}
↑に訂正したところ、他の入力項目も入ったメールが届くようになりました。
ただ、やはり満席か否かの返信コメントは追加されておりませんでした。
何が原因なのか、全然検討がつきません。
アドバイスを頂けると助かります。よろしくお願い致します。

[papinianus]

こんな感じでどうでしょうか

[sazami]

papinianus様が最初に書いていただいたコードを参考にさせて頂き、無事希望通りの物ができました。
if(obj[answer] && obj[answer] <= 0)→if(answer && obj[answer] <= 0)
とする事で解決いたしました。
他の事でお時間をとらせてしまい、誠に申し訳ございませんでした。
シンプルに作ることが出来て、大変嬉しいです。
お付き合いいただいた事、深く感謝申し上げます。

完成コード-----------------------------------------------

function submitForm(e){
var itemResponses = e.response.getItemResponses();
var message = '';
var username = '';
var K = '';
var mail = '';
for (var i = 0; i < itemResponses.length; i++) {
var itemResponse = itemResponses[i];
var question = itemResponse.getItem().getTitle();
var answer = itemResponse.getResponse();
if (question == 'お名前'){
username = answer;
}
if (question == 'メールアドレス'){
mail = answer;
}
k='';
const obj = getData();
if (question == '申込日'){
if(answer && obj[answer] <= 0) {
k='は定員に達していますので受付できません。';
} else {
k='に受付いたしました';
}
}
if (question == '申込日2'){
if(answer && obj[answer] <= 0) {
k='は定員に達していますので受付できません。';
} else {
k='に受付いたしました';
}
}
message = message + '. ' + question + ': ' + answer + k + '\n';
}
var title = 'お申込みありがとうございます';
var content = username + '様\n\nお申込ありがとうございます。\n'
+ '※このメールはお申込みをいただいた方に自動送信されます。\n\n'
+ message;
MailApp.sendEmail(mail, title, content);

}
function getData() {
var ret = {};
const url = "https://docs.google.com/spreadsheets/d/XXXXXXXXX/edit#gid=0";
var spreadsheet = SpreadsheetApp.openByUrl(url);
SpreadsheetApp.openByUrl(url).getSheetByName('申込人数').getRange(2,1,3,5).getValues().forEach(function(e){ ret[(e[0].getMonth()+1) + '月' + e[0].getDate() + '日'] = e[4]});
return ret;
}

Answer 2

API呼び出しが多すぎるので、

function getData() {
  var ret = {};
  const url = "https://docs.google.com/spreadsheets/d/XXXXXXXXX/edit#gid=0";
  SpreadsheetApp.openByUrl(url).getSheetByName("申込人数").getRange(2,1,3,5).getValues().forEach(function(e){ ret[(e[0].getMonth()+1) + "月" + e[0].getDate() + "日"] = e[4]});
  return ret;
}

を作っといて、問題のifブロックのところで

    const obj = getData();
    if (question == '申込日'){
        if(obj[answer] && obj[answer] <= 0) {
            k='は定員に達していますので受付できません。';
        } else {
            k='に受付いたしました';
        }
    }
    if (question == '申込日2'){
        if(obj[answer] && obj[answer] <= 0) {
            k='は定員に達していますので受付できません。';
        } else {
            k='に受付いたしました';
        }
    }

こんな感じ。

Comments

[sazami]

ご回答、ありがとうございます。
こちらのコードの場合、シート名「申込人数」はどこで指定されるのでしょうか？
また、申込日と申込日2は各3日づつあるのですが、その判定はどこでされているのでしょうか？
このようにスッキリとできれば理想なのですが、知識不足で理解が及んでおりません。
追加でアドバイスをいただけると助かります。

[papinianus]

getSheetByName()を忘れていました。判定ではなく、名前で取得すれば良いと思います。

answerは16日か21日か30日かのどれか1日だけが入っていると思っています。判定がしたいのですか？そうではないですよね、答えとして選んだ日の人数が分かればいいのですよね？回答者が選んだのが何日かを判定するのは無意味だと考えています。

getData()は`{"5月16日":45,"5月21日":46,"5月30日":45}`を作ろうとしています。
こういうデータになっていれば`obj["5月16日"]`とするだけで45が取り出せます
(var num = obj["5月16日"];とするとnumが45になる)
これが、0より多いかを判定すればいい、という仕様だったかと思います。
(さらに補足。仮にobj["5月16日"]と書いていますが、実際にはobj[answer]なので、得られる数値は、回答者が選んだ16日または21日または30日のどれかに対応する数値です。answerが何日であるか知らなくとも、全く同じ形で求める数値が得られる形式でコーディングすればよいと思います)

[sazami]

コードのご記載、ありがとうございます。
こちらで試させて頂きましたが、
メールを送信できませんでした: 受信者が指定されていません at submitForm(コード:28)
のエラーが出てしまいました。

おっしゃる通り、選んだ日の人数がわかればよいですね。
説明不足で申し訳なかったのですが、申込日と申込日2があり、それぞれ同じ日付の3日間がフォームに登録されており、応募者は申込日と申込日2の2日間を選択できます。
申込日と申込日2のそれぞれに対してメールで回答を自動返信したいので、
if (question == '申込日')...以下
部分をコピーし、'申込日'→'申込日2'に　申込日はnsu1(answer)に申込日2はnsu2(answer)にし、
function nsu(answer) {..以下もコピーしてnsu1、nsu2としてみましたが、submitFormも実行されていない状態になってしまいました。
そんな単純な事ではできなかったようです。
引き続きアドバイスをいただけると助かります。よろしくお願い致します。

[papinianus]

↑これはこっちのコードじゃなくて、もう一つの回答についてのコメントで理解したほうが良いですかね？

Answer 3

日付のif文の代わりに以下を使うようなイメージでしょうか。

for(i=2;i<=4;i++){

//i行目の日にちを取得する
var date = sheets.getSheetValues(i, 1, 1, 1);

//2行目の日にちと入力された日にちが一緒なら
if(date == answer ){

//i行目の満席判定を取得する
h1 = sheets.getSheetValues(i, 5, 1, 1);

//i行目の満席判定がマイナスなら
if ( h1 <= 0 ){
           k='は定員に達していますので受付できません。';
           
//i行目の満席判定がプラスなら
            } else {
           k='に受付いたしました';
          }
 

}


}

注意点
・日付のフォーマットは今のままではあっていないのでそろえる必要あり
・sheetsは今のままでは読み込まれていないので、読み込んでください

Comments

[sazami]

早速のご回答ありがとうございます。参考にさせて頂き、試してみます。
また改めてご報告させていただきます。

[sazami]

お世話になります。参考に色々試してみましたが、エラーでメールが届かなくなってしまいました。
↓エラー内容
メールを送信できませんでした: 受信者が指定されていません at submitForm(コード:58)
下記コードを設定しましたが、理解しきれておらず、書き方や書く場所が違っているのでしょうか。
追加のアドバイスを頂けると助かります。

function submitForm(e){
var itemResponses = e.response.getItemResponses();
var message = '';
var username = '';
var h1 = '';
var h2 = '';
var K = '';
var mail = '';
for (var i = 0; i < itemResponses.length; i++) {
var itemResponse = itemResponses[i];
var question = itemResponse.getItem().getTitle();
var answer = itemResponse.getResponse();
if (question == 'お名前'){
username = answer;
}
if (question == 'メールアドレス'){
mail = answer;
}

function nsu(){
//変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
var ur1="https://docs.google.com/spreadsheets/d/XXXXXXXXX/edit#gid=0";
var spreadsheet = SpreadsheetApp.openByUrl(ur1);
var sheet = spreadsheet.getSheetByName('申込人数');
var range = sheet.getRange(2,1,3,5);
return data;
k='';
h1='';
for(i=2;i<=4;i++){

//i行目の日にちを取得する
var date = sheets.getSheetValues(i, 1, 1, 1);

//2行目の日にちと入力された日にちが一緒なら
if(date == answer ){

//i行目の満席判定を取得する
h1 = sheets.getSheetValues(i, 5, 1, 1);

//i行目の満席判定がマイナスなら
if ( h1 <= 0 ){
k='は定員に達していますので受付できません。';

//i行目の満席判定がプラスなら
} else {
k='に受付いたしました';
}
}
}

message = message + '. ' + question + ': ' + answer + k + '\n';
}

var title2 = 'お申込みありがとうございます';
var content2 = username + '様\n\nお申込ありがとうございます。\n'
+ '※このメールはお申込みをいただいた方に自動送信されます。\n\n'
+ message;
MailApp.sendEmail(mail, title2, content2);
}
}