google sheet と openAI で英作文

function generateExamples() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange('A2:A'); // A列の単語リストを取得
  const words = range.getValues().flat(); // A列の単語を1D配列に変換
  const apiKey = 'your_key'; // あなたのOpenAI APIキーをここに入力

  // 単語ごとに例文を生成
  words.forEach(function(word, index) {
    if (word) {
      const response = getExampleFromOpenAI(word, apiKey); // ChatGPT APIにリクエスト
      const result = parseResponse(response); // APIのレスポンスから必要なデータを抽出
      sheet.getRange(index + 2, 2).setValue(result.meaning); // B列に意味（和訳）を入力
      sheet.getRange(index + 2, 3).setValue(result.example); // C列に英語の例文を入力
      sheet.getRange(index + 2, 4).setValue(result.japaneseTranslation); // D列に和訳を入力
    }
  });
}

// OpenAI APIを使って、単語の例文と和訳を取得
function getExampleFromOpenAI(word, apiKey) {
  const url = 'https://api.openai.com/v1/chat/completions'; // 新しいAPIエンドポイント

  const messages = [
    {
      role: "system",
      content: "You are a helpful assistant that provides word meanings, example sentences, and Japanese translations."
    },
    {
      role: "user",
      content: `Please provide the following for the English word "${word}":
1. A short Japanese meaning (単語の和訳, e.g., アワビ).
2. An example sentence using the word.
3. A Japanese translation of the sentence.
Return in this format:
Meaning: ...
Example: ...
Japanese Translation: ...`
    }
  ];

  const data = {
    model: 'gpt-3.5-turbo', // 新しいモデル名
    messages: messages,
    max_tokens: 100,
    temperature: 0.7
  };

  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + apiKey
    },
    payload: JSON.stringify(data)
  };

  const response = UrlFetchApp.fetch(url, options); // APIを呼び出して結果を取得
  return JSON.parse(response.getContentText()).choices[0].message.content.trim(); // レスポンスを解析して返す
}

function parseResponse(responseText) {
  Logger.log(responseText);  // ★レスポンスの中身を見る
  const lines = responseText.split('\n');
  const meaning = lines[0]?.replace('Meaning: ', '').trim() || '';
  const example = lines[1]?.replace('Example: ', '').trim() || '';
  const japaneseTranslation = lines[2]?.replace('Japanese Translation: ', '').trim() || '';
  return { meaning, example, japaneseTranslation };
}

<?php
try {

    $dbh = db_connect();
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $statement = $dbh->query("
        SELECT * FROM test
        WHERE (example NOT LIKE CONCAT('%', word, '%') OR example IS NULL OR word IS NULL);
    ");

    $result = $statement->fetchAll();
    $statement = null;
    $dbh = null;
} catch (PDOException $e) {
    header('Content-Type: text/plain; charset=UTF-8', true, 500);
    exit($e->getMessage());
}