Форматирование и оформление листа

Отчёт, который выгружается голыми числами, никто не читает. Разберёмся, как скрипт сам создаёт лист, рисует шапку, ставит формат «1 234,00 ₽», раскрашивает убытки красным — и делает это, не превращаясь в тормоза.

Форматирование в Apps Script — такие же методы диапазона, как setValues(), только меняют они не содержимое, а внешний вид: setBackground, setFontWeight, setNumberFormat, setBorder. И у них ровно та же экономика: один вызов на диапазон дёшев, тысяча вызовов на ячейки — дорого.

Создать лист, если его ещё нет

Скрипт-отчёт запускают не один раз. Наивное ss.insertSheet('Отчёт') сработает ровно однажды, а на втором запуске упадёт: A sheet with the name "Отчёт" already exists. Правильная заготовка — получить лист, а если его нет, создать; и почистить перед новой выгрузкой.

function getReportSheet() {
  const ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName('Отчёт');

  if (!sheet) {
    sheet = ss.insertSheet('Отчёт');
  } else {
    sheet.clear();               // содержимое + форматирование
    // sheet.clearContents();    // только значения, оформление останется
  }
  return sheet;
}

Разница между clear() и clearContents() важнее, чем кажется. clear() сносит и данные, и всё оформление — включая правила условного форматирования, которые вы навесили в прошлый раз. clearContents() оставляет оформление на месте: удобно, когда шапка и форматы настроены один раз и меняются только цифры.

Шапка: заголовки и их оформление

function buildReport() {
  const sheet = getReportSheet();
  const headers = ['Регион', 'Менеджер', 'Сумма', 'Комиссия', 'Дата сделки'];

  // Заголовки — одной строкой: массив из ОДНОГО массива
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // Оформление шапки — цепочкой, один диапазон
  sheet.getRange(1, 1, 1, headers.length)
    .setFontWeight('bold')
    .setFontColor('#ffffff')
    .setBackground('#1a73e8')
    .setHorizontalAlignment('center');

  sheet.setFrozenRows(1);                        // шапка не уезжает при прокрутке
  sheet.autoResizeColumns(1, headers.length);    // ширина по содержимому
}

Два момента. Заголовки — это [headers], массив из одного массива: одна строка, пять колонок. Забудете внешние скобки — получите ту же ошибку про несовпадение размеров, что и в первом уроке.

И методы диапазона возвращают сам диапазон, поэтому их выстраивают в цепочку. Это не украшательство: цепочка из четырёх вызовов на одном объекте Range обходится сильно дешевле, чем четыре отдельных getRange(...) подряд.

Числовые форматы

setNumberFormat() принимает ту же строку шаблона, что и меню «Формат → Числа → Другие форматы» в самой таблице. Формат меняет только отображение — в ячейке по-прежнему лежит число, и складывать его можно как обычно.

const lastRow = sheet.getLastRow();

// Суммы: 1 234,00 ₽
sheet.getRange(2, 3, lastRow - 1, 2).setNumberFormat('#,##0.00 \u20bd');

// Даты: 14.07.2026
sheet.getRange(2, 5, lastRow - 1, 1).setNumberFormat('dd.MM.yyyy');

// Проценты: 7,0%
// sheet.getRange(2, 6, lastRow - 1, 1).setNumberFormat('0.0%');

Ловушка с процентами стоит того, чтобы её проговорить. Формат 0.0% умножает значение на 100 при показе. Если вы уже посчитали «7» и поставите процентный формат, в ячейке появится 700,0%. В ячейку кладут долю — 0.07, — а процент рисует формат.

Границы, цвета и полосатые строки

const data = sheet.getRange(2, 1, lastRow - 1, 5);

// setBorder(верх, лево, низ, право, вертикальные, горизонтальные, цвет, стиль)
data.setBorder(
  true, true, true, true, true, true,
  '#d0d7de', null, SpreadsheetApp.BorderStyle.SOLID
);

// Полосатые строки — одной командой, без цикла
data.applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY);

У setBorder восемь параметров, и запомнить их порядок с первого раза невозможно — держите шпаргалку: сначала четыре внешние стороны (верх, лево, низ, право), потом две внутренние сетки (вертикальные линии, горизонтальные), потом цвет и стиль. Любой из первых шести можно передать как null — тогда эта граница останется как была.

Если цвет должен зависеть от данных (скажем, отрицательные суммы — розовым фоном), не ставьте фон в цикле по ячейкам. Соберите двумерный массив цветов той же формы, что и данные, и залейте одним вызовом setBackgrounds() — с буквой «s» на конце.

const values = data.getValues();

// Массив цветов той же формы: строк столько же, колонок столько же
const colors = values.map(row =>
  row.map(() => (row[2] < 0 ? '#fce8e6' : null))   // null — оставить как есть
);

data.setBackgrounds(colors);   // один вызов вместо тысячи

Условное форматирование из кода

Ручная раскраска через setBackgrounds() статична: изменит пользователь число — цвет останется старым. Условное форматирование живёт в самой таблице и пересчитывается само. Из скрипта его вешают правилами.

function addRules() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Отчёт');
  const lastRow = sheet.getLastRow();
  const amounts = sheet.getRange(2, 3, lastRow - 1, 1);

  const negative = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThan(0)
    .setBackground('#fce8e6')
    .setFontColor('#c5221f')
    .setRanges([amounts])
    .build();

  const big = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(100000)
    .setBackground('#e6f4ea')
    .setRanges([amounts])
    .build();

  // ВАЖНО: правила задаются целиком, а не «добавляются»
  sheet.setConditionalFormatRules([negative, big]);
}

Последняя строка — источник самой популярной беды с условным форматированием. setConditionalFormatRules() заменяет весь набор правил листа. Передали два правила — на листе стало ровно два, всё, что настраивал пользователь руками, исчезло.

Если чужие правила надо сохранить, сначала заберите текущий список и допишите свои:

const rules = sheet.getConditionalFormatRules();
rules.push(negative);
sheet.setConditionalFormatRules(rules);

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

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

Форматирование ходит по той же сети, что и данные. setBackground() на одной ячейке — это сетевой запрос; вызов в цикле по тысяче ячеек стоит ровно столько же, сколько тысяча setValue(), то есть около минуты. Все «множественные» методы существуют именно для того, чтобы этого избежать: setBackgrounds, setNumberFormats, setFontWeights, setHorizontalAlignments. Все они принимают двумерный массив ровно той же формы, что и диапазон.

Ещё одна тонкость — порядок. Apps Script копит изменения в буфере и отправляет пачкой, поэтому оформление, применённое к диапазону getRange(2, 1, lastRow - 1, 5), вычисляется от текущего lastRow. Отформатировали лист, а потом дописали строки — новые останутся без оформления. Правильный порядок: сначала записать данные, потом форматировать. А если нужно увидеть промежуточный результат прямо в таблице (например, в сайдбаре), вызывайте SpreadsheetApp.flush() — он принудительно сбрасывает буфер на лист.

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

• insertSheet() без проверки. Второй запуск падает с «лист уже существует». Всегда getSheetByName() и создание только при null.
• setConditionalFormatRules() затирает чужие правила. Метод заменяет весь набор целиком, а не добавляет к нему.
• Правила размножаются. Обратная ситуация: getConditionalFormatRules() + push в ежедневном скрипте копит дубликаты.
• Цвет в цикле по ячейкам. setBackground вместо setBackgrounds — тот же тормоз, что и setValue вместо setValues.
• Форматирование до записи данных. getLastRow() ещё не знает про новые строки, и они остаются неоформленными.
• Процент, посчитанный дважды. В ячейку кладём 0.07, а не 7: множитель 100 добавит формат.
• Цвет строкой «red». Надёжнее hex-код: '#fce8e6'. Именованные цвета поддержаны не везде одинаково.

Итоги

• Лист создают через getSheetByName() с проверкой на null, а не голым insertSheet().
• clear() сносит и данные, и оформление; clearContents() — только данные.
• Методы Range возвращают сам диапазон, поэтому их выстраивают в цепочку.
• Числовой формат меняет только вид: под ним всё равно лежит число.
• Массовые методы с «s» на конце (setBackgrounds, setNumberFormats) экономят те же десятки секунд, что и setValues.
• setConditionalFormatRules() заменяет весь набор правил листа — учитывайте это и не плодите дубликаты.
• Сначала данные, потом оформление. Иначе новые строки останутся неотформатированными.