google spread sheetで期日になったら通知するGAS

やりたいこと

Google Spread Sheetで業務や在庫などの期日を管理している場面で、期日間近になったら定期的に通知してくれるようにしたい。
期日x前に注意通知先のアドレス、期日過ぎたら警告通知先のアドレスに送信するようにする。

管理シートの形式

大項目	中項目	小項目	担当者	期日	注意通知の送り先email	警告通知の送り先email
AAA	aaa	あ	高橋	2023/8/1	takahashi@mail	section1@mail
AAA	bbb	い	佐藤	2023/5/1	satou@mail	section1@mail
BBB	ccc	あ	渡辺	2023/7/1	watanabe@mail	section1@mail
CCC	bbb	う	斉藤	2023/7/1	saitou@mail	section1@mail
BBB	ddd	お	高橋	2023/4/1	takahashi@mail	section1@mail
AAA	aaa	え	渡辺	2023/5/1	watanabe@mail	section1@mail
DDD	aaa	あ	渡辺	2023/8/1	watanabe@mail	section1@mail

プログラム

GASは普段プログラムを書かない人も使う可能性があると思うので、コメント多めに入れています。

function ExpiredNotification() {
    // ===== 実行する前のアラーム =====
    let result = Browser.msgBox('期限切れ物品に対し、メールを送信しますか？', Browser.Buttons.OK_CANCEL);
    if (result == 'cancel'){
        return;
    }

    // ===== 対象ファイル =====
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const ss_url = ss.getUrl();

    // ===== 対象シート =====
    // 対象シート上にクリックボタンを配置して実行する場合は、getActiveSheetにする。別シートから実行する場合は、getSheetByNameにする。
    //const sh = ss.getActiveSheet();
    const sh = ss.getSheetByName('保管リスト');

    // ===== 表の行方向範囲 =====
    const start = 2; //開始行番号
    const last = sh.getLastRow();


    // ===== 現日付 =====
    const today = new Date();

    // x日後に保管期日を超える物品に通知する場合は、today.getDate()にx日を足す。
    // 間もなく期限が切れることの通知（注意通知期日）
    const dateBorderA = 30; // ■■■任意設定■■■
    const shresholddateA = new Date(today.getFullYear(), today.getMonth(), today.getDate() + dateBorderA);

    // 期限が切れたことの通知（警告通知期日）
    const dateBorderB = 0; //■■■任意設定■■■
    const shresholddateB = new Date(today.getFullYear(), today.getMonth(), today.getDate() + dateBorderB);


    // ===== 表を連想配列として取り込む =====
    let data = [];
    let strLarge, strMedium, strSmall, strPerson, strLimit, strMail, strGroupMail
    for (let i=start; i<=last; i++){
        // 大項目
        strLarge = sh.getRange('A'+i).getValue();

        // 中項目
        strMedium = sh.getRange('B'+i).getValue();

        // 小項目
        strSmall = sh.getRange('C'+i).getValue();

        // 担当者
        strPerson = sh.getRange('D'+i).getValue();

        // 期日
        strLimit = sh.getRange('E'+i).getValue();

        // 注意通知のメールアドレス
        strMail = sh.getRange('F'+i).getValue();

        // 警告通知のメールアドレス
        strGroupMail = sh.getRange('G'+i).getValue();

        // 連想配列に格納
        data.push({large:strLarge, medium:strMedium, small:strSmall, person:strPerson, limit:strLimit, mail:strMail, groupmail:strGroupMail});
    }; // for i 終了


    // ===== 注意通知 =====
    // 同一連絡先にまとめて送りやすくするため、メールアドレスで降順sortする。
    data.sort((a, b)=>{
        if(a.mail > b.mail) return -1;
        if(a.mail < b.mail) return 1;
        return 0;
    });
    // 期日x日前且つ連絡先ありでフィルタリング
    const filterdataA = data.filter(x => x.limit <= shresholddateA && x.mail !== '');
    // 該当品がない場合はreturn。
    if (filterdataA == ''){
        return;
    }
    // メール配信
    SendMail(filterdataA, 'mail', '(注意)期日間近です', ss_url);


    // ===== 警告通知 =====
    // 同一連絡先にまとめて送りやすくするため、メールアドレスで降順sortする。
    data.sort((a, b)=>{
        if(a.groupmail > b.groupmail) return -1;
        if(a.groupmail < b.groupmail) return 1;
        return 0;
    });
    // 期日過ぎ且つ連絡先ありでフィルタリング
    const filterdataB = data.filter(x => x.limit <= shresholddateB && x.groupmail !== '');
    // 該当品がない場合はreturn
    if (filterdataB == ''){
        return;
    }
    SendMail(filterdataB, 'groupmail', '(警告)期日が過ぎています', ss_url);

} // function 終了


//  ===== メール送信関数 =====
function SendMail(inputData, keyName, strTitle, sheetURL) {
    // 同一連絡先はまとめてメール配信
    const len = Object.keys(inputData).length;   // フィルター後のデータ数
    const strSubject = strTitle;   // メール件名
    const strFooter = 'シートURL：' + sheetURL + '\n\n'
        + '※自動配信のため、本メールに返信不要';
    let strBody = '';   //メール本文の初期化
    let toAddress = '';

    try{
        for (let j=0; j<=len-1; j++){  // 配列は0はじまりなので、最後はlen-1
            //日付のフォーマット
            if (inputData[j].limit !== ''){
                if(isNaN(inputData[j].limit.getFullYear()) || isNaN(inputData[j].limit.getMonth()) || isNaN(inputData[j].limit.getDate())){
                    strLimit = '期日が正しく入力されていません。';
                }else{
                    strLimit = Utilities.formatDate(inputData[j].limit,"JST","yyyy/MM/dd");
                }

            }else{
                strLimit = '期日が空欄か、入力が正しくありません。';
            }
            // メール本文の作成。同一宛先で複数の物品がある場合は、追加していく。
            strBody = strBody
                + '・大項目：' + inputData[j].large + '\n'
                + '・中項目：' + inputData[j].medium + '\n'
                + '・小項目：' + inputData[j].small + '\n'
                + '・担当者：' + inputData[j].person + '\n'
                + '・期日：' + strLimit + '\n'
                + '\n';
            if (j<=len-2){  // 下にデータがまだある場合
                // 下のデータの連絡先が違う場合は、メール送信
                if (inputData[j][keyName] !== inputData[j+1][keyName]){
                    strBody = strBody + strFooter;
                    toAddress = inputData[j][keyName];
                    GmailApp.sendEmail(toAddress, strSubject, strBody);
                    strBody = '';  //メール本文の初期化
                }
            }else{  // 最終データの場合は、メール送信
                strBody = strBody + strFooter;
                toAddress = inputData[j][keyName];
                GmailApp.sendEmail(toAddress, strSubject, strBody);
                strBody = '';  //メール本文の初期化
            }
        } // for j 終了
    }catch(e){
        console.log(strBody);
        console.log(toAddress);
    }
}

変数や関数の補足

const start
には、表の開始番号を入れる。
今回の場合は、項目名が1行目なので2を入れている。

const dateBorderA = 30
が、期日30日前に注意通知をする設定箇所。
宛先は注意通知の送り先email。

const dateBorderA = 0
が、期日0日前(つまり期日切れ)に警告通知をする設定箇所。
宛先は警告通知の送り先email。

sh.getRange('A'+i).getValue()
でA列を取り込んでいる。
列が変わる場合はAの部分を変えること。
（他の列も同様）

GmailApp.sendEmail
の関数でメール送信している。
送信元も変更できるが、権限によってはできない。
複数宛に送信する場合はカンマ区切りでアドレスを入れればOK。

GASのトリガーで関数を実行するようにすれば、定期的に自動で実行してくれる。