Google Apps Script: Конвертация Excel в Google Таблицы

Что такое Google Apps Script и его возможности

Google Apps Script (GAS) — это облачная платформа для разработки скриптов, основанная на JavaScript, которая позволяет автоматизировать задачи, расширять функциональность и интегрировать различные сервисы Google Workspace, такие как Google Таблицы, Документы, Диск, Gmail и другие. С помощью GAS можно создавать кастомные меню, диалоговые окна, автоматические отчеты, управлять файлами на Диске и многое другое.

Саммари статьи

ChatGPT

Google AI

Grok

Perplexity

Преимущества конвертации Excel в Google Таблицы с использованием Apps Script

Конвертация файлов Excel (.xlsx, .xls) в нативный формат Google Таблиц с помощью Apps Script открывает ряд преимуществ:

• Автоматизация: Возможность настроить автоматическую конвертацию файлов, поступающих на Google Диск, например, по расписанию или при добавлении нового файла в определенную папку.
• Интеграция: Бесшовная интеграция данных из Excel в экосистему Google Workspace для дальнейшей обработки, анализа и совместной работы.
• Обработка данных: Гибкая обработка данных в процессе конвертации – очистка, трансформация, обогащение данных перед записью в Google Таблицу.
• Масштабируемость: Обработка множества файлов без ручного вмешательства.

Необходимые условия и подготовка к работе

Для работы с конвертацией Excel в Google Таблицы с использованием Apps Script потребуется:

• Аккаунт Google.
• Доступ к Google Диску и Google Таблицам.
• Базовое понимание JavaScript и принципов работы Google Apps Script.
• Файлы Excel, которые необходимо конвертировать, доступные на Google Диске или возможность их загрузки.
• Разрешения (scopes) для скрипта на доступ к Google Диску и Google Таблицам.

Основные методы и функции Apps Script для работы с файлами

Использование Drive API для доступа к файлам Excel

Для взаимодействия с файлами на Google Диске используется сервис DriveApp. Он позволяет находить файлы по имени, ID, типу MIME, получать содержимое файла и его метаданные.

/**
 * Находит файл Excel на Google Диске по его ID.
 * @param {string} fileId Идентификатор файла Excel на Google Диске.
 * @return {GoogleAppsScript.Drive.File | null} Объект файла или null, если файл не найден.
 */
function getExcelFileById(fileId: string): GoogleAppsScript.Drive.File | null {
  try {
    const file = DriveApp.getFileById(fileId);
    // Проверяем MIME тип, чтобы убедиться, что это Excel файл
    const mimeType = file.getMimeType();
    if (mimeType === MimeType.MICROSOFT_EXCEL || mimeType === MimeType.MICROSOFT_EXCEL_LEGACY) {
      return file;
    }
    Logger.log(`Файл с ID ${fileId} не является файлом Excel.`);
    return null;
  } catch (e) {
    Logger.log(`Ошибка при получении файла с ID ${fileId}: ${e}`);
    return null;
  }
}

Чтение данных из Excel файла (blob, InputStream)

Получив объект File из DriveApp, можно извлечь его содержимое в виде Blob. Однако Apps Script напрямую не предоставляет встроенных функций для парсинга .xlsx или .xls формата из Blob. Стандартный подход — конвертация файла Excel в Google Таблицу средствами Google Drive API.

Google Drive API (Advanced Service) позволяет инициировать импорт файла Excel в формат Google Таблиц. Для этого необходимо включить Drive API в расширенных сервисах Google Apps Script.

/**
 * Конвертирует файл Excel в Google Таблицу с использованием Drive API v2.
 * Требует включения Drive API в Advanced Google Services.
 * @param {string} excelFileId Идентификатор файла Excel на Google Диске.
 * @param {string} newSheetName Имя для новой Google Таблицы.
 * @return {string | null} ID созданной Google Таблицы или null в случае ошибки.
 */
function convertExcelToGoogleSheet(excelFileId: string, newSheetName: string): string | null {
  try {
    const excelFile = DriveApp.getFileById(excelFileId);
    const resource = {
      title: newSheetName,
      mimeType: MimeType.GOOGLE_SHEETS
    };
    // Используем Drive API (v2) для конвертации
    // Убедитесь, что 'Drive' API добавлен в Расширенные сервисы Google
    const googleSheetFile = Drive.Files.insert(resource, excelFile.getBlob());
    Logger.log(`Файл ${excelFile.getName()} успешно конвертирован в Google Таблицу с ID: ${googleSheetFile.id}`);
    return googleSheetFile.id;
  } catch (e) {
    // Обработка специфических ошибок API
    if (e.message.includes('File not found')) {
      Logger.log(`Ошибка конвертации: Файл Excel с ID ${excelFileId} не найден.`);
    } else if (e.message.includes('Unsupported Output Format')) {
       Logger.log(`Ошибка конвертации: Неподдерживаемый формат для конвертации.`);
    } else {
      Logger.log(`Ошибка конвертации файла с ID ${excelFileId}: ${e}`);
    }
    return null;
  }
}

Создание и запись данных в Google Таблицы

Если конвертация происходит через Drive API, новая Google Таблица создается автоматически. Если же требуется более сложная логика (например, чтение данных из Excel и запись в существующую таблицу или кастомная обработка), то после конвертации можно использовать сервис SpreadsheetApp для доступа к данным созданной таблицы.

/**
 * Получает данные из листа Google Таблицы.
 * @param {string} spreadsheetId ID Google Таблицы.
 * @param {string} sheetName Имя листа.
 * @return {Array<Array<any>> | null} Двумерный массив данных или null в случае ошибки.
 */
function getSheetData(spreadsheetId: string, sheetName: string): any[][] | null {
  try {
    const ss = SpreadsheetApp.openById(spreadsheetId);
    const sheet = ss.getSheetByName(sheetName);
    if (!sheet) {
      Logger.log(`Лист с именем '${sheetName}' не найден в таблице ${spreadsheetId}.`);
      return null;
    }
    return sheet.getDataRange().getValues();
  } catch (e) {
    Logger.log(`Ошибка при чтении данных из таблицы ${spreadsheetId}, лист '${sheetName}': ${e}`);
    return null;
  }
}

Реализация конвертации: пошаговая инструкция

Загрузка Excel файла из Google Drive или локального хранилища

1. Из Google Drive: Используйте DriveApp.getFileById() или DriveApp.getFilesByName() для получения объекта файла Excel.
2. Локально: Прямая загрузка локальных файлов в Apps Script ограничена. Обычно используется HTML Service для создания интерфейса загрузки файла, который затем сохраняется на Google Диск и обрабатывается скриптом.

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

Как упомянуто, прямой парсинг Excel средствами GAS затруднен. Наиболее эффективный метод – использование Drive API для автоматической конвертации всего файла. После конвертации в Google Sheet (convertExcelToGoogleSheet), доступ к данным осуществляется через SpreadsheetApp:

/**
 * Пример обработки данных после конвертации Excel в Google Sheet.
 * @param {string} googleSheetId ID созданной Google Таблицы.
 */
function processConvertedSheet(googleSheetId: string): void {
  try {
    const ss = SpreadsheetApp.openById(googleSheetId);
    const sheets = ss.getSheets();

    sheets.forEach(sheet => {
      const sheetName = sheet.getName();
      Logger.log(`Обработка листа: ${sheetName}`);
      const data = sheet.getDataRange().getValues();
      // Пример: Вывод первых 5 строк данных (если они есть)
      Logger.log(data.slice(0, 5));
      // Здесь можно добавить логику очистки, трансформации данных и т.д.
      // Например, удаление пустых строк или преобразование форматов данных
      const cleanedData = data.filter(row => row.some(cell => cell !== ""));
      // Запись очищенных данных обратно (или в другую таблицу)
      // sheet.clearContents();
      // sheet.getRange(1, 1, cleanedData.length, cleanedData[0].length).setValues(cleanedData);
    });
  } catch (e) {
    Logger.log(`Ошибка обработки данных в таблице ${googleSheetId}: ${e}`);
  }
}

Создание Google Таблицы и запись данных

Метод Drive.Files.insert с указанием mimeType: MimeType.GOOGLE_SHEETS автоматически создает новую Google Таблицу при конвертации.

Форматирование и настройка Google Таблицы (опционально)

После конвертации и получения ID новой Google Таблицы, можно использовать SpreadsheetApp для применения форматирования:

/**
 * Применяет базовое форматирование к листу Google Таблицы.
 * @param {string} spreadsheetId ID Google Таблицы.
 * @param {string} sheetName Имя листа для форматирования.
 */
function formatSheet(spreadsheetId: string, sheetName: string): void {
  try {
    const ss = SpreadsheetApp.openById(spreadsheetId);
    const sheet = ss.getSheetByName(sheetName);
    if (!sheet) return;

    const headerRange = sheet.getRange("A1:" + sheet.getLastColumn() + "1");
    headerRange.setFontWeight("bold");
    headerRange.setBackground("#f0f0f0");

    sheet.autoResizeColumns(1, sheet.getLastColumn());
    // Можно добавить установку фильтров, закрепление строк и т.д.
    // sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).createFilter();
    // sheet.setFrozenRows(1);

    Logger.log(`Форматирование применено к листу '${sheetName}' таблицы ${spreadsheetId}.`);

  } catch (e) {
     Logger.log(`Ошибка форматирования листа '${sheetName}' таблицы ${spreadsheetId}: ${e}`);
  }
}

Примеры кода и продвинутые техники

Пример простого скрипта для конвертации Excel в Google Таблицы

// Включите 'Drive API' в Расширенных сервисах Google (Ресурсы -> Расширенные сервисы Google)

/**
 * Основная функция для конвертации Excel в Google Таблицу.
 */
function mainConversion(): void {
  const EXCEL_FILE_ID: string = 'YOUR_EXCEL_FILE_ID'; // Замените на ID вашего Excel файла
  const NEW_SHEET_NAME: string = 'Конвертированные данные из Excel';

  if (!EXCEL_FILE_ID || EXCEL_FILE_ID === 'YOUR_EXCEL_FILE_ID') {
      Logger.log("Пожалуйста, укажите действительный ID файла Excel в переменной EXCEL_FILE_ID.");
      return;
  }

  // Шаг 1: Конвертация файла
  const newSheetId = convertExcelToGoogleSheet(EXCEL_FILE_ID, NEW_SHEET_NAME);

  if (newSheetId) {
    // Шаг 2: Обработка данных (опционально)
    processConvertedSheet(newSheetId);

    // Шаг 3: Форматирование (опционально)
    // Применим форматирование ко всем листам
    const ss = SpreadsheetApp.openById(newSheetId);
    ss.getSheets().forEach(sheet => {
        formatSheet(newSheetId, sheet.getName());
    });

    Logger.log(`Конвертация завершена. Новая Google Таблица: https://docs.google.com/spreadsheets/d/${newSheetId}/edit`);
  } else {
    Logger.log('Конвертация не удалась.');
  }
}

// --- Вспомогательные функции (convertExcelToGoogleSheet, processConvertedSheet, formatSheet) --- 
// Добавьте сюда код функций, определенных ранее

/**
 * Конвертирует файл Excel в Google Таблицу с использованием Drive API v2.
 * @param {string} excelFileId Идентификатор файла Excel.
 * @param {string} newSheetName Имя для новой Google Таблицы.
 * @return {string | null} ID созданной Google Таблицы или null.
 */
function convertExcelToGoogleSheet(excelFileId: string, newSheetName: string): string | null {
  // ... (код функции см. выше)
}

/**
 * Пример обработки данных после конвертации Excel в Google Sheet.
 * @param {string} googleSheetId ID созданной Google Таблицы.
 */
function processConvertedSheet(googleSheetId: string): void {
  // ... (код функции см. выше)
}

/**
 * Применяет базовое форматирование к листу Google Таблицы.
 * @param {string} spreadsheetId ID Google Таблицы.
 * @param {string} sheetName Имя листа.
 */
function formatSheet(spreadsheetId: string, sheetName: string): void {
  // ... (код функции см. выше)
}

Обработка больших файлов и оптимизация производительности

• Drive API: Использование Drive API для конвертации является наиболее эффективным способом для больших файлов, так как обработка происходит на стороне серверов Google.
• Пакетная обработка: При чтении/записи данных после конвертации используйте getValues() и setValues() для работы с целыми диапазонами вместо поэлементного доступа (getValue()/setValue()), чтобы минимизировать количество вызовов API.
• Время выполнения: Помните об ограничениях времени выполнения скриптов (6 минут для обычных аккаунтов, 30 минут для Workspace). Для очень долгих операций может потребоваться разбиение задачи или использование триггеров.

Автоматизация конвертации с использованием триггеров

Apps Script позволяет автоматизировать запуск скриптов с помощью триггеров:

• Триггеры по времени (Time-driven): Запуск скрипта по расписанию (например, каждый час, каждый день).
• Триггеры на события Drive (Event-driven): Запуск скрипта при добавлении нового файла в определенную папку на Google Диске (требует установки триггера через ScriptApp.newTrigger() или вручную в редакторе скриптов).

/**
 * Создает триггер, срабатывающий при добавлении файла в папку.
 * ВНИМАНИЕ: Этот тип триггера (onChange) может требовать установки вручную
 * или более сложных подходов для мониторинга конкретной папки.
 * Проще использовать триггер по времени для сканирования папки.
 */
function createFolderScanTrigger(): void {
  const FOLDER_ID = 'YOUR_FOLDER_ID'; // ID папки для мониторинга

  // Триггер по времени, запускающий сканирование папки каждые 15 минут
  ScriptApp.newTrigger('scanFolderForExcelFiles')
    .timeBased()
    .everyMinutes(15)
    .create();
  Logger.log('Триггер сканирования папки создан.');
}

/**
 * Сканирует указанную папку на наличие новых Excel файлов для конвертации.
 */
function scanFolderForExcelFiles(): void {
  const FOLDER_ID = 'YOUR_FOLDER_ID'; // ID папки для мониторинга
  const PROCESSED_FOLDER_ID = 'PROCESSED_FILES_FOLDER_ID'; // ID папки для перемещения обработанных файлов
  const TARGET_FOLDER_ID = 'TARGET_GOOGLE_SHEETS_FOLDER_ID'; // ID папки для сохранения Google Таблиц

  try {
    const folder = DriveApp.getFolderById(FOLDER_ID);
    const processedFolder = DriveApp.getFolderById(PROCESSED_FOLDER_ID);
    const targetFolder = DriveApp.getFolderById(TARGET_FOLDER_ID);

    const excelFiles = folder.getFilesByType(MimeType.MICROSOFT_EXCEL);
    const excelLegacyFiles = folder.getFilesByType(MimeType.MICROSOFT_EXCEL_LEGACY);

    while (excelFiles.hasNext()) {
      const file = excelFiles.next();
      processAndMoveFile(file, targetFolder, processedFolder);
    }
    while (excelLegacyFiles.hasNext()) {
        const file = excelLegacyFiles.next();
        processAndMoveFile(file, targetFolder, processedFolder);
    }

  } catch (e) {
    Logger.log(`Ошибка сканирования папки ${FOLDER_ID}: ${e}`);
  }
}

/**
 * Конвертирует файл и перемещает исходный и результирующий файлы.
 * @param {GoogleAppsScript.Drive.File} file Файл Excel для обработки.
 * @param {GoogleAppsScript.Drive.Folder} targetFolder Папка для сохранения Google Таблицы.
 * @param {GoogleAppsScript.Drive.Folder} processedFolder Папка для перемещения обработанного Excel.
 */
function processAndMoveFile(file: GoogleAppsScript.Drive.File, targetFolder: GoogleAppsScript.Drive.Folder, processedFolder: GoogleAppsScript.Drive.Folder): void {
    const fileName = file.getName();
    const newSheetName = fileName.replace(/\.xlsx?$/i, '') + ' (Converted)'; // Удаляем расширение

    Logger.log(`Начинается конвертация файла: ${fileName}`);
    const newSheetId = convertExcelToGoogleSheet(file.getId(), newSheetName);

    if (newSheetId) {
        // Перемещаем созданную Google Таблицу в целевую папку
        DriveApp.getFileById(newSheetId).moveTo(targetFolder);
        Logger.log(`Google Таблица ${newSheetName} перемещена в папку ${targetFolder.getName()}.`);

        // Перемещаем исходный Excel файл в папку обработанных
        file.moveTo(processedFolder);
        Logger.log(`Исходный файл ${fileName} перемещен в папку ${processedFolder.getName()}.`);
    } else {
        Logger.log(`Не удалось конвертировать файл: ${fileName}`);
        // Можно добавить логику для перемещения неотконвертированных файлов в папку ошибок
    }
}

Решение проблем и часто задаваемые вопросы

Ошибки при доступе к файлам и способы их устранения

• Exception: You do not have permission to call DriveApp.getFileById.: Убедитесь, что скрипт имеет необходимые разрешения (scopes) для доступа к Google Диску. При первом запуске или после добавления новых сервисов (как DriveApp или Drive API) Apps Script запросит авторизацию. Проверьте манифест проекта (appsscript.json) на наличие правильных OAuth scopes (https://www.googleapis.com/auth/drive).
• Файл не найден: Проверьте правильность ID файла. Убедитесь, что файл существует и доступен аккаунту, от имени которого запускается скрипт.

Проблемы с кодировкой и их решение

При использовании Drive API для конвертации, Google автоматически обрабатывает кодировки. Проблемы могут возникнуть, если вы пытаетесь читать содержимое Excel файла как текст (blob.getDataAsString()), что не рекомендуется для бинарных форматов. Придерживайтесь метода конвертации через Drive API.

Ограничения Google Apps Script и как их обходить

• Время выполнения: Как упоминалось, существуют лимиты времени выполнения. Для длительных операций используйте триггеры или разбейте задачу на части, сохраняя состояние между запусками (например, с помощью PropertiesService).
• Квоты API: Google API имеют дневные квоты на количество вызовов. Отслеживайте использование API в Google Cloud Platform Console, связанной с вашим скриптом. Оптимизируйте код для минимизации вызовов (пакетная обработка).
• Размер файла: Хотя Drive API эффективно справляется с конвертацией, очень большие или сложные Excel файлы могут вызвать ошибки или превысить лимиты. Тестируйте на реальных данных.

FAQ: Ответы на часто задаваемые вопросы по конвертации

1. Можно ли конвертировать только определенные листы из Excel?
   • Стандартная конвертация через Drive API конвертирует весь файл. Для выборочной конвертации листов нужно сначала конвертировать весь файл, а затем скопировать нужные листы в новую (или существующую) Google Таблицу с помощью SpreadsheetApp.
2. Сохраняется ли форматирование Excel при конвертации?
   • Базовое форматирование (шрифты, цвета, размеры ячеек) обычно сохраняется, но сложное форматирование, условное форматирование, сводные таблицы и макросы Excel не будут перенесены или могут быть интерпретированы некорректно. После конвертации может потребоваться дополнительная настройка форматирования средствами SpreadsheetApp.
3. Как обрабатывать защищенные паролем файлы Excel?
   • Google Apps Script и Drive API не могут напрямую конвертировать файлы Excel, защищенные паролем. Пароль необходимо снять перед загрузкой файла на Диск или перед запуском скрипта конвертации.
4. Что делать, если конвертация через Drive API не работает (ошибки)?
   • Убедитесь, что Drive API включен в расширенных сервисах. Проверьте логи выполнения скрипта на наличие подробных сообщений об ошибках. Убедитесь, что файл Excel не поврежден и соответствует стандартному формату (.xlsx или .xls).
5. Можно ли использовать сторонние библиотеки для парсинга Excel в GAS?
   • Теоретически, можно портировать или использовать JavaScript-библиотеки для парсинга Excel (например, SheetJS) в Apps Script, но это может быть сложно из-за ограничений среды выполнения GAS и необходимости загрузки библиотеки как внешнего ресурса или копирования кода. Использование встроенного Drive API обычно является более надежным и простым решением.