Таблицы Google Table глазами разработчика

Отличается ли подход к изучению работы с Google Table пользователей и разработчиков? Ответ положительный. Разница состоит в том, что задача первого - освоить предлагаемую функциональность по работе с данными в таблицах, а второго - автоматизировать эту работу и упростить путём создания требуемой функциональности.

Как вы считаете, является ли изучение документации хорошим стартом для разработчика? В большинстве случаев ответ будет отрицательным. И это в полной мере относится к Google Table. Знание общей функциональности важно, но гораздо важнее получить практические знания для эффективно работы с ними.

Google Table - это онлайн платформа, по причине чего желательно сразу использовать быстро работающий код. В этом и состоит отличие от, например, изучения языков программирования, где можно смело брать примеры из документации и всё будет работать быстро на более или менее современном компьютере. При использовании аналогичного подхода работы с таблицами Google можно получить крайне медленно работающую реализацию. Для примера сравним скорость выполнения кода ниже:

// получение ячейки - 4,5-5,8 секунд
for(let i = 1 ; i <= 1000; i++){ 
  sh.getRange('A'+i).getValue();
} 

// получение столбца - 0,6-0,7 секунды
sh.getRange(`A1:A1000`).getValues(); 
sh.getRangeList(['A1:A1000]').getRanges().map(range => range.getValues());  

// получение значений всего диапазона на листе - 1,8-2,4 секунды
sh.getDataRange().getValues();

Метод setValues() работает примерно в 2 раза быстрее getValues, но принципиально это картины не меняет. Более того, через некоторое время для получения 300 позиций потребовалось 20-25 секунд! С чем связана данная просадка производительности, признаться, меня не интересовала, поскольку есть более быстрый и стабильный вариант получения значений - getRanges, getRangeList и getDataRange.

У разных пользователей могут быть разные цифры замеры производительности и обращать внимание на их абсолютные значения не имеет никакого смысла. Сейчас соединение отличное, а через 10 минут - удовлетворительное. Смотреть нужно на порядок цифр, который наглядно говорит о том, что операции над отдельными ячейками крайне медленные и желательно их использовать как можно меньше, особенно в циклах. Получается, что в циклах иногда их можно использовать? Можно, если в условиях данной задачи они работают быстрее. Типичный пример - импорт заказа покупателя. Предположим, у нас есть каталог на несколько тысяч позиций и заказ покупателя из 10 позиций, количество которого нужно проставить в этом каталоге. В этом случае 10 вызовов setValue выполнятся быстрее, чем вариант с получением всего диапазона кодов товара из каталога с последующим обновлением ячеек методом setValues.

Также методы для работы с отдельной ячейкой можно использовать для сохранения и получения данных между сессиями. Ячейка позволяет хранить до 50 тыс. символов, что позволит сохранять не только настройки проекта, но и данные.

Почему у Apps Script такая низкая производительность? Потому что каждое обращение к объектам этой платформы - листу, ячейкам, форматированию, вызовом внешних скриптов и др. представляет собой запрос на сервер (автономную работу не рассматриваем), а не локально исполняемый код в браузере, как JavaScript. По это причине нужно стараться минимизировать количество обращений к сервисам App Script, а всю обработку данных производить локально при помощи JavaScript.

У пользователя для автоматизации есть формулы, а у разработчика ещё и скрипты. Это позволяет производить балансировку между функциональностью и производительностью. Если у пользователей одной из самых любимых операция является протяжка формулы ячейки на диапазон, то у разработчиков это будет функции для работы с массивом данных ARRAYFORMULA, BYROW и др.

Казалось бы, как может медленно работать всего один запрос на изменение ширины столбца по размеру содержащихся в нём данных? А на практике каждый такой запрос может съедать 3-4 секунды и приходится выбирать - либо скрипт формирует данные без форматирования, но быстро, либо с форматированием, но ощутимо медленнее.

Если каждый метод Apps Script - это запрос, то необходимо уменьшать их количество. На производительность оказывает большее влияние не объём выводимых на лист данных, а количество методов для их вывода.

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

={A1;B1;C1} - вывод массива данных в столбец
={A1\B1\C1} - вывод массива данных в строку (в иностранных примерах разделитель запятая, который для нашей локали нужно заменить на \)

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

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

let myList = [];

init();

function init(){
// много тяжелых операций

  return myList;
}

function onEdit(){

}

Выглядит всё привычно. При запуске макроса выполняется формирование списка в функции init. После этого пользователь редактирует ячейку и не понимает, почему таблица "подвисает". А подвисает она по той причине, что после каждой операции редактирования происходит перезапуск макроса и вызов функции init. Это несложно проверить при помощи вывода всплывающего сообщения методом toast.

Для решения этой проблемы нужно минимизировать время выполнения инициализации данных и производить их загрузку не при запуске макроса, а при вызове функции, где они используются. Что же выходит? Если список используется в десяти функциях, то в начале каждой из них вызывать функцию для формирования списка? Но тогда же получается дублирование кода, который нас учили избегать! Да, но так работает Google Table. Для минимизации операций формирования списка можно проверять его на наличие данных. Если они есть, то не нужно заново формировать список.