Google Spreadsheet から ChatGPT を呼び出す。

code.gs

/** @OnlyCurrentDoc */

/**
 * APIキーをユーザープロパティに設定します。
 * 安全性のため、APIキーがすでに存在する場合は上書きしません。
 */
function setAPIKey() {
  const ui = SpreadsheetApp.getUi();
  const existingKey = PropertiesService.getUserProperties().getProperty('OpenAI-APIKEY');
  if (existingKey) {
    ui.alert('APIキーはすでに設定されています。再設定するときは一度削除してください。');
    return;
  }

  const apikey = ui.prompt('APIキーを入力してください。', ui.ButtonSet.OK).getResponseText();

  if (apikey) {
    PropertiesService.getUserProperties().setProperty('OpenAI-APIKEY', apikey);
    ui.alert('APIキーが保存されました。');
  }
}

/**
 * 保存されたAPIキーを表示します。
 * APIキーが存在する場合はその値を、存在しない場合はアラートを表示します。
 */
function showAPIKey() {
  const apikey = PropertiesService.getUserProperties().getProperty('OpenAI-APIKEY') || '';
  const ui = SpreadsheetApp.getUi();
  if (apikey) {
    ui.alert(`現在のAPIキーは「${apikey}」です。`);
  } else {
    ui.alert('APIキーが存在しません。');
  }
}


/**
 * 保存されたAPIキーを削除します。
 */
function deleteAPIKey() {
  const userProperties = PropertiesService.getUserProperties();

  const response = SpreadsheetApp.getUi().alert(
    'API KEYを削除してもよろしいですか？', 
    SpreadsheetApp.getUi().ButtonSet.YES_NO
  );
  if (response == SpreadsheetApp.getUi().Button.YES) {
    userProperties.deleteProperty('OpenAI-APIKEY');
    SpreadsheetApp.getUi().alert('API KEYを削除しました。');
  }
}


/**
 * チャットログの処理を行う関数。
 * アクティブなセルの範囲を拡大して、ログを整形し、GPT-3のAPIに送信して応答を反映する。
 * 応答はログの下に新たなセル範囲を作成して展開される。
 */
function chat() {

  // 空文字でないものを改行区切りで結合し、最後に改行を加えて返す
  const joinContent = (contentValues) => contentValues.filter(content => content !== '').join('\n') + '\n';

  // keywordsの中から、keywordから始まる最初の要素を探し、なければdefaultKeywordを返す
  const expandKeyword = (keywords, keyword, defaultKeyword) => {
    if (!keyword) {
      return defaultKeyword;
    }
    const targetKeyword = keywords.find(candidate => candidate.startsWith(keyword));
    return targetKeyword || keyword;
  };

  // OpenAIのChat GPT APIを呼び出す
  const callChatGPTAPI= (messages) => {
    const url = 'https://api.openai.com/v1/chat/completions';
    const apikey = PropertiesService.getUserProperties().getProperty('OpenAI-APIKEY');
    const options = {
      'method': 'post',
      'headers': {
        'Authorization': `Bearer ${apikey}`,
        'Content-Type': 'application/json'
      },
      'payload': JSON.stringify({
        'model': 'gpt-3.5-turbo',
        'messages': messages
      })
    };
    const response = UrlFetchApp.fetch(url, options);
    const {usage, choices} = JSON.parse(response.getContentText());
    return [usage, choices[0]['message']];
  };


  // 上下左右に指定した数だけ範囲を拡大する
  const expandRange = (range, top, bottom, left, right) => {
    const sheet = range.getSheet();
    const row = range.getRow() - top;
    const col = range.getColumn() - left;
    const numRows = range.getNumRows() + top + bottom;
    const numCols = range.getNumColumns() + left + right;
    return sheet.getRange(row, col, numRows, numCols);
  };

// 範囲を、データが含まれている範囲に拡大したものを返す
const getExpandedRange = (range) => {
  const countData = (range) => range.getValues().flat().filter(v => v !== "").length;
  const isDataCountChanged = (range, top, bottom, left, right) => countData(expandRange(range, top, bottom, left, right)) != countData(range);

  if (range.getRow() > 1 && isDataCountChanged(range, 1, 0, 0, 0)) {
    return getExpandedRange(expandRange(range, 1, 0, 0, 0));
  }
  if (range.getLastRow() < range.getSheet().getLastRow() && isDataCountChanged(range, 0, 1, 0, 0)){
    return getExpandedRange(expandRange(range, 0, 1, 0, 0));
  }
  if (range.getColumn() > 1 && isDataCountChanged(range, 0, 0, 1, 0)) {
    return getExpandedRange(expandRange(range, 0, 0, 1, 0));
  }
  if (range.getLastColumn() < range.getSheet().getLastColumn() && isDataCountChanged(range, 0, 0, 0, 1)) {
    return getExpandedRange(expandRange(range, 0, 0, 0, 1));
  }
  return range;
};

  const roles = ['system', 'user', 'assistant'];
  const sheet = SpreadsheetApp.getActiveSheet();
  const activeRange = sheet.getActiveRange();
  const expandedRange = getExpandedRange(activeRange);

  // 範囲を選択して明示
  expandedRange.activate();
  expandedRange.setHorizontalAlignment('left')
    .setVerticalAlignment('top')
    .setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
  const values = expandedRange.getValues();

  if (values.length < 1 || values[0].length < 2 || !values[0][0]) {
    return;
  }

  let role = '';
  let content = '';
  let messagesToSend = [];

  values.forEach(rowvalues => {
    const [firstValue, ...contentValues] = rowvalues;

    rowvalues[0] = expandKeyword(roles, firstValue, role);

    if (roles.includes(rowvalues[0])) {
      if (role === rowvalues[0]) {
        content += joinContent(contentValues);
      } else {
        if (role && roles.includes(role)) {
          messagesToSend.push({ role, content });
        }
        role = rowvalues[0];
        content = joinContent(contentValues);
      }
    }
  });

  if (role && roles.includes(role)) {
    messagesToSend.push({ role, content });
  }

  expandedRange.setValues(values);

  const [useage, receivedMessage] = callChatGPTAPI(messagesToSend);

  const contentChunks = receivedMessage['content'].split(/^(```.*$)/m);
  const outputValues = contentChunks.map(chunk => [receivedMessage['role'], chunk]);
  outputValues.push(['トークン使用量', useage]);

  const outputRange = expandedRange.offset(expandedRange.getNumRows(), 0, outputValues.length, outputValues[0].length);
  outputRange.setValues(outputValues);
  outputRange.activate();
  outputRange.setHorizontalAlignment('left')
    .setVerticalAlignment('top')
    .setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
}


/** 
 * スプレッドシートを開いたときに表示されるメニューに ChatGPT を追加し、各種機能を表示するための関数
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('ChatGPT')
    .addItem('API KEY設定', 'setAPIKey')
    .addItem('API KEY表示', 'showAPIKey')
    .addItem('API KEY削除', 'deleteAPIKey')
    .addSeparator()
    .addItem('Chat', 'chat')
    .addToUi();
}