How to auto-update the formula in Google Sheets if the data in the cell is taken from the site by reference?

Question

Google Sheets

Hello, please help me to solve this issue. I've been struggling with it for two days now and I can't find a solution.

In a Google Spreadsheet, a cell contains the following formula:

=IMPORTJSON("https://data.gateapi.io/api2/1/ticker/btc_usdt";"baseVolume")

I need to make the formula recalculate (values ​​are updated). It is advisable to update every 30 seconds or every minute or at least once every 5 minutes. So that it is somehow updated)

Now, in order to make an update, you have to delete the formula from the cell and re-insert it. It is clear that this is a bad decision.

I tried to make in the table settings that all formulas would be recalculated every minute. It doesn't work, after googling I realized that now these settings only work for a few functions such as for example "NOW".

Then I tried to do something like this:

=ЕСЛИ(NOW > NOW-1; IMPORTJSON("https://data.gateapi.io/api2/1/ticker/btc_usdt";"baseVolume"))

Works once and does not recalculate further. I took a walk, I realized that the point is most likely that the Google table somehow hashes this link and does not recalculate the table because it thinks that the value is the same.

I tried to make the "update" button by clicking on which I wrote this in the scripts:

SpreadsheetApp.flush();

The same thing, it works once and then everything, the data is not updated. Pologaju that business too in hashing of the formula.

I could not solve this issue myself, I had to turn to you - the world mind) Please help me, how can this be implemented. Thank you all in advance ;)

Answer 1

A crutch can be done like this: pass some additional parameter to the formula (cell reference), and update this cell with a trigger. I usually put all such formulas into a single trigger function, and update them en masse.

Answer 2

The "Published" (File > Publish > Publish to Web) spreadsheet sheet is updated every five minutes. (I really don't know if this advice will help or not.)