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

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

ただいまの
回答率

88.58%

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

解決済

回答 2

投稿

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

mitsuba

score 20

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); // メール送信』

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

よろしくお願いします。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • papinianus

    2018/11/08 19:31

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

    キャンセル

  • papinianus

    2018/11/08 19:34

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

    キャンセル

  • mitsuba

    2018/11/09 15:03

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

    キャンセル

回答 2

checkベストアンサー

+1

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

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

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

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

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

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/11/19 18:35

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

    キャンセル

  • 2018/11/20 13:15

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

    キャンセル

  • 2018/11/28 18:05

    返答が遅くなって申し訳ないです。
    回数の制限をかけましたが、特段の設定なしに、動作しました。

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

    ※こちらはGsuiteで試しています。

    キャンセル

0

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

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

  • 追記依頼に一回答えていただいた時点では、番号はランダムではなくむしろシャッフルです。
  • シャッフル前提だと、番号は何らかの手法で生むしかない
    →ここが一番今のネック。1番や2番のチームはデスクやパーティションで区切られたブースを表現しているはずだが、そこに何席配置するかは出勤社員総数によって変動する可能性がある。また200人もいるとなると6人席だとしても30チーム以上必要になる。とりあえずコードに配列でベタ書きしたが、上手く入れるインターフェースが思いつかない(gasでは)。というか思い付く方法がどれもとても面倒。
  • どのような方法だろうが、生み出してシャッフルするなら、チーム番号シートは無用の長物
  • 手動実行しかない(なぜなら、200人の社員が今日(明日)何人出社するかは分からず、全員フォームに投稿したかどうかは、事前に判定できない。
    →やり方として、シャッフルしたチーム番号シートをキューだと思えば都度送信できなくないが、数名が有給をとると、チーム番号キューに残存がでてしまい、有効な座席配置は望めない

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

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

function setData(sheetname, toclear, dat, offsetR, offsetC) {
  const name = sheetname || "リスト";
  const shouldClear = toclear === undefined ? true : toclear;
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  if(shouldClear) { sheet.clear(); }
  sheet.getRange(1 + offsetR, 1 + offsetC, dat.length, dat[0].length).setValues(dat);  
}
function buildTeams() {
  const teamNums = [
    ["1",5], // ["チームナンバー", 0], //記号は意味がある、0のところには、同じチームに何人属すか
    ["2",5],
    ["3",4],
    ["4",4],
    ["5",4],
    ["6",4],
    ["7",4],
    ["8",4],
    ["9",4],
    ["10",4],
    ["11",4],
    ["12",4],
    ["13",4],
    ["14",4],
    ["15",4]
  ];
  var ret = [];
  for(var i = 0; i < teamNums.length; i++) {
    for(var j = teamNums[i][1]; j; j--) {
      ret.push([teamNums[i][0]]);
    }
  }
  for(var i = ret.length - 1; i > 0; i--){
    var r = Math.floor(Math.random() * (i + 1));
    var tmp = ret[i];
    ret[i] = ret[r];
    ret[r] = tmp;
}
  return ret;
}
function sendMails(sheetname) {
  const name = sheetname || "リスト";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  const vals = sheet.getDataRange().getDisplayValues();
  vals.shift(); //ヘッダ行を捨てる
  vals.forEach(function(row){
    MailApp.sendEmail(row[1], buildSubject(row[3]), buildSubject(row[3],row[4]));
  });
}
function buildSubject(name) {
  return name + "座席のお知らせ";
}
function buildBody(name, seat) {
  return  name + " さん\n\n" + "***\n\nフォーム投稿、ありがとうございます。\nテーブル番号を、お知らせします。\n\n◆テーブル番号\n" + seat + "\n*** \n\n\n短い時間ではありますが、一緒に楽しみましょう!\n\n";
}
function toutafait() {
  setData("リスト", false, buildTeams(), 1, 4);
  sendMails("リスト");
}

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

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

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

同じタグがついた質問を見る