OpenAI APIのCreate chat completionを使ってChatGPTと会話するGoogle Apps Scriptのウェブアプリです。事前にOpenAIのAPI Keyを準備してください。
スプレッドシートには、3つのシートConversations、History、Promptsを作っておきます。
-
Conversationsに記録するもの- アクセスした人のメールアドレス
- 会話ID
- 会話タイトル
- 有効か？
-
Historyに記録するもの- 時刻
- アクセスした人のメールアドレス
- 会話ID
- APIへの入力（最新のチャット入力）
- APIへの入力（オプションの値）
- APIからの出力
- prompt_tokens
- completion_tokens
- total_tokens
-
Promptsに記録するもの- アクセスした人のメールアドレス
- プロンプトのJSON文字列
さらに、スプレッドシートの拡張機能からApps Scriptを起動して、スクリプトプロパティのOPENAI_API_KEYにAPI Keyを保存します。そしてHTMLのIndexと、スクリプトのCodeに以下のコードをそれぞれ記述し、ウェブアプリとしてデプロイします。
Index.html
<!DOCTYPE html>
<html>
<head>
<title>ChatGPT with React</title>
<script src="https://unpkg.com/react@17/umd/react.production.min.js"></script>
<script src="https://unpkg.com/react-dom@17/umd/react-dom.production.min.js"></script>
<script src="https://unpkg.com/babel-standalone@6.26.0/babel.min.js"></script>
<script src="https://unpkg.com/react-markdown@4.3.1/umd/react-markdown.js"></script>
<style>
.spinner {
border: 4px solid rgba(0, 0, 0, 0.1);
border-left-color: #3f51b5;
border-radius: 50%;
width: 30px;
height: 30px;
animation: spin 1s linear infinite;
}
@keyframes spin {
to {
transform: rotate(360deg);
}
}
textarea {
resize: none;
}
.app-container {
display: flex;
flex-direction: row;
height: 100%;
}
.conversation-list-container {
flex: 0 0 30%;
overflow-y: auto;
border-right: 1px solid #ccc;
max-height: 100%;
}
.chat-container {
flex: 1;
overflow-y: auto;
padding: 1rem;
max-height: 100%;
}
.selected {
background-color: #e0e0e0;
font-weight: bold;
color: #424242;
}
.conversation-list-container ul {
list-style: none;
padding: 0;
margin: 0;
}
.conversation-list-container li {
position: relative;
text-overflow: ellipsis;
white-space: nowrap;
overflow: hidden;
}
.conversation-list-container li.selected:hover .icons-container {
display: block;
}
.icons-container {
position: absolute;
top: 0;
right: 5px;
display: none;
white-space: nowrap;
}
.icon {
cursor: pointer;
margin-left: 5px;
}
.prompt-item {
position: relative;
cursor: pointer;
padding: 10px;
border-bottom: 1px solid #eee;
}
.prompt-item:hover {
background-color: #f5f5f5;
}
.prompt-list-wrapper {
position: fixed;
top: 50%;
left: 50%;
transform: translate(-50%, -50%);
background-color: white;
border: 1px solid #ccc;
border-radius: 4px;
padding: 20px;
z-index: 1000;
min-width: 240px;
}
.close-button {
position: absolute;
top: 5px;
right: 10px;
background: none;
border: none;
font-size: 20px;
cursor: pointer;
}
.delete-icon {
position: absolute;
top: 0;
right: 0;
display: none;
}
.prompt-item:hover .delete-icon {
display: block;
}
</style>
</head>
<body>
<div id="root"></div>
<script type="text/babel">
class ResizableTextarea extends React.Component {
constructor(props) {
super(props);
this.textareaRef = React.createRef();
}
handleChange = (event) => {
this.props.onChange(event);
this.handleInput(event);
};
handleInput = (event) => {
const textarea = this.textareaRef.current;
textarea.style.height = "auto";
textarea.style.height = textarea.scrollHeight + "px";
};
render() {
const { name, value, onKeyPress } = this.props;
return (
<textarea
ref={this.textareaRef}
name={name}
value={value}
onChange={this.handleChange}
onKeyPress={onKeyPress}
rows="3"
style={{ width: "100%" }}
/>
);
}
}
class ConversationList extends React.Component {
handleTitleUpdate = (conversationId, currentTitle) => {
const newTitle = prompt("Enter the new title:", currentTitle);
if (newTitle) {
google.script.run.withSuccessHandler(() => {
this.props.onUpdateConversationTitle(conversationId, newTitle);
}).updateConversationTitle(conversationId, newTitle);
}
};
handleConversationDelete = (conversationId) => {
if (window.confirm("Are you sure you want to delete this conversation?")) {
google.script.run.withSuccessHandler(() => {
this.props.onDeleteConversation(conversationId);
}).deleteConversation(conversationId);
}
};
render() {
const { conversations, selectedConversationId } = this.props;
return (
<ul>
{conversations.slice().reverse().map((conversation) => (
<li
key={conversation.id}
onClick={() => this.props.onConversationSelect(conversation.id)}
className={conversation.id === selectedConversationId ? 'selected' : ''}
>
{conversation.title}
<div className="icons-container">
<span
className="icon"
onClick={(e) => {
e.stopPropagation();
this.handleTitleUpdate(conversation.id, conversation.title);
}}
>
✏️
</span>
<span
className="icon"
onClick={(e) => {
e.stopPropagation();
this.handleConversationDelete(conversation.id);
}}
>
🗑️
</span>
</div>
</li>
))}
</ul>
);
}
}
class PromptList extends React.Component {
constructor(props) {
super(props);
this.selectPrompt = this.selectPrompt.bind(this);
}
selectPrompt(prompt) {
this.props.onSelect(prompt);
}
handleClose = () => {
if (this.props.onClose) {
this.props.onClose();
}
};
handleClickOutside = (event) => {
if (this.props.onClose && this.wrapperRef && !this.wrapperRef.contains(event.target)) {
this.props.onClose();
}
};
componentDidMount() {
document.addEventListener('mousedown', this.handleClickOutside);
}
componentWillUnmount() {
document.removeEventListener('mousedown', this.handleClickOutside);
}
setWrapperRef = (node) => {
this.wrapperRef = node;
};
handleDeletePrompt = (index) => {
if (this.props.onDelete) {
this.props.onDelete(index);
}
};
render() {
const { prompts } = this.props;
return (
<div className="prompt-list-wrapper" ref={this.setWrapperRef}>
<button className="close-button" onClick={this.handleClose}>
×
</button>
<div className="prompt-list">
{ prompts.length === 0 ? (
<p>No prompts are saved.</p>
) : (
prompts.map((prompt, index) => (
<div
key={index}
className="prompt-item"
onClick={() => this.selectPrompt(prompt)}
>
<h4>{prompt.description}</h4>
<p>{prompt.text}</p>
<span
className="delete-icon"
onClick={(e) => {
e.stopPropagation();
this.handleDeletePrompt(index);
}}
>
🗑️
</span>
</div>
))
)}
</div>
</div>
);
}
}
class App extends React.Component {
constructor(props) {
super(props);
this.state = {
input: "",
system: "",
temperature: "",
maxTokens: "",
messages: [],
loading: false,
showAdvancedSettings: false,
chatAreaMaxHeight: this.calculateChatAreaMaxHeight(),
maxConversationLength: 10,
conversations: [],
currentConversation: null,
loadingConversation: false,
prompts: [],
showPromptList: false,
};
}
calculateChatAreaMaxHeight = () => {
return window.innerHeight * 0.65;
}
componentDidMount() {
window.addEventListener('resize', this.handleResize);
google.script.run.withSuccessHandler(conversations => {
this.setState({ conversations });
}).fetchConversations();
google.script.run.withSuccessHandler(prompts => {
this.setState({ prompts });
}).fetchPrompts();
}
componentWillUnmount() {
window.removeEventListener('resize', this.handleResize);
}
handleResize = () => {
this.setState({ chatAreaMaxHeight: this.calculateChatAreaMaxHeight() });
};
componentDidUpdate(prevProps, prevState) {
if (this.chatArea) {
this.chatArea.scrollTop = this.chatArea.scrollHeight;
}
const prevConversationId = prevState.currentConversation ? prevState.currentConversation.id : null;
const currentConversationId = this.state.currentConversation ? this.state.currentConversation.id : null;
if (prevConversationId !== currentConversationId) {
// currentConversation が変更された場合、messages を更新
this.setState({ messages: this.state.currentConversation ? this.state.currentConversation.messages : [] });
}
}
handleChange = (event) => {
this.setState({ [event.target.name]: event.target.value });
};
handleKeyPress = (event) => {
if (event.key === "Enter" && !event.shiftKey) {
event.preventDefault();
this.handleSubmit(event);
}
};
handleSubmit = (event) => {
event.preventDefault();
const newMessage = { role: "user", content: this.state.input };
this.setState((prevState) => ({
messages: [...prevState.messages, newMessage],
input: "",
loading: true,
}));
const { input, system, temperature, maxTokens, messages, maxConversationLength } = this.state;
const conversation = [...messages, newMessage].slice(-maxConversationLength);
const conversationId = this.state.currentConversation ? this.state.currentConversation.id : null;
google.script.run
.withSuccessHandler(conv => {
this.setState((prevState) => ({
messages: [...prevState.messages, { role: "assistant", content: conv.content }],
loading: false,
}));
if (!conversationId && conv.id && conv.title) {
this.setState(prevState => ({
conversations: [
...prevState.conversations,
{ id: conv.id, title: conv.title },
],
}));
}
})
.sendMessageToChatGPT(conversationId, conversation, system, temperature, maxTokens);
};
toggleAdvancedSettings = () => {
this.setState((prevState) => ({
showAdvancedSettings: !prevState.showAdvancedSettings,
}));
};
handleConversationSelect = (conversationId) => {
this.setState({ messages: [], loadingConversation: true });
google.script.run.withSuccessHandler((conversation) => {
this.setState({ currentConversation: conversation, messages: conversation.messages, loadingConversation: false });
}).fetchConversationById(conversationId);
};
handleConversationTitleUpdate = (conversationId, newTitle) => {
this.setState((prevState) => ({
conversations: prevState.conversations.map((conversation) =>
conversation.id === conversationId ? { ...conversation, title: newTitle } : conversation
),
}));
};
handleConversationDelete = (conversationId) => {
this.setState((prevState) => ({
conversations: prevState.conversations.filter((conversation) => conversation.id !== conversationId),
}));
if (this.state.currentConversation && this.state.currentConversation.id === conversationId) {
this.setState({ currentConversation: null });
}
};
togglePromptList = () => {
this.setState((prevState) => ({
showPromptList: !prevState.showPromptList,
}));
};
handlePromptSelect = (prompt) => {
this.setState({
input: prompt.text,
showPromptList: false,
});
};
savePrompt = async () => {
const description = prompt("Enter a description for the prompt:");
if (!description) return;
await this.setState((prevState) => ({
prompts: [
...prevState.prompts,
{
text: prevState.input,
description: description,
},
],
}));
console.log(this.state.prompts);
google.script.run.withFailureHandler((err) => {
alert(err);
}).savePrompts(this.state.prompts);
};
handleOpenPromptList = () => {
this.setState({ showPromptList: true });
};
handleClosePromptList = () => {
this.setState({ showPromptList: false });
};
handleDeletePrompt = async (index) => {
await this.setState((prevState) => ({
prompts: prevState.prompts.filter((_, i) => i !== index),
}));
google.script.run.withFailureHandler((err) => {
alert(err);
}).savePrompts(this.state.prompts);
};
render() {
const { input, system, temperature, maxTokens, messages, loading, showAdvancedSettings } = this.state;
return (
<div className="app-container">
<div className="conversation-list-container">
<button onClick={() => this.setState({ currentConversation: null })}>New chat</button>
<ConversationList
conversations={this.state.conversations}
selectedConversationId={this.state.currentConversation ? this.state.currentConversation.id : null}
onConversationSelect={this.handleConversationSelect}
onUpdateConversationTitle={this.handleConversationTitleUpdate}
onDeleteConversation={this.handleConversationDelete}
/>
</div>
<div className="chat-container">
{this.state.loadingConversation ? (
<div className="spinner" />
) : (
<div
ref={(el) => { this.chatArea = el; }}
style={{
maxHeight: `${this.state.chatAreaMaxHeight}px`,
overflowY: 'scroll',
marginBottom: '1em',
}}
>
{messages.map((message, index) => (
<p key={index} style={{ whiteSpace: 'pre-wrap' }}>
<strong>{message.role === "user" ? "User" : "ChatGPT"}:</strong>
<br />
{message.role === "user" ? (
message.content
) : (
<ReactMarkdown>{message.content}</ReactMarkdown>
)}
</p>
))}
{loading && (
<div className="spinner" />
)}
</div>
)}
<form onSubmit={this.handleSubmit}>
<label htmlFor="input">Your message:</label>
<ResizableTextarea
name="input"
value={input}
onChange={this.handleChange}
onKeyPress={this.handleKeyPress}
/>
<button type="submit" disabled={loading}>Send</button>
<button type="button" onClick={this.toggleAdvancedSettings}>
Advanced
</button>
<button type="button" onClick={this.togglePromptList}>Prompts</button>
<button type="button" onClick={this.savePrompt}>Save</button>
{
this.state.showPromptList && (
<PromptList
prompts={this.state.prompts}
onSelect={this.handlePromptSelect}
onClose={this.handleClosePromptList}
onDelete={this.handleDeletePrompt}
/>
)
}
{showAdvancedSettings && (
<div>
<label htmlFor="system">System parameter:</label>
<ResizableTextarea
name="system"
value={system}
onChange={this.handleChange}
/>
<label htmlFor="temperature">Temperature:</label>
<input
type="number"
name="temperature"
value={temperature}
min="0"
max="2"
step="0.01"
onChange={this.handleChange}
/>
<label htmlFor="maxTokens">Max tokens:</label>
<input
type="number"
name="maxTokens"
value={maxTokens}
min="1"
onChange={this.handleChange}
/>
<label htmlFor="maxConversationLength">Max conversation length:</label>
<input
type="number"
name="maxConversationLength"
value={this.state.maxConversationLength}
min="1"
onChange={this.handleChange}
/>
</div>
)}
</form>
</div>
</div>
);
}
}
ReactDOM.render(<App />, document.getElementById("root"));
</script>
</body>
</html>
Code.gs
//const ENV = 'prod';
const ENV = 'dev';
const HISTORY_SHEET_NAME = 'History';
const CONVERSATOINS_SHEET_NAME = 'Conversations';
const PROPMTS_SHEET_NAME = 'Prompts';
function doGet() {
return HtmlService.createHtmlOutputFromFile('Index.html');
}
function fetchRows_(sheetName) {
return SpreadsheetApp.getActive().getSheetByName(sheetName).getDataRange().getValues();
}
function fetchConversations() {
const email = Session.getActiveUser().getEmail();
return fetchRows_(CONVERSATOINS_SHEET_NAME).filter(row => row[0] === email && row[3]).map(row => ({ id: row[1], title: row[2] }));
}
function fetchConversationById(id) {
const row = fetchRows_(CONVERSATOINS_SHEET_NAME).find(row => row[1] === id);
if (!row) throw 'Invalid conversation ID: ' + id;
const title = row[2];
const messages = fetchRows_(HISTORY_SHEET_NAME).filter(row => row[2] === id)
.flatMap(row => ([{ role: 'user', content: row[3] }, { role: 'assistant', content: row[5] }]));
return { id, title, messages };
}
function update_(sheetName, id, columnIndex, value) {
const rowIndex = fetchRows_(sheetName).findIndex(row => row[1] === id);
if (rowIndex === -1) throw 'Invalid conversation ID: ' + id;
SpreadsheetApp.getActive().getSheetByName(sheetName).getRange(rowIndex + 1, columnIndex + 1).setValue(value);
}
function updateConversationTitle(id, title) {
update_(CONVERSATOINS_SHEET_NAME, id, 2, title);
}
function deleteConversation(id) {
update_(CONVERSATOINS_SHEET_NAME, id, 3, false);
}
// https://platform.openai.com/docs/api-reference/chat/create
function chatCompletion(args) {
console.log('Request:', args);
if (!args.messages) throw 'chatCompletion: messages is required.';
const openApiKey = PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');
if (!openApiKey) throw 'chatCompletion: Script Property OPENAI_API_KEY is required.';
args = Object.assign({
model: 'gpt-3.5-turbo',
}, args);
const url = 'https://api.openai.com/v1/chat/completions';
const res = UrlFetchApp.fetch(url, {
method: 'post',
headers: {
'Content-Type': 'application/json',
'Authorization': `Bearer ${openApiKey}`,
},
payload: JSON.stringify(args),
muteHttpExceptions: true
});
const json = JSON.parse(res.getContentText());
console.log('Response:', JSON.stringify(json, null, 2));
return json;
}
function simulatedChatCompletion(args) {
const input = args.messages.slice(-1)[0];
return {
"id": "chatcmpl-123",
"object": "chat.completion",
"created": Math.floor((new Date()).getTime() / 1000),
"choices": [{
"index": 0,
"message": {
"role": "assistant",
"content": `Response of "${input.content}"`,
},
"finish_reason": "stop"
}],
"usage": {
"prompt_tokens": 0,
"completion_tokens": 0,
"total_tokens": 0
}
}
}
function append_(sheetName, row) {
SpreadsheetApp.getActive().getSheetByName(sheetName).appendRow(row);
}
function sendMessageToChatGPT(id, conversation, system, temperature, max_tokens) {
const email = Session.getActiveUser().getEmail();
const input = conversation.slice(-1)[0];
let title = '';
if (!id) {
// 新しい会話IDを作る
id = Utilities.getUuid();
title = input.content.slice(0, 20);
const row = [email, id, title, true];
append_(CONVERSATOINS_SHEET_NAME, row);
}
const args = {
messages: system ? [{ role: 'system', content: system }, ...conversation] : conversation,
}
if (temperature) args.temperature = temperature;
if (max_tokens) args.max_tokens = max_tokens;
const json = (ENV === 'prod') ? chatCompletion(args) : simulatedChatCompletion(args);
const content = json.choices[0].message.content;
const { prompt_tokens, completion_tokens, total_tokens } = json.usage;
const row = [new Date(), email, id, input.content, JSON.stringify({system, temperature, max_tokens}), content, prompt_tokens, completion_tokens, total_tokens];
SpreadsheetApp.getActive().getSheetByName(HISTORY_SHEET_NAME).appendRow(row);
return { id, title, role: 'assistant', content };
}
function savePrompts(prompts) {
const email = Session.getActiveUser().getEmail();
const sheet = SpreadsheetApp.getActive().getSheetByName(PROPMTS_SHEET_NAME);
const rowIndex = sheet.getDataRange().getValues().findIndex(row => row[0] === email);
const str = JSON.stringify(prompts);
if (rowIndex === -1) {
sheet.appendRow([email, str]);
} else {
sheet.getRange(rowIndex + 1, 2).setValue(str);
}
}
function fetchPrompts() {
const email = Session.getActiveUser().getEmail();
const row = fetchRows_(PROPMTS_SHEET_NAME).find(row => row[0] === email);
return row ? JSON.parse(row[1]) : [];
}
function test() {
const conversation = [{ role: 'user', content: 'Hello World!' }];
const json = sendMessageToChatGPT(1, conversation);
console.log(json);
}