先日、社内の営業サポートの同僚にこんなお願いをされました。
「各営業メンバーが返信対応していないメールをリストアップして可視化したい！」

なるほど、それができればかなりの工数削減に繋がるのは間違いない。
それに何より単純作業に疲れたこいつの心を癒せるかもしれない。

ということで実際に作成したコードを紹介しつつまとめた記事となります。
また、コードの作成にあたり以下の記事も参考にさせていただいたので、合わせて紹介させていただきます。
【爆速レポ】Google Apps Script × AppSheet × Gmailで返信漏れを解消！～RPACommunityイベント　#RPALT

目次

  1.  事前準備
  2.  GAS：対象のスプレッドシートを取得
  3.  GAS：シート内の既存データを取得
  4.  GAS：Gmail からスレッドデータを取得
  5.  GAS：更新内容を作成
  6.  GAS：シートの更新を実行
  7.  GAS：完成系
  8.  GAS：トリガーの設定
  9.  動かしてみる
  10.  まとめ

事前準備

まず初めに、今回のスクリプトでメールをリストアップするための前提条件は以下です。
・リストアップしたいメールを Gmail で受信できる
・社内の営業メールはメーリングリスト用のメールアドレス（例：sales@hogehoge.com）を CC に指定するよう
ルール化されている※注1
・GAS を実行するユーザーがメーリングリストに追加されている※注1

※注1. 他の営業メンバーのメールをリストアップする必要がない場合は不要

スプレッドシートの準備

それでは開発の準備をしていきます。
まずはリストを書き出すためのスプレッドシートを用意します。


スプレッドシートのファイル名、シート名はなんでも構いません。
プログラム内で使用するのはシート名のみなので、今回は「未返信メールリスト」という名前でシートを一つ作成したいと思います。
この時作成したシート名（例：未返信メールリスト）とスプレッドシートの URL（例：https://docs.google.com/spreadsheets/d/************/edit）は後々使うのでメモしておいてください。

これから書き出されるリストのために、それぞれの列の見出しを作成しておくのもいいでしょう。
今回は1列目の左から「ステータス」、「件名」、「From」、「To」、「受信日時」、「スレッド ID」を設定して進めていきます。

また、「表示」タブ >「固定」から「1行」を選択することで、下にスクロールしても常に1行目が画面上部に固定されるようになります。
合わせて設定しておくと便利かと思います。

GAS の準備

次にコードを記述するための GAS の準備をしていきます。
GAS コンソールの開き方は、先ほど作成したスプレッドシートの「拡張機能」タブから「Apps Script」を選択することで開くことができます。

コードを記述するためのファイルを用意します。
一番左のサイドバーから「エディタ」を選択し、「ファイルを追加」ボタンから「スクリプト」を選択し、新しいファイルを作成します。
ファイル名は今回は「email-noreply-hunter」としたいと思います。

作成されたばかりのファイルには以下のような記述がデフォルトで用意されていると思います。

function myFunction() {

}

今回は関数名を myFunction から「emailNoreplyHunter」に変更して実装を進めていきたいと思います。

function emailNoreplyHunter() {

}

GAS：対象のスプレッドシートを取得

ここから「email-noreply-hunter.gs」ファイルに実際にコードを記述していきます。
最初に操作対象のスプレッドシートの情報を取得します。

function emailNoreplyHunter() {
  .
  .
  .
  const sheetData = targetSheet.getDataRange().getValues();
  let oldData = {};

  // シート内のステータスとスレッドIDを格納
  for (let i = 1; i < sheetData.length; i++) {
    const status = sheetData[i][0];  // ステータス
    const threadId = sheetData[i][5];  // スレッドID

    if (status !== "返信済み") {
      oldData[threadId] = { "status": status };
    }
  }
  Logger.log("シートのデータ取得完了");
}

SpreadsheetApp.openByUrl() でスクリプトで操作したいスプレッドシートを URL で開いています。ここでは「未返信メールリスト」という名前のシートが含まれているスプレッドシートを対象としています。
また、getSheetByName() でスプレッドシートの中から、指定したシート名（未返信メールリスト）のシートを取得しています。

対象シートが存在しない場合は Logger.log() でエラーメッセージを出力し、処理を中断しています。これにより、存在しないシートを操作してエラーになるのを防いでいます。

GAS：シート内の既存データを取得

シートを更新する前に、既存のデータを取得し引き継ぐ必要のある項目を保存しておきます。

function emailNoreplyHunter() {
  .
  .
  .
  const sheetData = targetSheet.getDataRange().getValues();
  let oldData = {};

  // シート内のステータスとスレッドIDを格納
  for (let i = 1; i < sheetData.length; i++) {
    const status = sheetData[i][0];  // ステータス
    const threadId = sheetData[i][5];  // スレッドID

    if (status !== "返信済み") {
      oldData[threadId] = { "status": status };
    }
  }
  Logger.log("シートのデータ取得完了");
}

getDataRange().getValues() で現在のシートにあるすべてのデータ（セル）を2次元配列として取得します。
ループの開始を i = 1 にすることで、1行目（見出し）をスキップしています。sheetData[i][0] は1列目（ステータス列）、sheetData[i][5] は6列目（スレッド ID）にあたります。

ステータスが「返信済み」以外のもの（例：「未返信」「保留」など）を oldData というオブジェクトに保存しています。後の処理でこの oldData を使い、連続でリストアップされたスレッドのステータスを維持できるようにします。
データが連続でリストアップされる場合は「未返信」の状態なので、「返信済み」のステータスは引き継がないようにしています。

GAS：Gmail からスレッドデータを取得

シートの更新内容を作成するために、Gmail の検索クエリを活用し、検索条件に一致するメールスレッドを取得しておきます。この処理は、「どのメールが未返信か？」を判定するための入口となるデータ収集ステップです。
条件を変えれば、特定の部署や顧客だけに絞って取得することもでき、業務に合わせたカスタマイズが可能です。

function emailNoreplyHunter() {
  .
  .
  .
  let allThreads = [];
  const maxResults = 500;
  const searchQuery = "cc:sales@hogehoge.com AND to:@hogehoge.com AND newer_than:1m";  // 検索条件

  try {
    let page = 0;
    while (true) {
      const threads = GmailApp.search(searchQuery, page * maxResults, maxResults);
      if (threads.length === 0) break;
      allThreads = allThreads.concat(threads);
      page++;
    }
  } catch (e) {
    Logger.log("Gmailのスレッド取得中にエラーが発生しました（エラー内容: " + e.message + "）");
    return;
  }

  Logger.log("Gmail からすべてのスレッドのデータ取得完了（スレッド数: " + allThreads.length + "）");
}

searchQuery にて、Gmail の検索演算子を使って以下の条件に合うスレッドを絞り込こむためのクエリを作成します。
・cc:sales@hogehoge.com … CC にメーリングリスト用のメールアドレスが含まれる
・to:@hogehoge.com … 自社ドメインに送信されたメール
・newer_than:1m … 過去1ヶ月以内に届いたメール

Gmail の検索は1回で最大500件しか取得できないため、while ループで繰り返し呼び出し、すべてのスレッドを allThreads に蓄積しています。
page * maxResults の部分でページごとのオフセットを調整します。

Gmail の API 操作で想定外のエラーが発生しても、try-catch によりスクリプトが落ちないよう保護しています。また、ログにエラー内容も残せるようにしています。

GAS：更新内容を作成

先ほど取得した Gmail スレッドの中から、返信がないまま12時間以上経過しているものを抽出し、スプレッドシートに書き出すためのデータ形式に整えていきます。

function emailNoreplyHunter() {
  .
  .
  .
  // スレッドを最終返信日時の昇順に並べ替え
  allThreads.sort((a, b) => a.getLastMessageDate() - b.getLastMessageDate());

  // 12時間以上経過しているものをフィルタリング
  const targetDateTime = new Date();
  targetDateTime.setHours(targetDateTime.getHours() - 12);  // 12時間前

  const values = allThreads
    .filter(thread => thread.getLastMessageDate() <= targetDateTime)  // 12時間以上経過したスレッド
    .map(thread => {
      const threadId = thread.getId();
      const lastMessageDate = thread.getLastMessageDate();
      const messages = thread.getMessages();
      const lastMessage = messages[messages.length - 1];

      const status = oldData[threadId] ? oldData[threadId]["status"] : "未返信";  // 既存データのステータスを引き継ぐ
      return [
        status,
        thread.getFirstMessageSubject(),
        lastMessage.getFrom(),
        lastMessage.getTo(),
        formatDate(lastMessageDate),
        threadId
      ];
    });

  Logger.log("更新内容の作成完了");
}

/**
 * 日付を指定のフォーマットに変換
 */
function formatDate(date) {
  const year = date.getFullYear();
  const month = String(date.getMonth() + 1).padStart(2, '0');
  const day = String(date.getDate()).padStart(2, '0');
  const hours = String(date.getHours()).padStart(2, '0');
  const minutes = String(date.getMinutes()).padStart(2, '0');
  return `${year}/${month}/${day} ${hours}:${minutes}`;
}

allThreads.sort() で最終返信日時を基準に古いスレッドから順番に並び替えています。これにより、後でスプレッドシートに表示する際に、対応が遅れているメールほど上に表示されます。

filter(thread => thread.getLastMessageDate() <= targetDateTime) のフィルタで「受信から12時間以上返信されていないメール」だけを対象にすることで、直近のやり取りは除外され対応漏れだけを可視化するようにします。

const status = oldData[threadId] ? oldData[threadId]["status"] : "未返信"; 更新前のスプレッドシートに書き込まれていたステータス（「保留」など）をそのまま残せるよう、過去の状態を引き継ぎます。また、更新前に存在しなかったものに関しては、一律で「未返信」のステータスを設定するようにします。

GAS で取得した Date オブジェクトはそのままだと扱いづらいため、function formatDate(date) で「YYYY/MM/DD HH:mm」形式に整形しています。このフォーマットで統一することで、スプレッドシート上でも視認性が高く、ソートなどもしやすくなります。

GAS：シートの更新を実行

いよいよ最後の工程です。
前の処理で収集したデータをスプレッドシートに書き出したり、プルダウンリストや条件付き書式を設定するなど、一連のスプレッドシートの更新処理をここで行います。

function emailNoreplyHunter() {
  .
  .
  .
  const lastRow = targetSheet.getLastRow();
  const oldDataLength = lastRow - 1;
  if (lastRow >= 2) {
    const range = targetSheet.getRange(2, 1, oldDataLength, targetSheet.getLastColumn());
    range.clear();
    range.clearDataValidations();
  }

  if (values.length === 0) {
    Logger.log("更新データなし");
    return;
  }

  if (values.length > oldDataLength) {
    targetSheet.insertRows(2, values.length - oldDataLength);
  }

  const dataRange = targetSheet.getRange(2, 1, values.length, values[0].length);
  dataRange.setValues(values);

  // プルダウンリストの設定
  targetSheet.getRange(2, 1, values.length, 1).setDataValidation(
    SpreadsheetApp.newDataValidation()
      .requireValueInList(["未返信", "返信済み", "保留"], true)
      .setAllowInvalid(false)
      .build()
  );

  // 条件付き書式の設定
  const rules = [
    SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=$A2="未返信"')
      .setBackground("#ea9999")
      .setRanges([dataRange])
      .build(),
    SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=$A2="返信済み"')
      .setBackground("#b7b7b7")
      .setRanges([dataRange])
      .build()
  ];
  targetSheet.setConditionalFormatRules(rules);

  Logger.log("更新完了");
}

range.clear() 、range.clearDataValidations() にてシート内の既存のデータ（2行目から最終行まで）を一度クリアしています。これにより、新しいデータを上書きできるようにします。データ検証ルール（プルダウンリストなど）もリセットしておくため、clearDataValidations() を使っています。

新しく書き込むデータの行数が現在シートにある行数より多い場合に、targetSheet.insertRows() で足りない行数を事前に挿入しておきます。
この処理は、シートの行数が書き込むデータより少ない場合に発生するエラーを回避するために挿入しています。

dataRange.setValues() で収集したデータ（ values ）をシートに書き込みます。データは2行目から開始して追加されます。

ステータス列（A列）はプルダウンで管理したいため、targetSheet.getRange().setDataValidation() でプルダウンリストを作成します。選択肢は「未返信」「返信済み」「保留」の3つで、setAllowInvalid() に false を指定することで選択肢以外の値の入力を制限しています。

targetSheet.setConditionalFormatRules() は条件付き書式を作成します。特定の条件（ステータス列が「未返信」もしくは「返信済み」の時）に応じて行の背景色を変更しています。
・「未返信」の行は赤色（ #ea9999 ）
・「返信済み」の行は灰色（ #b7b7b7 ）
この設定により、視覚的にステータスを識別しやすくなります。

GAS：完成系

お疲れ様です！以上で全ての処理が終わりました！
改めて、完成したコードの全体を下に載せておきます。

function emailNoreplyHunter() {
  // ==================================================================
  // 対象のスプレッドシートを取得
  // ==================================================================
  const spreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/************/edit");
  const sheetName = "未返信メールリスト";  // シート名
  const targetSheet = spreadsheet.getSheetByName(sheetName);

  if (!targetSheet) {
    Logger.log("対象のシートが存在しません");
    return;
  }

  // ==================================================================
  // シート内の既存データを取得
  // ==================================================================
  const sheetData = targetSheet.getDataRange().getValues();
  let oldData = {};

  // シート内のステータスとスレッドIDを格納
  for (let i = 1; i < sheetData.length; i++) {
    const status = sheetData[i][0];  // ステータス
    const threadId = sheetData[i][5];  // スレッドID

    if (status !== "返信済み") {
      oldData[threadId] = { "status": status };
    }
  }
  Logger.log("シートのデータ取得完了");

  // ==================================================================
  // Gmail からスレッドデータを取得
  // ==================================================================
  let allThreads = [];
  const maxResults = 500;
  const searchQuery = "cc:sales@hogehoge.com AND to:@hogehoge.com AND newer_than:1m";  // 検索条件

  try {
    let page = 0;
    while (true) {
      const threads = GmailApp.search(searchQuery, page * maxResults, maxResults);
      if (threads.length === 0) break;
      allThreads = allThreads.concat(threads);
      page++;
    }
  } catch (e) {
    Logger.log("Gmailのスレッド取得中にエラーが発生しました（エラー内容: " + e.message + "）");
    return;
  }

  Logger.log("Gmail からすべてのスレッドのデータ取得完了（スレッド数: " + allThreads.length + "）");

  // ==================================================================
  // 更新内容を作成
  // ==================================================================
  // スレッドを最終返信日時の昇順に並べ替え
  allThreads.sort((a, b) => a.getLastMessageDate() - b.getLastMessageDate());

  // 12時間以上経過しているものをフィルタリング
  const targetDateTime = new Date();
  targetDateTime.setHours(targetDateTime.getHours() - 12);  // 12時間前

  const values = allThreads
    .filter(thread => thread.getLastMessageDate() <= targetDateTime)  // 12時間以上経過したスレッド
    .map(thread => {
      const threadId = thread.getId();
      const lastMessageDate = thread.getLastMessageDate();
      const messages = thread.getMessages();
      const lastMessage = messages[messages.length - 1];

      const status = oldData[threadId] ? oldData[threadId]["status"] : "未返信";  // 既存データのステータスを引き継ぐ
      return [
        status,
        thread.getFirstMessageSubject(),
        lastMessage.getFrom(),
        lastMessage.getTo(),
        formatDate(lastMessageDate),
        threadId
      ];
    });

  Logger.log("更新内容の作成完了");

  // ==================================================================
  // シートの更新を実行
  // ==================================================================
  const lastRow = targetSheet.getLastRow();
  const oldDataLength = lastRow - 1;
  if (lastRow >= 2) {
    const range = targetSheet.getRange(2, 1, oldDataLength, targetSheet.getLastColumn());
    range.clear();
    range.clearDataValidations();
  }

  if (values.length === 0) {
    Logger.log("更新データなし");
    return;
  }

  if (values.length > oldDataLength) {
    targetSheet.insertRows(2, values.length - oldDataLength);
  }

  const dataRange = targetSheet.getRange(2, 1, values.length, values[0].length);
  dataRange.setValues(values);

  // プルダウンリストの設定
  targetSheet.getRange(2, 1, values.length, 1).setDataValidation(
    SpreadsheetApp.newDataValidation()
      .requireValueInList(["未返信", "返信済み", "保留"], true)
      .setAllowInvalid(false)
      .build()
  );

  // 条件付き書式の設定
  const rules = [
    SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=$A2="未返信"')
      .setBackground("#ea9999")
      .setRanges([dataRange])
      .build(),
    SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=$A2="返信済み"')
      .setBackground("#b7b7b7")
      .setRanges([dataRange])
      .build()
  ];
  targetSheet.setConditionalFormatRules(rules);

  Logger.log("更新完了");
}

/**
 * 日付を指定のフォーマットに変換
 */
function formatDate(date) {
  const year = date.getFullYear();
  const month = String(date.getMonth() + 1).padStart(2, '0');
  const day = String(date.getDate()).padStart(2, '0');
  const hours = String(date.getHours()).padStart(2, '0');
  const minutes = String(date.getMinutes()).padStart(2, '0');
  return `${year}/${month}/${day} ${hours}:${minutes}`;
}

GAS：トリガーの設定

ここまでに書いたスクリプトは、エディタを開いた状態でヘッダーに表示される「実行」ボタンから手動で動かすことができますが、せっかくなので毎日自動実行されるようにトリガーの設定をしてみましょう。

サイドバーから「トリガー」を選択し、「トリガーを追加」ボタンからトリガー設定のモーダルを表示します。

設定項目は以下です。
・実行する関数を選択 … 今回作成した関数名（emailNorepryHunter）を指定します。
・実行するデプロイを選択 … 選択した関数のどのバージョンをトリガーで動かすか指定します。
今回はデプロイしていないので、「Head」を選択します。
・イベントのソースを選択 … どの種類のイベント（きっかけ）でスクリプトを実行するかを決める項目です。
今回は「時間主導型」を選択してみます。
・時間ベースのトリガーのタイプを選択 … イベントのソースに「時間主導型」を選んだときに出てくる項目で、
スクリプトを実行したい時間間隔です。一日に一回実行したい場合は「日付ベースのタイマー」を選択します。
・時刻を選択 … どの時間帯にスクリプトを実行するかを指定できます。業務時間を避けたいため、
今回は「午前３時〜４時」を指定したいと思います。
・エラー通知設定 … トリガーでスクリプトを実行したときに、エラーが発生した場合にどう通知を受け取るかを
設定するものです。一番高頻度の「毎日通知を受け取る」に設定しておくのがおすすめです。

一通り設定できたら「保存」ボタンでトリガーを設定しましょう。
これで何もしなくても毎日スプレッドシートが更新されるようになりました。

動かしてみる

それでは最後に実際に動かしてみましょう。

成功すれば、このようなフォーマットで出力されるかと思います。
今日で返信漏れに怯える日々とはおさらばですね！

まとめ

今回は GAS のより実践的な内容の記事を書かせていただきました。エンジニアだけでなく、営業やその他の職種の方でも親しみやすい内容になったんじゃないかなと思っています。

反響が良ければ、GAS の作ってみたシリーズでまた記事を書いてみても面白いのかなと思っていたりいなかったり。それでは良いGASライフを！