Как ChatGPT получить доступ к Google Apps Script и таблицам?

/**
 * @typedef {Object} OpenAIMessage
 * @property {string} role - Роль отправителя ('system', 'user', 'assistant').
 * @property {string} content - Содержимое сообщения.
 */

/**
 * @typedef {Object} OpenAIChoice
 * @property {number} index
 * @property {OpenAIMessage} message
 * @property {string | null} finish_reason
 */

/**
 * @typedef {Object} OpenAIUsage
 * @property {number} prompt_tokens
 * @property {number} completion_tokens
 * @property {number} total_tokens
 */

/**
 * @typedef {Object} OpenAIResponse
 * @property {string} id
 * @property {string} object
 * @property {number} created
 * @property {string} model
 * @property {OpenAIChoice[]} choices
 * @property {OpenAIUsage} usage
 * @property {string | null} system_fingerprint
 */

/**
 * Отправляет запрос к API ChatGPT и возвращает ответ.
 *
 * @param {string} prompt Текст запроса для ChatGPT.
 * @param {string} apiKey API-ключ OpenAI.
 * @param {string} [model='gpt-3.5-turbo'] Модель для использования.
 * @param {number} [temperature=0.7] Температура генерации (креативность).
 * @return {string | null} Ответ от ChatGPT или null в случае ошибки.
 */
function callChatGPT(prompt: string, apiKey: string, model: string = 'gpt-3.5-turbo', temperature: number = 0.7): string | null {
  const API_ENDPOINT = 'https://api.openai.com/v1/chat/completions';

  // Рекомендуется хранить ключ в PropertiesService
  // const userProperties = PropertiesService.getUserProperties();
  // const apiKey = userProperties.getProperty('OPENAI_API_KEY');

  if (!apiKey) {
    Logger.log('API ключ OpenAI не найден.');
    return null;
  }

  const payload = {
    model: model,
    messages: [{ role: 'user', content: prompt }],
    temperature: temperature,
  };

  const options: GoogleAppsScript.URL_Fetch.URLFetchRequestOptions = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      Authorization: 'Bearer ' + apiKey,
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true, // Позволяет обрабатывать ошибки HTTP
  };

  try {
    const response = UrlFetchApp.fetch(API_ENDPOINT, options);
    const responseCode = response.getResponseCode();
    const responseBody = response.getContentText();

    if (responseCode === 200) {
      const jsonResponse: OpenAIResponse = JSON.parse(responseBody);
      if (jsonResponse.choices && jsonResponse.choices.length > 0) {
        return jsonResponse.choices[0].message.content.trim();
      } else {
        Logger.log('Ответ API не содержит ожидаемых данных.');
        Logger.log(responseBody);
        return null;
      }
    } else {
      Logger.log(`Ошибка запроса к API ChatGPT: ${responseCode}`);
      Logger.log(responseBody);
      return null;
    }
  } catch (error) {
    Logger.log(`Исключение при вызове API ChatGPT: ${error}`);
    return null;
  }
}

/**
 * Тестовая функция для проверки вызова ChatGPT.
 */
function testChatGPTCall() {
  // !!ВАЖНО: Замените 'ВАШ_API_КЛЮЧ' на ваш реальный ключ или используйте PropertiesService
  const apiKey = 'ВАШ_API_КЛЮЧ';
  const prompt = 'Напиши краткое описание преимуществ использования Google Apps Script для автоматизации задач в Google Sheets.';
  const result = callChatGPT(prompt, apiKey);

  if (result) {
    Logger.log('Ответ ChatGPT:');
    Logger.log(result);
  } else {
    Logger.log('Не удалось получить ответ от ChatGPT.');
  }
}

/**
 * Получает активный лист Google Таблицы.
 *
 * @return {GoogleAppsScript.Spreadsheet.Sheet | null} Объект активного листа или null.
 */
function getActiveSheet(): GoogleAppsScript.Spreadsheet.Sheet | null {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) {
    Logger.log('Не удалось получить активную таблицу.');
    return null;
  }
  return ss.getActiveSheet();
}

/**
 * Получает лист Google Таблицы по имени.
 *
 * @param {string} sheetName Имя листа.
 * @return {GoogleAppsScript.Spreadsheet.Sheet | null} Объект листа или null.
 */
function getSheetByName(sheetName: string): GoogleAppsScript.Spreadsheet.Sheet | null {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) {
    Logger.log('Не удалось получить активную таблицу.');
    return null;
  }
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Лист с именем '${sheetName}' не найден.`);
    return null;
  }
  return sheet;
}

/**
 * Читает данные из указанного диапазона листа.
 *
 * @param {string} sheetName Имя листа.
 * @param {string} rangeA1 Нотация диапазона (например, 'A2:A10').
 * @return {any[][] | null} Двумерный массив со значениями или null.
 */
function readDataFromSheet(sheetName: string, rangeA1: string): any[][] | null {
  const sheet = getSheetByName(sheetName);
  if (!sheet) {
    return null;
  }
  try {
    const range = sheet.getRange(rangeA1);
    return range.getValues();
  } catch (error) {
    Logger.log(`Ошибка при чтении диапазона ${rangeA1} с листа ${sheetName}: ${error}`);
    return null;
  }
}

/**
 * Пример функции, которая читает ключевые слова из столбца А,
 * отправляет их в ChatGPT для анализа и выводит результаты в лог.
 */
function processKeywordsFromSheet() {
  const SHEET_NAME = 'Keywords'; // Укажите имя вашего листа
  const KEYWORD_RANGE = 'A2:A5'; // Диапазон с ключевыми словами
  const API_KEY = 'ВАШ_API_КЛЮЧ'; // Используйте PropertiesService!

  const keywordData = readDataFromSheet(SHEET_NAME, KEYWORD_RANGE);

  if (!keywordData || !API_KEY) {
    Logger.log('Не удалось прочитать данные или отсутствует API ключ.');
    return;
  }

  keywordData.forEach((row, index) => {
    const keyword = row[0];
    if (keyword) {
      const prompt = `Классифицируй намерение пользователя для ключевого слова '${keyword}'. Возможные категории: Информационный, Навигационный, Транзакционный, Коммерческий. Ответь только категорией.`;
      const category = callChatGPT(prompt, API_KEY);

      if (category) {
        Logger.log(`Ключевое слово: ${keyword}, Категория: ${category}`);
        // Здесь можно добавить код для записи category обратно в таблицу
      } else {
        Logger.log(`Не удалось получить категорию для: ${keyword}`);
      }
      // Добавляем небольшую задержку, чтобы не превышать лимиты API
      Utilities.sleep(1000);
    }
  });
}

/**
 * Записывает данные в указанную ячейку листа.
 *
 * @param {string} sheetName Имя листа.
 * @param {string} cellA1 Нотация ячейки (например, 'B2').
 * @param {any} value Значение для записи.
 * @return {boolean} true в случае успеха, false в случае ошибки.
 */
function writeDataToCell(sheetName: string, cellA1: string, value: any): boolean {
  const sheet = getSheetByName(sheetName);
  if (!sheet) {
    return false;
  }
  try {
    const cell = sheet.getRange(cellA1);
    cell.setValue(value);
    return true;
  } catch (error) {
    Logger.log(`Ошибка при записи в ячейку ${cellA1} листа ${sheetName}: ${error}`);
    return false;
  }
}

/**
 * Читает ключевые слова из столбца А, получает категорию от ChatGPT
 * и записывает ее в столбец B.
 */
function processAndWriteKeywords() {
  const SHEET_NAME = 'Keywords'; // Имя листа
  const START_ROW = 2; // Начальная строка данных
  const KEYWORD_COLUMN = 1; // Колонка A
  const CATEGORY_COLUMN = 2; // Колонка B
  const API_KEY = 'ВАШ_API_КЛЮЧ'; // Используйте PropertiesService!

  const sheet = getSheetByName(SHEET_NAME);
  if (!sheet || !API_KEY) {
    Logger.log('Лист не найден или отсутствует API ключ.');
    return;
  }

  const lastRow = sheet.getLastRow();
  if (lastRow  {
    const keyword = row[0];
    if (keyword) {
      const prompt = `Классифицируй намерение пользователя для ключевого слова '${keyword}'. Возможные категории: Информационный, Навигационный, Транзакционный, Коммерческий. Ответь только категорией.`;
      const category = callChatGPT(prompt, API_KEY);
      const targetCell = sheet.getRange(START_ROW + index, CATEGORY_COLUMN);

      if (category) {
        targetCell.setValue(category);
        Logger.log(`Записана категория '${category}' для '${keyword}' в ячейку ${targetCell.getA1Notation()}`);
      } else {
        targetCell.setValue('Ошибка'); // Или оставить пустым
        Logger.log(`Не удалось получить категорию для: ${keyword}`);
      }
      Utilities.sleep(1000); // Пауза между запросами
    }
  });
}