gpt_in_gsheets.js

/**
 * @OnlyCurrentDoc
 *
 * Скрипт для интеграции OpenAI GPT в Google Таблицы.
 *
 * Возможности:
 * - Динамическое создание меню на основе конфигураций в листе "Operations".
 * - Асинхронная пакетная обработка GPT-запросов для ускорения выполнения.
 * - Использование плейсхолдеров {{column_name}} в промпте для подстановки данных из других колонок.
 * - Хранение API ключа в свойствах скрипта для безопасности.
 */

// --- КОНФИГУРАЦИЯ ---
const OPERATIONS_SHEET_NAME = 'Operations';
const MENU_NAME = 'GPT Operations';
const API_KEY_PROPERTY_NAME = 'OPENAI_API_KEY';
const MAX_OPERATIONS_IN_MENU = 20; // Максимальное количество операций в меню
const BATCH_SIZE = 100; // Количество строк для обработки в одном асинхронном пакете
const OPENAI_API_URL = 'https://api.openai.com/v1/chat/completions';

// --- ДИНАМИЧЕСКОЕ СОЗДАНИЕ МЕНЮ ---

/**
 * Стандартный триггер, который выполняется при открытии таблицы.
 * Создает кастомное меню в UI.
 */
function onOpen() {
  createDynamicMenu();
}

/**
 * Создает или обновляет меню "GPT Operations" на основе данных из листа "Operations".
 */
function createDynamicMenu() {
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Удаляем старое меню, чтобы избежать дублирования
  ss.removeMenu(MENU_NAME);

  const menu = ui.createMenu(MENU_NAME);
  const opsSheet = ss.getSheetByName(OPERATIONS_SHEET_NAME);

  if (!opsSheet) {
    menu.addItem('Создать лист "Operations"', 'createOperationsSheet');
    menu.addToUi();
    return;
  }

  const operations = opsSheet.getDataRange().getValues();
  // Пропускаем заголовок (первую строку)
  const opsData = operations.slice(1);

  if (opsData.length === 0) {
    menu.addItem('Нет операций для запуска', 'noop');
    menu.addSeparator();
  } else {
    opsData.forEach((op, index) => {
      if (index < MAX_OPERATIONS_IN_MENU) {
        const opName = op[0]; // 'name' колонка
        if (opName) {
          // ИЗМЕНЕНО: Используем эмодзи вместо слова "Запустить"
          menu.addItem(`🚀 ${opName}`, `runOp${index}`);
        }
      }
    });
    menu.addSeparator();
  }

  menu.addItem('Обновить меню операций', 'createDynamicMenu');
  menu.addToUi();
}

/**
 * Хак для регистрации динамических функций в глобальной области видимости.
 * Это позволяет вызывать mainRunner с разными параметрами из меню.
 */
(function(global) {
  for (let i = 0; i < MAX_OPERATIONS_IN_MENU; i++) {
    global[`runOp${i}`] = function() {
      mainRunner(i);
    };
  }
})(this);

/**
 * Пустая функция-заглушка для неактивных пунктов меню.
 */
function noop() {}

/**
 * Создает лист "Operations" с необходимыми заголовками.
 */
function createOperationsSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.insertSheet(OPERATIONS_SHEET_NAME);
  const headers = [
    'name', 'list', 'output_column', 'start_row', 'end_row', 
    'model', 'temperature', 'prompt'
  ];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
  sheet.setFrozenRows(1);
  SpreadsheetApp.getUi().alert('Лист "Operations" создан. Пожалуйста, заполните его и обновите меню через "GPT Operations" > "Обновить меню операций".');
}


// --- ОСНОВНАЯ ЛОГИКА ---

/**
 * Главная функция, выполняющая операцию с асинхронной пакетной обработкой.
 * @param {number} operationIndex - Индекс операции (строка в листе "Operations", 0-based).
 */
function mainRunner(operationIndex) {
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  try {
    // 1. Получаем параметры операции
    const opsSheet = ss.getSheetByName(OPERATIONS_SHEET_NAME);
    if (!opsSheet) throw new Error(`Лист "${OPERATIONS_SHEET_NAME}" не найден.`);
    
    const opRow = operationIndex + 2; 
    const opParams = opsSheet.getRange(opRow, 1, 1, 8).getValues()[0];
    
    const config = {
      name: opParams[0],
      sheetName: opParams[1],
      outputColName: opParams[2],
      startRow: parseInt(opParams[3], 10),
      endRow: parseInt(opParams[4], 10),
      model: opParams[5] || 'gpt-4o-mini',
      temperature: parseFloat(opParams[6]) || 0,
      promptTemplate: opParams[7]
    };

    if (!config.name || !config.sheetName || !config.outputColName || !config.startRow || !config.endRow || !config.promptTemplate) {
      throw new Error(`Не все обязательные параметры заполнены для операции в строке ${opRow} листа "Operations".`);
    }

    // 2. Подготовка к выполнению
    const targetSheet = ss.getSheetByName(config.sheetName);
    if (!targetSheet) throw new Error(`Целевой лист "${config.sheetName}" не найден.`);

    const headers = targetSheet.getRange(1, 1, 1, targetSheet.getLastColumn()).getValues()[0];
    const headerMap = createHeaderMap(headers);

    const outputColIndex = headerMap[config.outputColName.toLowerCase()];
    if (!outputColIndex) throw new Error(`Колонка вывода "${config.outputColName}" не найдена в листе "${config.sheetName}".`);

    // ИЗМЕНЕНО: Убран alert для подтверждения запуска
    
    const numRowsToProcess = config.endRow - config.startRow + 1;
    if (numRowsToProcess <= 0) {
      ui.alert('Диапазон строк для обработки пуст. Проверьте start_row и end_row.');
      return;
    }

    // Считываем все данные для обработки за один раз
    const allRowsData = targetSheet.getRange(config.startRow, 1, numRowsToProcess, headers.length).getValues();
    const apiKey = PropertiesService.getScriptProperties().getProperty(API_KEY_PROPERTY_NAME);
    if (!apiKey) {
      throw new Error('API ключ OpenAI не найден. Пожалуйста, добавьте его в Свойства скрипта (OPENAI_API_KEY).');
    }

    // 3. Цикл по пакетам (батчам) и асинхронное выполнение
    for (let i = 0; i < allRowsData.length; i += BATCH_SIZE) {
      const batchData = allRowsData.slice(i, i + BATCH_SIZE);
      const currentBatchStartRow = config.startRow + i;

      // Устанавливаем статус "Обработка..." для всего пакета
      const statusValues = Array(batchData.length).fill(['Обработка...']);
      const outputRange = targetSheet.getRange(currentBatchStartRow, outputColIndex, batchData.length, 1);
      outputRange.setValues(statusValues);
      SpreadsheetApp.flush();

      // Формируем массив запросов для UrlFetchApp.fetchAll
      const requests = batchData.map(rowData => {
        const prompt = buildPromptForRow(config.promptTemplate, headerMap, rowData);
        return createOpenAIRequest(prompt, config.model, config.temperature, apiKey);
      });

      // Асинхронно отправляем все запросы в пакете
      const responses = UrlFetchApp.fetchAll(requests);

      // Обрабатываем ответы и формируем массив результатов для записи
      const results = responses.map(response => {
        try {
          return [parseOpenAIResponse(response)];
        } catch (e) {
          return [`ОШИБКА: ${e.message}`];
        }
      });

      // Записываем все результаты пакета за один раз
      outputRange.setValues(results);
      SpreadsheetApp.flush();
    }

    ui.alert(`Операция "${config.name}" завершена!`);

  } catch (e) {
    ui.alert('Произошла ошибка', e.message, ui.ButtonSet.OK);
  }
}

/**
 * Создает карту "имя_колонки в нижнем регистре -> индекс колонки (1-based)".
 * @param {string[]} headers - Массив с именами заголовков.
 * @returns {Object.<string, number>}
 */
function createHeaderMap(headers) {
  const map = {};
  headers.forEach((header, index) => {
    if (header) {
      map[header.toString().toLowerCase()] = index + 1;
    }
  });
  return map;
}

/**
 * Собирает финальный промпт, подставляя значения из ячеек строки.
 * @param {string} template - Шаблон промпта с плейсхолдерами {{column_name}}.
 * @param {Object.<string, number>} headerMap - Карта имен колонок в индексы.
 * @param {any[]} rowData - Массив значений текущей строки.
 * @returns {string} - Готовый к отправке промпт.
 */
function buildPromptForRow(template, headerMap, rowData) {
  return template.replace(/{{(.*?)}}/g, (match, columnName) => {
    const colName = columnName.trim().toLowerCase();
    const colIndex = headerMap[colName];
    if (colIndex) {
      // rowData - 0-based массив, colIndex - 1-based
      return rowData[colIndex - 1];
    }
    // Если колонка не найдена, возвращаем плейсхолдер как есть
    return match; 
  });
}


// --- ВЗАИМОДЕЙСТВИЕ С API ---

/**
 * Создает объект запроса для UrlFetchApp.fetchAll.
 * @param {string} prompt - Полностью сформированный промпт.
 * @param {string} model - Название модели.
 * @param {number} temperature - Температура модели.
 * @param {string} apiKey - API ключ OpenAI.
 * @returns {Object} - Объект запроса.
 */
function createOpenAIRequest(prompt, model, temperature, apiKey) {
  const payload = {
    model: model,
    messages: [{ role: 'user', content: prompt }],
    temperature: temperature,
    max_tokens: 5000
  };

  return {
    url: OPENAI_API_URL,
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + apiKey,
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true, // Важно для обработки ошибок API
  };
}

/**
 * Разбирает ответ от API OpenAI, полученный через UrlFetchApp.fetchAll.
 * @param {HTTPResponse} response - Объект ответа от UrlFetchApp.
 * @returns {string} - Текстовый ответ от GPT.
 * @throws {Error} - Если произошла ошибка API или ответ имеет неверный формат.
 */
function parseOpenAIResponse(response) {
  const responseCode = response.getResponseCode();
  const responseBody = response.getContentText();

  if (responseCode === 200) {
    const data = JSON.parse(responseBody);
    if (data.choices && data.choices.length > 0 && data.choices[0].message) {
      return data.choices[0].message.content.trim();
    } else {
      throw new Error('API вернул неожиданный формат ответа.');
    }
  } else {
    // Попытка извлечь сообщение об ошибке из ответа API
    try {
      const errorData = JSON.parse(responseBody);
      const errorMessage = errorData.error ? errorData.error.message : responseBody;
      throw new Error(`API Error ${responseCode}: ${errorMessage}`);
    } catch (e) {
      // Если тело ответа не JSON, возвращаем как есть
      throw new Error(`API Error ${responseCode}: ${responseBody}`);
    }
  }
}