Двусторонняя синхронизация данных между листами Google Таблиц с фильтрацией по датам

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

Это требует нестандартных решений, так как обычные формулы вроде QUERY() не позволяют редактировать данные. Давайте разберём два подхода, которые помогут организовать такой обмен.

1. Фильтры вместо копирования

Первый вариант подойдёт, если ваша цель — только отображение данных без редактирования. Вместо создания отдельного листа используйте фильтрованные представления (Filter Views). Вот как это работает:

1. Перейдите в меню Данные → Фильтрованные представления → Создать новый фильтр.
2. Настройте фильтр по дате: выберите столбец с датами, укажите «Дата после» сегодняшнего дня и «Дата до» (сегодня + 14 дней).
3. Сохраните фильтр под именем «Следующие 14 дней».

Теперь любой пользователь может активировать этот фильтр через меню данных и видеть только актуальные записи. Преимущество в том, что все изменения вносятся напрямую в основной лист — никаких сложных скриптов.

Фильтрованные представления — это временные настройки. Они не изменяют исходные данные и не требуют копирования информации.

Но если вам нужно именно редактирование через Дашборд, переходим ко второму варианту.

2. Двусторонняя синхронизация через скрипты

Для двустороннего обмена данными понадобится написать скрипты в Google Apps Script. Это платформа для автоматизации задач, встроенная прямо в Таблицы. Не пугайтесь — я объясню каждый шаг максимально подробно.

Шаг 1: Настройка триггеров для новых данных

Когда в форму приходит новый ответ, его нужно автоматически добавить в Дашборд. Для этого:

1. Откройте ваш файл Таблиц и выберите Расширения → Apps Script.
2. Вставьте следующий код:

function onFormSubmit(e) {
  const workingSheet = SpreadsheetApp.getActive().getSheetByName('Рабочий');
  const dashboardSheet = SpreadsheetApp.getActive().getSheetByName('Дашборд');
  // Берём последнюю строку из формы
  const lastRow = workingSheet.getLastRow();
  const newData = workingSheet.getRange(lastRow, 1, 1, workingSheet.getLastColumn()).getValues()[0];
  // Переупорядочиваем столбцы под Дашборд (пример для 8 столбцов)
  const reorderedData = [newData[22], newData[23], newData[0], newData[18], newData[19], newData[16], newData[25], newData[17]];
  dashboardSheet.appendRow(reorderedData);
}

3. Сохраните проект (можно назвать его «Синхронизация»).
4. Нажмите на часы в левом меню (триггеры) → Добавить триггер → Выберите функцию onFormSubmit, источник данных «Из формы», событие «При отправке формы».

Цифры в квадратных скобках (например, newData[22]) — это номера столбцов в листе «Рабочий». Замените их на свои, ориентируясь на структуру вашей таблицы.

Шаг 2: Синхронизация изменений между листами

Теперь нужно, чтобы правки в Дашборде отражались в основном листе. Для этого используем триггер onEdit():

1. В том же скрипте добавьте:

function onEdit(e) {
  const editedSheet = e.range.getSheet().getName();
  // Проверяем, что правка в Дашборде или Рабочем
  if (editedSheet !== 'Дашборд' && editedSheet !== 'Рабочий') return;
  
  const sourceSheet = SpreadsheetApp.getActive().getSheetByName(editedSheet);
  const targetSheet = editedSheet === 'Дашборд' ? 'Рабочий' : 'Дашборд';
  
  const editedRow = e.range.getRow();
  const editedCol = e.range.getColumn();
  const uniqueId = sourceSheet.getRange(editedRow, 3).getValue(); // Пример: ID в столбце C
  
  // Ищем совпадение ID в целевом листе
  const targetData = targetSheet.getDataRange().getValues();
  for (let i = 0; i < targetData.length; i++) {
    if (targetData[i][2] === uniqueId) { // ID в целевом листе тоже в столбце C
      targetSheet.getRange(i+1, editedCol).setValue(e.value);
      break;
    }
  }
}

2. Сохраните изменения.

Важно: для корректной работы скрипта в обеих таблицах должен быть уникальный идентификатор строки (например, timestamp из формы). В коде выше он предполагается в столбце C — измените это значение, если у вас иная структура.

Шаг 3: Проверка и отладка

После настройки протестируйте систему:

1. Добавьте тестовую запись через форму — она должна появиться в Дашборде.
2. Измените любое поле в Дашборде — проверьте, обновилось ли оно в Рабочем листе.
3. Если синхронизация не работает:

• Откройте «Вид → Журнал» в Apps Script — там будут видны ошибки.
• Убедитесь, что триггеры установлены корректно (часы в левом меню скрипта).

Полезные заметки и подводные камни

• Уникальные идентификаторы — основа синхронизации. Если в ваших данных нет уникального значения (например, времени отправки формы), добавьте его вручную через скрипт.
• Скорость работы: при большом количестве строк скрипт может тормозить. В таком случае добавьте строку SpreadsheetApp.flush() после изменений — это принудительно сохранит данные.
• Безопасность: если Дашбордом будут пользоваться люди без доступа к Рабочему листу, опубликуйте его как отдельный файл через «Файл → Опубликовать в интернете», выбрав только нужный лист.

Пример таблицы с настройками можно посмотреть здесь (обратите внимание на вкладку «Скрипты»).

Если что-то пошло не так — смело пишите в комментарии. Чаще всего проблемы возникают из-за расхождений в номерах столбцов или отсутствия триггеров. Но с пошаговой проверкой вы точно справитесь!