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

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

新規登録して質問してみよう
ただいま回答率
85.46%
Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google Apps Script

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

Q&A

1回答

241閲覧

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

10-mo

総合スコア21

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google Apps Script

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

0グッド

2クリップ

投稿2024/03/19 05:35

実現したいこと

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

前提

[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は下書きメールにあるのですが、その他の情報がスプレの1行目やらその他の行やら、取得されて入っています。

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

該当のソースコード

//参考サイト(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); //スプレッドシートに書き戻し

// /回答の名前と一致する行の1列目を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") {

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'
  • "-------------------------------\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/ツールのバージョンなど)

特にありません。

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

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

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

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

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

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を検索する方が良いかと思います。
guest

回答1

0

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

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

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

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

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

ただいまの回答率
85.46%

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

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

質問する

関連した質問