Google Apps Script: Как прочитать данные из файла Excel?

  •   
  •   19257
  •   7 мин
  •   58

Google Apps Script (GAS) предоставляет мощные возможности для автоматизации задач в экосистеме Google Workspace. Часто возникает необходимость обрабатывать данные, хранящиеся в традиционных Excel-файлах (.xlsx, .xls). GAS позволяет эффективно решать эту задачу, интегрируя данные Excel в рабочие процессы Google Sheets, Docs, Drive и других сервисов.

Зачем использовать Google Apps Script для обработки Excel-файлов?

Использование GAS для работы с Excel-файлами оправдано в следующих сценариях:

  • Автоматизация: Запуск скриптов по расписанию или триггерам для регулярного импорта и обработки данных из Excel.
  • Интеграция: Бесшовная передача данных из Excel в Google Sheets для дальнейшего анализа, визуализации или совместной работы.
  • Централизация: Обработка данных из различных источников, включая Excel, в единой среде Google Workspace.
  • Кастомная логика: Реализация сложной бизнес-логики обработки данных, недоступной стандартными средствами импорта Google Sheets.

Предварительные требования: Настройка окружения и доступ к Google Sheets API

Для работы с Excel-файлами через Google Apps Script необходимо:

  1. Аккаунт Google: Доступ к Google Drive и Google Sheets.
  2. Проект Google Apps Script: Созданный скрипт, привязанный к Google Sheet или автономный.
  3. Разрешения: Скрипту потребуются разрешения на доступ к Google Drive (для чтения файлов) и Google Sheets (для записи и чтения данных).

Обзор различных способов чтения данных из Excel

Существует два основных подхода к чтению данных из Excel-файлов с помощью GAS:

  1. Конвертация в Google Sheets: Загрузка Excel-файла на Google Drive и его преобразование в формат Google Sheets. Затем данные читаются стандартными методами SpreadsheetApp.
  2. Прямое чтение (Advanced): Получение файла как объекта Blob и использование сторонних библиотек JavaScript (например, SheetJS/js-xlsx) для парсинга файла непосредственно в скрипте. Этот метод сложнее, но не требует предварительной конвертации.

Способ 1: Импорт Excel-файла в Google Sheets и чтение данных

Этот метод является наиболее простым и рекомендуемым для большинства задач, так как использует нативные возможности Google Workspace.

Загрузка Excel-файла в Google Drive

Перед обработкой файл Excel должен быть загружен на ваш Google Drive. Это можно сделать вручную или программно с помощью DriveApp.

Создание Google Sheet на основе загруженного файла

GAS позволяет конвертировать загруженный Excel-файл в Google Sheet. Это ключевой шаг для данного метода.

/**
 * Конвертирует Excel-файл в Google Sheet.
 *
 * @param {string} fileId Идентификатор Excel-файла на Google Drive.
 * @returns {GoogleAppsScript.Spreadsheet.Spreadsheet | null} Созданный Google Sheet или null в случае ошибки.
 */
function convertExcelToGoogleSheet(fileId: string): GoogleAppsScript.Spreadsheet.Spreadsheet | null {
  try {
    const excelFile = DriveApp.getFileById(fileId);
    const fileName = excelFile.getName();
    const blob = excelFile.getBlob();

    // Опции для конвертации
    const resource = {
      title: fileName.replace(/\.(xlsx|xls)$/, ""), // Убираем расширение Excel
      mimeType: MimeType.GOOGLE_SHEETS
    };

    // Вызов Drive API v2 для конвертации
    // Примечание: Требуется включить Drive API в 'Сервисы' редактора скриптов.
    const newSheetFile = Drive.Files?.insert(resource, blob, { convert: true });

    if (!newSheetFile || !newSheetFile.id) {
       Logger.log('Не удалось конвертировать файл ID: ' + fileId);
       return null;
    }

    Logger.log(`Файл ${fileName} (ID: ${fileId}) успешно конвертирован в Google Sheet (ID: ${newSheetFile.id})`);
    return SpreadsheetApp.openById(newSheetFile.id);

  } catch (e: any) {
    Logger.log(`Ошибка при конвертации файла ID ${fileId}: ${e.message}`);
    return null;
  }
}

Использование SpreadsheetApp для доступа к данным листа

После конвертации файла в Google Sheet, вы можете использовать стандартный сервис SpreadsheetApp для доступа к его содержимому.

/**
 * Пример получения данных из первого листа сконвертированного файла.
 *
 * @param {string} excelFileId Идентификатор исходного Excel-файла на Google Drive.
 */
function readDataFromConvertedSheet(excelFileId: string): void {
  const spreadsheet = convertExcelToGoogleSheet(excelFileId);

  if (spreadsheet) {
    // Получаем первый лист
    const sheet: GoogleAppsScript.Spreadsheet.Sheet = spreadsheet.getSheets()[0];
    if (!sheet) {
      Logger.log('В сконвертированной таблице нет листов.');
      return;
    }
    Logger.log(`Читаем данные из листа: ${sheet.getName()}`);

    // Получаем все данные листа
    const data: any[][] = sheet.getDataRange().getValues();

    // Пример обработки: Вывод первых 5 строк (если они есть)
    for (let i = 0; i < Math.min(data.length, 5); i++) {
      Logger.log(`Строка ${i + 1}: ${data[i].join(', ')}`);
    }

    // Дальнейшая обработка данных...
    // Например, анализ данных рекламной кампании
    // processCampaignData(data);

    // Важно: Удаление временной таблицы после обработки (если она не нужна)
    // DriveApp.getFileById(spreadsheet.getId()).setTrashed(true);
  }
}

Чтение данных из ячеек, строк и столбцов

Сервис SpreadsheetApp и объект Sheet предоставляют методы для гибкого чтения данных:

  • getDataRange().getValues(): Получить все данные листа в виде двумерного массива.
  • getRange(row, column).getValue(): Получить значение одной ячейки.
  • getRange(row, column, numRows, numColumns).getValues(): Получить значения диапазона ячеек.
  • getLastRow(), getLastColumn(): Получить размеры области данных.

Способ 2: Использование Blob для чтения Excel-файла напрямую (Advanced)

Этот метод позволяет читать данные из Excel-файла без предварительной конвертации в Google Sheets. Он требует использования внешних JavaScript-библиотек, так как GAS не имеет встроенных функций для парсинга бинарного формата .xlsx.

Получение файла Excel в виде Blob

Сначала необходимо получить содержимое файла в виде объекта Blob.

/**
 * Получает файл Excel с Google Drive в виде Blob.
 *
 * @param {string} fileId Идентификатор файла Excel.
 * @returns {GoogleAppsScript.Base.Blob | null} Blob файла или null при ошибке.
 */
function getExcelFileAsBlob(fileId: string): GoogleAppsScript.Base.Blob | null {
  try {
    const file = DriveApp.getFileById(fileId);
    // Проверка MIME-типа (опционально, но рекомендуется)
    const mimeType = file.getMimeType();
    if (mimeType === MimeType.MICROSOFT_EXCEL || mimeType === MimeType.MICROSOFT_EXCEL_LEGACY) {
      return file.getBlob();
    }
     else {
      Logger.log(`Файл ID ${fileId} не является файлом Excel. MIME-тип: ${mimeType}`);
      return null;
    }
  } catch (e: any) {
    Logger.log(`Ошибка получения файла ID ${fileId} как Blob: ${e.message}`);
    return null;
  }
}

Использование внешних библиотек для парсинга Excel (например, SheetJS)

Поскольку GAS не может напрямую парсить .xlsx, необходимо использовать сторонние библиотеки. Популярным выбором является SheetJS (js-xlsx). Её нужно адаптировать для использования в среде GAS:

  1. Скачать xlsx.full.min.js с сайта SheetJS.
  2. Скопировать код библиотеки в файл .gs вашего проекта (или использовать продвинутые методы загрузки через HtmlService или внешние CDN, что менее надежно в GAS).

Внимание: Интеграция и поддержка внешних библиотек в GAS может быть нетривиальной задачей и требует внимательного тестирования.

Пример кода: Чтение данных с использованием SheetJS и Blob

Примечание: Этот пример предполагает, что код библиотеки SheetJS (XLSX) доступен в глобальной области видимости скрипта.

declare const XLSX: any; // Объявление для TypeScript, что XLSX существует

/**
 * Читает данные из Excel Blob с использованием SheetJS.
 *
 * @param {GoogleAppsScript.Base.Blob} excelBlob Blob Excel-файла.
 * @returns {object | null} Объект с данными листов или null при ошибке.
 */
function readExcelBlobWithSheetJS(excelBlob: GoogleAppsScript.Base.Blob): object | null {
  try {
    // Проверка наличия XLSX (SheetJS)
    if (typeof XLSX === 'undefined') {
      Logger.log('Библиотека SheetJS (XLSX) не найдена в окружении скрипта.');
      return null;
    }

    // Получаем бинарные данные из Blob
    const bytes = excelBlob.getBytes();
    // Читаем книгу Excel
    const workbook = XLSX.read(bytes, { type: 'array' });

    const result: { [key: string]: any[][] } = {};

    // Обрабатываем каждый лист
    workbook.SheetNames.forEach((sheetName: string) => {
      const worksheet = workbook.Sheets[sheetName];
      // Конвертируем лист в массив массивов (JSON)
      const sheetData: any[][] = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
      result[sheetName] = sheetData;
      Logger.log(`Прочитан лист '${sheetName}', строк: ${sheetData.length}`);
    });

    return result;

  } catch (e: any) {
    Logger.log(`Ошибка парсинга Excel Blob с помощью SheetJS: ${e.message}`);
    return null;
  }
}

/**
 * Основная функция для демонстрации чтения через Blob.
 *
 * @param {string} excelFileId ID файла Excel на Google Drive.
 */
function processExcelDirectly(excelFileId: string): void {
  const blob = getExcelFileAsBlob(excelFileId);
  if (blob) {
    const excelData = readExcelBlobWithSheetJS(blob);
    if (excelData) {
      // Пример: Вывод данных из первого листа
      const firstSheetName = Object.keys(excelData)[0];
      if (firstSheetName) {
        const sheetContent = excelData[firstSheetName];
        Logger.log(`Данные из листа '${firstSheetName}':`);
        // Вывод первых 5 строк
        for (let i = 0; i < Math.min(sheetContent.length, 5); i++) {
          Logger.log(sheetContent[i].join(', '));
        }
        // Дальнейшая обработка...
      }
    }
  }
}

Обработка ошибок и исключений при парсинге

Прямой парсинг более подвержен ошибкам:

  • Несовместимый формат: Библиотека может не поддерживать определенные версии или функции Excel.
  • Поврежденный файл: Ошибки при чтении структуры файла.
  • Ошибки библиотеки: Баги в самой библиотеке парсинга.

Необходимо использовать блоки try...catch для перехвата исключений и логирования ошибок, чтобы диагностировать проблемы.

Практические примеры и сценарии использования

Автоматизация импорта данных из Excel-отчетов

Часто маркетинговые или финансовые отчеты поступают в формате Excel. GAS может автоматически:

  1. Проверять папку на Google Drive на наличие новых Excel-файлов.
  2. Конвертировать их в Google Sheets (Способ 1).
  3. Извлекать ключевые метрики (например, расходы на рекламу, CTR, конверсии).
  4. Записывать данные в основную аналитическую таблицу Google Sheets.

Создание пользовательских функций для анализа данных Excel

Можно создать кастомную функцию в Google Sheets, которая принимает ID Excel-файла, читает его (любым из способов) и возвращает результат анализа.

/**
 * Пользовательская функция для подсчета строк в Excel-файле.
 *
 * @param {string} fileId ID Excel-файла на Google Drive.
 * @return {number | string} Количество строк данных или сообщение об ошибке.
 * @customfunction
 */
function COUNTEXCELROWS(fileId: string): number | string {
  if (!fileId) {
    return 'Укажите ID файла Excel.';
  }
  // Используем Способ 1 для простоты в пользовательской функции
  const spreadsheet = convertExcelToGoogleSheet(fileId);
  if (spreadsheet) {
    const sheet = spreadsheet.getSheets()[0];
    if (sheet) {
      const rowCount = sheet.getLastRow();
      // Удаляем временную таблицу
      try { DriveApp.getFileById(spreadsheet.getId()).setTrashed(true); } catch(e){} 
      return rowCount;
    } else {
       try { DriveApp.getFileById(spreadsheet.getId()).setTrashed(true); } catch(e){} 
      return 'Ошибка: В файле нет листов.';
    }
  } else {
    return 'Ошибка: Не удалось прочитать файл.';
  }
}

Интеграция с другими сервисами Google (Docs, Calendar, Gmail)

Данные, извлеченные из Excel, можно использовать для:

  • Создания отчетов: Формирование Google Docs с результатами анализа.
  • Планирования: Создание событий в Google Calendar на основе дат из Excel.
  • Уведомлений: Отправка писем через Gmail с ключевыми показателями или предупреждениями.

Заключение и рекомендации

Сравнение различных подходов и выбор оптимального

  • Способ 1 (Конвертация в Sheets):

    • Плюсы: Простота реализации, надежность, использование нативных API Google.
    • Минусы: Требует создания временного файла Google Sheet, может быть медленнее для очень больших файлов, возможно искажение сложного форматирования при конвертации.
    • Рекомендация: Использовать в большинстве случаев, особенно для регулярной автоматизации и когда не требуется сохранение исходного форматирования Excel.

  • Способ 2 (Прямое чтение Blob):

    • Плюсы: Не создает дополнительных файлов, потенциально быстрее для чтения без записи, лучше сохраняет оригинальную структуру (при использовании правильной библиотеки).
    • Минусы: Требует интеграции и поддержки сторонних библиотек, сложнее в реализации и отладке, зависимость от внешнего кода.
    • Рекомендация: Использовать, когда критически важно избежать создания Google Sheet, при работе с очень большими файлами, где конвертация становится узким местом, или когда требуется доступ к специфическим метаданным Excel, не переносящимся при конвертации.

Советы по оптимизации производительности

  • Минимизируйте вызовы API: Читайте данные большими блоками (getDataRange().getValues()) вместо чтения по ячейкам.
  • Используйте Drive API v2/v3: Для операций с файлами (как в примере конвертации) он часто эффективнее DriveApp.
  • Очищайте временные файлы: Если используете Способ 1, не забывайте удалять созданные Google Sheets после обработки (setTrashed(true)), если они больше не нужны.
  • Обработка больших файлов: Для очень больших файлов рассмотрите возможность их разделения или обработки частями, чтобы избежать превышения лимитов времени выполнения GAS.
  • Кэширование: Используйте CacheService для временного хранения данных, если один и тот же файл обрабатывается многократно.

Полезные ресурсы и ссылки для дальнейшего изучения

  • Официальная документация Google Apps Script: Сервисы SpreadsheetApp, DriveApp, Drive API.
  • Документация библиотеки SheetJS (если выбран Способ 2).
  • Форумы сообщества Google Apps Script.

Выбор метода чтения Excel-файлов в Google Apps Script зависит от конкретных требований задачи, сложности данных и предпочтений разработчика. Оба подхода имеют свои преимущества и могут быть эффективно использованы для автоматизации и интеграции данных в экосистеме Google Workspace.

Метки:

Добавить комментарий

Google Apps Script для Google Docs: как автоматизировать работу с документами?
Введение в Google Apps Script для Google Docs Что такое Google Apps Script и зачем он нужен для Google Docs Google Apps Script (GAS) — это облачный язык сценариев, основанный на JavaScript, который позволяет автоматизировать задачи и расширять функциональность приложений Google Workspace, включая Google Docs.
  •  9
  •   2025
Как отправить письмо из Google Apps Script?
Что такое Google Apps Script и для чего он нужен Google Apps Script (GAS) — это облачный язык сценариев, позволяющий автоматизировать задачи в Google Workspace, такие как Gmail, Sheets, Docs и другие.
  •  10
  •   2025
Ограничение размера файла в Google Apps Script: все, что вам нужно знать
Google Apps Script (GAS) — мощная платформа для автоматизации и расширения функциональности сервисов Google Workspace.
  •  11
  •   2025
Google Apps Script: Как Развернуть Скрипт как Веб-Приложение?
Что такое веб-приложение на Google Apps Script? Веб-приложение на Google Apps Script — это скрипт, который можно развернуть как самостоятельное веб-приложение, доступное через URL.
  •  11
  •   2025