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

Question

Googleフォームに投稿すると、ランダムの席（数字）が割り当てられ、割り当てられた席（数字）を、
フォーム投稿者へ自動返信される様な仕組みを作成したいと思っております。


Googleフォームと連動している回答のスプレッドシートは、以下の様に設定しております。

≪リストシート≫
![イメージ説明](00d6165bf5b0c4c999ebadbd0db060d6.png)

≪チーム番号シート≫
![イメージ説明](2b5b0a6e81e059dbba71f92d111af010.png)


まず、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 + " さん

"; 
       content += "＊＊＊

";  
       content += "フォーム投稿、ありがとうございます。
";
       content += "テーブル番号を、お知らせします。

";
       content += "◆テーブル番号
";
       content +=   　　seat + "
";
       content += "＊＊＊

";
       content += "短い時間ではありますが、一緒に楽しみましょう！

";
    var mailto = cell.getValue() 
    MailApp.sendEmail(mailto, subject, content); // メール送信
      }
    }
```


【エラー内容】
　「無効なメール: メールアドレス（行 46、ファイル「コード」）」
　　＃行46は、『MailApp.sendEmail(mailto, subject, content); // メール送信』
        
検索した内容や過去作成したコードを利用し作成しております為、
初歩的なミス等が多い場合は、合わせてお教えいただけますとたすかります。

よろしくお願いします。

Accepted Answer

使い回しではあるんですが、仕様ちがうんで回答をかえます。

* 使い方
0. 【フォーム投稿受付前】下記をフォームが到着するスプレッドシートにスクリプトとして書き、シート名をリストおよびチーム番号にします
0. 【フォーム投稿受付前】チーム番号シートのA1セルに`=buildTeams()`と入れます。少しまつと、205行目まで番号が埋まります(17*12は204なんで200を越えるのはしかたないです。11人席をつくれば人数をあわせることは可能)
0. 【フォーム投稿受付前】↑で作ったシャッフル済みの番号に偏りがないかをみます。偏りがある場合はA1セルを消し、再度書きかえます。また偏りがなくOKだと思えば、コピーして値のみはりつけをします(←A1をさわらなければ変わらないはずですが、万一リストが書きかわると問題なので念の為)
0. 【フォーム投稿受付前】onSubmitをトリガー設定します(フォーム送信時)
0. フォームを公開し、投稿を受け付けます

※下記に【2】氏名、とありますが、これは正確にフォームの名称と対応している必要があります。たとえば2が全角２であるなら、エラーにならないよう事前に修正してください。

仕様としてチーム番号シートを見ればあらかじめ、何番目に投稿すれば何番のチームになるかは分かります。しかし狙って何番目の投稿者になるかは予測できないであろうこと、このシートは閲覧させないであろうことから許容できる仕様だと思っております。また、ランダムとシャッフルの意味の違いを考えればこれはやむないことです。偏りのないシャッフルを考えると全体について母数が多い状態つまり事前にシャッフルをする、がベストです。

```javascript
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 + " さん

" + "＊＊＊

フォーム投稿、ありがとうございます。
テーブル番号を、お知らせします。

◆テーブル番号
" + seat + "
＊＊＊

短い時間ではありますが、一緒に楽しみましょう！

";
}
```

Answer

エラーの直接的な原因は、セルの番地の誤りです。
メールアドレスは、`mailto`、mailtoは`cell.getValue()`、cellは、`ss.getRange(i, 2)`ですが、初回のループにおいて、iは1なので、最初にB1を取得します。メールアドレスという文字はメールアドレスではないのでエラー。

しかしながら、ここから直してもつらいものがあるので、もうなかったことになりました。

* 追記依頼に一回答えていただいた時点では、番号はランダムではなくむしろシャッフルです。
* シャッフル前提だと、番号は何らかの手法で生むしかない
→ここが一番今のネック。1番や2番のチームはデスクやパーティションで区切られたブースを表現しているはずだが、そこに何席配置するかは出勤社員総数によって変動する可能性がある。また200人もいるとなると6人席だとしても30チーム以上必要になる。とりあえずコードに配列でベタ書きしたが、上手く入れるインターフェースが思いつかない(gasでは)。というか思い付く方法がどれもとても面倒。
* どのような方法だろうが、生み出してシャッフルするなら、チーム番号シートは無用の長物
* 手動実行しかない(なぜなら、200人の社員が今日(明日)何人出社するかは分からず、全員フォームに投稿したかどうかは、事前に判定できない。
→やり方として、シャッフルしたチーム番号シートをキューだと思えば都度送信できなくないが、数名が有給をとると、チーム番号キューに残存がでてしまい、有効な座席配置は望めない

仕様をもっと具体的に明確にしてください。

下記のbuildTeamにある配列を適切に(人数にあわせて)作り直して、toutafait関数を必要な都度実行してくださいな。
```javascript
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 + " さん

" + "＊＊＊

フォーム投稿、ありがとうございます。
テーブル番号を、お知らせします。

◆テーブル番号
" + seat + "
＊＊＊

短い時間ではありますが、一緒に楽しみましょう！

";
}
function toutafait() {
  setData("リスト", false, buildTeams(), 1, 4);
  sendMails("リスト");
}
```

これってよく考えたら、一回やったら前回のform投稿が残っているから2回目以降正常に動かなくないですかね…なんか壮大に無駄なことしてる気がする…仕様を、仕様をちゃんと考えてください。コンサルさせるなら十分なお金をください…

関連した質問