【簡単！画像あり！GAS利用】手動でToDoを表で管理し、自動メールでリマインドする[No109]

新しいことに挑戦すると日々のタスクも増えてしまいます。ついつい忘れて続かなく三日坊主になってしまった経験が誰にでもあるのではないかと思っています。

タスクがある日は、自動でリマインダーメールを送ることで問題は解決すると考えます。

今回は、Google Apps Script（GAS）とスプレッドシートを使ってツールを自作で用意しましたので、紹介します。コピペと少しの変更ですぐ使えると思います。

我ながら善きツールを作成できたと実感しています。このツールを利用されることで、皆さまの作業漏れや作業忘れなどが少しでも少なる失念防止になれば嬉しいです。

動作イメージ

実際には、毎日自動で動作するようにトリガー設定をします。説明がしやすいように今回はトリガー設定はしていません。

以下は時系列順になっています。

１．「ログ」シート、「テンプレート」シート、「シート作成」シートを用意する（手動、事前準備）

appsscript.jsonは「"timeZone": "Asia/Tokyo"」にする。画面左端の歯車アイコンの「プロジェクトの設定」で「「appsscript.json」マニフェストファイルをエディタで表示する」をONの時に表示されます。

２．プログラムを実行する（手動、自動）

事前に「シート作成」で日付シートを用意しておいてもOKですが、ここではその説明はしないで進めます。

３．「ログ」シートにログが出力される（自動）

日付シートがなかった場合は、「テンプレート」シートがひな形となり、1ヶ月単位の日付シートが作成されます。日付シートがない場合は、メール通知もしません。

４．日付シートにメール通知がほしい日の予定を入力する（手動）

通知がほしい日の行に、「タイトル」「詳細」を入力して、ほしい時間のチェックボックス（checkbox）をオンにします。

「タイトル」または「詳細」に入力がないとメール通知しません。

「タイトル」または「詳細」に入力があり、[01時]～[23時]の時間列にチェックされていれば、その時間の00分にメールで通知します。

しかし、「タイトル」または「詳細」に入力があり、時間列にチェックがないと即時で通知します。

５．プログラムを実行する（手動、自動）

６．「ログ」シートにログが出力される、トリガーが設定される（自動）

７．動作確認時の時刻

８．動作確認時の時刻

９．メールが送信される（自動）

１０．「ログ」シートにログが出力される、日付シートの実績欄が更新される（自動）

日付シートの右側の2列は、エラーチェック用です。過去日で予定と実績の回数が一致していないと、赤くなります。

動作について

「MainSTEP01」関数を毎日0時～1時に1日1回起動するように設定する。起動したら、日付シートの存在チェックを行う。日付シートがある場合は、「タイトル」または「詳細」が入力されていたときは、メール送信対象となる。01時～23時の時間列のチェックボックスにチェックによって、回数[予定]の回数が増減する。回数[予定]の回数が１以上であれば、チェックした時間列の時間の00分に「MainSTEP02」が実行しメール通知する。回数[予定]の回数が０であれば、即時に「MainSTEP02」が実行しメール通知する。日付シートがない場合は、日付シートの作成だけを行う。

準備作業

「ログ」シートの用意

下図のように作成します。利用する文字列を一部記載します。

・シート名：ログ

・項目名：Date,Message

「テンプレート」シートの用意　その１

下図のように作成します。利用する文字列を一部記載します。

・シート名：テンプレート

・項目名：No,日付,曜日,タイトル,詳細,回数[予定],00時は「=text(TIME(0,0,0),"hh時")」,01時は「=text(TIME(0,0,1),"hh時")」,・・・,23時は「=text(TIME(23,0,0),"h時")」,回数[実績],エラー判定

・その他（日付）：＜上書き日付＞,「=text(B2+1,"yyyy/mm/dd")」

・その他（曜日）：「=TEXT(B2,"ddd")」

・その他（回数[予定]）：「=COUNTIF(G2:AD2,true)」

・その他（00時～23時）：チェックボックス

・その他（エラー判定）：「=IF(B2 < text(TODAY(),"yyyy/mm/dd"),EXACT(F2,AE2),TRUE)」

「テンプレート」シートの用意　その２

下図のように設定する。利用する文字列を一部記載します。

・「AF2:AF32」と「FALSE」、「B2:C32」と「=WEEKDAY($B2)=1」、「B2:C32」と「=WEEKDAY($B2)=7」、「A1:AD997」

「シート作成」シートの用意

下図のように設定する。利用する文字列を一部記載します。

「作成年月」「⇒プログラム利用」「年」「=TEXT(A2,"yyyy")」「月」「=TEXT(A2,"MM")」

Apps Scriptの設定など

１．appsscript.jsonの設定

前述したように、appsscript.jsonは「"timeZone": "Asia/Tokyo"」にする。画面左端の歯車アイコンの「プロジェクトの設定」で「「appsscript.json」マニフェストファイルをエディタで表示する」をONの時に表示されます。

２．コードの用意　その１


// 区切り線■■■■■
function MainSTEP01() {
  try {
    console.log('▼開始▼（' + arguments.callee.name + '）');
    const date = new Date();
    let shName = Utilities.formatDate(date, 'Asia/Tokyo', 'yyMM');

    makesheet01(shName, date);

    let { listRange, listValue } = getTargetList(shName);
    let targetRow = getTargetDateRow(shName, date, listRange, listValue);

    makesheet02(shName, date, listRange, listValue);

    let message = [];

    let title = listValue[targetRow][listValue[0].indexOf('タイトル')].toString();
    message.push('[' + 'タイトル' + '⇒' + title + ']');

    let detail = listValue[targetRow][listValue[0].indexOf('詳細')];
    message.push('[' + '詳細' + '⇒' + detail + ']');

    let num = parseInt(listValue[targetRow][listValue[0].indexOf('回数[予定]')], 10);
    message.push('[' + '回数[予定]' + '⇒' + num + ']');

    outPutLog(date, 'MainSTEP01' + '：' + message.join(','));

    console.log('title:' + title + ' ' + 'detail:' + detail + ' ' + 'num:' + num);

    if (!(title === "" || title === null) || !(detail === "" || detail === null)) {
      if (0 < num) {
        console.log('メール予約完了');
        setupSchedulesOfMails(date, listRange, listValue, targetRow);
        outPutLog(date, 'MainSTEP01' + '：' + 'メール予約完了');
      } else {
        console.log('メール送信完了');
        MainSTEP02();
        outPutLog(date, 'MainSTEP01' + '：' + 'メール送信完了');
      }
    } else {
      console.log('何もしない');
      outPutLog(date, 'MainSTEP01' + '：' + '何もしない');
    }

    console.log('▲完了▲（' + arguments.callee.name + '）');
  } catch (err) {
    console.log(err);
  }
}

// 区切り線■■■■■
function MainSTEP02() {
  try {
    console.log('▼開始▼（' + arguments.callee.name + '）');

    const date = new Date();

    let shName = Utilities.formatDate(date, 'Asia/Tokyo', 'yyMM');

    let { listRange, listValue } = getTargetList(shName);
    let targetRow = getTargetDateRow(shName, date, listRange, listValue);

    sendMail(date, listRange, listValue, targetRow);
    writeSheet(shName, listRange, listValue, targetRow);

    outPutLog(date, 'MainSTEP02' + '：' + 'メール送信完了');
    console.log('▲完了▲（' + arguments.callee.name + '）');
  } catch (err) {
    console.log(err);
  }
}

// 区切り線■■■■■
function sendMail(date, listRange, listValue, targetRow) {
  console.log('▼開始▼（' + arguments.callee.name + '）');

  let nowTime = Utilities.formatDate(date, 'Asia/Tokyo', 'MM月dd日HH時');

  const recipient = '◆◆◆【ここに送信先のメールアドレスを記載してください】◆◆◆';
  const subject = '作業確認のご連絡' + '(' + nowTime + ')' + ' ' + listValue[targetRow][listValue[0].indexOf('タイトル')];
  const body = listValue[targetRow][listValue[0].indexOf('詳細')];
  const options = {};
  // GmailApp.sendEmail(recipient, subject, body, options);

  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function makeSelectSheet() {
  try {
    console.log('▼開始▼（' + arguments.callee.name + '）');

    const spSheet = SpreadsheetApp.getActiveSpreadsheet();
    let selectMakeSheet = spSheet.getSheetByName('シート作成');

    let dateMonthYear = selectMakeSheet.getRange('E2').getValue();
    let dateMonth = selectMakeSheet.getRange('F2').getValue();
    let dateDate = '01';

    let dateStr = dateMonthYear + '/' + dateMonth + '/' + dateDate;
    let dateSelect = new Date(dateStr);
    let shName = Utilities.formatDate(dateSelect, 'Asia/Tokyo', 'yyMM');

    makesheet01(shName, dateSelect);
    let { listRange, listValue } = getTargetList(shName);
    makesheet02(shName, dateSelect, listRange, listValue);
  } catch (err) {
    console.log(err);
  }
}

// 区切り線■■■■■
function insertTriggers(funcName, date) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  ScriptApp.newTrigger(funcName).timeBased().at(date).create();
  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function deleteTriggers(funcName) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => {
    if (trigger.getHandlerFunction() !== funcName) return;
    ScriptApp.deleteTrigger(trigger);
  });
  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function makesheet01(shName, date) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  const spSheet = SpreadsheetApp.getActiveSpreadsheet();
  let targetSheet = spSheet.getSheetByName(shName);

  if (!targetSheet) {
    copySheet(shName);

    let beforeName = '＜上書き日付＞';
    let aftereName = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/01');
    replaceSheet(shName, beforeName, aftereName);
  } else {
    console.log('既に' + '[' + shName + ']' + 'シートが在るため、処理をスキップします。');
  }
  console.log('▲完了▲（' + arguments.callee.name + '）');
}

function makesheet02(shName, date, listRange, listValue) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  setNumberFormatSheet(shName, listRange, listValue);
  adjustmentSheet(shName, date, listRange, listValue);
  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function copySheet(shName) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  const spSheet = SpreadsheetApp.getActiveSpreadsheet();
  let copiedSheet = spSheet.getSheetByName('テンプレート');
  let newSheet = copiedSheet.copyTo(spSheet);
  newSheet.setName(shName);
  newSheet.activate();
  spSheet.moveActiveSheet(2);
  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function replaceSheet(shName, beforeName, afterName) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  const spSheet = SpreadsheetApp.getActiveSpreadsheet();
  let targetSheet = spSheet.getSheetByName(shName);
  let textFinder = targetSheet.createTextFinder(beforeName);
  // textFinder.replaceAllWith(afterName);

  // // すべての検索結果を取得する
  // const ranges = textFinder.findAll();
  // // 検索結果の位置をA1表記で取得する
  // for (let i in ranges) {
  //   let range = ranges[i].getA1Notation();
  //   targetSheet.getRange(range).setNumberFormat('@');
  // }

  textFinder.replaceAllWith(afterName);
  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function setNumberFormatSheet(shName, listRange, listValue) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  const spSheet = SpreadsheetApp.getActiveSpreadsheet();
  let targetSheet = spSheet.getSheetByName(shName);

  let targetCol = [];
  targetCol.push(listValue[0].indexOf('日付'));
  targetCol.push(listValue[0].indexOf('曜日'));

  for (let i = 0; i < targetCol.length; i++) {
    targetSheet.getRange(1, targetCol[i] + 1, listRange.getNumRows()).setNumberFormat('@');
  }
  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function adjustmentSheet(shName, date, listRange, listValue) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  const spSheet = SpreadsheetApp.getActiveSpreadsheet();
  let targetSheet = spSheet.getSheetByName(shName);

  let targetCol = listValue[0].indexOf('日付');

  let str = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM');

  //パターンA▼▼▼
  for (let i = listRange.getNumRows(); i > 1; i--) {
    let range = targetSheet.getRange(i, targetCol + 1);
    let value = range.getValue().toString();

    let regexp = new RegExp('^(?!.*(' + str + ')).*$', 'gi');
    // if (value.match(regexp)) { console.log(value) };
    if (value.match(regexp)) { targetSheet.deleteRow(i) };

    // if (value.match(/^(?!.*(2023\/02)).*$/gi)) { console.log(value) };
    // if (value.match(/^(?!.*(2023\/02)).*$/gi)) { targetSheet.deleteRow(i) };
  }
  //パターンA▲▲▲
  //パターンB▼▼▼
  // let keys = targetSheet.getRange(1, targetCol + 1, listRange.getNumRows()).getValues();
  // // let keys = targetSheet.getRange(getColName(targetCol + 1) + '1' + ':' + getColName(targetCol + 1) + listRange.getNumRows()).getValues();
  // keys = keys.flat();// 配列を1次元に変換
  // keys = keys.reverse();

  // let regexp = new RegExp('^(?!.*(' + str + ')).*$', 'gi');

  // for (let v of keys) {
  //   // if (v.match(/2023\/03/))
  //   if (v.match(regexp)) {
  //     for (let i = 1; i < listRange.getNumRows(); i++) {
  //       let range = targetSheet.getRange(i + 1, targetCol + 1);
  //       let value = range.getValue().toString();
  //       if (v === value) { console.log(value) };
  //       if (v === value) { targetSheet.deleteRow(i + 1) };
  //     }
  //   }
  // }
  //パターンB▲▲▲
  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function setupSchedulesOfMails(date, listRange, listValue, targetRow) {
  console.log('▼開始▼（' + arguments.callee.name + '）');

  const object = getDataInTargetRow(listRange, listValue, targetRow);

  let arr = [];
  let str = '';

  if (object['00時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 00:00:00'))); str += '[0時]'; };
  if (object['01時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 01:00:00'))); str += '[1時]'; };
  if (object['02時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 02:00:00'))); str += '[2時]'; };
  if (object['03時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 03:00:00'))); str += '[3時]'; };
  if (object['04時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 04:00:00'))); str += '[4時]'; };
  if (object['05時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 05:00:00'))); str += '[5時]'; };
  if (object['06時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 06:00:00'))); str += '[6時]'; };
  if (object['07時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 07:00:00'))); str += '[7時]'; };
  if (object['08時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 08:00:00'))); str += '[8時]'; };
  if (object['09時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 09:00:00'))); str += '[9時]'; };
  if (object['10時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 10:00:00'))); str += '[10時]'; };
  if (object['11時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 11:00:00'))); str += '[11時]'; };
  if (object['12時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 12:00:00'))); str += '[12時]'; };
  if (object['13時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 13:00:00'))); str += '[13時]'; };
  if (object['14時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 14:00:00'))); str += '[14時]'; };
  if (object['15時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 15:00:00'))); str += '[15時]'; };
  if (object['16時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 16:00:00'))); str += '[16時]'; };
  if (object['17時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 17:00:00'))); str += '[17時]'; };
  if (object['18時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 18:00:00'))); str += '[18時]'; };
  if (object['19時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 19:00:00'))); str += '[19時]'; };
  if (object['20時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 20:00:00'))); str += '[20時]'; };
  if (object['21時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 21:00:00'))); str += '[21時]'; };
  if (object['22時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 22:00:00'))); str += '[22時]'; };
  if (object['23時'] == true) { arr.push(new Date(Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd 23:00:00'))); str += '[23時]'; };

  deleteTriggers('MainSTEP02');
  for (let s of arr) insertTriggers('MainSTEP02', s);

  outPutLog(date, 'setupSchedulesOfMails' + '：' + 'トリガー設定' + '⇒' + str);

  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function writeSheet(shName, listRange, listValue, targetRow) {
  console.log('▼開始▼（' + arguments.callee.name + '）');

  let r, c;
  r = targetRow; c = listValue[0].indexOf('回数[実績]');

  const spSheet = SpreadsheetApp.getActiveSpreadsheet();
  let targetSheet = spSheet.getSheetByName(shName);
  targetSheet.getRange(r + 1, c + 1).setValue(parseInt(listValue[r][c], 10) + 1);

  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function outPutLog(date, message) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  const spSheet = SpreadsheetApp.getActiveSpreadsheet();
  let logSheet = spSheet.getSheetByName('ログ');

  let nowTime = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');

  const dataArray = [nowTime, message];
  const dataArrayTwo = [dataArray];
  const dataArrayLen = dataArray.length;
  const lastRow = logSheet.getLastRow();

  logSheet.getRange(lastRow + 1, 1, 1, dataArrayLen).setValues(dataArrayTwo);
  console.log(message);
  console.log('▲完了▲（' + arguments.callee.name + '）');
}

// 区切り線■■■■■
function getTargetList(shName) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  const spSheet = SpreadsheetApp.getActiveSpreadsheet();

  let targetSheet = spSheet.getSheetByName(shName);

  let lastRow = targetSheet.getLastRow();// 最終行を取得
  let lastCol = targetSheet.getLastColumn();// 最終列を取得

  let listRange = targetSheet.getRange(1, 1, lastRow, lastCol);
  let listValue = listRange.getValues();

  console.log('▲完了▲（' + arguments.callee.name + '）');
  return { listRange, listValue };
}

// 区切り線■■■■■
function getTargetDateRow(shName, date, listRange, listValue) {
  console.log('▼開始▼（' + arguments.callee.name + '）');

  let nowDate = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd');

  let targetCol = listValue[0].indexOf('日付');

  //パターンA▼▼▼
  let arr = []
  for (let i = 0; i < listRange.getNumRows(); i++) { arr.push(listValue[i][targetCol]); }

  let targetRow = arr.indexOf(nowDate);
  //パターンA▲▲▲
  //パターンB▼▼▼
  // let targetRow = 0;
  // const spSheet = SpreadsheetApp.getActiveSpreadsheet();
  // let targetSheet = spSheet.getSheetByName(shName);

  // for (let i = 1; i < listRange.getNumRows(); i++) {
  //   let range = targetSheet.getRange(i + 1, targetCol + 1);
  //   let value = range.getValue().toString();
  //   let regexp = new RegExp('^' + nowDate + '', 'gi');
  //   if (value.match(regexp)) { targetRow = i; break; }
  // }
  //パターンB▲▲▲
  console.log('▲完了▲（' + arguments.callee.name + '）');
  return targetRow;
}

// 区切り線■■■■■
function getDataInTargetRow(listRange, listValue, targetRow) {
  console.log('▼開始▼（' + arguments.callee.name + '）');
  let object = {};

  for (let i = 0; i < listValue[0].length; i++) { //neta_横の列数を取得する lengthではだめだった。参考サイトなし (datas.pop().lengthでは1行無くなっちゃう)
    let key = listValue[0][i];
    let value = listValue[targetRow][i];
    object[key] = value;
  }
  console.log('▲完了▲（' + arguments.callee.name + '）');
  return object;
}

// 区切り線■■■■■
function getColName(x) {
  let str = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  let ret = str.slice(x - 1, x);
  return ret;
}

３．コードの用意　その２

トリガーで実行しているのは、「MainSTEP01」か「MainSTEP02」の２つです。もちろん、その配下の関数も実行はしていますが。

「SendMail」関数の「'◆◆◆【ここに送信先のメールアドレスを記載してください】◆◆◆';」にメールアドレスを入力すれば、そのアドレス宛に送信されます。

個人的には「'◆◆◆【ここに送信先のメールアドレスを記載してください】◆◆◆';」だけを修正していただければ大丈夫と思いますが、実行される前にソースをご確認ください。

ソースでは「// GmailApp.sendEmail(recipient, subject, body, options);」のように、メール送信をコメントアウトしています。コメントアウトした状態で「作成シート」シートの「年月」を入力して、「makeSelectSheet」を実行して、エラーが発生しなく日付シートが作成できることを確認ください。その後、「MainSTEP01」と「MainSTEP02」関数を実行して、エラーが発生しないことを確認したら、コメントを解除してみてください。