How to Automatically Categorize Inquiries and Generate FAQ Candidates with Google Sheets and Gemini API [Apps Script Implementation]
How to Automatically Categorize Inquiries and Generate FAQ Candidates with Google Sheets and Gemini API [Apps Script Implementation]
As you continue to handle inquiries, data keeps piling up in Google Sheets.
Questions received via LINE, inquiries from Google Forms, email correspondence history, and customer requests entered by sales representatives.
However, in the field, you often hear comments like these:
"The same questions keep coming in," "Answers vary slightly depending on the person in charge," "I want to turn them into an FAQ, but I don't have time to organize them," "I don't know which questions should be posted on the Web or LINE FAQ."
In this state, inquiry data remains nothing more than a history.
In this article, I will organize how to implement automatic inquiry categorization and FAQ candidate output from a practical perspective using a combination of Google Sheets × Apps Script × Gemini API.
What this system can do
The system we are building consists of two main stages.
The first is the automatic categorization of each individual inquiry.
We send the inquiry content to the Gemini API and have it return the following information:
Category (product, pricing, delivery time, etc.)
Summary
Urgency
Recommended department
Whether it is a candidate for an FAQ
Draft response
Points requiring internal verification
The second is the output of FAQ candidates from accumulated data.
Pass multiple inquiries to the Gemini API at once to organize frequently asked questions. You can automatically generate FAQ candidates by category, such as delivery dates, returns, and payment methods.
Once this system is in place, the inquiry history accumulated in Google Sheets becomes easier to use as material for FAQs and manuals.
Overall Structure and Flow
text
問い合わせデータをGoogle Sheetsに蓄積
↓
Apps Scriptで未分類の行を取得
↓
Gemini APIへ問い合わせ内容を送信
↓
Gemini APIが分類・要約・回答案をJSONで返す
↓
Apps Scriptで結果をGoogle Sheetsに書き戻す
↓
複数件をまとめてFAQ候補として別シートに出力
↓
人間が確認して正式FAQ化The important thing here is the design of letting the AI handle categorization and drafting rather than leaving the final response to the Gemini API.
FAQs and response templates are directly linked to the quality of customer support. Fees, delivery dates, return policies, and contract details must always be verified by a human. Therefore, the operation should treat AI output as "drafts," "candidates," or "requires verification."
Sheet Design | First, prepare two sheets
Inquiry Management Sheet
The sheet name will be Inquiry Management.
Column items: A: Reception Date/Time, B: Inquiry Source, C: Customer Name, D: Inquiry Content, E: Response Content, F: Status, G: AI Classification, H: Summary, I: FAQ Candidate, J: Recommended Department, K: Urgency, L: Draft Response, M: Internal Verification Items, N: AI Processed, O: Processed Date/Time
FAQ Candidate Sheet
The sheet name will be FAQ Candidates.
Column items: A: Creation Date/Time, B: Category, C: Frequently Asked Question, D: Draft Response, E: Supporting Inquiry, F: Internal Verification Items, G: Publication Approval, H: Status, I: Verification Person, J: Last Updated Date
It is important to keep the results categorized by AI separate from the official responses verified by humans. This acts as a guard to prevent AI response drafts from being used directly as official FAQs.
API Key Storage and Basic Settings
Saving the API Key in Script Properties
Avoid writing the API key directly into Apps Script. Manage it using script properties.
javascript
function setProperties() {
PropertiesService.getScriptProperties().setProperties({
GEMINI_API_KEY: 'ここにGemini APIキー',
GEMINI_MODEL: 'gemini-3.5-flash'
});
}Run this function once at the beginning. The model name may change due to updates in the API specifications. If it does not work, check the available model names in Google AI Studio or the official Gemini API documentation.
Implementation for Sending Inquiry Content to the Gemini API
Function to categorize a single inquiry
javascript
function callGeminiForInquiry_(inquiryText, answerText) {
const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
const model = PropertiesService.getScriptProperties().getProperty('GEMINI_MODEL') || 'gemini-3.5-flash';
const url = `https://generativelanguage.googleapis.com/v1beta/models/${model}:generateContent`;
const prompt = `
あなたは企業の問い合わせ対応データを整理する業務アシスタントです。
以下の問い合わせ内容を、Google Sheetsに書き戻しやすいJSON形式で分類してください。
目的：
問い合わせ対応の属人化を減らし、FAQ候補や社内ナレッジとして活用するため。
分類カテゴリ候補：
- 商品について
- 料金について
- 納期について
- 注文・予約について
- 支払いについて
- 返品・交換について
- キャンセルについて
- トラブル対応
- 法人対応
- その他
条件：
- 個人情報は出力しない
- 未確認事項は断定しない
- 回答案は丁寧で簡潔にする
- 社内確認が必要な場合は明記する
- FAQ化に向いているかを判断する
- 料金、納期、返品、契約条件は特に慎重に扱う
問い合わせ内容：
${inquiryText}
既存の回答内容：
${answerText || 'なし'}
`;
const payload = {
contents: [{ parts: [{ text: prompt }] }],
generationConfig: {
responseFormat: {
text: {
mimeType: 'application/json',
schema: {
type: 'object',
properties: {
category: { type: 'string' },
summary: { type: 'string' },
faqCandidate: { type: 'boolean' },
urgency: { type: 'string' },
recommendedDepartment: { type: 'string' },
draftAnswer: { type: 'string' },
confirmationNeeded: { type: 'array', items: { type: 'string' } },
caution: { type: 'string' }
},
required: ['category', 'summary', 'faqCandidate', 'urgency',
'recommendedDepartment', 'draftAnswer', 'confirmationNeeded', 'caution']
}
}
}
}
};
const options = {
method: 'post',
contentType: 'application/json',
headers: { 'x-goog-api-key': apiKey },
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
const response = UrlFetchApp.fetch(url, options);
const code = response.getResponseCode();
const body = response.getContentText();
if (code < 200 || code >= 300) {
throw new Error(`Gemini API error: ${code} ${body}`);
}
const json = JSON.parse(body);
const text = json.candidates?.[0]?.content?.parts?.[0]?.text;
if (!text) throw new Error('Gemini API response does not contain text.');
return JSON.parse(text);
}Using structured output makes writing back to Google Sheets smoother.
Automatically processing uncategorized inquiries
Function to process unprocessed rows in order
javascript
function classifyInquiries() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('問い合わせ管理');
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const row = values[i];
const inquiryText = row[3]; // D列：問い合わせ内容
const answerText = row[4]; // E列：回答内容
const processed = row[13]; // N列：AI処理済み
if (!inquiryText) continue;
if (processed === '済') continue;
try {
const result = callGeminiForInquiry_(inquiryText, answerText);
sheet.getRange(i + 1, 7).setValue(result.category);
sheet.getRange(i + 1, 8).setValue(result.summary);
sheet.getRange(i + 1, 9).setValue(result.faqCandidate ? 'はい' : 'いいえ');
sheet.getRange(i + 1, 10).setValue(result.recommendedDepartment);
sheet.getRange(i + 1, 11).setValue(result.urgency);
sheet.getRange(i + 1, 12).setValue(result.draftAnswer);
sheet.getRange(i + 1, 13).setValue((result.confirmationNeeded || []).join('\n'));
sheet.getRange(i + 1, 14).setValue('済');
sheet.getRange(i + 1, 15).setValue(new Date());
Utilities.sleep(1000);
} catch (error) {
sheet.getRange(i + 1, 14).setValue('エラー');
sheet.getRange(i + 1, 15).setValue(new Date());
logError_('classifyInquiries', error.message, i + 1);
}
}
}The 'AI Processed' column is key. Without this, you would send the same inquiry to the API multiple times. To keep costs and processing time down, be sure to include a mechanism to manage processed items.
Error log design
In API integration, unexpected errors will inevitably occur.
Invalid API key
Model name change
Input data too long
Temporary communication error
API usage limits
Sheet structure change
Do not leave rows where errors occurred as they are; include a mechanism to check them later.
javascript
function logError_(functionName, message, rowNumber) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName('エラーログ');
if (!sheet) {
sheet = ss.insertSheet('エラーログ');
sheet.appendRow(['日時', '関数名', '行番号', 'エラー内容']);
}
sheet.appendRow([new Date(), functionName, rowNumber || '', message]);
}Generating FAQ candidates in bulk
Sending FAQ candidates to the Gemini API in bulk
Process only those inquiries that have already been categorized by AI and have 'Yes' for FAQ candidacy.
javascript
function generateFaqCandidates() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const inquirySheet = ss.getSheetByName('問い合わせ管理');
const faqSheet = ss.getSheetByName('FAQ候補') || ss.insertSheet('FAQ候補');
const values = inquirySheet.getDataRange().getValues();
const targetRows = [];
for (let i = 1; i < values.length; i++) {
const row = values[i];
if (row[13] === '済' && row[8] === 'はい') {
targetRows.push({
rowNumber: i + 1,
category: row[6],
inquiryText: row[3],
answerText: row[4]
});
}
}
if (targetRows.length === 0) {
SpreadsheetApp.getUi().alert('FAQ候補にできる問い合わせがありません。');
return;
}
const result = callGeminiForFaqCandidates_(targetRows);
if (faqSheet.getLastRow() === 0) {
faqSheet.appendRow(['作成日時', 'カテゴリ', 'よくある質問', '回答案', '根拠となる問い合わせ',
'社内確認事項', '公開可否', 'ステータス', '確認担当', '最終更新日']);
}
result.faqs.forEach(function(faq) {
faqSheet.appendRow([
new Date(), faq.category, faq.question, faq.draftAnswer,
(faq.sourceRows || []).join(', '),
(faq.confirmationNeeded || []).join('\n'),
faq.publishability, '下書き', '', ''
]);
});
}The returned FAQ candidates are written to the FAQ Candidates sheet. Initially, they are all set to 'Draft' status.
Executing from the Google Sheets menu
Opening the Apps Script editor every time is cumbersome. We will add a menu to the sheet so that it can be executed like a button.
javascript
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('AI問い合わせ処理')
.addItem('未処理問い合わせをAI分類', 'classifyInquiries')
.addItem('FAQ候補を生成', 'generateFaqCandidates')
.addToUi();
}Since the menu is displayed when opening Google Sheets, operations staff can also execute the process directly from the sheet.
Operational workflow in practice
Step 1 | Accumulate inquiries in Google Sheets
Centralize inquiry management via LINE Official Account, Google Forms, email forwarding, etc.
Step 2 | Exclude and anonymize personal information
Before sending to the AI, remove names, phone numbers, email addresses, addresses, order numbers, etc. What is needed for FAQ generation is not personal information, but the content of the question and the response policy.
Steps 3-7 | From categorization to official FAQ
Execute 'AI Categorize Unprocessed Inquiries' from the menu -> Human verifies categorization results -> Generate FAQ candidates -> Staff reviews draft responses -> Deploy only verified FAQs to websites, LINE, and internal manuals.
By following this flow, inquiry history becomes material for FAQs.
Tips for category design
In inquiry categorization, category design affects accuracy. If you make it too granular from the start, categorization tends to become inconsistent.
It is realistic to start with broader categories first.
text
商品について / 料金について / 納期について / 注文・予約について
支払いについて / 返品・交換について / キャンセルについて
トラブル対応 / 法人対応 / その他For an e-commerce site, add categories like 'Shipping', 'Gift Options', 'Coupons', and 'Receipts'; for B2B services, add 'Document Requests', 'Quote Requests', and 'Security'. Since categories also affect the structure of FAQs and manuals, it is important to set them at a granularity that is easy to utilize later.
Do not publish AI output as-is | Verification flow protects quality
The most important aspect of this system is not to publish FAQ drafts created by AI as they are.
In particular, a person in charge must always verify the following content.
Pricing, delivery times, returns, and cancellation policies
Contract details, legal matters, and terms of service
Content related to medical, financial, or safety matters
Content containing personal information
Warranty details and internal company rules
It is recommended to manage statuses as follows.
text
下書き → 確認中 → 修正必要 → 公開可 / 社内限定 / 非公開In the FAQ candidate sheet, set everything to "Draft" initially, and only change it to "Ready to Publish" after a person in charge has verified it. Without this verification flow, the risk of incorrect FAQs being published remains.
Apps Script Execution Limits and Handling Large Volumes of Data
Apps Script has limitations when processing large volumes of data. While a few dozen items may not be an issue, sending hundreds of items to the API at once may trigger execution time or API limits.
Setting a limit on the number of items processed per execution is a practical approach.
javascript
function classifyInquiriesWithLimit() {
const limit = 30;
let count = 0;
// ... (通常のclassifyInquiriesと同じ処理)
// ループ内に if (count >= limit) break; を追加
}Designing the system to use a processed flag and re-processing error rows later makes operations easier to manage.
Tasks Suited and Not Suited for This System
Suited Tasks
Inquiry management for LINE Official Accounts
Organizing inquiries from Google Forms
Creating FAQs for e-commerce sites
Organizing questions after seminar registration
Converting internal inquiries into FAQs
Creating support manuals for new employee training
The effects are more noticeable once a certain number of inquiries have accumulated. As you reach 50 or 100 entries, it becomes easier to identify common questions and inconsistencies in responses.
Tasks that require caution
Requiring immediate and accurate automated replies
Handling high-risk responses such as legal, medical, or financial matters
Wanting to publish FAQs completely automatically
Needing to process large volumes of data in real-time
For these use cases, you should consider using dedicated inquiry management systems, CRMs, or Cloud Run, rather than relying solely on GAS and the Gemini API.
Always keep operational notes
This type of automation tends to become something only the creator understands. Much like Excel macros, GAS can easily become siloed to a single person.
Leaving operational notes like the following in your sheet or internal documentation will make handovers smoother.
text
【連携名】問い合わせデータAI分類・FAQ候補生成
【目的】Google SheetsにためたデータをGemini APIで分類し、FAQ候補を作成する
【対象シート】問い合わせ管理 / 【出力先シート】FAQ候補
【実行方法】Google Sheetsメニュー「AI問い合わせ処理」から実行
【注意点】AI出力は下書き扱い。正式FAQ化前に人間が確認する。個人情報はAIに送らない
【確認担当】〇〇部 担当者名 / 【最終更新日】2026/05/20Summary | Converting inquiry data into FAQs standardizes response quality
Inquiry support does not end with sending a reply. Each piece of data reflects the anxieties and questions of your customers.
By combining Google Sheets and the Gemini API, you can create a workflow that turns inquiry support into internal knowledge.
Automatically categorize inquiry content
Create drafts for summaries and response suggestions
Identify questions that can be turned into FAQs
Output FAQ candidates to a separate sheet
Have a human review and finalize them as official FAQs
However, the AI only creates drafts. Content related to pricing, delivery times, returns, contracts, terms of service, and personal information must always be reviewed by a responsible person.
Whether you turn them into FAQs, internal manuals, improve product pages, or use them for new employee training, establishing this workflow turns inquiry handling from a simple task into a way to build company knowledge.
Beyond generative AI, build DX that is actually used in the field.
Linking Google Sheets with the Gemini API for inquiry categorization and FAQ candidate output is a practical first step toward preventing customer support from becoming dependent on specific individuals and ensuring knowledge remains within the company.
On my note, I share information about corporate generative AI utilization and DX promotion from a practical perspective.
I summarize how to improve sales, meetings, document creation, Excel tasks, email correspondence, and internal knowledge organization using tools like ChatGPT, Claude, Microsoft Copilot, Gemini, and NotebookLM.
Rather than just introducing AI tools, I share practical application methods that are easy to implement in actual work, focusing on themes like 'AI utilization used in the field,' 'DX that leads to business improvement,' and 'building systems to reduce reliance on specific individuals.'
Please feel free to read my other articles as well.
