Автоматическое копирование строк в Google Таблицах по условию

Устали вручную копировать строки из одного листа Google Таблиц в другой, когда они соответствуют определенному условию? Представьте, что вы управляете задачами, и как только статус меняется на ‘Готово’, строка автоматически перемещается в архивный лист. Это не только экономит время, но и значительно снижает вероятность ошибок. В этом руководстве мы покажем вам, как настроить автоматическое копирование строк по условию, используя мощь Google Apps Script. Мы разберем пошаговый процесс, от написания скрипта до настройки триггеров, а также рассмотрим частые ошибки и способы их устранения.

Видеоинструкция

Метод 1: Автоматическое отображение данных с помощью функции QUERY (для динамического просмотра)

Этот метод не копирует строки в буквальном смысле, а динамически отображает на другом листе только те данные, которые соответствуют заданному условию. Это отличный вариант, если вам нужен ‘живой’ отчет или отфильтрованный список без изменения исходных данных.

=QUERY('Исходный лист'!A:Z; "SELECT * WHERE B = 'Готово'"; 1)

Метод 2: Автоматическое копирование и перемещение строк с Google Apps Script (для реальной автоматизации)

Google Apps Script позволяет создавать мощные автоматизации, которые реагируют на изменения в вашей таблице. Мы напишем скрипт, который будет отслеживать изменения и копировать/перемещать строки при выполнении заданного условия.

function copyRowOnCondition(e) {
  // Проверяем, что событие является изменением (onEdit) и имеет диапазон
  if (!e || !e.range) {
    Logger.log('Скрипт запущен не через событие onEdit или отсутствует диапазон.');
    return;
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheetName = 'Исходный лист'; // Имя листа, откуда копируем (например, 'Задачи')
  const targetSheetName = 'Целевой лист';   // Имя листа, куда копируем (например, 'Выполненные задачи')
  const conditionColumnIndex = 2; // Колонка, в которой проверяем условие. B = 2, C = 3 и т.д. (A=1)
  const conditionValue = 'Готово'; // Значение, при котором строка будет скопирована

  const sourceSheet = ss.getSheetByName(sourceSheetName);
  const targetSheet = ss.getSheetByName(targetSheetName);

  if (!sourceSheet || !targetSheet) {
    Logger.log('Ошибка: Один из листов не найден. Проверьте имена листов в скрипте.');
    return;
  }

  // Проверяем, что изменение произошло на исходном листе
  if (e.source.getActiveSheet().getName() !== sourceSheetName) {
    return; // Выходим, если изменение не на нашем листе
  }

  const range = e.range;
  const row = range.getRow();
  const col = range.getColumn();
  const cellValue = range.getValue();

  // Проверяем, что изменение произошло в нужной колонке и значение соответствует условию
  if (col === conditionColumnIndex && cellValue === conditionValue) {
    // Получаем данные всей строки
    const rowData = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn()).getValues()[0];
    targetSheet.appendRow(rowData); // Копируем строку на целевой лист

    // Опционально: Удалить исходную строку после копирования
    // Если вы хотите перемещать, а не только копировать, раскомментируйте следующую строку.
    // sourceSheet.deleteRow(row);
  }
}
    

Частые ошибки / Устранение неполадок

• Скрипт не запускается:
  • Проверьте триггер: Убедитесь, что триггер настроен правильно (функция copyRowOnCondition, тип события При изменении, источник Из таблицы).
  • Проверьте разрешения: Возможно, скрипту не были предоставлены необходимые разрешения. Попробуйте запустить скрипт вручную из редактора (Выполнить > copyRowOnCondition) – это часто вызывает запрос разрешений.
  • Имя функции: Убедитесь, что имя функции в триггере точно совпадает с именем в коде (copyRowOnCondition).
• Копируется не та строка/колонка или не срабатывает условие:
  • Имена листов: Внимательно проверьте переменные sourceSheetName и targetSheetName. Они должны точно совпадать с именами листов в вашей таблице.
  • Индекс колонки: Переменная conditionColumnIndex должна быть числом, соответствующим номеру колонки (A=1, B=2, C=3 и т.д.).
  • Значение условия: Переменная conditionValue должна точно совпадать со значением в ячейке (учитывайте регистр, пробелы).
  • Тип данных: Если условие числовое (например, conditionValue = 100), убедитесь, что в ячейке тоже число, а не текст ‘100’.
• Ошибка ‘Sheet not found’:
  • Это почти всегда означает, что имена листов в скрипте (sourceSheetName, targetSheetName) не совпадают с фактическими именами листов в вашей таблице.
• Скрипт работает слишком медленно или выдает ошибку превышения времени выполнения:
  • Если у вас очень большая таблица или скрипт выполняет много операций, это может быть проблемой. Для оптимизации можно использовать методы пакетной обработки данных (например, getValues() и setValues() для целых диапазонов вместо отдельных ячеек).
  • Если вы используете deleteRow(row), это может быть медленно на больших объемах. Рассмотрите альтернативы, такие как перемещение строк в конец листа или пометка их как ‘архивированных’.
• Бесконечный цикл при удалении строки:
  • Если ваш скрипт удаляет строку (sourceSheet.deleteRow(row);), а триггер При изменении срабатывает на удаление, это может вызвать бесконечный цикл. Чтобы избежать этого, можно добавить дополнительную проверку в скрипт или использовать другой тип триггера, если это возможно. В нашем скрипте это маловероятно, так как мы проверяем e.range и e.col, но стоит иметь в виду.
• Ошибка QUERY GROUP BY в Google Таблицах:
  • Если вы используете функцию QUERY и столкнулись с ошибкой GROUP BY, это обычно связано с неправильным использованием агрегатных функций без соответствующей группировки. Подробное решение этой проблемы вы найдете в нашей статье: Ошибка QUERY GROUP BY в Google Таблицах: как исправить.

Заключение

Автоматическое копирование строк по условию в Google Таблицах значительно упрощает управление данными и повышает вашу продуктивность. Независимо от того, используете ли вы простую функцию QUERY для динамического отображения или мощный Google Apps Script для реального перемещения данных, эти инструменты помогут вам автоматизировать рутинные задачи. Не забывайте регулярно проверять свои скрипты и триггеры, чтобы убедиться в их корректной работе.

Полезные ссылки:
Почему не работает автосохранение в Google Таблицах,
Медиана с условием в Google Sheets: Полное руководство,
Ошибка QUERY GROUP BY в Google Таблицах: как исправить.