Service invoked too many times in a short time: exec qps. Как исправить?

Question

[lantan55]

Здравствуйте! Вопрос такой:
Знакомый попросил помочь ему с гугл таблицей, задача была такая: При вставке значения в ячейку, в соседней ячейке будет высчитываться текущее время заполнения ячейки и в зависимости от того, попала ли ячейка в нужный диапазон, она подкрашивалась бы либо зеленым, либо красным. Про цвета понятно, сделали стандартными средствами гугл таблиц.
На месте, где должно высчитываться время стоит формула =GETDATE(A3), которую описал в Редакторе скриптов

function GETDATE(input) {  
  if (input)
    return Utilities.formatDate(new Date(), "GMT+03:00", "HH:mm:ss");
  else
    return '';
};

Но спустя какое то время, он обратился ко мне с вопросом: чё то не работает твоя формула.
И я увидел, что у него в таблице на странице моя формула протянута на ячеек 50, листов в файле = кол-во дней в году и одновременно постоянно к этой таблице подключены около 6 человек.
Как то так:

Я так понимаю, что задача теперь уже совсем не для гугл таблиц. И теперь у меня два вопроса:
1. Возможно ли как то исправить текущую формулу, чтобы она была менее затратна по ресурсам.
2. Где, кроме гугл таблиц можно такое реализовать? Возможно есть уже какие то готовые аналоги?

Answer 1

[dollar]

Главное, что можно сделать из простого - это вместо 50 вызовов делать один вызов на диапазон.

Код (грубый пример):

function GETDATE(input) {  
  if (input) {
    if (typeof input.map === "function") return input.map(GETDATE);
    return Utilities.formatDate(new Date(), "GMT+03:00", "HH:mm:ss");
  } else
    return '';
};

В самой таблице нужно будет не растягивать формулу, как обычно, а указать ее в первой ячейке, но в параметры передать диапазон:
=GETDATE(A3:A53)

Это должно немного снизить нагрузку. Когда пересчитывается весь столбец A3:A53, то по цепочке произойдет не 50 запросов, а один. Хотя визуально обновится весь столбец с датами.

Далее нужно смотреть, как устроена сама таблица. Если в A3 формула и она постоянно пересчитывается, то это плохо.

Еще такое (не очень красивое) решение. Можно сделать кнопку, которую, предполагается, нужно нажимать не часто. То есть пользователь редактировал таблицу минут 10, а затем нажимает кнопку, и соответствующий скрипт проставляет даты. Погрешность увеличится, удобство уменьшится, и отдельный вопрос, как это всё сделать, но в лимиты можно будет влезть.

Comments

[lantan55]

Спасибо за ответ. Это действительно должно немного разгрузить табличку, но скопировав ваш пример, у меня во все ячейки проставляется последнее время.

Далее нужно смотреть, как устроена таблица. Если в A3 формула и она постоянно пересчитывается, то это плохо.

Нет, на первом скриншоте дополнительные формулы только в ячейках C30,C31
=(C30*8)/60 и =СУММ(C3:C29)

[dollar]

Мда, это проблема. Элегантного решения пока не вижу. Можно, конечно, замутить сохранение старых значений и старых дат в каком-то секретном диапазоне, т.е. это по сути аналог глобальных переменных (или баз данных), но это уже достойно полноценного задания для программиста.

[lantan55]

dollar, да, согласен с Вами, что это уже выходит за рамки задач для гугл Таблиц. Большое спасибо за помощь)

[dollar]

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

[dollar]

Еще такое (не очень красивое) решение. Можно сделать кнопку, которую, предполагается, нужно нажимать не часто. То есть пользователь редактировал таблицу минут 10, а затем нажимает кнопку, и соответствующий скрипт проставляет даты. Погрешность увеличится, удобство уменьшится, и отдельный вопрос, как это всё сделать, но в лимиты можно будет влезть.

[lantan55]

dollar, Я уже думал про отдельную кнопку, но это действительно не самое изящное решение) А вообще ему там прям особо 10 минут нечего редактировать. На деле это выглядело бы так: Он зашёл, занёс данные, нажал на кнопку, время проставилось. Всё, через какое то время другой зашёл, занёс данные, нажал кнопку. В лимиты бы точно поместилось бы всё, тк вообще не было бы этих тонн формул. И ещё, там в онлайне сидят всегда 10 человек не потому, что они без остановки всё там редактируют, а потому что из - за объёма этой таблицы, им приходится её не закрывать, чтобы она потом час не открывалась)