Как получить именованный диапазон в Google Sheets с помощью Apps Script?

/**
 * Получает объект Range по его имени.
 *
 * @param {string} rangeName Имя искомого диапазона.
 * @returns {SpreadsheetApp.Range | null} Объект диапазона или null, если не найден.
 */
function getNamedRangeObject(rangeName: string): GoogleAppsScript.Spreadsheet.Range | null {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const namedRange = ss.getRangeByName(rangeName);
  
  if (!namedRange) {
    Logger.log(`Именованный диапазон с именем '${rangeName}' не найден.`);
    return null;
  } 
  
  Logger.log(`Найден именованный диапазон '${rangeName}' на листе '${namedRange.getSheet().getName()}' по адресу ${namedRange.getA1Notation()}`);
  return namedRange;
}

/**
 * Получает и логирует значения из именованного диапазона.
 *
 * @param {string} rangeName Имя диапазона (например, 'campaign_keywords').
 */
function logValuesFromNamedRange(rangeName: string): void {
  const namedRange = getNamedRangeObject(rangeName); // Используем предыдущую функцию

  if (namedRange) {
    // Получаем двумерный массив значений
    const values: any[][] = namedRange.getValues();
    
    // Фильтруем пустые строки, если необходимо
    const keywords: string[] = values
                                .flat() // Делаем массив одномерным
                                .filter(keyword => typeof keyword === 'string' && keyword.trim() !== ''); 
                                
    if (keywords.length > 0) {
      Logger.log(`Ключевые слова из диапазона '${rangeName}':`);
      keywords.forEach((keyword, index) => {
        Logger.log(`${index + 1}. ${keyword}`);
      });
    } else {
      Logger.log(`Диапазон '${rangeName}' не содержит данных.`);
    }
  } 
  // Сообщение об ошибке логируется внутри getNamedRangeObject
}

// Пример вызова:
// logValuesFromNamedRange('campaign_keywords');

function safeGetRangeValues(rangeName: string): any[][] | null {
  try {
    /** @type {SpreadsheetApp.Spreadsheet} */
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const range = ss.getRangeByName(rangeName);

    if (range) {
      return range.getValues();
    } else {
      console.error(`Именованный диапазон '${rangeName}' не найден.`);
      // Можно выбросить ошибку или вернуть null/пустой массив, 
      // в зависимости от логики приложения.
      return null; 
    }
  } catch (e) {
    console.error(`Ошибка при получении диапазона '${rangeName}': ${e}`);
    return null;
  }
}

/**
 * Возвращает массив всех именованных диапазонов в активной таблице.
 *
 * @returns {GoogleAppsScript.Spreadsheet.NamedRange[]} Массив объектов NamedRange.
 */
function getAllNamedRanges(): GoogleAppsScript.Spreadsheet.NamedRange[] {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const namedRanges: GoogleAppsScript.Spreadsheet.NamedRange[] = ss.getNamedRanges();
  
  Logger.log(`Найдено ${namedRanges.length} именованных диапазонов в таблице.`);
  return namedRanges;
}

/**
 * Логирует имя и адрес каждого именованного диапазона в таблице.
 */
function logAllNamedRangeDetails(): void {
  const namedRanges = getAllNamedRanges();

  if (namedRanges.length === 0) {
    Logger.log('В этой таблице нет именованных диапазонов.');
    return;
  }

  namedRanges.forEach(namedRange => {
    try {
      const range = namedRange.getRange();
      const name = namedRange.getName();
      const sheetName = range.getSheet().getName();
      const a1Notation = range.getA1Notation();
      Logger.log(`- Имя: ${name}, Лист: ${sheetName}, Адрес: ${a1Notation}`);
    } catch (e) {
      // Ошибка может возникнуть, если диапазон ссылается на удаленный лист
      Logger.log(`Ошибка обработки диапазона '${namedRange.getName()}': ${e}`);
    }
  });
}

/**
 * Находит именованные диапазоны, имена которых начинаются с заданного префикса.
 *
 * @param {string} prefix Префикс для поиска.
 * @returns {GoogleAppsScript.Spreadsheet.NamedRange[]} Отфильтрованный массив NamedRange.
 */
function filterNamedRangesByPrefix(prefix: string): GoogleAppsScript.Spreadsheet.NamedRange[] {
  const allRanges = getAllNamedRanges();
  const filteredRanges = allRanges.filter(namedRange => {
    return namedRange.getName().startsWith(prefix);
  });
  
  Logger.log(`Найдено ${filteredRanges.length} диапазонов с префиксом '${prefix}'.`);
  return filteredRanges;
}

// Пример: получить все диапазоны с конфигурационными параметрами
// const configRanges = filterNamedRangesByPrefix('config_');

/**
 * Читает конфигурационные параметры из диапазона 'settings'.
 * Предполагается, что диапазон состоит из двух столбцов: Ключ | Значение.
 *
 * @returns {Record | null} Объект с настройками или null при ошибке.
 */
function readConfiguration(): Record | null {
  const configRange = getNamedRangeObject('settings');
  if (!configRange) {
    return null;
  }

  const values: any[][] = configRange.getValues();
  const config: Record = {};

  values.forEach(row => {
    // Проверяем, что в строке есть ключ (первый столбец) и он не пустой
    if (row.length > 0 && row[0] !== '') {
      config[row[0]] = row.length > 1 ? row[1] : null; // Берем значение из второго столбца или null
    }
  });

  Logger.log('Конфигурация успешно прочитана:');
  Logger.log(JSON.stringify(config, null, 2));
  return config;
}

/**
 * Обновляет статус обработки в именованном диапазоне 'processing_status'.
 *
 * @param {string} status Новый статус (например, 'Completed', 'Error').
 * @param {string} timestamp Временная метка.
 */
function updateProcessingStatus(status: string, timestamp: string): void {
  const statusRange = getNamedRangeObject('processing_status');
  if (!statusRange) {
    return; // Ошибка уже залогирована в getNamedRangeObject
  }

  // Предполагаем, что диапазон 'processing_status' - это одна ячейка
  // или мы хотим записать в верхнюю левую ячейку.
  try {
    statusRange.setValue(`Статус: ${status} (${timestamp})`);
    Logger.log(`Статус в диапазоне 'processing_status' обновлен.`);
  } catch (e) {
    Logger.log(`Не удалось обновить статус: ${e}`);
  }
}

// Пример вызова:
// const now = new Date().toLocaleString('ru-RU');
// updateProcessingStatus('Completed', now);

/**
 * Переназначает именованный диапазон на новый адрес.
 *
 * @param {string} rangeName Имя диапазона для обновления.
 * @param {string} newA1Notation Новый адрес в формате A1 (например, 'Sheet2!B2:D10').
 * @returns {boolean} true в случае успеха, false при ошибке.
 */
function reassignNamedRange(rangeName: string, newA1Notation: string): boolean {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const namedRange = ss.getRangeByName(rangeName);
  
  try {
    // Получаем новый диапазон по A1 нотации
    const newRange = ss.getRange(newA1Notation);
    
    if (namedRange) {
      // Если диапазон с таким именем уже существует, удаляем его
      ss.removeNamedRange(rangeName);
      Logger.log(`Существующий диапазон '${rangeName}' удален.`);
    }
    
    // Создаем новый именованный диапазон
    ss.setNamedRange(rangeName, newRange);
    Logger.log(`Именованный диапазон '${rangeName}' успешно переназначен на ${newA1Notation}.`);
    return true;

  } catch (e) {
    Logger.log(`Ошибка при переназначении диапазона '${rangeName}': ${e}. Возможно, некорректный адрес '${newA1Notation}'.`);
    return false;
  }
}

// Пример:
// reassignNamedRange('report_data', 'Отчет Q3!A1:G100');

/**
 * Получает данные из именованного диапазона, используя кеш.
 *
 * @param {string} rangeName Имя диапазона.
 * @param {number} expirationSeconds Время жизни кеша в секундах (например, 3600 для 1 часа).
 * @returns {any[][] | null} Данные из диапазона или null при ошибке.
 */
function getCachedRangeValues(rangeName: string, expirationSeconds: number = 3600): any[][] | null {
  const cache = CacheService.getScriptCache();
  const cacheKey = `named_range_${rangeName}_values`;
  
  const cachedData = cache.get(cacheKey);
  if (cachedData) {
    Logger.log(`Данные для '${rangeName}' получены из кеша.`);
    return JSON.parse(cachedData);
  }
  
  Logger.log(`Данные для '${rangeName}' не найдены в кеше, читаем из таблицы.`);
  const range = getNamedRangeObject(rangeName);
  if (range) {
    const values = range.getValues();
    // Кешируем данные
    cache.put(cacheKey, JSON.stringify(values), expirationSeconds);
    return values;
  } else {
    return null; // Диапазон не найден
  }
}

/**
 * Создает или обновляет именованный диапазон для данных отчета.
 *
 * @param {string} reportSheetName Имя листа с отчетом.
 * @param {string} reportName Имя отчета (используется для имени диапазона).
 */
function setupReportNamedRange(reportSheetName: string, reportName: string): void {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(reportSheetName);

  if (!sheet) {
    Logger.log(`Лист '${reportSheetName}' не найден.`);
    return;
  }

  // Определяем диапазон данных на листе (например, все заполненные ячейки)
  const dataRange = sheet.getDataRange(); 
  const rangeName = `report_${reportName}_data`;
  
  try {
    // Удаляем старый диапазон, если он есть (используем reassign)
    reassignNamedRange(rangeName, dataRange.getA1Notation());
    Logger.log(`Именованный диапазон '${rangeName}' создан/обновлен для листа '${reportSheetName}'.`);
  } catch (e) {
     Logger.log(`Ошибка при создании/обновлении именованного диапазона '${rangeName}': ${e}`);
  }
}

// Пример:
// setupReportNamedRange('Отчет Продаж Q4', 'sales_q4');