Фильтрация, сортировка, вычисления

Один и тот же отчёт можно собрать за 78 секунд или за 0,6 — разница только в том, сколько раз скрипт сходил к листу. Здесь мы измерим эту разницу и научимся считать всё в памяти.

Правило пакетной обработки: читаем всё за один вызов, считаем в массиве средствами JavaScript, пишем результат за один вызов. Обращение к листу внутри цикла — почти всегда ошибка проектирования.

Замер: сколько стоит поход к листу

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

function slowReport() {
  const t0 = Date.now();
  const src = SpreadsheetApp.getActive().getSheetByName('Продажи');
  const dst = SpreadsheetApp.getActive().getSheetByName('Отчёт');
  const lastRow = src.getLastRow();

  let out = 2;
  for (let row = 2; row <= lastRow; row++) {
    const manager = src.getRange(row, 1).getValue();   // поход к листу
    const region  = src.getRange(row, 2).getValue();   // ещё один
    const amount  = src.getRange(row, 3).getValue();   // и ещё

    if (amount > 50000) {
      dst.getRange(out, 1).setValue(manager);          // и на запись тоже
      dst.getRange(out, 2).setValue(region);
      dst.getRange(out, 3).setValue(amount);
      out++;
    }
  }
  console.log('Заняло: ' + (Date.now() - t0) + ' мс');
}

Результат:

Заняло: 78214 мс

Больше минуты. Причём цифра плавает от запуска к запуску: она зависит от сети и загруженности серверов Google, у вас может выйти и 50 секунд, и полторы минуты. Посчитаем, откуда столько. 1000 строк × 3 чтения = 3000 обращений, плюс записи. Каждое — сетевой запрос в Sheets API. Умножаем на 20–30 мс задержки и получаем ровно тот порядок, что в логе.

Теперь то же самое, но данные читаются один раз.

function fastReport() {
  const t0 = Date.now();
  const ss = SpreadsheetApp.getActive();
  const src = ss.getSheetByName('Продажи');
  const dst = ss.getSheetByName('Отчёт');
  const lastRow = src.getLastRow();
  if (lastRow < 2) return;

  // 1. Одно чтение
  const rows = src.getRange(2, 1, lastRow - 1, 3).getValues();

  // 2. Вся работа — в памяти
  const big = rows.filter(row => row[2] > 50000);

  // 3. Одна запись
  if (big.length > 0) {
    dst.getRange(2, 1, big.length, 3).setValues(big);
  }
  console.log('Отобрано ' + big.length + ' строк за ' + (Date.now() - t0) + ' мс');
}

Результат:

Отобрано 213 строк за 612 мс

Сто с лишним раз быстрее. И это не оптимизация ради оптимизации: первый вариант на 5000 строк просто не доживёт до конца — упрётся в шестиминутный лимит и умрёт с Exceeded maximum execution time, оставив половину отчёта записанной. Второй на 5000 строк отработает примерно за те же секунду-полторы.

Три инструмента, которые закрывают почти всё

filter — отобрать строки

filter возвращает новый массив из тех строк, на которых функция вернула true. Исходный массив не меняется.

const rows = sheet.getRange(2, 1, lastRow - 1, 3).getValues();

// Москва и сумма больше 50 000
const selected = rows.filter(([manager, region, amount]) =>
  region === 'Москва' && amount > 50000
);

console.log(selected.length);

Форма строк сохраняется — каждая остаётся массивом из трёх элементов, поэтому результат filter можно отдать в setValues() без переделки.

map — превратить строки в другие строки

map перебирает массив и строит новый той же длины. Здесь важно не потерять двумерность: если на выходе нужна таблица, каждый элемент обязан остаться массивом.

// Меняем порядок колонок и добавляем комиссию 7%
const report = rows.map(([manager, region, amount]) => [
  region,
  manager,
  amount,
  Math.round(amount * 0.07)
]);

// 4 колонки — ровно столько, сколько в каждой строке report
dst.getRange(2, 1, report.length, 4).setValues(report);

Размерность диапазона снова считается из данных: report.length строк и 4 колонки. Никаких зашитых чисел.

reduce — свернуть в итог

reduce нужен, когда из тысячи строк надо получить одно значение или один объект. Классика — сумма по группам.

function byRegion() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Продажи');
  const lastRow = sheet.getLastRow();
  const rows = sheet.getRange(2, 1, lastRow - 1, 3).getValues();

  // { 'Москва': 4812000, 'Казань': 1930500, ... }
  const totals = rows.reduce((acc, [manager, region, amount]) => {
    acc[region] = (acc[region] || 0) + amount;
    return acc;
  }, {});

  // Объект → двумерный массив для листа, по убыванию суммы
  const table = Object.entries(totals)
    .sort((a, b) => b[1] - a[1])
    .map(([region, sum]) => [region, sum]);

  const dst = SpreadsheetApp.getActive().getSheetByName('Свод');
  dst.getRange(2, 1, table.length, 2).setValues(table);
  console.log(table);
}

Результат:

[ [ 'Москва', 4812000 ], [ 'Казань', 1930500 ], [ 'Пермь', 874300 ] ]

Строка acc[region] = (acc[region] || 0) + amount — весь смысл группировки: если региона в накопителе ещё нет, берём ноль, иначе прибавляем к тому, что уже накопили. Так за один проход по массиву собирается сводка, которую в таблице пришлось бы делать через СУММЕСЛИ.

Сортировка

sort() у массивов сортирует на месте — портит исходный массив и возвращает ссылку на него же. Если исходные данные ещё понадобятся, копируйте: const sorted = [...rows].sort(...).

// По сумме, от большей к меньшей (колонка с индексом 2)
const byAmount = [...rows].sort((a, b) => b[2] - a[2]);

// По менеджеру, по алфавиту, с учётом русской раскладки
const byName = [...rows].sort((a, b) => a[0].localeCompare(b[0], 'ru'));

// Сначала по региону, внутри региона — по убыванию суммы
const combo = [...rows].sort((a, b) =>
  a[1].localeCompare(b[1], 'ru') || b[2] - a[2]
);

Про localeCompare с явным 'ru' вспоминают не сразу. Голый sort() без компаратора сравнивает элементы как строки, по кодам символов — и «Ёлкин» уезжает в конец списка, а числа выстраиваются как 1, 10, 100, 2. Для чисел компаратор (a, b) => a - b обязателен всегда.

Есть и штатный range.sort(3) — он сортирует прямо на листе. Работает, но это отдельный сетевой вызов; если вы всё равно держите данные в памяти, дешевле отсортировать массив.

Как это работает

Внутри Apps Script есть кэш и отложенная запись. Чтение обязано быть синхронным: скрипт останавливается и ждёт ответа Sheets API — отсюда 20–30 мс на каждый getValue(). Запись складывается в буфер и уходит пачкой при сбросе, поэтому setValue() в цикле кажется быстрым, хотя счёт всё равно придёт: буфер сбрасывается перед любым следующим чтением, и цикл «прочитал-записал-прочитал» бьёт по производительности вдвойне.

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

Массив на 5000 строк × 10 колонок — это 50 000 значений в памяти. Для V8, на котором работает Apps Script, это пустяк. Реальный потолок памяти вы почувствуете где-то на сотнях тысяч строк, и такие объёмы обычно означают, что данным пора в BigQuery, а не в Таблицы.

Частые ошибки

• getValue() или setValue() внутри цикла. Самая дорогая ошибка в Apps Script. Увидели такое — переписывайте на пакетную схему.
• setValues() на пустом массиве. Отфильтровали и не нашли ни одной строки — getRange(2, 1, 0, 3) падает. Перед записью проверяйте if (result.length > 0).
• map вернул плоский массив. rows.map(r => r[0]) даёт список строк, а не таблицу. Для записи нужно rows.map(r => [r[0]]).
• sort() без компаратора на числах. Получите порядок 1, 10, 100, 2 — сортировку по кодам символов.
• sort() испортил исходные данные. Он меняет массив на месте; копируйте через [...rows], если исходник ещё нужен.
• Смешивание типов. Если в колонке сумм затесалась строка «нет данных», amount > 50000 вернёт false молча, без ошибки. Подстрахуйтесь: typeof amount === 'number'.

Итоги

• Обращение к листу стоит десятки миллисекунд; вычисление в памяти — микросекунды. Отсюда вся разница в скорости.
• Схема на все случаи: одно getValues() → filter/map/reduce/sort → одно setValues().
• На 1000 строк построчный цикл занял 78 секунд, пакетный вариант — 0,6.
• filter сохраняет форму строк, map её задаёт, reduce сворачивает массив в сводку.
• Числа сортируются только с компаратором, кириллица — через localeCompare(…, 'ru').
• Перед записью проверяйте, что массив не пустой.