Chapter 3 (Advanced): How to Automatically Create Management Tables with AI and the Google Sheets API
■ Introduction
In this advanced section, we will cover business automation by integrating AI and APIs.
If you have not read Chapter 1 yet, it will be easier to understand this chapter if you first check the overall picture in Chapter 1.
This chapter is a continuation of the previous one.
In the previous chapter, we created a process to retrieve inquiry emails using the Gmail API and classify the inquiry content using AI.
If you have not read Chapter 2 yet, it will be easier to understand the flow of the process if you check Chapter 2 before reading this chapter.
Chapter 2 (Advanced): How to Automatically Classify Inquiry Emails using AI and the Gmail API
In this chapter, we will create a method to automatically register the AI classification results created in Chapter 2 into a management table using the Google Sheets API.
Even if you can classify inquiry emails with AI, if a person still has to manually transcribe the results into a management table every time, manual work remains.
・Enter the reception date and time
・Enter the sender
・Enter the subject
・Enter the inquiry summary
・Enter the classification
・Enter the priority
・Enter the candidate person in charge
・Enter the status
Such transcription work becomes a burden as the number of items increases.
By using the Google Sheets API, you can register AI classification results directly into the management table.
In this chapter, we will create the columns for the inquiry management table and build a process to automatically register AI classification results into Google Sheets.
■ What we will build in this chapter
The process we will build in this chapter is as follows.
第2章のAI分類結果を受け取る
→ Google Sheets APIでスプレッドシート情報を取得
→ 問い合わせ管理シートを確認
→ 必要に応じてシートを作成
→ ヘッダー行を作成
→ 登録済みメッセージIDを取得
→ 未登録の問い合わせだけを抽出
→ Google Sheets APIで管理表へ行追加
→ 実行ログで登録結果を確認We will not perform Slack notifications in this chapter.
First, we will create a state where AI classification results are automatically registered in the Google Sheets management table.
Slack notifications will be covered in Chapter 4.
■ Reasons for using the Google Sheets API
There are two ways to operate Google Sheets: the simple Google Apps Script functions and the Google Sheets API.
In this chapter, as the title suggests, we will use the Google Sheets API.
Using the Google Sheets API allows you to handle retrieving, appending, updating, and creating spreadsheet values via the API.
In inquiry management, it is important to keep the results classified by AI as a list.
AI classifies the inquiry content.
The Google Sheets API registers those classification results into the management table.
The roles are as follows.
AI：問い合わせ内容を分類する
Google Sheets API：分類結果を管理表へ登録する
Google Apps Script：AI APIとGoogle Sheets APIをつなぐWith this division of roles, you can automate everything from AI classification to management table creation.
■ Items to include in the management table
We will prepare the following items for the inquiry management table.
受付日時
送信者
件名
問い合わせ概要
分類
優先度
担当候補
ステータス
分類理由
対応メモ
スレッドID
メッセージID
登録日時By using these items, you can check the inquiry content, priority, candidate person in charge, and response status in a list.
The message ID is particularly important.
If you process the same email multiple times, it will be registered in the management table as a duplicate.
By saving the message ID, you can determine whether it has already been registered.
■ Contents to be explained from here
・Preparation for using the Google Sheets API
・Steps to enable the Google Sheets API in Google Apps Script
・How to safely save the spreadsheet ID
・Code to retrieve spreadsheet information using the Google Sheets API
・Code to create an inquiry management sheet
・Code to create a header row
・Code to convert AI classification results into single-row data
・Code to retrieve registered message IDs
・Code to extract only unregistered data
・Code to add rows using the Google Sheets API
・Completed code connecting to the Gmail API classification process from Chapter 2
・Test code
・Points to note during operation
■ Preparation for using the Google Sheets API
From here, we will begin the actual preparation for using the Google Sheets API.
When using the Google Sheets API in Google Apps Script, you must enable the Google Sheets API on the script side.
The steps are as follows.
1. Google Apps Scriptを開く
2. 左側メニューの「サービス」を開く
3. 「サービスを追加」を選ぶ
4. Google Sheets APIを選ぶ
5. 識別子が「Sheets」になっていることを確認する
6. 追加するOnce this setting is configured, you will be able to use Google Sheets API operations such as Sheets.Spreadsheets.Values.append within Google Apps Script.
A permission confirmation will be displayed upon the first execution.
Since this is a process that accesses a spreadsheet, check the permissions requested and proceed.
■ Confirming the Spreadsheet ID
The Google Sheets URL contains the spreadsheet ID.
For example, if the URL is as follows:
https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXX/editThe part that says XXXXXXXXXXXXXXXXXXXXXXXXXXXX is the spreadsheet ID.
We will save this ID in the Google Apps Script properties to use it.
■ Saving the Spreadsheet ID
Instead of writing the spreadsheet ID directly into the code, we save it in the script properties.
function setSheetId() {
PropertiesService.getScriptProperties().setProperty(
'SHEET_ID',
'＜ここにGoogle SheetsのIDを入れる＞'
);
}This function is only executed during initial setup.
After execution, we call it from the script properties instead of writing the spreadsheet ID directly in the code.
■ Function to Retrieve Configuration Information
We will add the spreadsheet ID to the getConfig() function created in Chapter 2.
function getConfig() {
return {
aiApiKey: PropertiesService.getScriptProperties().getProperty('AI_API_KEY'),
sheetId: PropertiesService.getScriptProperties().getProperty('SHEET_ID')
};
}By structuring it this way, you can manage the AI API key and Google Sheets ID together.
In Chapter 4, we will add the Slack Webhook URL here.
■ Deciding on the Inquiry Management Sheet Name
Decide on the sheet name to be used as the inquiry management table.
function getInquirySheetName() {
return '問い合わせ管理';
}By making the sheet name a function, it becomes easier to change later.
It also makes it easier to handle cases where you create multiple management tables.
■ Retrieving Spreadsheet Information with the Google Sheets API
First, we retrieve the spreadsheet information using the Google Sheets API.
function getSpreadsheetInfo() {
const config = getConfig();
return Sheets.Spreadsheets.get(config.sheetId);
}Using this function, you can retrieve a list of sheets and sheet IDs contained in the target spreadsheet.
When adding sheets via the Google Sheets API, there are times when you need to handle not just the sheet name, but also the sheet ID.
■ Checking if the Inquiry Management Sheet Exists
Check if the inquiry management sheet exists in the target spreadsheet.
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;
}This function returns the sheet information if the specified sheet name exists.
If it does not exist, it returns null.
■ Creating a Sheet with the Google Sheets API
If the inquiry management sheet does not exist, we create a new one.
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();
}When this function is executed, an inquiry management sheet will be created automatically if it does not already exist.
If it already exists, it will use the existing sheet without creating anything.
■ Creating the header row
First, create a header row for the inquiry management table.
function getInquiryHeaders() {
return [
'受付日時',
'送信者',
'件名',
'問い合わせ概要',
'分類',
'優先度',
'担当候補',
'ステータス',
'分類理由',
'対応メモ',
'スレッドID',
'メッセージID',
'登録日時'
];
}By defining header items as a function, it becomes easier to manage if you need to add columns later.
■ Checking if the header exists
If a header already exists, ensure it is not overwritten.
First, check the value of cell 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];
}If there is a value in cell A1, it is determined that the header already exists.
■ Writing the header row with the Google Sheets API
Write the header to the first row only if it does not exist.
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'
});
}When this function is executed, a header is created in the first row of the inquiry management table.
If a header already exists, it does nothing.
■ Converting AI classification results into a single-row data format
Convert the AI classification results from Chapter 2 into a single-row data format that can be registered in Google Sheets.
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()
];
}The 'Response Memo' field is left blank initially so that a person can enter it later.
For the registration date and time, insert the date and time when the script is executed.
■ Verifying row data with sample data
First, verify that the row data is created correctly using sample data.
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));
}With this test, you can confirm the format of the row to be registered in Google Sheets.
■ Appending classification results with the Google Sheets API
Append the classification results to Google Sheets.
With the Google Sheets API, you can use Values.append to add a row to the end.
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'
});
}Using this function, you can add data to the end of the inquiry management table.
By using insertDataOption: 'INSERT_ROWS', it will be added as a new row.
■ Saving classification results in bulk
Save the AI classification results to Google Sheets in bulk.
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);
}Using this function, you can register AI classification results to Google Sheets in bulk.
It is easier to handle bulk registration than adding them one by one.
■ Testing Google Sheets registration with sample data
First, we will verify Google Sheets registration using sample data without using the actual Gmail API or AI API.
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);
}If you run this function and one row is added to Google Sheets, it is a success.
Verify with the sample first, then connect it to the Gmail API classification process from Chapter 2.
■ Retrieving registered message IDs
To avoid registering the same email multiple times, we retrieve the registered message IDs.
The message ID is stored in the 12th column.
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);
}This function returns the message IDs already registered in the inquiry management table as a Set.
Using a Set makes it easier to check for duplicates.
■ Extracting only unregistered results
Using the registered message IDs, we extract only the unregistered inquiries.
function filterNewResults(results) {
const registeredIds = getRegisteredMessageIds();
return results.filter(result => {
return result.messageId && !registeredIds.has(result.messageId);
});
}Using this function prevents duplicate registration of the same email.
Even if the same email is retrieved again by the Gmail API, it will only be registered once in the management table.
■ Saving only unregistered classification results
We save only the unregistered items to Google Sheets while preventing duplicate registration.
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);
}It is safer to use this function during operation.
It prevents the same inquiry email from being registered multiple times.
■ Connecting to the Gmail API classification process from Chapter 2
We register the results of classifyInquiryEmails() created in Chapter 2 to the Google Sheets API.
function runClassificationAndSaveToSheet() {
try {
const results = classifyInquiryEmails();
saveNewClassifiedResultsToSheet(results);
Logger.log('問い合わせ管理表への登録が完了しました');
} catch (error) {
logError('runClassificationAndSaveToSheet', error);
}
}When this function is executed, the following flow occurs.
Gmail APIで問い合わせメールを取得
→ AIで問い合わせ内容を分類
→ Google Sheets APIで管理表へ登録Once you have reached this point, you can automatically create an inquiry management table.
■ Formatting the appearance of the management table
You can also configure settings such as column width and frozen rows using the Google Sheets API.
First, retrieve the sheet ID of the inquiry management sheet.
function getInquirySheetId() {
const sheetInfo = getInquirySheetInfo();
if (!sheetInfo || !sheetInfo.properties) {
throw new Error('問い合わせ管理シートが見つかりません');
}
return sheetInfo.properties.sheetId;
}Obtaining the sheet ID allows you to use it for display settings and data validation settings.
■ Freezing the header row
Freezing the header row makes the management table easier to read.
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);
}Freezing the first row makes it easier to check item names even as the number of rows increases.
■ Making the header row bold
Make the header row bold to improve the readability of the management table.
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);
}Improving the appearance makes the management table easier to use.
■ Setting status options as data validation
In inquiry management, the status column is important.
If status notations vary, it becomes difficult to aggregate data later.
未対応
対応中
確認中
完了
要確認
保留Use the Google Sheets API to set data validation for the status column.
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);
}Fixing the status options prevents inconsistencies in notation.
■ Setting priority options as data validation
Standardize the notation for priority as well.
高
中
低Use the Google Sheets API to set data validation for the priority column.
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);
}This makes it easier to keep both AI classification results and values from manual corrections consistent.
■ Summarizing the initial settings for the management table
Create a function that executes the preparation of the inquiry management table all at once.
function setupInquiryManagementSheet() {
setupInquiryHeaderIfNeeded();
freezeHeaderRow();
boldHeaderRow();
setStatusValidation();
setPriorityValidation();
}Running this function first ensures the management table is in a usable state.
■ The completed registration process
Consolidate the preparation of the inquiry management table, Gmail API retrieval, AI classification, and Google Sheets API registration.
function runInquiryManagementFlow() {
try {
setupInquiryManagementSheet();
const results = classifyInquiryEmails();
saveNewClassifiedResultsToSheet(results);
Logger.log('問い合わせ管理表への登録が完了しました');
} catch (error) {
logError('runInquiryManagementFlow', error);
}
}Executing this function will perform the following all at once.
問い合わせ管理表の準備
→ Gmail APIで問い合わせメール取得
→ AIで問い合わせ内容分類
→ Google Sheets APIで未登録分を登録Connecting to the processing in Chapter 2, you can automatically create an inquiry management table.
■ Completed code for this chapter
The main code to be added in this chapter is summarized below.
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);
}By adding this code to the code from Chapter 2, you can register AI classification results into a management table using the Google Sheets API.
In Chapter 4, we will create a process to send notifications to the person in charge via the Slack API based on the contents of this management table.
■ Points to note during operation
When creating an inquiry management table using the Google Sheets API, please note the following.
・同じメールを二重登録しない
・ステータスの表記をそろえる
・優先度の表記をそろえる
・担当候補の表記をそろえる
・最初は少ない件数で試す
・個人情報や機密情報を不用意に共有しない
・編集権限を必要な人だけにする
・対応メモ欄の運用ルールを決めておくGoogle Sheets is convenient, but if the sharing scope is too broad, information management becomes lax.
If the inquiry content contains personal or confidential information, set the sharing permissions carefully.
■ Summary
Automatically registering AI classification results into a management table using the Google Sheets API makes inquiry management much easier.
You can classify inquiry emails retrieved via the Gmail API using AI and register the results in Google Sheets.
Since you can view the reception date and time, sender, subject, summary, classification, priority, candidate for person in charge, and status in a list, it becomes easier to grasp the status of responses.
By including a process to prevent duplicate registration, you can also prevent the same email from being registered multiple times.
By using input rules for status and priority, you can also reduce variations in notation.
In this chapter, we have created the automatic creation of the inquiry management table.
In the next chapter, we will create a process to send notifications to the person in charge via the Slack API based on the contents of this management table.
■ Next time
Chapter 4 (Advanced): How to Automate Notifications to the Person in Charge with AI and the Slack API
※ Under construction