Диапазоны и массивы

Apps Script видит лист не как сетку ячеек, а как обычный двумерный массив JavaScript. Кто усвоил это твёрдо — пишет скрипты, которые отрабатывают за полсекунды. Кто нет — ждёт по полторы минуты и упирается в лимит времени.

Range (диапазон) — прямоугольный кусок листа: от одной ячейки до всей таблицы. Всё чтение и запись в Apps Script идут через диапазоны. Отдельного объекта «ячейка» в API нет — одна ячейка это просто диапазон 1×1.

Зачем вообще думать о диапазонах

Скрипт живёт не внутри таблицы. Он выполняется на серверах Google, а таблица — отдельный сервис. Каждое обращение вида getValue() или setValue() — это сетевой запрос туда и обратно. Один такой запрос стоит десятки миллисекунд. Незаметно, пока запрос один. На тысяче строк это уже минута ожидания, а у скрипта есть жёсткий потолок: 6 минут на выполнение (30 минут для аккаунтов Workspace), после чего он падает с Exceeded maximum execution time.

Отсюда правило, вокруг которого крутится весь этот раздел: прочитал всё одним вызовом → посчитал в памяти → записал всё одним вызовом. Диапазон — это тот самый «один вызов».

getValues() отдаёт двумерный массив

Возьмём лист «Заказы»: в первой строке заголовки, дальше данные — клиент, товар, цена.

function readOrders() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Заказы');

  // getRange(строка, колонка, сколько строк, сколько колонок)
  const range = sheet.getRange(2, 1, 4, 3);
  const values = range.getValues();

  console.log(values.length);      // 4  — строк
  console.log(values[0].length);   // 3  — колонок
  console.log(values[0]);          // первая строка данных целиком
  console.log(values[0][1]);       // вторая колонка первой строки
}

Результат в логах (Ctrl+Enter в редакторе):

4
3
[ 'Иванов', 'Ноутбук', 74990 ]
Ноутбук

Обратите внимание на четыре аргумента getRange. Их легко перепутать, поэтому держите под рукой:

getValues() всегда возвращает массив массивов, даже если диапазон — одна ячейка. sheet.getRange('A1').getValues() даст [['Клиент']], и добираться до значения придётся через [0][0]. Если нужна ровно одна ячейка, честнее взять getValue() (без s) — он вернёт само значение.

Обход строк

Раз это обычный массив, работают все привычные приёмы JavaScript. Деструктуризация делает код читаемым — вместо безымянных row[0] и row[2] появляются нормальные переменные.

function totalRevenue() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Заказы');
  const lastRow = sheet.getLastRow();

  // Данных нет — только заголовок. Дальше идти нельзя.
  if (lastRow < 2) {
    console.log('Лист пустой');
    return;
  }

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

  let total = 0;
  for (const [client, product, price] of rows) {
    if (typeof price === 'number') {
      total += price;
    }
  }

  console.log('Строк: ' + rows.length + ', выручка: ' + total);
}

Результат:

Строк: 4, выручка: 168470

Про lastRow - 1 стоит сказать отдельно. getLastRow() возвращает номер последней непустой строки — если данных четыре строки плюс заголовок, это 5. Отнимаем единицу за заголовок и получаем 4 строки данных. А когда лист пуст и в нём только шапка, getLastRow() равен 1, lastRow - 1 даёт 0 — и getRange падает с The number of rows in the range must be at least 1. Отсюда проверка в начале. Эта ошибка догоняет скрипт не сразу, а через месяц, когда кто-нибудь почистит лист.

setValues(): размеры обязаны совпадать

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

Посчитаем НДС и запишем его в колонку D.

function writeVat() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Заказы');
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

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

  // Каждая строка результата — массив из одного элемента: колонка одна.
  const vat = prices.map(row => [Math.round(row[0] * 20 / 120)]);

  sheet.getRange(2, 4, vat.length, 1).setValues(vat);
}

Ключевая строка — [Math.round(...)] в квадратных скобках. Начинающие пишут так:

// ❌ Не сработает: получится плоский массив [12498, 3331, ...]
const vat = prices.map(row => Math.round(row[0] * 20 / 120));
sheet.getRange(2, 4, vat.length, 1).setValues(vat);

Ошибка:

Exception: The parameters (number[]) don't match the method signature
for SpreadsheetApp.Range.setValues.

Плоский массив [12498, 3331] API не понимает: он ждёт «строки, а в них ячейки», то есть [[12498], [3331]]. Правило простое: внешний массив — строки, внутренний — колонки. Одна колонка на десять строк это десять массивов по одному элементу, а одна строка на десять колонок — один массив из десяти.

Второй частый сбой — несовпадение количества. Прочитали 50 строк, отфильтровали до 30, а пишете в диапазон на 50:

Exception: The number of rows in the data does not match
the number of rows in the range. The data has 30 but the range has 50.

Лечится тем, что размер диапазона всегда берут из самих данных: getRange(2, 4, vat.length, vat[0].length). Никаких зашитых цифр.

Индексы: массив с нуля, лист с единицы

Это ошибка номер один у всех, кто приходит из формул. В таблице первая строка — первая. В JavaScript первый элемент — нулевой. Когда данные читаются со второй строки (после заголовка), сдвиг становится двойным.

Формула перевода одна и её стоит запомнить: номер строки = i + startRow, где startRow — та строка, с которой начинали читать. Читали с 2-й — значит i + 2.

function markExpensive() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Заказы');
  const START_ROW = 2;
  const lastRow = sheet.getLastRow();
  if (lastRow < START_ROW) return;

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

  rows.forEach((row, i) => {
    const price = row[2];
    if (price > 50000) {
      const sheetRow = i + START_ROW;          // 0 → 2, 1 → 3, 2 → 4
      sheet.getRange(sheetRow, 5).setValue('дорогой');
    }
  });
}

Забыли + 2 — пометки уедут на строку вверх и затрут заголовок. Данные при этом не пропадут, и заметите вы это далеко не сразу. Кстати, этот пример специально написан «в лоб», через setValue в цикле: на десяти строках так можно, на тысяче — нельзя. Как переписать его правильно, разберём в следующем уроке.

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

Под капотом getValues() дёргает Sheets API и тащит по сети весь прямоугольник разом. Стоимость вызова почти не зависит от того, 10 там ячеек или 10 000 — платите вы в основном за сам факт обращения, за круговую задержку. Поэтому один вызов на 5000 ячеек в десятки раз дешевле, чем 5000 вызовов по одной.

Записи Apps Script, наоборот, копит в буфере и отправляет пачкой, когда буфер надо сбросить: перед следующим чтением, в конце функции или по явному SpreadsheetApp.flush(). Из-за этого setValue в цикле выглядит быстрее, чем есть, — счёт приходит позже.

Ещё одна вещь, которую полезно знать заранее: типы. getValues() отдаёт JavaScript-значения, а не текст с листа. Числа приходят как number, даты — как объекты Date (а не как строка «14.07.2026»), галочки-чекбоксы — как boolean, пустая ячейка — как пустая строка '', а не null и не undefined. Проверка if (row[0] === null) не сработает никогда; правильно писать if (row[0] === ''). И если в ячейке формула, вы получите её посчитанный результат — чтобы добраться до самой формулы, есть getFormulas().

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

• Плоский массив в setValues(). Всегда массив массивов, даже для одной колонки: [[1], [2], [3]].
• Размер диапазона зашит цифрой. После фильтрации строк стало меньше — и запись падает. Берите размер из data.length и data[0].length.
• Забыли про сдвиг заголовка. i + 1 вместо i + 2 — и всё съезжает на строку.
• Пустой лист. getLastRow() - 1 даёт 0, и getRange кидает исключение. Проверяйте перед чтением.
• Сравнение пустой ячейки с null. Пусто — это ''.
• «Рваный» массив. Если в одной строке 3 элемента, а в другой 2, setValues() откажется работать: прямоугольник обязан быть ровным.

Итоги

• Лист в Apps Script — это двумерный массив: values[строка][колонка].
• getRange(строка, колонка, сколькоСтрок, сколькоКолонок) — нумерация с единицы.
• getValues() читает прямоугольник одним сетевым вызовом и всегда возвращает массив массивов.
• setValues() требует точного совпадения формы данных и диапазона.
• Массив считает с нуля, лист — с единицы: номер строки = i + startRow.
• Даты приходят объектами Date, пустые ячейки — пустыми строками.