質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.61%

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

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 1,337

score 18

前提・実現したいこと

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
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

0

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='に受付いたしました';
        }
    }


こんな感じ。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/04/01 22:27

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

    キャンセル

  • 2019/04/01 22:34 編集

    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が何日であるか知らなくとも、全く同じ形で求める数値が得られる形式でコーディングすればよいと思います)

    キャンセル

  • 2019/04/02 01:08

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

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

    キャンセル

  • 2019/04/02 01:10

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

    キャンセル

0

日付の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は今のままでは読み込まれていないので、読み込んでください

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/30 10:10

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

    キャンセル

  • 2019/04/01 22:21

    お世話になります。参考に色々試してみましたが、エラーでメールが届かなくなってしまいました。
    ↓エラー内容
    メールを送信できませんでした: 受信者が指定されていません 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);
    }
    }

    キャンセル

0

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

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);
    }
}

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/04/02 22:23

    追加でコメントさせて頂きます。
    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);

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

    キャンセル

  • 2019/04/03 14:39

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

    キャンセル

  • 2019/04/03 22:57

    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;
    }

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.61%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問