Исправление ошибок #REF в Google Таблицах

Isanes Francois
среда, 17 апреля 2024 г., 13:43:53
Google Apps Script

Понимание проблем с вложениями в Google Таблицах

При работе с Google Sheets распространенной задачей является отправка данных таблицы в виде вложений Excel по электронной почте. Этот процесс можно упростить с помощью Google Apps Script, позволяя пользователям отправлять несколько листов в одном электронном письме. Однако могут возникнуть проблемы, например ошибка #REF, которая обычно указывает на проблему со ссылкой в ​​экспортируемых данных.

Эта проблема часто проявляется, когда Google Sheets использует сложные формулы, такие как QUERY(), которые плохо преобразуются при преобразовании листов в формат Excel. Ошибка нарушает целостность данных во вложении, что представляет собой серьезную проблему для пользователей, использующих этот экспорт для составления отчетов или анализа.

Команда Описание
SpreadsheetApp.getActiveSpreadsheet() Получает активную электронную таблицу, к которой привязан скрипт.
spreadSheet.getSheetByName(sheet).getSheetId() Возвращает уникальный идентификатор листа в электронной таблице.
UrlFetchApp.fetch(url, params) Выполняет запрос по указанному URL-адресу, используя различные параметры для управления HTTP-запросом.
Utilities.sleep(milliseconds) Приостанавливает выполнение сценария на указанное количество миллисекунд, чтобы предотвратить превышение ограничений скорости API.
ScriptApp.getOAuthToken() Извлекает токен OAuth 2.0 для текущего пользователя для аутентификации запросов.
getBlob() Получает данные файла, полученного по URL-адресу, в виде большого двоичного объекта, который используется для прикрепления файлов к электронным письмам.

Объяснение функциональности скрипта

Предоставленный скрипт предназначен для автоматизации процесса отправки нескольких таблиц Google в виде вложений Excel в одном электронном письме. Он начинается с объявления массива имен листов, предназначенных для экспорта. Скрипт извлекает активную электронную таблицу и перебирает массив имен листов, чтобы создать URL-адреса загрузки для каждого листа. Эти URL-адреса специально отформатированы для экспорта листов в файлы Excel. Использование «Utilities.sleep(10000);» Здесь крайне важно ввести задержку между запросами на выборку, помогая управлять нагрузкой на серверы Google и не допускать превышения скриптом ограничений скорости.

Каждый URL-адрес извлекает соответствующий лист в виде большого двоичного объекта, которому затем присваивается имя в соответствии с предопределенным массивом имен файлов. Этот шаг имеет решающее значение, поскольку он преобразует данные из таблиц в формат, подходящий для вложений в электронную почту. После подготовки всех больших файловых объектов сценарий создает объект электронной почты с назначенными получателями, строкой темы и основным сообщением. Большие двоичные объекты прикрепляются к этому электронному письму, которое затем отправляется с помощью MailApp.sendEmail(message);' команда. Эта функция является частью службы MailApp Google Apps Script, позволяющей сценариям отправлять электронные письма, приглашения и уведомления.

Устранение ошибок #REF в Google Sheets при экспорте

Решение для сценариев Google Apps

function sendExcelAttachmentsInOneEmail() {
  var sheets = ['OH INV - B2B', 'OH INV - Acc', 'OH INV - B2C', 'B2B', 'ACC', 'B2C'];
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadSheetId = spreadSheet.getId();
  var urls = sheets.map(sheet => {
    var sheetId = spreadSheet.getSheetByName(sheet).getSheetId();
    return \`https://docs.google.com/spreadsheets/d/${spreadSheetId}/export?format=xlsx&gid=${sheetId}\`;
  });
  var reportName = spreadSheet.getSheetByName('IMEIS').getRange(1, 14).getValue();
  var params = {
    method: 'GET',
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
  };
  var fileNames = ['OH INV - B2B.xlsx', 'OH INV - Acc.xlsx', 'OH INV - B2C.xlsx', 'B2B.xlsx', 'ACC.xlsx', 'B2C.xlsx'];
  var blobs = urls.map((url, index) => {
    Utilities.sleep(10000);  // Delay added to avoid hitting rate limits
    var response = UrlFetchApp.fetch(url, params);
    return response.getBlob().setName(fileNames[index]);
  });
  var message = {
    to: 'email@domain.com',
    cc: 'email@domain.com',
    subject: 'Combined REPORTS - ' + reportName,
    body: "Hi Team,\n\nPlease find attached Reports.\n\nBest Regards!",
    attachments: blobs
  }
  MailApp.sendEmail(message);
}

Расширенная информация о проблемах экспорта в Google Таблицы

Экспорт данных из Google Sheets в форматы Excel с помощью сценариев может выявить основные сложности в управлении данными, особенно при использовании расширенных функций, таких как QUERY(). Ошибка #REF, возникающая при таком экспорте, обычно указывает на неразрешенные ссылки в среде Excel, которых нет в самих Google Таблицах. Это несоответствие часто возникает из-за того, что некоторые функции Google Таблиц, такие как определенные операции QUERY() или пользовательские сценарии, не поддерживаются или ведут себя иначе в Excel.

Эта проблема подчеркивает важность обеспечения совместимости между формулами Google Sheets и обработкой формул и запросов данных в Excel. Разработчикам часто приходится реализовывать дополнительные проверки или альтернативные методы для обеспечения целостности данных при переходе из среды Google в среду Microsoft, особенно при автоматизации таких процессов, как вложение данных электронных таблиц по электронной почте.

  1. Почему при экспорте из Google Таблиц в Excel появляется ошибка #REF?
  2. Ошибка #REF обычно возникает из-за того, что определенные ссылки или формулы в Google Таблицах не распознаются или несовместимы со средой формул Excel.
  3. Как предотвратить превышение ограничений скорости с помощью сценариев Google Apps?
  4. Реализация пауз в скрипте с помощью Utilities.sleep(миллисекунды) может помочь управлять частотой запросов и избежать превышения ограничений скорости Google.
  5. Что делает muteHttpExceptions при вызове выборки URL-адреса?
  6. Это позволяет сценарию продолжать выполнение без выдачи исключения в случае сбоя HTTP-запроса, что полезно для корректного управления ошибками.
  7. Могу ли я настроить имя файла каждого листа при экспорте в Excel?
  8. Да, вы можете задать собственные имена для каждого большого двоичного объекта, преобразованного из листа, прежде чем прикреплять его к электронному письму, как показано в сценарии.
  9. Есть ли способ напрямую экспортировать Google Таблицы в Excel без промежуточных скриптов?
  10. Да, вы можете вручную загрузить Google Sheets в формате Excel прямо из меню «Файл» в Google Sheets, но для автоматизации этого требуется создание сценариев.

Благодаря этому исследованию становится очевидным, что, хотя Google Apps Script предоставляет мощные инструменты для автоматизации и улучшения функциональности Google Таблиц, при взаимодействии с различными платформами, такими как Excel, возникают определенные сложности. Ошибки #REF — распространенная ошибка, особенно при работе со сложными запросами и ссылками на данные, которые плохо транслируются за пределами экосистемы Google. Понимание этих ограничений и планирование их в сценариях может значительно снизить возникновение таких проблем, что приведет к более плавным процессам управления данными.