Google Apps ScriptとReactでChatGPTと会話するウェブアプリ

OpenAI APIの[Create chat completion](https://platform.openai.com/docs/api-reference/chat/create)を使って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`に以下のコードをそれぞれ記述し、ウェブアプリとしてデプロイします。

```html: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}>
                &times;
              </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>
```

```javascript: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);
}
```