Фильтрация данных по дате — одна из самых частых задач при работе с таблицами Google Sheets с помощью Google Apps Script. Будь то анализ маркетинговых кампаний, отслеживание финансовых транзакций или управление проектами, возможность извлекать данные за определенные периоды является ключевой.
Зачем фильтровать данные по дате?
- Аналитика и отчетность: Создание отчетов за конкретные дни, недели, месяцы или кварталы.
- Автоматизация процессов: Запуск скриптов для обработки только актуальных данных (например, сегодняшних записей).
- Динамическое отображение: Показ пользователю данных, релевантных текущему моменту или выбранному периоду.
- Уведомления: Оповещение о событиях, срок которых наступил или скоро наступит.
Основные понятия: даты и форматы дат в Google Sheets и Apps Script
Google Sheets хранит даты как числовые значения (количество дней с 30 декабря 1899 года). Google Apps Script работает с датами через стандартный объект JavaScript Date. При получении данных из ячейки таблицы с помощью getValue() или getValues(), Apps Script автоматически преобразует числовое представление даты Sheets в объект Date JavaScript, если формат ячейки распознан как дата. Важно следить за консистентностью форматов дат в таблице.
Предварительная подготовка: подключение к таблице и получение данных
Прежде чем фильтровать, необходимо получить данные из таблицы. Стандартный подход — использование SpreadsheetApp для доступа к таблице и листу, а затем getDataRange().getValues() для извлечения всех данных в виде двумерного массива.
/**
* Получает все данные с указанного листа.
* @param {string} spreadsheetId Идентификатор таблицы.
* @param {string} sheetName Имя листа.
* @returns {any[][]} Двумерный массив данных.
* @throws {Error} Если таблица или лист не найдены.
*/
function getAllData(spreadsheetId: string, sheetName: string): any[][] {
const ss = SpreadsheetApp.openById(spreadsheetId);
if (!ss) {
throw new Error(`Таблица с ID '${spreadsheetId}' не найдена.`);
}
const sheet = ss.getSheetByName(sheetName);
if (!sheet) {
throw new Error(`Лист с именем '${sheetName}' не найден в таблице.`);
}
return sheet.getDataRange().getValues();
}
// Пример использования
// const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID';
// const SHEET_NAME = 'Лист1';
// const allData = getAllData(SPREADSHEET_ID, SHEET_NAME);
// console.log(allData);
Простые методы фильтрации данных по дате
Рассмотрим базовые сценарии фильтрации, предполагая, что дата находится в определенном столбце (например, индекс 0).
Фильтрация данных за определенный день
Для этого сравниваем дату из строки с искомой датой. Важно учитывать, что объект Date включает время. Если нужно сравнить только день, месяц и год, необходимо нормализовать обе даты.
/**
* Нормализует дату, устанавливая время на 00:00:00.000.
* @param {Date} date Объект Date для нормализации.
* @returns {Date} Нормализованный объект Date.
*/
function normalizeDate(date: Date): Date {
const newDate = new Date(date);
newDate.setHours(0, 0, 0, 0);
return newDate;
}
/**
* Фильтрует данные по определенной дате.
* @param {any[][]} data Двумерный массив данных.
* @param {number} dateColumnIndex Индекс столбца с датой.
* @param {Date} targetDate Целевая дата для фильтрации.
* @returns {any[][]} Отфильтрованный массив данных.
*/
function filterBySpecificDay(data: any[][], dateColumnIndex: number, targetDate: Date): any[][] {
const normalizedTargetDate = normalizeDate(targetDate).getTime(); // Сравниваем миллисекунды для эффективности
const header = data.shift(); // Сохраняем заголовок
const filteredData = data.filter(row => {
const cellValue = row[dateColumnIndex];
// Проверяем, является ли значение в ячейке действительным объектом Date
if (cellValue instanceof Date && !isNaN(cellValue.getTime())) {
const rowDate = normalizeDate(cellValue).getTime();
return rowDate === normalizedTargetDate;
}
return false; // Игнорируем строки, где нет корректной даты
});
if (header) {
filteredData.unshift(header); // Возвращаем заголовок
}
return filteredData;
}
// Пример использования
// const today = new Date();
// const dataForToday = filterBySpecificDay(allData, 0, today);
// console.log(dataForToday);
Фильтрация данных в заданном диапазоне дат (от и до)
Аналогично предыдущему пункту, сравниваем дату строки с началом и концом диапазона.
/**
* Фильтрует данные в заданном диапазоне дат (включительно).
* @param {any[][]} data Двумерный массив данных.
* @param {number} dateColumnIndex Индекс столбца с датой.
* @param {Date} startDate Дата начала диапазона.
* @param {Date} endDate Дата конца диапазона.
* @returns {any[][]} Отфильтрованный массив данных.
*/
function filterByDateRange(data: any[][], dateColumnIndex: number, startDate: Date, endDate: Date): any[][] {
const normalizedStartDate = normalizeDate(startDate).getTime();
// Для конечной даты устанавливаем конец дня, чтобы включить весь день
const normalizedEndDate = normalizeDate(new Date(endDate.getFullYear(), endDate.getMonth(), endDate.getDate() + 1)).getTime();
// Альтернативно: установить 23:59:59.999
// const endOfDayEndDate = new Date(endDate);
// endOfDayEndDate.setHours(23, 59, 59, 999);
// const normalizedEndDate = endOfDayEndDate.getTime();
const header = data.shift();
const filteredData = data.filter(row => {
const cellValue = row[dateColumnIndex];
if (cellValue instanceof Date && !isNaN(cellValue.getTime())) {
const rowTime = cellValue.getTime(); // Используем getTime() для сравнения, время учитывается
return rowTime >= normalizedStartDate && rowTime < normalizedEndDate;
}
return false;
});
if (header) {
filteredData.unshift(header);
}
return filteredData;
}
// Пример использования
// const reportStartDate = new Date(2023, 10, 1); // 1 Ноября 2023
// const reportEndDate = new Date(2023, 10, 30); // 30 Ноября 2023
// const novemberData = filterByDateRange(allData, 0, reportStartDate, reportEndDate);
// console.log(novemberData);
Фильтрация данных до определенной даты
/**
* Фильтрует данные до определенной даты (не включительно).
* @param {any[][]} data Двумерный массив данных.
* @param {number} dateColumnIndex Индекс столбца с датой.
* @param {Date} beforeDate Дата, до которой нужно фильтровать.
* @returns {any[][]} Отфильтрованный массив данных.
*/
function filterBeforeDate(data: any[][], dateColumnIndex: number, beforeDate: Date): any[][] {
const normalizedBeforeDate = normalizeDate(beforeDate).getTime();
const header = data.shift();
const filteredData = data.filter(row => {
const cellValue = row[dateColumnIndex];
if (cellValue instanceof Date && !isNaN(cellValue.getTime())) {
const rowTime = cellValue.getTime();
return rowTime < normalizedBeforeDate;
}
return false;
});
if (header) {
filteredData.unshift(header);
}
return filteredData;
}
Фильтрация данных после определенной даты
/**
* Фильтрует данные после определенной даты (включительно).
* @param {any[][]} data Двумерный массив данных.
* @param {number} dateColumnIndex Индекс столбца с датой.
* @param {Date} afterDate Дата, после которой нужно фильтровать.
* @returns {any[][]} Отфильтрованный массив данных.
*/
function filterAfterDate(data: any[][], dateColumnIndex: number, afterDate: Date): any[][] {
const normalizedAfterDate = normalizeDate(afterDate).getTime();
const header = data.shift();
const filteredData = data.filter(row => {
const cellValue = row[dateColumnIndex];
if (cellValue instanceof Date && !isNaN(cellValue.getTime())) {
const rowTime = cellValue.getTime();
return rowTime >= normalizedAfterDate;
}
return false;
});
if (header) {
filteredData.unshift(header);
}
return filteredData;
}
Продвинутая фильтрация с использованием объектов Date и сравнения
Хотя прямое сравнение миллисекунд (getTime()) часто эффективно, работа с объектами Date дает больше гибкости.
Создание объектов Date для точного сравнения
Объекты Date можно создавать с конкретными значениями года, месяца, дня, часов и т.д. Это полезно для задания точных границ фильтрации.
// Создание даты: 15 января 2024, 00:00:00
const specificDate = new Date(2024, 0, 15); // Месяцы в JS начинаются с 0 (Январь = 0)
// Создание даты и времени: 15 января 2024, 14:30:00
const specificDateTime = new Date(2024, 0, 15, 14, 30, 0);
Использование методов setHours(), setMinutes(), setSeconds(), setMilliseconds() для игнорирования времени
Как показано в функции normalizeDate, метод setHours(0, 0, 0, 0) — стандартный способ обнулить время для сравнения только дат. Это критически важно, если в таблице даты могут содержать разное время, а фильтрация нужна по дням.
Обработка различных форматов дат в таблице
Если столбец содержит даты в разных форматах (например, строки ‘ДД.ММ.ГГГГ’ или ‘MM/DD/YYYY’ вместо объектов Date), Apps Script может не преобразовать их автоматически. В таких случаях требуется предварительный парсинг строк в объекты Date перед фильтрацией. Это может существенно замедлить процесс.
/**
* Пытается преобразовать значение в объект Date.
* Поддерживает объекты Date и строки формата 'ДД.ММ.ГГГГ', 'ГГГГ-ММ-ДД'.
* @param {any} value Значение для преобразования.
* @returns {Date | null} Объект Date или null, если преобразование невозможно.
*/
function parseDateValue(value: any): Date | null {
if (value instanceof Date && !isNaN(value.getTime())) {
return value;
}
if (typeof value === 'string') {
try {
// Попытка парсинга 'ДД.ММ.ГГГГ'
let parts = value.match(/^(\d{1,2})\.(\d{1,2})\.(\d{4})$/);
if (parts) {
// new Date(year, monthIndex, day)
const date = new Date(parseInt(parts[3]), parseInt(parts[2]) - 1, parseInt(parts[1]));
if (!isNaN(date.getTime())) return date;
}
// Попытка парсинга ISO 'ГГГГ-ММ-ДД'
parts = value.match(/^(\d{4})-(\d{1,2})-(\d{1,2})$/);
if (parts) {
const date = new Date(parseInt(parts[1]), parseInt(parts[2]) - 1, parseInt(parts[3]));
if (!isNaN(date.getTime())) return date;
}
// Можно добавить другие форматы
// Универсальная попытка (может быть неточной)
const parsedDate = new Date(value);
if (!isNaN(parsedDate.getTime())) return parsedDate;
} catch (e) {
// Ошибка парсинга
console.error(`Ошибка парсинга даты: ${value}`, e);
}
}
// Если число, предполагаем формат Google Sheets
if (typeof value === 'number' && value > 0) {
// Преобразование серийного номера даты Google Sheets в Date
const excelEpoch = new Date(1899, 11, 30); // В Excel/Sheets отсчет с 30.12.1899
const date = new Date(excelEpoch.getTime() + value * 24 * 60 * 60 * 1000);
if (!isNaN(date.getTime())) return date;
}
return null;
}
// Пример использования в фильтре:
// ... внутри filter ...
// const rowDateObject = parseDateValue(row[dateColumnIndex]);
// if (rowDateObject) {
// const rowTime = normalizeDate(rowDateObject).getTime();
// // ... сравнение ...
// }
// ...
Примеры сложных фильтров: последняя неделя, последний месяц
Для таких фильтров удобно вычислять начальную и конечную дату динамически.
/**
* Фильтрует данные за последние N дней (включая сегодня).
* @param {any[][]} data Данные.
* @param {number} dateColumnIndex Индекс столбца даты.
* @param {number} days Количество дней.
* @returns {any[][]} Отфильтрованные данные.
*/
function filterLastNDays(data: any[][], dateColumnIndex: number, days: number): any[][] {
const endDate = new Date(); // Сегодня
const startDate = new Date();
startDate.setDate(endDate.getDate() - (days - 1)); // Отсчитываем N-1 дней назад
// Нормализуем даты для корректного сравнения по дням
const normalizedStartDate = normalizeDate(startDate).getTime();
const normalizedEndDate = normalizeDate(new Date(endDate.getFullYear(), endDate.getMonth(), endDate.getDate() + 1)).getTime(); // Конец сегодняшнего дня
const header = data.shift();
const filteredData = data.filter(row => {
const cellValue = row[dateColumnIndex];
if (cellValue instanceof Date && !isNaN(cellValue.getTime())) {
const rowTime = cellValue.getTime();
return rowTime >= normalizedStartDate && rowTime < normalizedEndDate;
}
return false;
});
if (header) {
filteredData.unshift(header);
}
return filteredData;
}
// Пример: данные за последнюю неделю (7 дней)
// const lastWeekData = filterLastNDays(allData, 0, 7);
/**
* Фильтрует данные за предыдущий полный месяц.
* @param {any[][]} data Данные.
* @param {number} dateColumnIndex Индекс столбца даты.
* @returns {any[][]} Отфильтрованные данные.
*/
function filterPreviousMonth(data: any[][], dateColumnIndex: number): any[][] {
const today = new Date();
const year = today.getFullYear();
const month = today.getMonth(); // Текущий месяц (0-11)
// Первый день предыдущего месяца
const startDate = new Date(year, month - 1, 1);
// Первый день текущего месяца (конец предыдущего)
const endDate = new Date(year, month, 1);
const startTime = startDate.getTime();
const endTime = endDate.getTime(); // Не включаем сам этот день
const header = data.shift();
const filteredData = data.filter(row => {
const cellValue = row[dateColumnIndex];
if (cellValue instanceof Date && !isNaN(cellValue.getTime())) {
const rowTime = cellValue.getTime();
return rowTime >= startTime && rowTime < endTime;
}
return false;
});
if (header) {
filteredData.unshift(header);
}
return filteredData;
}
// Пример: данные за прошлый месяц
// const previousMonthData = filterPreviousMonth(allData, 0);
Оптимизация скорости фильтрации данных
При работе с большими объемами данных производительность становится критичной.
Сравнение скорости различных методов фильтрации
- Циклы
forvsArray.filter(): Современные движки JavaScript (V8, на котором работает Apps Script) хорошо оптимизируют встроенные методы массивов.Array.filter()обычно не уступает, а часто и превосходит по скорости стандартные циклыforилиforEach, к тому же код становится чище. getValue()vsgetValues(): Чтение данных из таблицы по одной ячейке (getValue()в цикле) крайне неэффективно из-за множественных вызовов API. Всегда используйтеgetValues()для получения всего диапазона данных за один вызов, а затем фильтруйте массив в скрипте.- Сравнение
getTime(): Сравнение числовых представлений дат (getTime()) обычно быстрее, чем создание и сравнение объектовDateвнутри цикла.
Использование Array.filter() для эффективной фильтрации
Как показано в примерах выше, Array.filter() является предпочтительным методом для фильтрации массивов данных в Apps Script. Он создает новый массив, содержащий только те элементы, для которых колбэк-функция вернула true.
Оптимизация циклов и условий
- Выносите вычисления за пределы цикла: Если граничные даты или другие условия не меняются внутри цикла, вычислите их один раз перед началом фильтрации (например,
normalizedTargetDate = normalizeDate(targetDate).getTime();). - Проверяйте тип данных: Добавляйте проверку
cellValue instanceof Date && !isNaN(cellValue.getTime()), чтобы избежать ошибок при попытке вызвать методыDateу некорректных значений и для пропуска пустых ячеек. - Минимизируйте создание объектов
Dateвнутри цикла: Если возможно, работайте сgetTime().
Примеры реальных сценариев фильтрации данных по дате
Автоматическое создание отчетов за определенный период
Скрипт может запускаться по триггеру (например, еженедельно) для сбора данных за прошедшую неделю из основной таблицы, их фильтрации с помощью filterByDateRange или filterLastNDays, и записи результата на отдельный лист отчета или отправки по email.
Фильтрация данных для отображения на веб-странице
Если вы создаете веб-приложение с помощью HTML Service, Apps Script может выступать бэкендом. Пользователь на веб-странице выбирает диапазон дат, эти даты передаются в серверную функцию Apps Script, которая фильтрует данные из Google Sheets и возвращает результат для отображения.
Отправка уведомлений по электронной почте на основе фильтрации по дате
Скрипт может ежедневно проверять таблицу с задачами или событиями. Используя фильтрацию по дате (например, filterBySpecificDay для сегодняшних дедлайнов или filterByDateRange для задач на ближайшие 3 дня), он может автоматически отправлять напоминания ответственным лицам по электронной почте (MailApp.sendEmail).
Интеграция с другими сервисами Google (например, Calendar)
Можно создать скрипт, который читает события из Google Calendar (CalendarApp.getEvents(startDate, endDate)), затем находит соответствующие записи в Google Sheets (например, логи рабочего времени или детали проекта), используя фильтрацию по дате, и объединяет информацию для создания сводного отчета.