スプレッドシートから、下書きメールを一括で作成したい

実現したいこと

スプレッドシートから、下書きメールを一括で作成したい

前提

[13日一覧]と[13日来場者]という二枚のスプレッドシートがあります。一覧はデータベースで、来場者のシートは、IDやQRコードを生成しているシートになります。データベースから来場者シートを転記し、そのシートでIDとQRコードを生成しています。

一覧シート
A：タイムスタンプ
B：メアド
C：時間
D：名前
E：人数（10時）
F：人数（11時）
G：人数（13時）
H：人数（14時）
I：人数（15時）

来場者シート
A：来場者ID
B：メアド
C：名前
D：来場方法
E：人数
F：受付状況
G：来場時間
H：QRコード
I：フラグ

発生している問題・エラーメッセージ

・メールの下書きの内容が、スプレッドシートの不要なデータが入ってしまい、取得したいデータが取れていません。タイトルと日本語で書かれたbodyは下書きメールにあるのですが、その他の情報がスプレの１行目やらその他の行やら、取得されて入っています。

エラーメッセはありません。

該当のソースコード

//参考サイト（https://note.com/taraco123/n/nd53a0c2329d2）
//

//QRコード作成＋IDを自動生成
//--------------スプレッドシート（フォームの回答）のデータ取得-----------
function qrcreat() {
var ss = SpreadsheetApp.getActiveSpreadsheet();//スプレッドシートを有効にする
var fsheet = ss.getSheetByName("13日一覧");//フォームの回答
var fsheet_Range = fsheet.getDataRange();
var fData = fsheet_Range.getValues();

//--------------各項目を抜き出し----------
var stamp = fData[0]; //タイムスタンプ
var mail = fData[1]; //メール
var date = fData[2]; //時間
var name = fData[3]; //名前
var total = fData[4]+fData[5]+fData[6]+fData[7]+fData[8]; //人数
var way = fData[10]; //来場方法

//----------------ここまでがフォームの回答を取得-------------------//
//----------------ここからが、来場者シートに転記-------------------//

var sht = ss.getSheetByName("13日来場者");
//console.log(sht);

var lastrow = sht.getLastRow(); // 最終行を取得
var lastcol = sht.getLastColumn(); // 最終列を取得
var range = sht.getRange(1, 1, lastrow, lastcol);
var values = range.getValues(); // 情報をオンメモリに保持

//----------------ここからが、QR作成とID設定------------------//
// /*来場者シートのデータを全部取得し、1行ずつチェック。
// A列が空欄の場合はランダムな文字列を記入。
// I列にQRコードの数式を設定
// */
for (var i = 1; i < lastrow; i++) {
data = values[i][0];
if (data == "") {
values[i][0] = getRndStr();
}
values[i][8] = false;
var qrc1 = '=image("https://chart.apis.google.com/chart?chs=250x250&cht=qr&chl=\"&A' + (i + 1) + ')';
values[i][7] = qrc1;
}
range.setValues(values); //スプレッドシートに書き戻し

// /回答の名前と一致する行の１列目をidとして取得する/
for (let row = 1; row <= lastrow; row++) {
if (sht.getRange(row, 3).getValue() == name) {
var id = sht.getRange(row, 1).getValue();
}
}

// メール作成時にstamp, email, date, id, name, totalを渡す。
createDraft(stamp, email, date, id, name, total);
}

//----------------ここからが、下書きメール作成------------------//

function createDraft(stamp, email, date, id, name, total) {

//11時のみ送信
if (date === "2024年4月1日（月） 11:00") {

const mailTitle = "イベント予約結果について";
const imageurl = 'https://chart.apis.google.com/chart?chs=250x250&cht=qr&chl=' + id;
const response = UrlFetchApp.fetch(imageurl); // option削除
const blob = response.getBlob().getAs(MimeType.JPEG);
const option = {
method: "get",
"attachments":blob,
'name':'イベント事務局'
}
// // QRコード付きのメール
var mailBody = "この度はお申込みをいただきありがとうございます。下記の内容でご予約を承りました。\n 当日、このメールに添付されているQRコードを受付でご提示ください。 \n\n"

' 【受付日】: ' + stamp + ' \n'
【予約日】:${date}\n
' 【氏名】:' + name + '様\n'
' 【来場者ID】:' + id + '\n'
' 【お申込み人数】：' + total + '名\n'
' 【URL】:' + + '名\n'
"-------------------------------\n"
"-------------------------------\n"

GmailApp.createDraft(email, mailTitle, mailBody, option);
}

//----------------ここからが、11時以外の下書きメール作成------------------//
// //11時以外
if (date !== "2024年4月1日（月） 11:00") {

' 【受付日】: ' + stamp + ' \n'
【予約日】：${date}\n
' 【氏名】：' + name + '様\n'
' 【来場者ID】：' + id + '\n'
' 【お申込み人数】：' + total + '名\n'
"-------------------------------\n"
"-------------------------------\n"

GmailApp.createDraft(email, mailTitle, mailBody, option);
}
}

function getRndStr() {
var str = "abcdefghijklmnopqrstuvwxyz0123456789";
var len = 8;
var result = "";
for (var i = 0; i < len; i++) {
result += str.charAt(Math.floor(Math.random() * str.length));
}
return result;
}

// function updateForm() {
// console.log("ダミーのupdateForm()が呼ばれました。")
// }

試したこと

function createDraft(stamp, email, date, id, name, total)
で、function qrcreatで書いたコードを受けるようにしたのですが、上手くいきませんでした。

どのように変更すればうまくいくのか、ご教示ください。
よろしくお願いいたします。

補足情報（FW/ツールのバージョンなど）

特にありません。

YellowGreen

2024/03/19 07:03

希望の処理がよくわかりませんが、データベースの各行のデータごとに下書きを作成するのであれば、 var fsheet_Range = fsheet.getDataRange(); var fData = fsheet_Range.getValues(); としてfDataの値を取得しているのですから、 fData はデータのセル範囲の各セルの二次元配列になっているので、タイムスタンプ以下の各列の要素は各行ごとの繰り返し処理で例えば、 for (var i = 0; i < fData.length; i ++) { // 見出しがあるなら i = 1 var stamp = fData[i][0]; //タイムスタンプ var mail = fData[i][1]; //メール var date = fData[i][2]; //時間 var name = fData[i][3]; //名前 var total = fData[i][4]+fData[i][5]+fData[i][6]+fData[i][7]+fData[i][8]; //人数 var way = fData[i][10]; //来場方法のように取得して、その先の下書き作成までを繰返し処理の中に入れておく必要があると思いますがいかがでしょうか。

YellowGreen

2024/03/20 04:14

個別に変更箇所を説明していくと長くなるので、修正後のスクリプトを回答欄に記入しました。基本的には上で説明したように各行単位に処理を行っています。各列の値は、配列に分割代入しています。

YellowGreen

2024/03/20 04:58

名前の一致でidを検索していますが、同姓同名を考慮するとメールアドレスの一致でidを検索する方が良いかと思います。

行動規範の内容に同意します

回答1件

かなり推測の部分がありますが、
例えば次のようなスクリプトをご希望でしょうか。

来場者シートの処理を先に行ってから、
一覧シートの各行ごとに各列のセルの値を取得して
来場者シートと名前が一致（同姓同名は考慮しない）したら
メールの下書きを作成します。

JavaScript
1//QRコード作成＋IDを自動生成
2//--------------スプレッドシート（フォームの回答）のデータ取得-----------
3function qrcreat() {
4  const ss = SpreadsheetApp.getActiveSpreadsheet();//スプレッドシートを有効にする
5
6  // 来場者シートの処理
7
8  const sht = ss.getSheetByName("13日 来場者");
9  const lastRow = sht.getLastRow(); // 最終行を取得
10  const lastCol = sht.getLastColumn(); // 最終列を取得
11  const range = sht.getRange(1, 1, lastRow, lastCol);
12  const values = range.getValues(); // 情報をオンメモリに保持
13
14  //----------------ここからが、QR作成とID設定------------------//
15  // /*来場者シートのデータを全部取得し、1行ずつチェック。
16  // A列が空欄の場合はランダムな文字列を記入。
17  // I列にQRコードの数式を設定
18  // */
19  for (let i = 1; i < lastRow; i++) {
20    if (values[i][0] == "") {
21      values[i][0] = getRndStr();
22    }
23    values[i][7] = '=image("https://chart.apis.google.com/chart?chs=250x250&cht=qr&chl=\"&A' + (i + 1) + ')';
24    values[i][8] = false;
25  }
26  range.setValues(values); //スプレッドシートに書き戻し
27
28  // 一覧シートの処理
29
30  const fsheet = ss.getSheetByName("13日 一覧"); // フォームの回答
31  const fsheet_Range = fsheet.getDataRange();
32  const fData = fsheet_Range.getValues(); // タイムスタンプの日付をそのまま取得
33
34  for (let i = 1; i < fData.length; i++) {
35    //--------------各項目を抜き出し----------
36    const [
37      stamp,      // A列: タイムスタンプ 
38      email,       // B列: メール
39      date,       // C列: 時間
40      name,       // D列: 名前
41      timeZone10, // E列: 10時
42      timeZone11, // F列: 11時
43      timeZone13, // G列: 13時
44      timeZone14, // H列: 14時
45      timeZone15, // I列: 15時
46      , // J列: （★未使用）
47      way, // K列: 来場方法（★未使用）
48    ] = fData[i];
49    const total = +timeZone10 + +timeZone11 + +timeZone13 + +timeZone14 + +timeZone15; // 人数を合計
50
51    // 回答の名前と一致するidを取得
52    const id = values.find(v => v[2] == name)?.[0];
53
54    // メール作成時にstamp, email, date, id, name, totalを渡す。
55    if (id) {
56      createDraft(stamp, email, date, id, name, total);
57    } else {
58      console.log(`来場者シートに [${name}] という名前が見つかりませんでした。`);
59    }
60  }
61}
62
63//----------------ここからが、下書きメール作成------------------//
64
65function createDraft(stamp, email, date, id, name, total) {
66  // タイムスタンプを文字列に
67  const weekDays = '日月火水木金土';
68  stamp = Utilities.formatDate(stamp, 'JST', `yyyy年M月d日（${weekDays.at(stamp.getDay())}） H:mm`);
69
70  //11時のみ送信
71  if (date === "2024年4月1日（月） 11:00") {
72
73    const mailTitle = "イベント予約結果について";
74    const imageurl = 'https://chart.apis.google.com/chart?chs=250x250&cht=qr&chl=' + id;
75    const response = UrlFetchApp.fetch(imageurl); // option削除
76    const blob = response.getBlob().getAs(MimeType.JPEG);
77    const option = {
78      method: "get",
79      "attachments": blob,
80      'name': 'イベント事務局'
81    }
82    // QRコード付きのメール　（★URLは？？）
83    const mailBody = `この度はお申込みをいただきありがとうございます。下記の内容でご予約を承りました。\n 当日、このメールに添付されているQRコードを受付でご提示ください。
84
85 【受付日】: ${stamp}
86 【予約日】: ${date}
87 【氏 名】: ${name} 様
88 【来場者ID】: ${id}
89 【お申込み人数】： ${total} 名
90 【URL】: 名
91-------------------------------
92-------------------------------
93`;
94
95    GmailApp.createDraft(email, mailTitle, mailBody, option);
96  } else {
97
98  //----------------ここからが、11時以外の下書きメール作成------------------//
99  // //11時以外
100
101    const mailTitle = "イベント予約結果について";
102    const imageurl = 'https://chart.apis.google.com/chart?chs=250x250&cht=qr&chl=' + id;
103    const response = UrlFetchApp.fetch(imageurl); // option削除
104    const blob = response.getBlob().getAs(MimeType.JPEG);
105    const option = {
106      method: "get",
107      "attachments": blob,
108      'name': 'イベント事務局'
109    }
110    // // QRコード付きのメール
111    const mailBody = `この度はお申込みをいただきありがとうございます。下記の内容でご予約を承りました。\n 当日、このメールに添付されているQRコードを受付でご提示ください。
112
113 【受付日】: ${stamp}
114 【予約日】： ${date}
115 【氏 名】： ${name} 様
116 【来場者ID】: ${id}
117 【お申込み人数】： ${total} 名
118-------------------------------
119-------------------------------
120`;
121
122    GmailApp.createDraft(email, mailTitle, mailBody, option);
123  }
124}
125
126function getRndStr() {
127  const str = "abcdefghijklmnopqrstuvwxyz0123456789";
128  const len = 8;
129  let result = "";
130  for (let i = 0; i < len; i++) {
131    result += str.charAt(Math.floor(Math.random() * str.length));
132  }
133  return result;
134}

同姓同名を考慮してメアドでidを検索するなら

JavaScript
1    // 回答の名前またはメールアドレスと一致するidを取得
2    // const id = values.find(v => v[2] == name)?.[0];
3    const id = values.find(v => v[1] == email)?.[0];

JavaScript
1      // console.log(`来場者シートに [${name}] という名前が見つかりませんでした。`);
2      console.log(`来場者シートに [${email}] というメールアドレスが見つかりませんでした。`);

の2箇所を修正します。

投稿2024/03/20 04:11

編集2024/03/20 05:07

YellowGreen

総合スコア731

YellowGreen

2024/03/20 04:28 編集

スプレッドシートのデータ形式がわからないので、タイムスタンプはフォームの回答が自動蓄積されたままであるものとして日付データ希望日はご提示のスクリプトが一致を直接比較しているので文字列データであることを前提にしています。参加人数は。文字列であっても希望の時間帯だけに値があって他の時間帯が空白なら、文字列として結合しても構わないかと思いましたが、念のため数値にしてから合計しています。

10-mo

2024/03/20 05:49

こちらのレス、反応遅くなり申し訳ありません。また、質問内容がわかりづらくなっておりましてご迷惑をお掛け致しました。ご提示させていただいたスクリプトで稼働するものの、下書きメールの内容が、本来は、「送信先のメールアド・タイトル・本文・添付ファイル（ＱＲコード）」になることを想定して作りました。しかし、実際動かしてみると、送信先メールアド⇨✖ タイトル⇨◯ 本文⇨✖ ＱＲ⇨◯ というものになっていました。上記いただたスクリプトで試してみたところ、正常に作動していました。ありがとうございます！findの使い方、こうすれば良いのですね。。。他のやり方でも応用できそうです。本当にここまでご教示いただきありがとうございました！

行動規範の内容に同意します