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

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

新規登録して質問してみよう
ただいま回答率
85.50%
Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Q&A

解決済

2回答

2480閲覧

GASのセル列指定方法およびメールが送付されないエラーについて

mitsuba

総合スコア20

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

0グッド

0クリップ

投稿2018/11/08 09:24

Googleフォームに投稿すると、ランダムの席(数字)が割り当てられ、割り当てられた席(数字)を、
フォーム投稿者へ自動返信される様な仕組みを作成したいと思っております。

Googleフォームと連動している回答のスプレッドシートは、以下の様に設定しております。

≪リストシート≫
イメージ説明

≪チーム番号シート≫
イメージ説明

まず、Googleフォームに投稿されると、リストシートのE列にチーム番号シートの中から、
ランダムの数字が割り当てられる様なスクリプトを作成しました。

下記のコードは、リストシートのE2セルへ反映する様な設定になっている為、
投稿された順に、E3,E4,E5・・・へランダムに番号を割り当てたいのですが、
対応方法を教えていただけないでしょうか?

var sheetName1 = "チーム番号"; /* チーム番号シート名 */ var sheetName2 = "リスト"; /* 一覧のシート名 */ var wordsRow = 1; /* 単語が格納されている先頭の行番号 */ var wordsCol = 1; /* 単語が格納されているカラム番号 */ var words = 15; /* チーム番号の数*/ var storeRow = 2; /* リストの行番号 */ var storeCol = 5; /* リストの絡む番号 */ function pickRandomWord() { var ss = SpreadsheetApp.getActiveSpreadsheet(); row = Math.floor(Math.random() * words); Logger.log("row = " + row); var word = ss.getSheetByName(sheetName1).getRange(wordsRow + row, wordsCol).getValue(); Logger.log("word = " + word); ss.getSheetByName(sheetName2).getRange(storeRow, storeCol).setValue(word); }

また、リストシートに割り当てられた番号をB列のアドレスへメールを送付する為に、
以下のコードを作成しましたが、エラーが表示されてしまう為、対処方法をお教えいただけないでしょうか?

メール送信時のスクリプトは、以下となります。

function mail() { var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("リスト"); var dr = ss.getDataRange(); for (var i = 1; i <= ss.getLastRow(); i++){ var cell = ss.getRange(i, 2); var values = dr.getDisplayValues(); var name = values[i-1][5]; var seat = values[i-1][6]; var subject = name + '座席のお知らせ'; var content = name + " さん\n\n"; content += "***\n\n"; content += "フォーム投稿、ありがとうございます。\n"; content += "テーブル番号を、お知らせします。\n\n"; content += "◆テーブル番号\n"; content +=   seat + "\n"; content += "*** \n\n\n"; content += "短い時間ではありますが、一緒に楽しみましょう!\n\n"; var mailto = cell.getValue() MailApp.sendEmail(mailto, subject, content); // メール送信 } }

【エラー内容】
「無効なメール: メールアドレス(行 46、ファイル「コード」)」
#行46は、『MailApp.sendEmail(mailto, subject, content); // メール送信』

検索した内容や過去作成したコードを利用し作成しております為、
初歩的なミス等が多い場合は、合わせてお教えいただけますとたすかります。

よろしくお願いします。

気になる質問をクリップする

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

papinianus

2018/11/08 09:49

ランダムとはサイコロを振った状態を指します。サイコロはまれですが6が3回続けて出ることがあります。ほんとうにランダムにしたいのですね?
mitsuba

2018/11/08 10:02

ご教示いただき、ありがとうございます。チーム分けをしたいので、人数が均等になるように番号を割り振りたいと思っております。なお、人数はMAX200名程の予定です。
papinianus

2018/11/08 10:31

ランダムではなさそうなので伺いますが、チーム番号はどうやって決めるのですか?まず必ず数値なのかというのと例えばリス組が5人でクマ組は6人とかいうのはどう指定するのが実態に合いますか?物理オフィスに左右されますよね??
papinianus

2018/11/08 10:34

メールはいつ出すのですか?フォーム投稿直後?それともある時間にまとめて?社員が応募してなかったら(座席にあまりがあったら)どうすべき?
mitsuba

2018/11/09 06:03

仕様をきちんとお伝えできておらず、申し訳ありません。 また、色々なリスク含め、ご検討いただきまして、本当にありがとうございます。 今回、実現したい内容としては、懇親会の出席者200人が12チーム(各17名づつ)に分けられるように、席を配置したいと思っております。 毎日実装はせずに、本ツールでは1回のみ実装する形となります。 なお、回答に記載いただきました200名全員が、フォーム投稿しなかった場合の対処をどうすべきか?という点については、 当日欠席者もいると思いますので、チームの人数に多少ばらつきが生じてしまうのはやむを得ないと思っておりました。 メールは、フォーム投稿直後に本人へ返信したいと思っておりました。 素人考えにて、混乱させてしまい申し訳ありません。 上記内容含めましても、やはり現実的に難しい場合は、お教えいただけますと幸いです。
guest

回答2

0

ベストアンサー

使い回しではあるんですが、仕様ちがうんで回答をかえます。

  • 使い方
  1. 【フォーム投稿受付前】下記をフォームが到着するスプレッドシートにスクリプトとして書き、シート名をリストおよびチーム番号にします
  2. 【フォーム投稿受付前】チーム番号シートのA1セルに=buildTeams()と入れます。少しまつと、205行目まで番号が埋まります(17*12は204なんで200を越えるのはしかたないです。11人席をつくれば人数をあわせることは可能)
  3. 【フォーム投稿受付前】↑で作ったシャッフル済みの番号に偏りがないかをみます。偏りがある場合はA1セルを消し、再度書きかえます。また偏りがなくOKだと思えば、コピーして値のみはりつけをします(←A1をさわらなければ変わらないはずですが、万一リストが書きかわると問題なので念の為)
  4. 【フォーム投稿受付前】onSubmitをトリガー設定します(フォーム送信時)
  5. フォームを公開し、投稿を受け付けます

※下記に【2】氏名、とありますが、これは正確にフォームの名称と対応している必要があります。たとえば2が全角2であるなら、エラーにならないよう事前に修正してください。

仕様としてチーム番号シートを見ればあらかじめ、何番目に投稿すれば何番のチームになるかは分かります。しかし狙って何番目の投稿者になるかは予測できないであろうこと、このシートは閲覧させないであろうことから許容できる仕様だと思っております。また、ランダムとシャッフルの意味の違いを考えればこれはやむないことです。偏りのないシャッフルを考えると全体について母数が多い状態つまり事前にシャッフルをする、がベストです。

javascript

1function buildTeams() { 2 const teamNums = [ 3 ["1",12], // ["チームナンバー", 0], //記号は意味がある、0のところには、同じチームに何人属すか 4 ["2",12], 5 ["3",12], 6 ["4",12], 7 ["5",12], 8 ["6",12], 9 ["7",12], 10 ["8",12], 11 ["9",12], 12 ["10",12], 13 ["11",12], 14 ["12",12], 15 ["13",12], 16 ["14",12], 17 ["15",12], 18 ["16",12], 19 ["17",12], 20 ]; 21 var ret = []; 22 for(var i = 0; i < teamNums.length; i++) { 23 for(var j = teamNums[i][1]; j; j--) { 24 ret.push([teamNums[i][0]]); 25 } 26 } 27 for(var i = ret.length - 1; i > 0; i--){ 28 var r = Math.floor(Math.random() * (i + 1)); 29 var tmp = ret[i]; 30 ret[i] = ret[r]; 31 ret[r] = tmp; 32} 33 return [["チーム番号"]].concat(ret); 34} 35function onSubmit(e) { 36 const inputs = e.namedValues; 37 const rowNum = e.range.rowStart; 38 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 39 const teamNum = spreadsheet.getSheetByName("チーム番号").getRange(rowNum, 1, 1, 1).getValue(); 40 spreadsheet.getSheetByName("リスト").getRange(rowNum,5,1,1).setValue(teamNum); 41 MailApp.sendEmail(inputs["メールアドレス"], buildSubject(inputs["【2】氏名"]), buildBody(inputs["【2】氏名"],teamNum)); 42} 43function buildSubject(name) { 44 return name + "座席のお知らせ"; 45} 46function buildBody(name, seat) { 47 return name + " さん\n\n" + "***\n\nフォーム投稿、ありがとうございます。\nテーブル番号を、お知らせします。\n\n◆テーブル番号\n" + seat + "\n*** \n\n\n短い時間ではありますが、一緒に楽しみましょう!\n\n"; 48}

投稿2018/11/09 11:06

編集2018/11/13 10:27
papinianus

総合スコア12705

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

mitsuba

2018/11/12 06:34

再度、ご検討いただき、本当にありがとうございます。 シャッフルとランダムの違いについて、深く考えずに投稿いたしまして、失礼いたしました。 回答いただいた内容で「on Submin」を実行いたしましたところ、 【「TypeErroe:undefinedからプロパティ「namedValues」を読み取れません。】というエラーが表示されてしまいます。 エラー行は、行36の【const inputs = e.namedValues;】が指定されております。  ※表記の注意ポイントとしてご連絡いただいた部分に、関係しておりますでしょうか?  【2】氏名の欄は、全角で「鈴木 太郎」とフォームに投稿された事を想定し、リストシートのD2に「鈴木 太郎」記載されている状態です。 度重なるご質問となりまして大変恐れいりますが、ご教示いただけますと幸いです。 よろしくお願いします。
papinianus

2018/11/12 23:32

関数を実行、から試しておられませんか? 4.のトリガーを設定したあと、フォームを送信することでしか正常に動作しません。 テスト方法は別途考えてみます
mitsuba

2018/11/13 02:31

ご回答ありがとうございます。 フォームから投稿してもE2セルに数字が記載されなかった為、関数を実行から 試しましたところ、上記の様なエラーとなりました。    トリガー設定は、「関数:onSubmit」「デプロイ:Head」「ソール:スプレッドシートから」 「イベントの種類:フォーム送信時」にしておりますが、Deveeloper Hub画面では、全てエラーとなってしまっております。 設定方法に誤りがありますでしょうか?   また、テスト方法のご配慮もありがとうございます。 公開前までは、自身でフォームから投稿して確認する事ができると考えています。
papinianus

2018/11/13 03:24

E2への記入は起こりません。そこに記入してもあまり意味がないと思ったので。 設定にあやまりはなさそうです。 エラーは「eが設定されてないよ」という意味ですが、これは「フォーム送信時」だと自動的に設定されるはずなので、特にコードに誤りがあったり、文字の書き間違いってことはないはずです。 こちらはこちらで記事投稿時には動いていたのですが、ただ、ご覧になったとおり、最近developerhubというのにアップデートされて仕様が違ってきたのかもしれませんね。 今、動かせる環境にいないので、後ほど試してみます。
mitsuba

2018/11/13 09:46

ご回答、ありがとうございます。 E2へ番号が記載されない場合、フォーム回答者がどの番号を割り振られたか、 確認する事は難しいという事でしょうか? 投稿者がフォーム投稿後にメールを返信する仕様となっておりますが、メールを削除してしまったりしたメンバーがいた場合、事務局として本人へ伝えられる様に、全体の把握はできたらよいなと考えておりました。 私が想像していた以上に実装が難しいという事が分かりまして、申し訳ない限りですが、 developerhubの仕様変更について、もしなにかお分かりになりましたら、お教えいただけますとたすかります。
papinianus

2018/11/13 10:31 編集

この一つ前のmitsuba様のコメントを見誤っていました。developer hubでのエラー内容を教えていただけませんか? 操作としては、headとかフォーム送信時とか書いてあるところを右クリックして「失敗した実行」をクリック、その後切り替わって表示されたリストをもう一度右クリックして「StackDriverのエラー」で過去の失敗時のメッセージを教えてください (なおdeveloperhubの仕様変更については、これまでこのようなhubは存在していなかったのに先週末くらいからこのような画面になったという意味で、設定項目の仕様は変わっていないようでした。また今確認してもこちらのテスト環境からはエラーなくメールが送れています) 誤解のないように再度書きます * 実装そのものは容易な部類です(動いてないのは私の相談スキル不足です) * E列への転記がないことそれ自身は、前回までは、エラーではありませんでした * E列への転記が必要との趣旨は理解したので、追記しました * うえに書いたとおり既に発生したエラーは教えてください。でないといくらやってもエラーで転記されないことが予想されます
mitsuba

2018/11/13 12:30

ご返信および実装までお試しいただきまして、ありがとうございます。 StackDriverのエラーは、以下と表示されています。 ReferenceError: 「buildSubject」が定義されていません。 at onSubmit(コード:39) developerhubの仕様は、あまり大きく変更されていない為、 今回のエラーとは関係ない旨、承知いたしました。 また、再掲ありがとうございます。私の理解不足な点が多く、申し訳ありません。 E列への転記が実施されると知り、安心いたしました。 引き続き、アドバイスいただけますとたすかります。 よろしくお願いします。
papinianus

2018/11/13 12:34

申し訳ないです。そのエラーは私のコピペミスです。現在のコードをまるっとコピペしなおして、フォームから送信してみて下さい。 トリガー設定はコピペし直しても残ります。
mitsuba

2018/11/14 00:48

papinianus様  改めてコードをコピーし、フォームより回答いたしましたら、無事にメールが送付される様になりました。 大変感謝しております。この度は、本当にありがとうございました。
mitsuba

2018/11/16 00:30

papinianus様 度々のご質問となり、申し訳ありません。 先日、無事にフォーム回答後メールが送付される様になりましたが、Googleフォームの設定で、 回答数を1回に制限してしまうと、スクリプトが実行されず、メールが送付されなくなってしまいました。 できれば、1人1回の回答に制限したいのですが、スクリプトの変更をする必要がありますでしょうか? もし、なにかご存じでしたら、お教えいただけますとたすかります。
papinianus

2018/11/19 09:35

それで取れないというのは初耳なので見てみます。
mitsuba

2018/11/20 04:15

papinianus様 度々お手数をおかけいたしまして、申し訳ありません。 新規フォームおよび新規スプレッドシートを作成して再度設定してみましたが、結果は変わらない状況です。
papinianus

2018/11/28 09:05

返答が遅くなって申し訳ないです。 回数の制限をかけましたが、特段の設定なしに、動作しました。 後から、フォームの制限をしたことが影響する場合もあるようには思いますが、試行されたとおり、フォームを再度作成すると、スクリプトの動作権限があらためて設定されるため、動作しないことはないはず。 エラーのメールがgoogleからきていませんか?トリガー設定に「エラー通知設定」があるのでそれをただちに受け取るにして送信していただけませんか? ※こちらはGsuiteで試しています。
guest

0

エラーの直接的な原因は、セルの番地の誤りです。
メールアドレスは、mailto、mailtoはcell.getValue()、cellは、ss.getRange(i, 2)ですが、初回のループにおいて、iは1なので、最初にB1を取得します。メールアドレスという文字はメールアドレスではないのでエラー。

しかしながら、ここから直してもつらいものがあるので、もうなかったことになりました。

  • 追記依頼に一回答えていただいた時点では、番号はランダムではなくむしろシャッフルです。
  • シャッフル前提だと、番号は何らかの手法で生むしかない

→ここが一番今のネック。1番や2番のチームはデスクやパーティションで区切られたブースを表現しているはずだが、そこに何席配置するかは出勤社員総数によって変動する可能性がある。また200人もいるとなると6人席だとしても30チーム以上必要になる。とりあえずコードに配列でベタ書きしたが、上手く入れるインターフェースが思いつかない(gasでは)。というか思い付く方法がどれもとても面倒。

  • どのような方法だろうが、生み出してシャッフルするなら、チーム番号シートは無用の長物
  • 手動実行しかない(なぜなら、200人の社員が今日(明日)何人出社するかは分からず、全員フォームに投稿したかどうかは、事前に判定できない。

→やり方として、シャッフルしたチーム番号シートをキューだと思えば都度送信できなくないが、数名が有給をとると、チーム番号キューに残存がでてしまい、有効な座席配置は望めない

仕様をもっと具体的に明確にしてください。

下記のbuildTeamにある配列を適切に(人数にあわせて)作り直して、toutafait関数を必要な都度実行してくださいな。

javascript

1function setData(sheetname, toclear, dat, offsetR, offsetC) { 2 const name = sheetname || "リスト"; 3 const shouldClear = toclear === undefined ? true : toclear; 4 const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name); 5 if(shouldClear) { sheet.clear(); } 6 sheet.getRange(1 + offsetR, 1 + offsetC, dat.length, dat[0].length).setValues(dat); 7} 8function buildTeams() { 9 const teamNums = [ 10 ["1",5], // ["チームナンバー", 0], //記号は意味がある、0のところには、同じチームに何人属すか 11 ["2",5], 12 ["3",4], 13 ["4",4], 14 ["5",4], 15 ["6",4], 16 ["7",4], 17 ["8",4], 18 ["9",4], 19 ["10",4], 20 ["11",4], 21 ["12",4], 22 ["13",4], 23 ["14",4], 24 ["15",4] 25 ]; 26 var ret = []; 27 for(var i = 0; i < teamNums.length; i++) { 28 for(var j = teamNums[i][1]; j; j--) { 29 ret.push([teamNums[i][0]]); 30 } 31 } 32 for(var i = ret.length - 1; i > 0; i--){ 33 var r = Math.floor(Math.random() * (i + 1)); 34 var tmp = ret[i]; 35 ret[i] = ret[r]; 36 ret[r] = tmp; 37} 38 return ret; 39} 40function sendMails(sheetname) { 41 const name = sheetname || "リスト"; 42 const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name); 43 const vals = sheet.getDataRange().getDisplayValues(); 44 vals.shift(); //ヘッダ行を捨てる 45 vals.forEach(function(row){ 46 MailApp.sendEmail(row[1], buildSubject(row[3]), buildSubject(row[3],row[4])); 47 }); 48} 49function buildSubject(name) { 50 return name + "座席のお知らせ"; 51} 52function buildBody(name, seat) { 53 return name + " さん\n\n" + "***\n\nフォーム投稿、ありがとうございます。\nテーブル番号を、お知らせします。\n\n◆テーブル番号\n" + seat + "\n*** \n\n\n短い時間ではありますが、一緒に楽しみましょう!\n\n"; 54} 55function toutafait() { 56 setData("リスト", false, buildTeams(), 1, 4); 57 sendMails("リスト"); 58}

これってよく考えたら、一回やったら前回のform投稿が残っているから2回目以降正常に動かなくないですかね…なんか壮大に無駄なことしてる気がする…仕様を、仕様をちゃんと考えてください。コンサルさせるなら十分なお金をください…

投稿2018/11/08 12:50

編集2018/11/08 14:38
papinianus

総合スコア12705

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問