L
L
lantan552019-10-15 14:00:24
Google Sheets
lantan55, 2019-10-15 14:00:24

Service invoked too many times in a short time: exec qps. How to fix?

Hello! The question is this:
A friend asked me to help him with a Google spreadsheet, the task was as follows: When inserting a value into a cell, the current cell filling time will be calculated in the neighboring cell, and depending on whether the cell fell into the desired range, it would be tinted either green or red. It’s clear about the colors, they made it the standard means of Google tables.
In the place where the time should be calculated is the formula =GETDATE(A3), which I described in the Script Editor

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

But after some time, he turned to me with a question: why is your formula not working.
And I saw that in his table on the page my formula is stretched to 50 cells, sheets in the file = number of days in a year and at the same time about 6 people are constantly connected to this table.
Something like this:
5da5a1def2461935287299.jpeg
I understand that the task is no longer for Google Spreadsheets. And now I have two questions:
1. Is it possible to somehow fix the current formula so that it is less resource-intensive.
2. Where, besides Google Spreadsheets, can this be implemented? Perhaps there are already some ready-made analogues?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
D
dollar, 2019-10-15
@dollar

The main thing that can be done from a simple one is instead of 50 calls, make one call per range.

Code (rough example):
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 '';
};
In the table itself, you will not need to stretch the formula, as usual, but specify it in the first cell, but pass the range to the parameters:
This should slightly reduce the load. When the entire column A3:A53 is recalculated, then not 50 queries will occur along the chain, but one. Although the entire column with dates will be visually updated.
Next, you need to look at how the table itself is arranged. If there is a formula in A3 and it is constantly recalculated, then this is bad.
Another (not very beautiful) solution. You can make a button that is supposed to be pressed infrequently. That is, the user edited the table for 10 minutes, and then presses the button, and the corresponding script puts down the dates. The error will increase, convenience will decrease, and a separate question is how to do it all, but it will be possible to climb into the limits .

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question