見出し画像

第3章（応用編）：AIとGoogle Sheets APIで管理表を自動作成する方法

相沢アイ

■はじめに

応用編では、AIとAPIを連携させた業務自動化を扱います。

第1章（応用編）：AIとAPIで問い合わせメールを管理表へ自動登録する方法

まだ第1章を読んでいない方は、先に第1章で全体像を確認してから本章を読むと理解しやすくなります。
本章は前章の続きです。
前章では、Gmail APIで問い合わせメールを取得し、AIで問い合わせ内容を分類する処理を作成しました。
まだ第2章を読んでいない方は、先に第2章を確認してから本章を読むと、処理のつながりが分かりやすくなります。

第2章（応用編）：AIとGmail APIで問い合わせメールを自動分類する方法

この章では、第2章で作成したAI分類結果を、Google Sheets APIで管理表へ自動登録する方法を作ります。
問い合わせメールをAIで分類できても、その結果を毎回人が管理表へ転記していたら、まだ手作業が残ります。
・受付日時を入力する
・送信者を入力する
・件名を入力する
・問い合わせ概要を入力する
・分類を入力する
・優先度を入力する
・担当候補を入力する
・ステータスを入力する
このような転記作業は、件数が増えるほど負担になります。
Google Sheets APIを使うと、AI分類結果をそのまま管理表へ登録できます。
この章では、問い合わせ管理表の列を作成し、AI分類結果をGoogle Sheetsへ自動登録する処理を作ります。

■この章で作るもの

この章で作る処理は下記です。

第2章のAI分類結果を受け取る
→ Google Sheets APIでスプレッドシート情報を取得
→ 問い合わせ管理シートを確認
→ 必要に応じてシートを作成
→ ヘッダー行を作成
→ 登録済みメッセージIDを取得
→ 未登録の問い合わせだけを抽出
→ Google Sheets APIで管理表へ行追加
→ 実行ログで登録結果を確認

この章では、Slack通知は行いません。
まずは、AI分類結果がGoogle Sheetsの管理表へ自動登録される状態を作ります。
Slack通知は第4章で扱います。

■Google Sheets APIを使う理由

Google Sheetsを操作する方法には、Google Apps Scriptの簡易機能とGoogle Sheets APIがあります。
この章では、タイトルどおりGoogle Sheets APIを使います。
Google Sheets APIを使うと、スプレッドシートの値の取得、追記、更新、シート作成などをAPI経由で扱えます。
問い合わせ管理では、AIで分類した結果を一覧表として残すことが重要です。
AIは問い合わせ内容を分類します。
Google Sheets APIは、その分類結果を管理表へ登録します。
役割は下記です。

AI：問い合わせ内容を分類する
Google Sheets API：分類結果を管理表へ登録する
Google Apps Script：AI APIとGoogle Sheets APIをつなぐ

この役割分担にすると、AI分類から管理表作成までを自動化できます。

■管理表に入れる項目

問い合わせ管理表には、下記の項目を用意します。

受付日時
送信者
件名
問い合わせ概要
分類
優先度
担当候補
ステータス
分類理由
対応メモ
スレッドID
メッセージID
登録日時

この項目にしておくと、問い合わせの内容、優先度、担当候補、対応状況を一覧で確認できます。
特に重要なのは、メッセージIDです。
同じメールを何度も処理すると、管理表に重複登録されます。
メッセージIDを保存しておけば、すでに登録済みかどうかを判定できます。

■ここから解説する内容

・Google Sheets APIを使うための準備
・Google Apps ScriptでGoogle Sheets APIを有効化する手順
・スプレッドシートIDを安全に保存する方法
・Google Sheets APIでスプレッドシート情報を取得するコード
・問い合わせ管理シートを作成するコード
・ヘッダー行を作成するコード
・AI分類結果を1行データに変換するコード
・登録済みメッセージIDを取得するコード
・未登録データだけを抽出するコード
・Google Sheets APIで行追加するコード
・第2章のGmail API分類処理とつなげる完成コード
・テスト用コード
・運用時の注意点

■Google Sheets APIを使うための準備

ここからは、実際にGoogle Sheets APIを使う準備に入ります。
Google Apps ScriptでGoogle Sheets APIを使う場合は、スクリプト側でGoogle Sheets APIを有効化します。
手順は下記です。

1. Google Apps Scriptを開く
2. 左側メニューの「サービス」を開く
3. 「サービスを追加」を選ぶ
4. Google Sheets APIを選ぶ
5. 識別子が「Sheets」になっていることを確認する
6. 追加する

この設定を行うと、Google Apps Script内で Sheets.Spreadsheets.Values.append などのGoogle Sheets API操作を使えるようになります。
初回実行時には権限の確認が表示されます。
スプレッドシートへアクセスする処理なので、許可する内容を確認して進めます。

■スプレッドシートIDを確認する

Google SheetsのURLには、スプレッドシートIDが含まれています。
たとえばURLが下記の場合です。

https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXX/edit

この中の XXXXXXXXXXXXXXXXXXXXXXXXXXXX の部分がスプレッドシートIDです。
このIDをGoogle Apps Scriptのスクリプトプロパティに保存して使います。

■スプレッドシートIDを保存する

スプレッドシートIDは、コードに直接書かず、スクリプトプロパティに保存します。

function setSheetId() {
  PropertiesService.getScriptProperties().setProperty(
    'SHEET_ID',
    '＜ここにGoogle SheetsのIDを入れる＞'
  );
}

この関数は初期設定のときだけ実行します。
実行後は、コード内にスプレッドシートIDを直接書かず、スクリプトプロパティから呼び出します。

■設定情報を取得する関数

第2章で作成した getConfig() に、スプレッドシートIDを追加します。

function getConfig() {
  return {
    aiApiKey: PropertiesService.getScriptProperties().getProperty('AI_API_KEY'),
    sheetId: PropertiesService.getScriptProperties().getProperty('SHEET_ID')
  };
}

この形にしておくと、AI APIキーとGoogle Sheets IDをまとめて管理できます。
第4章では、ここにSlack Webhook URLを追加します。

■問い合わせ管理シート名を決める

問い合わせ管理表として使うシート名を決めます。

function getInquirySheetName() {
  return '問い合わせ管理';
}

シート名を関数にしておくと、後から変更しやすくなります。
複数の管理表を作る場合にも対応しやすくなります。

■Google Sheets APIでスプレッドシート情報を取得する

まずは、Google Sheets APIでスプレッドシート情報を取得します。

function getSpreadsheetInfo() {
  const config = getConfig();
  return Sheets.Spreadsheets.get(config.sheetId);
}

この関数を使うと、対象スプレッドシートに含まれるシート一覧やシートIDを取得できます。
Google Sheets APIでシートを追加する場合、シート名だけでなくシートIDを扱う場面があります。

■問い合わせ管理シートが存在するか確認する

対象のスプレッドシートに、問い合わせ管理シートがあるか確認します。

function getInquirySheetInfo() {
  const spreadsheet = getSpreadsheetInfo();
  const sheetName = getInquirySheetName();
  const sheets = spreadsheet.sheets || [];
  const targetSheet = sheets.find(sheet => {
    return sheet.properties && sheet.properties.title === sheetName;
  });
  return targetSheet || null;
}

この関数では、指定したシート名が存在する場合はシート情報を返します。
存在しない場合は null を返します。

■Google Sheets APIでシートを作成する

問い合わせ管理シートが存在しない場合は、新しく作成します。

function createInquirySheetIfNeeded() {
  const config = getConfig();
  const sheetInfo = getInquirySheetInfo();
  if (sheetInfo) {
    return sheetInfo;
  }
  const request = {
    requests: [
      {
        addSheet: {
          properties: {
            title: getInquirySheetName()
          }
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(request, config.sheetId);
  return getInquirySheetInfo();
}

この関数を実行すると、問い合わせ管理シートがなければ自動で作成されます。
すでに存在する場合は、何も作らず既存シートを使います。

■ヘッダー行を作成する

問い合わせ管理表には、最初にヘッダー行を作成します。

function getInquiryHeaders() {
  return [
    '受付日時',
    '送信者',
    '件名',
    '問い合わせ概要',
    '分類',
    '優先度',
    '担当候補',
    'ステータス',
    '分類理由',
    '対応メモ',
    'スレッドID',
    'メッセージID',
    '登録日時'
  ];
}

ヘッダー項目を関数にしておくと、後で列を増やす場合も管理しやすくなります。

■ヘッダーが存在するか確認する

すでにヘッダーがある場合は、上書きしないようにします。
まず、A1セルの値を確認します。

function hasInquiryHeader() {
  const config = getConfig();
  const sheetName = getInquirySheetName();
  const range = `${sheetName}!A1:A1`;
  const response = Sheets.Spreadsheets.Values.get(config.sheetId, range);
  return response.values && response.values.length > 0 && response.values[0][0];
}

A1セルに値がある場合は、すでにヘッダーがあると判断します。

■Google Sheets APIでヘッダー行を書き込む

ヘッダーがない場合だけ、1行目にヘッダーを書き込みます。

function setupInquiryHeaderIfNeeded() {
  const config = getConfig();
  createInquirySheetIfNeeded();
  if (hasInquiryHeader()) {
    return;
  }
  const sheetName = getInquirySheetName();
  const headers = getInquiryHeaders();
  const range = `${sheetName}!A1:M1`;
  const valueRange = {
    values: [headers]
  };
  Sheets.Spreadsheets.Values.update(valueRange, config.sheetId, range, {
    valueInputOption: 'RAW'
  });
}

この関数を実行すると、問い合わせ管理表の1行目にヘッダーが作られます。
すでにヘッダーがある場合は何もしません。

■AI分類結果を1行データに変換する

第2章のAI分類結果を、Google Sheetsへ登録できる1行データに変換します。

function convertResultToRow(result) {
  return [
    result.receivedAt || '',
    result.from || '',
    result.subject || '',
    result.summary || '',
    result.category || '',
    result.priority || '',
    result.assignee || '',
    result.status || '未対応',
    result.reason || '',
    '',
    result.threadId || '',
    result.messageId || '',
    new Date().toISOString()
  ];
}

対応メモ は人が後から入力できるように、最初は空欄にしています。
登録日時は、スクリプト実行時の日時を入れます。

■サンプルデータで行データを確認する

まずはサンプルデータで、行データが正しく作られるか確認します。

function testConvertResultToRow() {
  const sampleResult = {
    receivedAt: new Date().toString(),
    from: 'customer@example.com',
    subject: 'ログインできない件について',
    summary: 'ログイン時のエラーにより管理画面へ入れない問い合わせ',
    category: '技術問い合わせ',
    priority: '高',
    assignee: '技術サポート',
    status: '未対応',
    reason: 'ログインエラーと至急確認の記載があるため',
    threadId: 'sample-thread-id',
    messageId: 'sample-message-id'
  };
  const row = convertResultToRow(sampleResult);
  Logger.log(JSON.stringify(row, null, 2));
}

このテストで、Google Sheetsに登録する行の形を確認できます。

■Google Sheets APIで分類結果を追記する

分類結果をGoogle Sheetsへ追記します。
Google Sheets APIでは、Values.append を使って末尾に行を追加できます。

function appendRowsToInquirySheet(rows) {
  const config = getConfig();
  const sheetName = getInquirySheetName();
  const range = `${sheetName}!A:M`;
  const valueRange = {
    values: rows
  };
  Sheets.Spreadsheets.Values.append(valueRange, config.sheetId, range, {
    valueInputOption: 'USER_ENTERED',
    insertDataOption: 'INSERT_ROWS'
  });
}

この関数を使うと、問い合わせ管理表の末尾にデータを追加できます。
insertDataOption: 'INSERT_ROWS' により、新しい行として追加されます。

■分類結果をまとめて保存する

AI分類結果をまとめてGoogle Sheetsへ保存します。

function saveClassifiedResultsToSheet(results) {
  setupInquiryHeaderIfNeeded();
  if (!results || results.length === 0) {
    Logger.log('登録対象のデータがありません');
    return;
  }
  const rows = results.map(result => convertResultToRow(result));
  appendRowsToInquirySheet(rows);
  Logger.log('登録件数: ' + rows.length);
}

この関数を使うと、AI分類結果をまとめてGoogle Sheetsへ登録できます。
1件ずつ追加するより、まとめて登録する方が扱いやすくなります。

■サンプルデータでGoogle Sheets登録をテストする

最初は、実際のGmail APIやAI APIを使わず、サンプルデータでGoogle Sheets登録を確認します。

function testSaveSampleResultsToSheet() {
  const sampleResults = [
    {
      receivedAt: new Date().toString(),
      from: 'customer@example.com',
      subject: 'ログインできない件について',
      summary: 'ログイン時のエラーにより管理画面へ入れない問い合わせ',
      category: '技術問い合わせ',
      priority: '高',
      assignee: '技術サポート',
      status: '未対応',
      reason: 'ログインエラーと至急確認の記載があるため',
      threadId: 'sample-thread-id',
      messageId: 'sample-message-id'
    }
  ];
  saveClassifiedResultsToSheet(sampleResults);
}

この関数を実行し、Google Sheetsに1行追加されれば成功です。
まずはサンプルで確認し、その後に第2章のGmail API分類処理とつなげます。

■登録済みメッセージIDを取得する

同じメールを何度も登録しないために、登録済みのメッセージIDを取得します。
メッセージIDは、12列目に保存しています。

function getRegisteredMessageIds() {
  const config = getConfig();
  const sheetName = getInquirySheetName();
  const range = `${sheetName}!L2:L`;
  let response;
  try {
    response = Sheets.Spreadsheets.Values.get(config.sheetId, range);
  } catch (error) {
    logError('getRegisteredMessageIds', error);
    return new Set();
  }
  if (!response.values || response.values.length === 0) {
    return new Set();
  }
  const ids = response.values
    .flat()
    .filter(value => value);
  return new Set(ids);
}

この関数では、問い合わせ管理表に登録済みのメッセージIDをSetとして返します。
Setにしておくと、重複判定がしやすくなります。

■未登録の結果だけを抽出する

登録済みメッセージIDを使って、未登録の問い合わせだけを抽出します。

function filterNewResults(results) {
  const registeredIds = getRegisteredMessageIds();
  return results.filter(result => {
    return result.messageId && !registeredIds.has(result.messageId);
  });
}

この関数を使うと、同じメールの二重登録を防げます。
Gmail APIで同じメールが再取得されても、管理表には1回だけ登録されます。

■未登録の分類結果だけを保存する

重複登録を防ぎながら、未登録分だけGoogle Sheetsへ保存します。

function saveNewClassifiedResultsToSheet(results) {
  setupInquiryHeaderIfNeeded();
  const newResults = filterNewResults(results);
  if (!newResults || newResults.length === 0) {
    Logger.log('新規登録対象のデータがありません');
    return;
  }
  const rows = newResults.map(result => convertResultToRow(result));
  appendRowsToInquirySheet(rows);
  Logger.log('新規登録件数: ' + rows.length);
}

運用時には、この関数を使う方が安全です。
同じ問い合わせメールが重複して登録されることを防げます。

■第2章のGmail API分類処理とつなげる

第2章で作成した classifyInquiryEmails() の結果を、Google Sheets APIへ登録します。

function runClassificationAndSaveToSheet() {
  try {
    const results = classifyInquiryEmails();
    saveNewClassifiedResultsToSheet(results);
    Logger.log('問い合わせ管理表への登録が完了しました');
  } catch (error) {
    logError('runClassificationAndSaveToSheet', error);
  }
}

この関数を実行すると、下記の流れになります。

Gmail APIで問い合わせメールを取得
→ AIで問い合わせ内容を分類
→ Google Sheets APIで管理表へ登録

ここまでできれば、問い合わせ管理表の自動作成ができます。

■管理表の見た目を整える

Google Sheets APIでも、列幅や固定行などの設定を行えます。
まず、問い合わせ管理シートのシートIDを取得します。

function getInquirySheetId() {
  const sheetInfo = getInquirySheetInfo();
  if (!sheetInfo || !sheetInfo.properties) {
    throw new Error('問い合わせ管理シートが見つかりません');
  }
  return sheetInfo.properties.sheetId;
}

シートIDを取得しておくと、表示設定や入力規則の設定に使えます。

■ヘッダー行を固定する

ヘッダー行を固定すると、管理表が見やすくなります。

function freezeHeaderRow() {
  const config = getConfig();
  const sheetId = getInquirySheetId();
  const request = {
    requests: [
      {
        updateSheetProperties: {
          properties: {
            sheetId: sheetId,
            gridProperties: {
              frozenRowCount: 1
            }
          },
          fields: 'gridProperties.frozenRowCount'
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(request, config.sheetId);
}

1行目を固定しておくと、行数が増えても項目名を確認しやすくなります。

■ヘッダー行を太字にする

ヘッダー行を太字にして、管理表として見やすくします。

function boldHeaderRow() {
  const config = getConfig();
  const sheetId = getInquirySheetId();
  const request = {
    requests: [
      {
        repeatCell: {
          range: {
            sheetId: sheetId,
            startRowIndex: 0,
            endRowIndex: 1
          },
          cell: {
            userEnteredFormat: {
              textFormat: {
                bold: true
              }
            }
          },
          fields: 'userEnteredFormat.textFormat.bold'
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(request, config.sheetId);
}

見た目を整えると、管理表として使いやすくなります。

■ステータス候補を入力規則にする

問い合わせ管理では、ステータス列が重要です。
ステータスの表記がばらつくと、後で集計しにくくなります。

未対応
対応中
確認中
完了
要確認
保留

Google Sheets APIで、ステータス列に入力規則を設定します。

function setStatusValidation() {
  const config = getConfig();
  const sheetId = getInquirySheetId();
  const request = {
    requests: [
      {
        setDataValidation: {
          range: {
            sheetId: sheetId,
            startRowIndex: 1,
            endRowIndex: 1000,
            startColumnIndex: 7,
            endColumnIndex: 8
          },
          rule: {
            condition: {
              type: 'ONE_OF_LIST',
              values: [
                { userEnteredValue: '未対応' },
                { userEnteredValue: '対応中' },
                { userEnteredValue: '確認中' },
                { userEnteredValue: '完了' },
                { userEnteredValue: '要確認' },
                { userEnteredValue: '保留' }
              ]
            },
            showCustomUi: true,
            strict: true
          }
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(request, config.sheetId);
}

ステータス候補を固定すると、表記ゆれを防げます。

■優先度候補を入力規則にする

優先度も表記をそろえます。

高
中
低

Google Sheets APIで、優先度列に入力規則を設定します。

function setPriorityValidation() {
  const config = getConfig();
  const sheetId = getInquirySheetId();
  const request = {
    requests: [
      {
        setDataValidation: {
          range: {
            sheetId: sheetId,
            startRowIndex: 1,
            endRowIndex: 1000,
            startColumnIndex: 5,
            endColumnIndex: 6
          },
          rule: {
            condition: {
              type: 'ONE_OF_LIST',
              values: [
                { userEnteredValue: '高' },
                { userEnteredValue: '中' },
                { userEnteredValue: '低' }
              ]
            },
            showCustomUi: true,
            strict: true
          }
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(request, config.sheetId);
}

AI分類結果も、手動修正時の値も、同じ表記にそろえやすくなります。

■管理表の初期設定をまとめる

問い合わせ管理表の準備をまとめて実行する関数を作ります。

function setupInquiryManagementSheet() {
  setupInquiryHeaderIfNeeded();
  freezeHeaderRow();
  boldHeaderRow();
  setStatusValidation();
  setPriorityValidation();
}

この関数を最初に実行しておくと、管理表として使いやすい状態になります。

■登録処理の完成形

問い合わせ管理表の準備、Gmail API取得、AI分類、Google Sheets API登録までをまとめます。

function runInquiryManagementFlow() {
  try {
    setupInquiryManagementSheet();
    const results = classifyInquiryEmails();
    saveNewClassifiedResultsToSheet(results);
    Logger.log('問い合わせ管理表への登録が完了しました');
  } catch (error) {
    logError('runInquiryManagementFlow', error);
  }
}

この関数を実行すると、下記が一気に行われます。

問い合わせ管理表の準備
→ Gmail APIで問い合わせメール取得
→ AIで問い合わせ内容分類
→ Google Sheets APIで未登録分を登録

第2章の処理とつながり、問い合わせ管理表を自動作成できます。

■この章の完成コード

この章で追加する主要コードをまとめると下記です。

function setSheetId() {
  PropertiesService.getScriptProperties().setProperty(
    'SHEET_ID',
    '＜ここにGoogle SheetsのIDを入れる＞'
  );
}
function getConfig() {
  return {
    aiApiKey: PropertiesService.getScriptProperties().getProperty('AI_API_KEY'),
    sheetId: PropertiesService.getScriptProperties().getProperty('SHEET_ID')
  };
}
function getInquirySheetName() {
  return '問い合わせ管理';
}
function getSpreadsheetInfo() {
  const config = getConfig();
  return Sheets.Spreadsheets.get(config.sheetId);
}
function getInquirySheetInfo() {
  const spreadsheet = getSpreadsheetInfo();
  const sheetName = getInquirySheetName();
  const sheets = spreadsheet.sheets || [];
  const targetSheet = sheets.find(sheet => {
    return sheet.properties && sheet.properties.title === sheetName;
  });
  return targetSheet || null;
}
function createInquirySheetIfNeeded() {
  const config = getConfig();
  const sheetInfo = getInquirySheetInfo();
  if (sheetInfo) {
    return sheetInfo;
  }
  const request = {
    requests: [
      {
        addSheet: {
          properties: {
            title: getInquirySheetName()
          }
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(request, config.sheetId);
  return getInquirySheetInfo();
}
function getInquiryHeaders() {
  return [
    '受付日時',
    '送信者',
    '件名',
    '問い合わせ概要',
    '分類',
    '優先度',
    '担当候補',
    'ステータス',
    '分類理由',
    '対応メモ',
    'スレッドID',
    'メッセージID',
    '登録日時'
  ];
}
function hasInquiryHeader() {
  const config = getConfig();
  const sheetName = getInquirySheetName();
  const range = `${sheetName}!A1:A1`;
  const response = Sheets.Spreadsheets.Values.get(config.sheetId, range);
  return response.values && response.values.length > 0 && response.values[0][0];
}
function setupInquiryHeaderIfNeeded() {
  const config = getConfig();
  createInquirySheetIfNeeded();
  if (hasInquiryHeader()) {
    return;
  }
  const sheetName = getInquirySheetName();
  const headers = getInquiryHeaders();
  const range = `${sheetName}!A1:M1`;
  const valueRange = {
    values: [headers]
  };
  Sheets.Spreadsheets.Values.update(valueRange, config.sheetId, range, {
    valueInputOption: 'RAW'
  });
}
function convertResultToRow(result) {
  return [
    result.receivedAt || '',
    result.from || '',
    result.subject || '',
    result.summary || '',
    result.category || '',
    result.priority || '',
    result.assignee || '',
    result.status || '未対応',
    result.reason || '',
    '',
    result.threadId || '',
    result.messageId || '',
    new Date().toISOString()
  ];
}
function appendRowsToInquirySheet(rows) {
  const config = getConfig();
  const sheetName = getInquirySheetName();
  const range = `${sheetName}!A:M`;
  const valueRange = {
    values: rows
  };
  Sheets.Spreadsheets.Values.append(valueRange, config.sheetId, range, {
    valueInputOption: 'USER_ENTERED',
    insertDataOption: 'INSERT_ROWS'
  });
}
function saveClassifiedResultsToSheet(results) {
  setupInquiryHeaderIfNeeded();
  if (!results || results.length === 0) {
    Logger.log('登録対象のデータがありません');
    return;
  }
  const rows = results.map(result => convertResultToRow(result));
  appendRowsToInquirySheet(rows);
  Logger.log('登録件数: ' + rows.length);
}
function getRegisteredMessageIds() {
  const config = getConfig();
  const sheetName = getInquirySheetName();
  const range = `${sheetName}!L2:L`;
  let response;
  try {
    response = Sheets.Spreadsheets.Values.get(config.sheetId, range);
  } catch (error) {
    logError('getRegisteredMessageIds', error);
    return new Set();
  }
  if (!response.values || response.values.length === 0) {
    return new Set();
  }
  const ids = response.values
    .flat()
    .filter(value => value);
  return new Set(ids);
}
function filterNewResults(results) {
  const registeredIds = getRegisteredMessageIds();
  return results.filter(result => {
    return result.messageId && !registeredIds.has(result.messageId);
  });
}
function saveNewClassifiedResultsToSheet(results) {
  setupInquiryHeaderIfNeeded();
  const newResults = filterNewResults(results);
  if (!newResults || newResults.length === 0) {
    Logger.log('新規登録対象のデータがありません');
    return;
  }
  const rows = newResults.map(result => convertResultToRow(result));
  appendRowsToInquirySheet(rows);
  Logger.log('新規登録件数: ' + rows.length);
}
function getInquirySheetId() {
  const sheetInfo = getInquirySheetInfo();
  if (!sheetInfo || !sheetInfo.properties) {
    throw new Error('問い合わせ管理シートが見つかりません');
  }
  return sheetInfo.properties.sheetId;
}
function freezeHeaderRow() {
  const config = getConfig();
  const sheetId = getInquirySheetId();
  const request = {
    requests: [
      {
        updateSheetProperties: {
          properties: {
            sheetId: sheetId,
            gridProperties: {
              frozenRowCount: 1
            }
          },
          fields: 'gridProperties.frozenRowCount'
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(request, config.sheetId);
}
function boldHeaderRow() {
  const config = getConfig();
  const sheetId = getInquirySheetId();
  const request = {
    requests: [
      {
        repeatCell: {
          range: {
            sheetId: sheetId,
            startRowIndex: 0,
            endRowIndex: 1
          },
          cell: {
            userEnteredFormat: {
              textFormat: {
                bold: true
              }
            }
          },
          fields: 'userEnteredFormat.textFormat.bold'
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(request, config.sheetId);
}
function setStatusValidation() {
  const config = getConfig();
  const sheetId = getInquirySheetId();
  const request = {
    requests: [
      {
        setDataValidation: {
          range: {
            sheetId: sheetId,
            startRowIndex: 1,
            endRowIndex: 1000,
            startColumnIndex: 7,
            endColumnIndex: 8
          },
          rule: {
            condition: {
              type: 'ONE_OF_LIST',
              values: [
                { userEnteredValue: '未対応' },
                { userEnteredValue: '対応中' },
                { userEnteredValue: '確認中' },
                { userEnteredValue: '完了' },
                { userEnteredValue: '要確認' },
                { userEnteredValue: '保留' }
              ]
            },
            showCustomUi: true,
            strict: true
          }
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(request, config.sheetId);
}
function setPriorityValidation() {
  const config = getConfig();
  const sheetId = getInquirySheetId();
  const request = {
    requests: [
      {
        setDataValidation: {
          range: {
            sheetId: sheetId,
            startRowIndex: 1,
            endRowIndex: 1000,
            startColumnIndex: 5,
            endColumnIndex: 6
          },
          rule: {
            condition: {
              type: 'ONE_OF_LIST',
              values: [
                { userEnteredValue: '高' },
                { userEnteredValue: '中' },
                { userEnteredValue: '低' }
              ]
            },
            showCustomUi: true,
            strict: true
          }
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(request, config.sheetId);
}
function setupInquiryManagementSheet() {
  setupInquiryHeaderIfNeeded();
  freezeHeaderRow();
  boldHeaderRow();
  setStatusValidation();
  setPriorityValidation();
}
function runClassificationAndSaveToSheet() {
  try {
    const results = classifyInquiryEmails();
    saveNewClassifiedResultsToSheet(results);
    Logger.log('問い合わせ管理表への登録が完了しました');
  } catch (error) {
    logError('runClassificationAndSaveToSheet', error);
  }
}
function runInquiryManagementFlow() {
  try {
    setupInquiryManagementSheet();
    const results = classifyInquiryEmails();
    saveNewClassifiedResultsToSheet(results);
    Logger.log('問い合わせ管理表への登録が完了しました');
  } catch (error) {
    logError('runInquiryManagementFlow', error);
  }
}
function testConvertResultToRow() {
  const sampleResult = {
    receivedAt: new Date().toString(),
    from: 'customer@example.com',
    subject: 'ログインできない件について',
    summary: 'ログイン時のエラーにより管理画面へ入れない問い合わせ',
    category: '技術問い合わせ',
    priority: '高',
    assignee: '技術サポート',
    status: '未対応',
    reason: 'ログインエラーと至急確認の記載があるため',
    threadId: 'sample-thread-id',
    messageId: 'sample-message-id'
  };
  const row = convertResultToRow(sampleResult);
  Logger.log(JSON.stringify(row, null, 2));
}
function testSaveSampleResultsToSheet() {
  const sampleResults = [
    {
      receivedAt: new Date().toString(),
      from: 'customer@example.com',
      subject: 'ログインできない件について',
      summary: 'ログイン時のエラーにより管理画面へ入れない問い合わせ',
      category: '技術問い合わせ',
      priority: '高',
      assignee: '技術サポート',
      status: '未対応',
      reason: 'ログインエラーと至急確認の記載があるため',
      threadId: 'sample-thread-id',
      messageId: 'sample-message-id'
    }
  ];
  saveClassifiedResultsToSheet(sampleResults);
}

このコードを第2章のコードに追加すると、AI分類結果をGoogle Sheets APIで管理表へ登録できます。
第4章では、この管理表の内容をもとに、Slack APIで担当者通知を送る処理を作ります。

■運用時に注意すること

Google Sheets APIで問い合わせ管理表を作る場合は、下記に注意します。

・同じメールを二重登録しない
・ステータスの表記をそろえる
・優先度の表記をそろえる
・担当候補の表記をそろえる
・最初は少ない件数で試す
・個人情報や機密情報を不用意に共有しない
・編集権限を必要な人だけにする
・対応メモ欄の運用ルールを決めておく

Google Sheetsは便利ですが、共有範囲を広げすぎると情報管理が甘くなります。
問い合わせ内容に個人情報や機密情報が含まれる場合は、共有権限を慎重に設定します。

■まとめ

AI分類結果をGoogle Sheets APIで管理表へ自動登録すると、問い合わせ管理がかなり楽になります。
Gmail APIで取得した問い合わせメールをAIで分類し、その結果をGoogle Sheetsへ登録できます。
受付日時、送信者、件名、概要、分類、優先度、担当候補、ステータスを一覧で見られるため、対応状況を把握しやすくなります。
重複登録を防ぐ処理を入れておけば、同じメールが何度も登録されることも防げます。
ステータスや優先度の入力規則を使えば、表記ゆれも減らせます。
この章では、問い合わせ管理表の自動作成までを作りました。
次章では、この管理表の内容をもとに、Slack APIで担当者通知を送る処理を作ります。

■次回

第4章（応用編）：AIとSlack APIで担当者通知を自動化する方法
※作成中

いいなと思ったら応援しよう！