スプレッドシートで簡単リマインドツール作成！GASコードはコピペでOK

こんにちは、限界個人事業主です。

今回は、スプレッドシートで管理しているものをGASを使って自動リマインドした方法を書いています。

業務を効率化したい、業務の抜け漏れを防ぐためにスプレッドシートで管理しているものを自動リマインドする方法を書いています。

ちなみに、私はプロのプログラマーではなく、AIを使ってコードを書いているズブの素人。

そんな私でもできたので、「パソコンは苦手」「プログラムと聞くだけでアレルギーが・・」という方でもご安心ください。

この記事を読み終えた頃にはスプレッドシートを使ったリマインドツールが完成しています。

【完成品】スプレッドシートを使ったリマインドツール

今回作成するリマインドツールは以下の機能を持っています。

指定したシートの「メモ／期限／提出」をチェックする
「提出」が空欄 or「未」で、かつ「期限が今日〜3日後」の行だけを対象にする
対象があれば、メールアドレス宛にリマインドメールを送る

使うのはスプレッドシートとGASになります。

まずはスプレッドシートを開いてください。

→スプレッドシートURL

スプレッドシートの1行目に3つの項目を入力します。

• A1：メモ
• B1：期限
• C1：提出

上記の文字を入力します。

リマインドツールのコード設定

スプレッドシートのツールバーの拡張機能の『Apps Script』をクリックします。

するとGASのエディタ画面が開きます。

この画面にリマインド用のコードを貼り付ける流れになります。

今回使用するコードは以下。

/***** 設定 *****/
const SHEET_NAME = 'シート1';
const HEADER_MEMO = 'メモ';
const HEADER_DUE  = '期限';
const HEADER_DONE = '提出';
const REMIND_WINDOW_DAYS = 3; // 本番: 期限まで残り0〜3日のみ送信

/***** 使い方 *****
 * 1) スクリプトプロパティに 'email' を登録（送信先）
 * 2) 本番運用: createDailyTrigger() を一度実行して毎朝9時に自動実行
 * 3) すぐテスト送信: sendRemindersTest() を実行（期間制限なしで対象に送る）
 ************************/

/** 本番向け：期限まで残り0〜3日のみ送信（多重送信防止つき） */
function sendReminders() {
  coreSendReminders({ forceAll: false });
}

/** テスト用：実行したら対象条件に合う行へ “期間制限なし” で即送信（多重送信防止つき） */
function sendRemindersTest() {
  coreSendReminders({ forceAll: true });
}

/** 共通コア */
function coreSendReminders({ forceAll }) {
  const prop = PropertiesService.getScriptProperties();
  const to = String(prop.getProperty('email') || '').trim();
  if (!to) throw new Error('スクリプトプロパティ「email」が未設定です。');

  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName(SHEET_NAME);
  if (!sh) throw new Error(`シートが見つかりません: ${SHEET_NAME}`);

  const lastRow = sh.getLastRow();
  if (lastRow < 2) return;

  const lastCol = sh.getLastColumn();
  const headers = sh.getRange(1, 1, 1, lastCol).getValues()[0];

  const colMemo = headerIndex_(headers, HEADER_MEMO);
  const colDue  = headerIndex_(headers, HEADER_DUE);
  const colDone = headerIndex_(headers, HEADER_DONE);
  if (!colMemo || !colDue || !colDone) {
    throw new Error('ヘッダー名「メモ」「期限」「提出」のいずれかが見つかりません（1行目に完全一致で配置してください）。');
  }

  const data = sh.getRange(2, 1, lastRow - 1, lastCol).getValues();
  const tz = Session.getScriptTimeZone();
  const today = stripTime_(new Date(), tz);
  const sheetId = ss.getId();

  let sent = 0;
  data.forEach((row, i) => {
    const r = i + 2; // 行番号
    const dueRaw = row[colDue - 1];
    const doneRaw = String(row[colDone - 1] ?? '').trim();
    const memo = String(row[colMemo - 1] ?? '').trim();

    // 期限が空なら対象外
    const due = parseAnyDate_(dueRaw, tz);
    if (!due) return;

    // 提出が空欄 or 「未」のときのみ対象（◯は対象外）
    if (doneRaw !== '' && doneRaw !== '未') return;

    const dueDay = stripTime_(due, tz);
    const daysLeft = Math.ceil((dueDay.getTime() - today.getTime()) / 86400000);

    // 本番は0〜3日だけ。テストは制限なし。
    if (!forceAll) {
      if (daysLeft < 0 || daysLeft > REMIND_WINDOW_DAYS) return;
    }

    // 多重送信防止：同じ（シートID / 行番号 / 期限 / daysLeft or TEST）では1回だけ
    const dueStr = Utilities.formatDate(dueDay, tz, 'yyyy/MM/dd');
    const key = makeOnceKey_(sheetId, r, dueStr, forceAll ? 'TEST' : String(daysLeft));
    if (alreadySent_(key)) return;

    // メール本文（指示の行は削除済み）
    const body =
      'こんにちは！\n' +
      `期限：${dueStr}\n` +
      (forceAll
        ? 'まだ完了していないものがあるのでテスト実行のリマインドです！\n\n'
        : `まだ完了していないものがあるので${daysLeft}日前のリマインドです！\n\n`) +
      '内容↓\n' +
      memo;

    const subject = `【リマインド】${forceAll ? 'テスト送信' : `期限まで残り${daysLeft}日`}（${dueStr}）`;
    MailApp.sendEmail({ to, subject, body });

    markSent_(key); // 送信記録
    sent++;
    console.log(JSON.stringify({ row: r, to, due: dueStr, left: daysLeft, mode: forceAll ? 'TEST' : 'PROD' }));
  });

  console.log(`sendReminders done. mode=${forceAll ? 'TEST' : 'PROD'}, sent=${sent}`);
}

/** 多重送信防止キー作成 */
function makeOnceKey_(sheetId, rowNumber, dueStr, suffix) {
  return `sent:${sheetId}:${rowNumber}:${dueStr}:${suffix}`;
}
function alreadySent_(key) {
  return PropertiesService.getScriptProperties().getProperty(key) ? true : false;
}
function markSent_(key) {
  PropertiesService.getScriptProperties().setProperty(key, String(Date.now()));
}

/** ヘッダー名→列番号（1始まり）。前後空白・全角半角の揺れを軽減 */
function headerIndex_(headers, target) {
  const norm = s => String(s ?? '')
    .trim()
    .replace(/[　]/g, ' ') // 全角空白→半角
    .replace(/[０-９Ａ-Ｚａ-ｚ]/g, d => String.fromCharCode(d.charCodeAt(0) - 0xFEE0)); // 全角英数→半角
  const t = norm(target);
  for (let c = 0; c < headers.length; c++) {
    if (norm(headers[c]) === t) return c + 1;
  }
  return 0;
}

/** 期限セルを可能な範囲で Date に正規化（yyyy/MM/dd 扱いが基本） */
function parseAnyDate_(val, tz) {
  if (val === null || val === undefined || val === '') return null;

  // 既にDate
  if (Object.prototype.toString.call(val) === '[object Date]' && !isNaN(val)) {
    return new Date(val.getTime());
  }

  // 数値（シリアル/UNIX想定）
  if (typeof val === 'number') {
    if (val > 20000 && val < 60000) { // シリアル日付
      const base = new Date(Date.UTC(1899, 11, 30));
      return new Date(base.getTime() + Math.round(val * 86400000));
    }
    if (val > 1e12) return new Date(val);       // ms
    if (val > 1e9)  return new Date(val * 1000); // s
  }

  // 文字列を yyyy/MM/dd 優先で解釈
  if (typeof val === 'string') {
    const s = val.trim();
    if (!s) return null;

    const z2h = s
      .replace(/[０-９]/g, d => String.fromCharCode(d.charCodeAt(0) - 0xFEE0))
      .replace(/[年\/\-\.．。、｡]/g, '/')
      .replace(/月/g, '/').replace(/日/g, '');

    const mYMD = z2h.match(/^(\d{4})\/(\d{1,2})\/(\d{1,2})$/);
    if (mYMD) {
      const y = +mYMD[1], mon = +mYMD[2] - 1, d = +mYMD[3];
      const dt = new Date(y, mon, d);
      if (!isNaN(dt)) return dt;
    }

    const dt = new Date(z2h);
    if (!isNaN(dt)) return dt;
  }

  return null;
}

/** 指定TZの00:00へ丸め */
function stripTime_(date, tz) {
  const y = Utilities.formatDate(date, tz, 'yyyy');
  const m = Utilities.formatDate(date, tz, 'MM');
  const d = Utilities.formatDate(date, tz, 'dd');
  return new Date(Number(y), Number(m) - 1, Number(d));
}

/**（任意）毎朝9時に本番ジョブを走らせるトリガーを1つだけ作成 */
function createDailyTrigger() {
  ScriptApp.getProjectTriggers()
    .filter(t => t.getHandlerFunction() === 'sendReminders')
    .forEach(t => ScriptApp.deleteTrigger(t));
  ScriptApp.newTrigger('sendReminders').timeBased().atHour(9).everyDays(1).create();
}

/**（任意）現在のトリガー確認 */
function debugListTriggers() {
  const ts = ScriptApp.getProjectTriggers();
  console.log(JSON.stringify(ts.map(t => ({ handler: t.getHandlerFunction(), type: t.getEventType() })), null, 2));
}

コードを貼り付けたら次に送信先のメールアドレスの設定をします。

送信先のメールアドレスの設定

今回のリマインドツールではリマインド内容をメールに送信するものとなっているため、送信先のメールアドレスの設定が必要となります。

GASエディタ画面の左側から『プロジェクトの設定』をクリックします。

画面を下にスクロールすると『スクリプトプロパティの追加』があるのでクリックしてください。

• プロパティにはemail
• 値：リマインドを送信する先のメールアドレス

上記を入力してください。

「なぜこのように少し面倒な設定をするの？直接コードに書いたらいいじゃん！」と思われるかもしれません。

プログラムの中に直接書くのは、セキュリティ上のリスクが高いのでNGとされているのでGASの場合はスクリプトプロパティと呼ばれる設定をすることが推奨されています。

さらにデメリットもあります。

コードに直書きすると「漏れやすくて消しづらい」。
スクリプトプロパティに入れると「外に出にくくて、後から差し替えやすい」。

上記のような理由からも、スクリプトプロパティを活用したほうが良いということになります。

話は少し逸れましたが、プロパティを保存を押すと以下のような表示になります。

こうなることで、リマインドツールの設定は終了です。

次にリマインドしたい内容の入力になります。

コードを実行してみよう！

まずは、実際にコードが動くのかをテストします。

スプレッドシートのメモ列と期日列に入力します。

メモにはリマインドしたい内容を記入します。

今回は送信テストと入れてあります。

期限も入力をします。

入力し終えたら、GASエディタから実行します。

赤枠の関数の部分をクリックしてsendRemindersTestを選択して実行を押してください。

そうすることで、設定してあるメールアドレスに通知が飛びます。

テスト通知がうまく飛んでいたら、sendRemindersTestをsendRemindersに変更をして、再度実行をしてください。

毎朝9時に期限3日前で、提出に◯が入っていないものがメールで通知が飛ぶようになります。

なお、初回の実行時は承認作業が必要となります。

『権限を確認』をクリック。

自分のグーグルアカウントを選択してください。

ここで、安全なページに戻るを押してしまうと、コードが実行されず、先程のエディタ画面へ戻されてしまうのでご注意ください。

プロジェクト名（安全ではないページ）に移動をクリックすると・・。

『次へ』をクリックします。

今回のコードでアクセスする情報が表示されるので、すべて選択して、『続行』を押してください。

これで承認作業が完了となります。

警告表示に関して

GASの初回実行時は今回のような警告が表示され、コードを実行するためには承認が必要となります。

Googleが悪意があるコードの実行を防ぐために未確認アプリに対して、今回のような警告をだす仕組みとなっています。

最後に

このようなリマンドツールは正直スマホアプリとかでも便利なものがたくさんできていますが、

会社の業務でスプレッドシートで情報を管理している場合などはより活用できるかと思います。

今回作成したリマインドツールを応用することでリマインドの送信先をslackやChatwork、LINEといったコミュニケーションツールに設定することも可能です。