[GAS Library] Department Classification Library

📋 Overview

The Department Classification Library is an AI-powered automatic classification tool that runs in the Google Apps Script environment. It uses ChatGPT or Gemini to automatically classify company department names into predefined business segments.

🎯 Key Features

• 🤖 AI-Driven: Uses OpenAI ChatGPT or Google Gemini
• 📊 Google Sheets Integration: Seamless spreadsheet integration
• 🌐 Multi-language Support: Supports Japanese and English department names
• 🛡️ Error Resilient: Robust handling of API quota limits and network errors
• ⚙️ Customizable: Flexible segment definitions
• 📈 Batch Processing: Efficient processing of large datasets

🚀 Quick Start

1. Add Library

Add the library to your Google Apps Script project:

Library ID: [YOUR_LIBRARY_ID_HERE]
Identifier: DeptClassifier

2. Initial Setup

javascript
function setup( ) { 
 DeptClassifier.setupSheets();
}

3. Configuration

Configure the following in the Configuration sheet:

• AI Provider: ChatGPT or Gemini
• Model: gpt-4, gemini-pro, etc.
• API Key: Corresponding API key

4. Run Classification

javascriptfunction classify() {
DeptClassifier.classifyDepartments();
}

‍

📖 Detailed Specifications

‍

Default Segment Definitions

‍

‍

🔧 API Reference

Basic Functions

setupSheets()

Executes initial setup and creates necessary sheets.

javascript

DeptClassifier.setupSheets();

Created Sheets:

• Department Data: Department names and classification results
• Segment Definitions: Classification category definitions
• Configuration: AI API settings

classifyDepartments()

Main classification execution function.

javascript

DeptClassifier.classifyDepartments();

Process:

1. Load department names from Department Data sheet
2. Execute classification using configured AI API
3. Write results to sheet

classifyDepartmentsSafe()

Safe classification execution considering API quota limits.

javascript

DeptClassifier.classifyDepartmentsSafe();

Features:

• Smaller batch size (3 items)
• Longer wait times (10 seconds)
• Intelligent fallback

Advanced Functions

classifyDepartmentsCustom(departments, config)

Execute classification with custom settings.

Parameters:

• departments (Array): Array of department names to classify
• config (Object): Custom configuration object

javascript
const customConfig = {  
aiProvider: 'ChatGPT',  
model: 'gpt-4', 
apiKey: 'your-api-key', 
segments: [    
{ name: 'Sales', description: 
'Sales-related departments'
},    
{ 
name: 'Tech', description: 'Technology-related departments' 
}  
]};
const results = DeptClassifier.classifyDepartmentsCustom(  ['Sales Department', 'Engineering Team'],   customConfig
);

‍

retryFailedClassifications()

Retry failed classifications.

javascript
DeptClassifier.retryFailedClassifications();

‍

manualClassifyDepartments(departmentNames, segmentNames)

Execute manual classification.

javascript
DeptClassifier.manualClassifyDepartments(  
['Partner Sales Division', 'Business Planning Office'],  
['Partner Sales', 'Sales Planning/PMM']
);

‍

📊 Sheet Specifications

‍

🛠️ Usage Examples

Basic Usage Example

javascript// 
1. Setup
function initializeLibrary() {  DeptClassifier.setupSheets();  console.log('Setup completed');}
// 2. Run classification
function runClassification() {  try {    DeptClassifier.classifyDepartments();    console.log('Classification completed');  } catch (error) {    console.error('Error:', error.message);  }}
// 3. Check results
function checkResults() {  // Manually check sheet contents}

‍

Large Dataset Processing

javascript
function processLargeDataset() {  
// Safe classification execution  
DeptClassifier.classifyDepartmentsSafe();    
// Retry failed items  
Utilities.sleep(60000); // Wait 1 minute  
DeptClassifier.retryFailedClassifications();
}

‍

Custom Workflow

javascript
function customWorkflow() { 
// 1. Custom segments for tech companies  
const techSegments = [    
{ name: 'Frontend', description: 'Frontend development' },    
{ name: 'Backend', description: 'Backend development' },   
{ name: 'DevOps', description: 'Infrastructure & operations' },   
{ name: 'Product', description: 'Product management' },    
{ name: 'Sales', description: 'Sales & business development' }  ];   

// 2. Classify with custom configuration  
const departments = [   
'Frontend Development',    
'Backend Engineering',   
'SRE Team',    
'Product Manager',    
'Sales Division'  ];   
const config = {    
aiProvider: 'ChatGPT',    
model: 'gpt-4',    
apiKey: 'your-openai-key',   
segments: techSegments  };    
const results = DeptClassifier.classifyDepartmentsCustom(departments, config); 
console.log('Custom classification results:', results);}

‍

⚠️ Troubleshooting

Common Errors and Solutions

1. Configuration incomplete

Cause: Incomplete Configuration sheet settingsSolution:

javascript

// Check configuration
DeptClassifier.testConfiguration();

2. API quota exceeded (429 error)

Cause: API quota limit exceededSolution:

javascript

// Execute in safe mode
DeptClassifier.classifyDepartmentsSafe();

// Or switch to different API// Change AI Provider in Configuration sheet

3. Invalid response from AI

Cause: Unexpected response from AI APISolution:

javascript

// Execute retry
DeptClassifier.retryFailedClassifications();

// Or manual classification
DeptClassifier.manualClassifyDepartments(
 ['Problematic department name'],
 ['Appropriate segment name']
);

4. Cannot read properties of undefined

Cause: API response structure issuesSolution:

• Check API key
• Verify model name
• Check network connection

Debug Functions

javascript

// Configuration test
function debugConfiguration() {
 const isValid = DeptClassifier.testConfiguration();
 if (!isValid) {
   console.log('Please check configuration');
 }
}

// Small sample test
function debugClassification() {
 try {
   const results = DeptClassifier.testSingleClassification();
   console.log('Test successful:', results);
 } catch (error) {
   console.error('Test failed:', error.message);
 }
}

💰 Cost Estimates

OpenAI API

• GPT-4: ~$0.03-0.06 per 1K tokens
• GPT-4 Turbo: ~$0.01-0.03 per 1K tokens
• GPT-3.5 Turbo: ~$0.001-0.002 per 1K tokens

Google AI API

• Gemini Pro: Free tier available, then ~$0.00025 per 1K tokens

Cost estimate for classifying 100 departments:

• GPT-4: ~$0.50-1.00
• GPT-4 Turbo: ~$0.20-0.50
• GPT-3.5 Turbo: ~$0.05-0.10
• Gemini Pro: ~$0.01-0.05

🔒 Security and Privacy

API Key Management

• Remove API keys before sharing sheets with others
• Use dedicated API keys for production environments
• Regular API key rotation

Data Privacy

• Note that department names are sent externally via API
• Consider anonymizing sensitive organization names beforehand
• Ensure log outputs don't contain confidential information

📈 Performance

Processing Capacity

• Standard Mode: 5 departments/batch, 3-second intervals
• Safe Mode: 3 departments/batch, 10-second intervals
• Custom Mode: Adjustable by configuration

Recommended Settings

• Small scale (~50 departments): Standard mode
• Medium scale (50-200 departments): Safe mode
• Large scale (200+ departments): Batch splitting + safe mode

🤝 Contributing and Support

Bug Reports

Please report bugs via GitHub Issues:

• Execution environment (browser, GAS version)
• Complete error messages
• Steps to reproduce

Feature Requests

Features under consideration:

• Support for more AI providers
• Real-time classification API
• Learning functionality for classification accuracy
• Custom prompt templates

License

MIT License - Commercial use allowed

📚 References

• OpenAI API Documentation
• Google AI Studio
• Google Apps Script Guide

Version: 1.2.0
Last Updated: September 2025