スプレッドシートとGASでGmailの自動送信をしたいです。

Question

GASとスプレッドシートを使って、自動でメールが送信されるようにしたいです。

【スプレッドシート】
タスクを管理しています。
複数人で管理しており、終わった人は自分の名前の列、対象のタスクの行、のセルにあるチェックボックスにチェックを入れます。（シート1）

・昨日が期限だがまだチェックが入ってない人
・今日が期限だがチェックがまだ入ってない人
・明日が期限だがチェックがまだ入ってない人
に自動でメールが送信されるようにしたいです。

シート1、シート2、シート3を使い、シート4に以下の内容が表示されるようにはできました。
◎シート4

	A	B	C	D
1	配信日	TO	件名	本文
2	(今日の日付)	[e-mail],[e-mail]	昨日までのタスクが～	～～～
3	(今日の日付)	[e-mail]	昨日までのタスクが～	～～～
4	(今日の日付)	[e-mail]	昨日までのタスクが～	～～～
5	(今日の日付)	[e-mail],[e-mail]	今日までのタスクが～	～～～
6	(今日の日付)	[e-mail]	今日までのタスクが～	～～～
7	(今日の日付)	[e-mail]	今日までのタスクが～	～～～
8	(今日の日付)	[e-mail],[e-mail]	明日までのタスクが～	～～～
9	(今日の日付)	[e-mail]	明日までのタスクが～	～～～
:	:	:	:	:

A列はtoday関数です。
B列は表示するまでに、filter関数、if関数、today関数を使用しています。
C列は直接入力しています。
D列は表示するまでに、filter関数、if関数、today関数を使用しています。その他"&"、"CHAR(10)"を使っています。

◎GAS
https://blog.members.co.jp/article/46994
を参考にして、設定でき送信も問題なくできたのですが、これだと1行しか実行できません。

const sheet = SpreadsheetApp.getActive().getSheetByName('シート4');//''の間にシート名を入れるとそのシート名の情報を持ってくる
const recipient = sheet.getRange(2, 2).getValue(); // TO B2
const subject = sheet.getRange(2, 3).getValue(); // 件名 C2
const body = sheet.getRange(2, 4).getValue(); // 本文 D2
const remind_hour = "21";//この時間だけ動作
const date = Utilities.formatDate(new Date(), "Asia/Tokyo", "yyyy/MM/dd"); //今日の日付
const hour = Utilities.formatDate(new Date(), "Asia/Tokyo", "H"); //今の時間
const workday = Utilities.formatDate(sheet.getRange(2, 1).getValue(), "Asia/Tokyo", "yyyy/MM/dd"); //配信日 A2

function sendMail(){
if(hour == remind_hour && workday == date){ //日時が合っているか
GmailApp.sendEmail(recipient, subject, body,
{from:'[e-mail]',
name:'タスク通知'
}); //下書きにする場合はcreateDraft、直接送る場合はsendEmail
}
}

function onOpen(){
SpreadsheetApp.getUi()
.createMenu('メール')
.addItem('メール送信', 'sendMail')
.addToUi();
}

75行すべて実行したいのですが、どのように書き換えたらいいのでしょうか...
また、可能であれば「昨日期限」「今日期限」「明日期限」で送信時刻を変えたいです。

よろしくお願い致します。

Answer 1

const recipient = sheet.getRange(2, 2).getValue(); // TO B2
const subject = sheet.getRange(2, 3).getValue(); // 件名 C2
const body = sheet.getRange(2, 4).getValue(); // 本文 D2

ここで単一行しか取得していないので、１行しか実行されません。

const recipients = sheet.getRange(2, 2, 74, 1).getValues().flat(); // TO B2~B75
const subjects = sheet.getRange(2, 3, 74, 1).getValues().flat(); // 件名 C2~C75
const bodys = sheet.getRange(2, 4, 74, 1).getValues().flat(); // 本文 D2~D75

for (let i = 1; i <= 75; i++) {
  let recipient = recipients[i-1]
  let subject = subjects[i-1]
  let body = bodys[i-1]
  GmailApp.sendEmail(recipient, subject, body,
    {
      from: '[e-mail]',
      name: 'タスク通知'
     }); //下書きにする場合はcreateDraft、直接送る場合はsendEmail
   }

複数行取得するようにすれば実現可能ではないかと思います。

---

送信時間を変えるについては、判別できる情報があれば
下記のようにすることで変更可能になります。

// 上から、昨日期限、今日期限、明日期限の 送信時間
const yesterdayDeadlineHour = "18"
const todayDeadlineHour = "20"
const tomorrowDeadlineHour = "22" 
let remind_hour = "21";// 代入させるため、letに変更

// セルから取得するなど
let xxxx = "??期限"

if (xxxx == "昨日期限") remind_hour = yesterdayDeadlineHour
else if (xxxx == "今日期限") remind_hour = todayDeadlineHour
else if (xxxx == "明日期限") remind_hour = tomorrowDeadlineHour

if (hour == remind_hour && workday == date) { //日時が合っているか

・・・以下略・・・

Comments

[rrr17]

ありがとうございます！
できました！
追加で質問させて頂いてもいいでしょうか？
今後行数を増やす可能性があるのですが、どこを変更したらいいでしょうか。

[k.a_teratail]

コードでの74と75のところを変更する必要があります。

例えば行数が97行の場合
以下３パターンの方法があります。
----
・シートの入力値から参照した場合

// シートの最終行、97が取得できる
// ただし、97行目以降のセルに値が入力されていると
// その行数が取得されるので注意
const row = sheet.getLastRow()

----
・コードで定義した場合
// 反映する行数
// 反映行数が何度も変わる場合、手動での修正が必要
const row = 97

----
・特定列の最終行の場合

// A列の最終行
const colRange = "A:A"
// 97が取得できる
const row = sheet.getRange(`${colRange}${sheet.getRange(colRange).getLastRow()}`).getNextDataCell(SpreadsheetApp.Direction.UP).getLastRow()

----
・以下共通で修正
sheet.getRange(2, 2, row - 1, 1).getValues().flat() // B2〜B97
// 他の範囲も同様に

// 97回繰り返す
for (let i = 1; i <= row; i++)

[rrr17]

ありがとうございます。
増やすこともできました。
最後にもう一点お伺いしてもいいでしょうか？
B列が空白だった場合はスキップしたいのですがどのようにしたらいいのでしょうか。

[k.a_teratail]

B列のデータはrecipientsにあり
recipientで一つずつ取得していると仮定してお答えします。

if (recipient != "") { // 空白ではない時にメール送信
GmailApp.sendEmail()
}