Как настроить ежедневное обновление отчёта в Google Таблицах через Apps Script

Ежедневный отчёт часто обновляют одинаково: открывают источник, копируют новые строки, очищают старый хвост и проверяют итог. Если структура стабильна, эту операцию можно вынести в Apps Script и запускать по расписанию. Сначала нужно сделать надёжным сам перенос, а уже затем добавлять триггер: расписание не исправляет ошибку в коде.

Ниже — авторский учебный пример для одной Google-таблицы. На листе «Источник» хранится текущий реестр, на листе «Отчёт» — его обновляемая копия значений. Это не загрузка из CRM и не автоматический пересчёт внешнего сервиса. Если источник сам устарел, скрипт добросовестно скопирует устаревшие данные.

Когда нужен скрипт, а когда хватит формулы

Для постоянно связанного представления диапазона часто проще формула. Скрипт уместен, когда нужно выполнять понятную последовательность: проверить заголовки, выбрать значения, записать их в выделенную область, убрать остаток прежнего отчёта и оставить запись о выполнении. Такое решение удобно разбирать по этапам.

В нашем примере результат не накапливается. Каждый запуск заменяет текущую копию: если сегодня в источнике 12 заказов, отчёт показывает эти 12 заказов, а не добавляет их к вчерашним. Для истории по датам нужен другой алгоритм, ключи записей и отдельное хранилище. Не пытайтесь получить архив простым повторным копированием в один и тот же диапазон.

Подготовьте источник и место вывода

Создайте два листа с точными именами «Источник» и «Отчёт». В A1:D1 источника запишите «Дата», «Заказ», «Статус», «Сумма». Ниже поместите несколько учебных строк. Выделите первые четыре столбца отчёта только под автоматический результат: ручные заметки и формулы размещайте на другом листе. Проверьте, что на листе отчёта достаточно строк для ожидаемого реестра.

Пример работает с вычисленными значениями ячеек: формулы источника не переносятся как формулы. Форматирование, ширина столбцов и оформление отчёта задаются отдельно. Если исходные даты отображаются числами или суммы выглядят непривычно, сначала настройте формат соответствующих столбцов принимающего листа.

Откройте «Расширения → Apps Script» из таблицы. Возьмите идентификатор файла из его адреса: это участок между /d/ и следующим слешем. Подставьте его в переменную BOOK_ID. Идентификатор помогает явно указать документ при запуске по времени, когда пользователь не выбирает активный лист мышью.

Скрипт проверяет данные до записи

В редакторе сохраните следующую функцию. Она читает таблицу одним блоком и записывает результат одним блоком. Проверка размеров сделана намеренно: учебный вариант не расширяет лист автоматически, а останавливается с понятным сообщением.

function refreshReport() {
  const BOOK_ID = 'ВСТАВЬТЕ_ИДЕНТИФИКАТОР_СВОЕЙ_ТАБЛИЦЫ';
  const book = SpreadsheetApp.openById(BOOK_ID);
  const source = book.getSheetByName('Источник');
  const report = book.getSheetByName('Отчёт');
  if (!source || !report) {
    throw new Error('Нужны листы Источник и Отчёт');
  }
  const last = source.getLastRow();
  if (last < 1) throw new Error('Источник пуст');
  const values = source.getRange(1, 1, last, 4).getValues();
  const expected = ['Дата', 'Заказ', 'Статус', 'Сумма'];
  if (values[0].some((v, i) => v !== expected[i])) {
    throw new Error('Не совпадают заголовки A1:D1');
  }
  if (report.getMaxRows() < values.length ||
      report.getMaxColumns() < 4) {
    throw new Error('Расширьте лист Отчёт перед обновлением');
  }
  const oldLast = report.getLastRow();
  report.getRange(1, 1, values.length, 4).setValues(values);
  if (oldLast > values.length) {
    report.getRange(values.length + 1, 1,
                    oldLast - values.length, 4).clearContent();
  }
  console.log(JSON.stringify({
    event: 'report_refreshed',
    dataRows: values.length - 1,
    finishedAt: new Date().toISOString()
  }));
}

Здесь last — последняя заполненная строка листа, а не только первого столбца. Поэтому держите источник отдельным реестром: посторонняя заметка далеко внизу другого столбца увеличит читаемый прямоугольник. Если в источнике останется только заголовок, отчёт получит заголовок без записей; если лист полностью пуст, код завершится ошибкой до изменения отчёта.

Зачем очищать хвост после записи? Допустим, вчера было восемь заказов, сегодня — пять. Перезапись первых шести строк вместе с заголовком не затронет три старые записи ниже. clearContent удаляет их содержимое, сохраняя оформление ячеек. При этом новая запись сначала проходит проверку размеров. В показанном коде запись и очистка — два последовательных вызова: если первый уже завершился, а второй сообщил об ошибке, функция не содержит команды восстановления прежних значений. Проверьте состояние отчёта и причину исключения перед повтором.

Пример рассчитан на доверенный реестр и одного ответственного за обновление. Он не обезвреживает произвольный внешний текст и не координирует одновременно запущенные копии. Не запускайте ручное обновление поверх уже работающего триггера. Для нескольких конкурирующих процессов потребуется отдельная блокировка и продуманная схема сохранения результата.

Сначала ручной запуск и контрольный сценарий

Выберите refreshReport в редакторе и нажмите «Выполнить». При первом запуске подтвердите необходимые разрешения под аккаунтом, которому доступны таблица и проект. Просмотрите запрос доступа, а не подтверждайте его механически: здесь скрипт должен работать с таблицей, а отправка почты или чтение посторонних сервисов в коде отсутствуют.

После выполнения откройте отчёт. Сравните первый и последний заказ, количество строк и сумму нескольких выбранных записей с источником. Затем удалите одну учебную строку из источника и повторите запуск. В отчёте не должно остаться её прежнего хвоста. Ещё один полезный тест — временно переименовать заголовок «Заказ»: функция должна сообщить об ошибке до перезаписи данных. После проверки верните заголовок.

Такие испытания проверяют конкретное поведение примера. Сам по себе статус «Выполнено» означает отсутствие необработанного исключения, но не доказывает правильность бизнес-содержания. Например, перепутанные местами суммы двух заказов код не распознает, если структура диапазона осталась допустимой.

Ежедневный запуск по времени

Когда ручной перенос проверен, откройте в Apps Script раздел «Триггеры», нажмите добавление и выберите функцию refreshReport. Источник события — по времени, периодичность — ежедневно; укажите подходящий интервал. Проверьте часовой пояс проекта в настройках, чтобы утреннее обновление не оказалось вечерним для ваших коллег.

Устанавливаемый триггер выполняется от имени создавшего его пользователя. Если ответственный меняется, недостаточно просто передать коллеге ссылку: нужно организовать доступ и создание расписания под нужной учётной записью. Не создавайте одинаковые ежедневные триггеры у каждого участника команды — получите несколько независимых запусков.

Google не обещает запуск ровно в выбранную минуту: время может немного распределяться внутри интервала. Поэтому отчёт «к 09:00 строго» нельзя обеспечивать одной надеждой на ежедневный триггер на девять часов. Выберите окно с запасом и контролируйте фактическое завершение, если от отчёта зависит следующая операция.

Где видеть ошибки и ограничения

Для ручной отладки используйте журнал выполнения. Для фоновых запусков откройте «Выполнения» и проверьте статус, время и текст исключения. Запись report_refreshed показывает, что функция дошла до конца; dataRows помогает заметить неожиданно пустую выгрузку. Не добавляйте в журнал целые заказы с персональными данными только ради удобства отладки.

Симптом	Что проверить
Ручной запуск работает, расписание ошибается	Кто создал триггер, его доступ, разрешения и выбранная функция
Ошибка заголовков	Точные имена и порядок A1:D1, пробелы в тексте
Данных стало меньше, старые строки остались	Дошёл ли запуск до очистки хвоста; выделена ли область только под отчёт
Превышена квота	Текст исключения, число триггеров и объём работы, а не только размер файла

Apps Script ограничивает длительность отдельных выполнений и суммарную работу триггеров; квоты зависят от типа аккаунта и могут меняться. Поэтому читайте и пишите диапазоны пакетно, не обращайтесь к таблице отдельно для каждой ячейки и не исправляйте превышение лимита бесконечными повторами. При необходимости уменьшите задачу или разделите её с явным контролем обработанных данных.

После изменения кода, требующего новых разрешений, выполните функцию вручную под владельцем триггера и пройдите авторизацию. Фоновый запуск не покажет вам интерактивное окно подтверждения. Устанавливаемые триггеры также могут присылать уведомления об ошибках: используйте их вместе с историей выполнений, а не вместо проверки актуальности самого отчёта.